Added line is this color
Deleted line is this color
- -<div style="margin: 0in 0in 0pt; vertical-align: top; line-height: 140%"> -<span style= -"font-weight: normal; font-size: 16pt; color: black; line-height: 140%">SCOPE_IDENTITY -(Transact-SQL)</span></div> -<div style="margin: 0in 0in 7.5pt; vertical-align: top; line-height: 140%"> -<span style= -"font-weight: normal; font-size: 8.5pt; line-height: 140%">Updated:</span> -<strong><span style="font-size: 8.5pt; color: #a3a3a3; line-height: 140%">1 -February 2007</span></strong></div> -<div style="margin: 0in 0in 7.5pt; vertical-align: top; line-height: 140%"> -<span style="font-weight: normal; font-size: 8.5pt; line-height: 140%">Returns -the last identity value inserted into an identity column in the same scope. A -scope is a module: a stored procedure, trigger, function, or batch. Therefore, -two statements are in the same scope if they are in the same stored procedure, -function, or batch.</span></div> -<div style="margin: 0in 0in 7.5pt; vertical-align: top; line-height: 140%"> -<span style="font-weight: normal; font-size: 8.5pt; line-height: 140%"><a href= -"http://msdn2.microsoft.com/en-us/library/ms177563(SQL.90).aspx"><span style= -"line-height: 140%">Transact-SQL Syntax Conventions</span></a></span></div> -<p style="margin: 0in 0in 0pt; vertical-align: top; line-height: 140%"> -<span style= -"font-weight: normal; font-size: 8.5pt; line-height: 140%"><a href= -"http://msdn2.microsoft.com/en-us/library/ms190315(SQL.90).aspx##"><span style= -"line-height: 140%">Examples</span></a></span></p> -<div style="margin: 0in 0in 0pt; vertical-align: top; line-height: 140%"> -<a name="sectionToggle1" id="sectionToggle1"><span style= -"font-size: 8.5pt; line-height: 140%"><strong>A. Using @@IDENTITY and -SCOPE_IDENTITY with triggers</strong></span></a></div> -<div style="margin: 0in 0in 7.5pt; vertical-align: top; line-height: 140%"> -<span style="font-weight: normal; font-size: 8.5pt; line-height: 140%">The -following example creates two tables,</span> <span style= -"font-weight: normal; font-size: 10pt; line-height: 140%">TZ</span> -<span style="font-weight: normal; font-size: 8.5pt; line-height: 140%">and</span> -<span style= -"font-weight: normal; font-size: 10pt; line-height: 140%">TY</span><span style= -"font-weight: normal; font-size: 8.5pt; line-height: 140%">, and an</span> -<span style= -"font-weight: normal; font-size: 10pt; line-height: 140%">INSERT</span> -<span style="font-weight: normal; font-size: 8.5pt; line-height: 140%">trigger -on</span> <span style= -"font-weight: normal; font-size: 10pt; line-height: 140%">TZ</span><span style= -"font-weight: normal; font-size: 8.5pt; line-height: 140%">. When a row is -inserted to table</span> <span style= -"font-weight: normal; font-size: 10pt; line-height: 140%">TZ</span><span style= -"font-weight: normal; font-size: 8.5pt; line-height: 140%">, the trigger -(</span><span style= -"font-weight: normal; font-size: 10pt; line-height: 140%">Ztrig</span><span style="font-weight: normal; font-size: 8.5pt; line-height: 140%">) -fires and inserts a row in</span> <span style= -"font-weight: normal; font-size: 10pt; line-height: 140%">TY</span><span style= -"font-weight: normal; font-size: 8.5pt; line-height: 140%">.</span></div> -<div style= -"background: #dddddd; margin: 0in 0in 0pt; vertical-align: top; line-height: 140%"> -<span style="font-weight: normal; font-size: 8.5pt; line-height: 140%"><a href= -"javascript:CopyCode('ctl00_LibFrame_ctl12other');"><span style= -"line-height: 140%">Copy Code</span></a></span></div> -<div style= -"background: #dddddd; margin: 0in 11.25pt 0pt 0in; vertical-align: top; line-height: 140%"> -<span style="font-weight: normal; font-size: 10pt; line-height: 140%">USE -tempdb GO</span></div> -<div style= -"background: #dddddd; margin: 0in 11.25pt 0pt 0in; vertical-align: top; line-height: 140%"> -<span style="font-weight: normal; font-size: 10pt; line-height: 140%">CREATE -TABLE TZ ( Z_id int IDENTITY(1,1)PRIMARY KEY, Z_name varchar(20) NOT -NULL)</span></div> -<div style= -"background: #dddddd; margin: 0in 11.25pt 0pt 0in; vertical-align: top; line-height: 140%"> -<span style="font-weight: normal; font-size: 10pt; line-height: 140%">INSERT TZ -VALUES ('Lisa')</span></div> -<div style= -"background: #dddddd; margin: 0in 11.25pt 0pt 0in; vertical-align: top; line-height: 140%"> -<span style="font-weight: normal; font-size: 10pt; line-height: 140%">INSERT TZ -VALUES ('Mike')</span></div> -<div style= -"background: #dddddd; margin: 0in 11.25pt 0pt 0in; vertical-align: top; line-height: 140%"> -<span style="font-weight: normal; font-size: 10pt; line-height: 140%">INSERT TZ -VALUES ('Carla')</span></div> -<div style= -"background: #dddddd; margin: 0in 11.25pt 0pt 0in; vertical-align: top; line-height: 140%"> -<span style="font-weight: normal; font-size: 10pt; line-height: 140%">SELECT * -FROM TZ</span></div> -<div style= -"background: #dddddd; margin: 0in 11.25pt 0pt 0in; vertical-align: top; line-height: 140%"> - </div> -<div style= -"background: #dddddd; margin: 0in 11.25pt 0pt 0in; vertical-align: top; line-height: 140%"> -<span style="font-weight: normal; font-size: 10pt; line-height: 140%">--Result -set: This is how table TZ looks.</span></div> -<div style= -"background: #dddddd; margin: 0in 11.25pt 0pt 0in; vertical-align: top; line-height: 140%"> - </div> -<div style= -"background: #dddddd; margin: 0in 11.25pt 0pt 0in; vertical-align: top; line-height: 140%"> -<span style="font-weight: normal; font-size: 10pt; line-height: 140%">Z_id -Z_name</span></div> -<div style= -"background: #dddddd; margin: 0in 11.25pt 0pt 0in; vertical-align: top; line-height: 140%"> -<span style= -"font-weight: normal; font-size: 10pt; line-height: 140%">-------------</span></div> -<div style= -"background: #dddddd; margin: 0in 11.25pt 0pt 0in; vertical-align: top; line-height: 140%"> -<span style="font-weight: normal; font-size: 10pt; line-height: 140%">1 -Lisa</span></div> -<div style= -"background: #dddddd; margin: 0in 11.25pt 0pt 0in; vertical-align: top; line-height: 140%"> -<span style="font-weight: normal; font-size: 10pt; line-height: 140%">2 -Mike</span></div> -<div style= -"background: #dddddd; margin: 0in 11.25pt 0pt 0in; vertical-align: top; line-height: 140%"> -<span style="font-weight: normal; font-size: 10pt; line-height: 140%">3 -Carla</span></div> -<div style= -"background: #dddddd; margin: 0in 11.25pt 0pt 0in; vertical-align: top; line-height: 140%"> - </div> -<div style= -"background: #dddddd; margin: 0in 11.25pt 0pt 0in; vertical-align: top; line-height: 140%"> -<span style="font-weight: normal; font-size: 10pt; line-height: 140%">CREATE -TABLE TY ( Y_id int IDENTITY(100,5)PRIMARY KEY, Y_name varchar(20) -NULL)</span></div> -<div style= -"background: #dddddd; margin: 0in 11.25pt 0pt 0in; vertical-align: top; line-height: 140%"> - </div> -<div style= -"background: #dddddd; margin: 0in 11.25pt 0pt 0in; vertical-align: top; line-height: 140%"> -<span style="font-weight: normal; font-size: 10pt; line-height: 140%">INSERT TY -(Y_name) VALUES ('boathouse')</span></div> -<div style= -"background: #dddddd; margin: 0in 11.25pt 0pt 0in; vertical-align: top; line-height: 140%"> -<span style="font-weight: normal; font-size: 10pt; line-height: 140%">INSERT TY -(Y_name) VALUES ('rocks')</span></div> -<div style= -"background: #dddddd; margin: 0in 11.25pt 0pt 0in; vertical-align: top; line-height: 140%"> -<span style="font-weight: normal; font-size: 10pt; line-height: 140%">INSERT TY -(Y_name) VALUES ('elevator')</span></div> -<div style= -"background: #dddddd; margin: 0in 11.25pt 0pt 0in; vertical-align: top; line-height: 140%"> - </div> -<div style= -"background: #dddddd; margin: 0in 11.25pt 0pt 0in; vertical-align: top; line-height: 140%"> -<span style="font-weight: normal; font-size: 10pt; line-height: 140%">SELECT * -FROM TY</span></div> -<div style= -"background: #dddddd; margin: 0in 11.25pt 0pt 0in; vertical-align: top; line-height: 140%"> - </div> -<div style= -"background: #dddddd; margin: 0in 11.25pt 0pt 0in; vertical-align: top; line-height: 140%"> -<span style="font-weight: normal; font-size: 10pt; line-height: 140%">--Result -set: This is how TY looks:</span></div> -<div style= -"background: #dddddd; margin: 0in 11.25pt 0pt 0in; vertical-align: top; line-height: 140%"> - </div> -<div style= -"background: #dddddd; margin: 0in 11.25pt 0pt 0in; vertical-align: top; line-height: 140%"> -<span style="font-weight: normal; font-size: 10pt; line-height: 140%">Y_id -Y_name</span></div> -<div style= -"background: #dddddd; margin: 0in 11.25pt 0pt 0in; vertical-align: top; line-height: 140%"> -<span style= -"font-weight: normal; font-size: 10pt; line-height: 140%">---------------</span></div> -<div style= -"background: #dddddd; margin: 0in 11.25pt 0pt 0in; vertical-align: top; line-height: 140%"> -<span style="font-weight: normal; font-size: 10pt; line-height: 140%">100 -boathouse</span></div> -<div style= -"background: #dddddd; margin: 0in 11.25pt 0pt 0in; vertical-align: top; line-height: 140%"> -<span style="font-weight: normal; font-size: 10pt; line-height: 140%">105 -rocks</span></div> -<div style= -"background: #dddddd; margin: 0in 11.25pt 0pt 0in; vertical-align: top; line-height: 140%"> -<span style="font-weight: normal; font-size: 10pt; line-height: 140%">110 -elevator</span></div> -<div style= -"background: #dddddd; margin: 0in 11.25pt 0pt 0in; vertical-align: top; line-height: 140%"> - </div> -<div style= -"background: #dddddd; margin: 0in 11.25pt 0pt 0in; vertical-align: top; line-height: 140%"> -<span style="font-weight: normal; font-size: 10pt; line-height: 140%">/*Create -the trigger that inserts a row in table TY when a row is inserted in table -TZ*/</span></div> -<div style= -"background: #dddddd; margin: 0in 11.25pt 0pt 0in; vertical-align: top; line-height: 140%"> - </div> -<div style= -"background: #dddddd; margin: 0in 11.25pt 0pt 0in; vertical-align: top; line-height: 140%"> -<span style="font-weight: normal; font-size: 10pt; line-height: 140%">CREATE -TRIGGER Ztrig ON TZ FOR INSERT</span></div> -<div style= -"background: #dddddd; margin: 0in 11.25pt 0pt 0in; vertical-align: top; line-height: 140%"> -<span style= -"font-weight: normal; font-size: 10pt; line-height: 140%">AS</span></div> -<div style= -"background: #dddddd; margin: 0in 11.25pt 0pt 0in; vertical-align: top; line-height: 140%"> -<span style= -"font-weight: normal; font-size: 10pt; line-height: 140%">BEGIN</span></div> -<div style= -"background: #dddddd; margin: 0in 11.25pt 0pt 0in; vertical-align: top; line-height: 140%"> -<span style="font-weight: normal; font-size: 10pt; line-height: 140%"> -INSERT TY VALUES ('')</span></div> -<div style= -"background: #dddddd; margin: 0in 11.25pt 0pt 0in; vertical-align: top; line-height: 140%"> -<span style= -"font-weight: normal; font-size: 10pt; line-height: 140%">END</span></div> -<div style= -"background: #dddddd; margin: 0in 11.25pt 0pt 0in; vertical-align: top; line-height: 140%"> - </div> -<div style= -"background: #dddddd; margin: 0in 11.25pt 0pt 0in; vertical-align: top; line-height: 140%"> -<span style="font-weight: normal; font-size: 10pt; line-height: 140%">/*FIRE -the trigger and determine what identity values you obtain with the @@IDENTITY -and SCOPE_IDENTITY functions.*/</span></div> -<div style= -"background: #dddddd; margin: 0in 11.25pt 0pt 0in; vertical-align: top; line-height: 140%"> - </div> -<div style= -"background: #dddddd; margin: 0in 11.25pt 0pt 0in; vertical-align: top; line-height: 140%"> -<span style="font-weight: normal; font-size: 10pt; line-height: 140%">INSERT TZ -VALUES ('Rosalie') SELECT SCOPE_IDENTITY() AS [SCOPE_IDENTITY]</span></div> -<div style= -"background: #dddddd; margin: 0in 11.25pt 0pt 0in; vertical-align: top; line-height: 140%"> -<span style= -"font-weight: normal; font-size: 10pt; line-height: 140%">GO</span></div> -<div style= -"background: #dddddd; margin: 0in 11.25pt 0pt 0in; vertical-align: top; line-height: 140%"> -<span style="font-weight: normal; font-size: 10pt; line-height: 140%">SELECT -@@IDENTITY AS [@@IDENTITY]</span></div> -<div style= -"background: #dddddd; margin: 0in 11.25pt 0pt 0in; vertical-align: top; line-height: 140%"> -<span style= -"font-weight: normal; font-size: 10pt; line-height: 140%">GO</span></div> -<div style="margin: 0in 0in 7.5pt; vertical-align: top; line-height: 140%"> -<span style="font-weight: normal; font-size: 8.5pt; line-height: 140%">Here is -the result set.</span></div> -<div style= -"background: #dddddd; margin: 0in 0in 0pt; vertical-align: top; line-height: 140%"> -<span style="font-weight: normal; font-size: 8.5pt; line-height: 140%"><a href= -"javascript:CopyCode('ctl00_LibFrame_ctl13other');"><span style= -"line-height: 140%">Copy Code</span></a></span></div> -<div style= -"background: #dddddd; margin: 0in 11.25pt 0pt 0in; vertical-align: top; line-height: 140%"> -<span style= -"font-weight: normal; font-size: 10pt; line-height: 140%">SCOPE_IDENTITY -4</span></div> -<div style= -"background: #dddddd; margin: 0in 11.25pt 0pt 0in; vertical-align: top; line-height: 140%"> - </div> -<div style= -"background: #dddddd; margin: 0in 11.25pt 0pt 0in; vertical-align: top; line-height: 140%"> -<span style= -"font-weight: normal; font-size: 10pt; line-height: 140%">/*SCOPE_IDENTITY -returned the last identity value in the same scope. This was the insert on -table TZ.*/</span></div> -<div style= -"background: #dddddd; margin: 0in 11.25pt 0pt 0in; vertical-align: top; line-height: 140%"> - </div> -<div style= -"background: #dddddd; margin: 0in 11.25pt 0pt 0in; vertical-align: top; line-height: 140%"> -<span style= -"font-weight: normal; font-size: 10pt; line-height: 140%">@@IDENTITY -115</span></div> -<div style= -"background: #dddddd; margin: 0in 11.25pt 0pt 0in; vertical-align: top; line-height: 140%"> - </div> -<div style= -"background: #dddddd; margin: 0in 11.25pt 0pt 0in; vertical-align: top; line-height: 140%"> -<span style= -"font-weight: normal; font-size: 10pt; line-height: 140%">/*@@IDENTITY returned -the last identity value inserted to TY by the trigger. This fired because of an -earlier insert on TZ.*/</span></div> -<div style="margin: 0in 0in 0pt; vertical-align: top; line-height: 140%"> -<span style="font-size: 8.5pt; line-height: 140%"><strong>B. Using @@IDENTITY -and SCOPE_IDENTITY() with replication</strong></span></div> -<div style="margin: 0in 0in 7.5pt; vertical-align: top; line-height: 140%"> -<span style="font-weight: normal; font-size: 8.5pt; line-height: 140%">The -following examples shows how to use @@IDENTITY and SCOPE_IDENTITY() for inserts -in a database that is published for merge replication. Both tables in the -examples are in the</span> <span style= -"font-size: 8.5pt; line-height: 140%"><strong>AdventureWorks</strong></span> -<span style="font-weight: normal; font-size: 8.5pt; line-height: 140%">sample -database:</span> <span style= -"font-weight: normal; font-size: 10pt; line-height: 140%">Person.ContactType</span> -<span style="font-weight: normal; font-size: 8.5pt; line-height: 140%">is not -published, and</span> <span style= -"font-weight: normal; font-size: 10pt; line-height: 140%">Sales.Customer</span> -<span style="font-weight: normal; font-size: 8.5pt; line-height: 140%">is -published. Merge replication adds triggers to tables that are published. -Therefore,</span> <span style= -"font-weight: normal; font-size: 10pt; line-height: 140%">@@IDENTITY</span> -<span style="font-weight: normal; font-size: 8.5pt; line-height: 140%">can -return the value from the insert into a replication system table instead of the -insert into a user table.</span></div> -<div style="margin: 0in 0in 7.5pt; vertical-align: top; line-height: 140%"> -<span style= -"font-weight: normal; font-size: 8.5pt; line-height: 140%">The</span> -<span style= -"font-weight: normal; font-size: 10pt; line-height: 140%">Person.ContactType</span> -<span style="font-weight: normal; font-size: 8.5pt; line-height: 140%">table -has a maximum identity value of 20. If you insert a row into the table,</span> -<span style= -"font-weight: normal; font-size: 10pt; line-height: 140%">@@IDENTITY</span> -<span style= -"font-weight: normal; font-size: 8.5pt; line-height: 140%">and</span> -<span style= -"font-weight: normal; font-size: 10pt; line-height: 140%">SCOPE_IDENTITY()</span> -<span style="font-weight: normal; font-size: 8.5pt; line-height: 140%">return -the same value.</span></div> -<div style= -"background: #dddddd; margin: 0in 0in 0pt; vertical-align: top; line-height: 140%"> -<span style="font-weight: normal; font-size: 8.5pt; line-height: 140%"><a href= -"javascript:CopyCode('ctl00_LibFrame_ctl14other');"><span style= -"line-height: 140%">Copy Code</span></a></span></div> -<div style= -"background: #dddddd; margin: 0in 11.25pt 0pt 0in; vertical-align: top; line-height: 140%"> -<span style="font-weight: normal; font-size: 10pt; line-height: 140%">USE -AdventureWorks; GO INSERT INTO Person.ContactType ([Name]) VALUES ('Assistant -to the Manager') GO SELECT SCOPE_IDENTITY() AS [SCOPE_IDENTITY] GO SELECT -@@IDENTITY AS [@@IDENTITY] GO</span></div> -<div style="margin: 0in 0in 7.5pt; vertical-align: top; line-height: 140%"> -<span style="font-weight: normal; font-size: 8.5pt; line-height: 140%">Here is -the result set.</span></div> -<div style= -"background: #dddddd; margin: 0in 0in 0pt; vertical-align: top; line-height: 140%"> -<span style="font-weight: normal; font-size: 8.5pt; line-height: 140%"><a href= -"javascript:CopyCode('ctl00_LibFrame_ctl15other');"><span style= -"line-height: 140%">Copy Code</span></a></span></div> -<div style= -"background: #dddddd; margin: 0in 11.25pt 0pt 0in; vertical-align: top; line-height: 140%"> -<span style= -"font-weight: normal; font-size: 10pt; line-height: 140%">SCOPE_IDENTITY 21 -@@IDENTITY 21</span></div> -<div style="margin: 0in 0in 7.5pt; vertical-align: top; line-height: 140%"> -<span style= -"font-weight: normal; font-size: 8.5pt; line-height: 140%">The</span> -<span style= -"font-weight: normal; font-size: 10pt; line-height: 140%">Sales.Customer</span> -<span style="font-weight: normal; font-size: 8.5pt; line-height: 140%">table -has a maximum identity value of 29483. If you insert a row into the -table,</span> <span style= -"font-weight: normal; font-size: 10pt; line-height: 140%">@@IDENTITY</span> -<span style= -"font-weight: normal; font-size: 8.5pt; line-height: 140%">and</span> -<span style= -"font-weight: normal; font-size: 10pt; line-height: 140%">SCOPE_IDENTITY()</span> -<span style="font-weight: normal; font-size: 8.5pt; line-height: 140%">return -different values.</span> <span style= -"font-weight: normal; font-size: 10pt; line-height: 140%">SCOPE_IDENTITY()</span> -<span style="font-weight: normal; font-size: 8.5pt; line-height: 140%">returns -the value from the insert into the user table, whereas</span> <span style= -"font-weight: normal; font-size: 10pt; line-height: 140%">@@IDENTITY</span> -<span style="font-weight: normal; font-size: 8.5pt; line-height: 140%">returns -the value from the insert into the replication system table. Use</span> -<span style= -"font-weight: normal; font-size: 10pt; line-height: 140%">SCOPE_IDENTITY()</span> -<span style="font-weight: normal; font-size: 8.5pt; line-height: 140%">for -applications that require access to the inserted identity value.</span></div> -<div style= -"background: #dddddd; margin: 0in 0in 0pt; vertical-align: top; line-height: 140%"> -<span style="font-weight: normal; font-size: 8.5pt; line-height: 140%"><a href= -"javascript:CopyCode('ctl00_LibFrame_ctl16other');"><span style= -"line-height: 140%">Copy Code</span></a></span></div> -<div style= -"background: #dddddd; margin: 0in 11.25pt 0pt 0in; vertical-align: top; line-height: 140%"> -<span style="font-weight: normal; font-size: 10pt; line-height: 140%">INSERT -INTO Sales.Customer ([TerritoryID],[CustomerType]) VALUES (8,'S') GO SELECT -SCOPE_IDENTITY() AS [SCOPE_IDENTITY] GO SELECT @@IDENTITY AS [@@IDENTITY] -GO</span></div> -<div style="margin: 0in 0in 7.5pt; vertical-align: top; line-height: 140%"> -<span style="font-weight: normal; font-size: 8.5pt; line-height: 140%">Here is -the result set.</span></div> -<div style= -"background: #dddddd; margin: 0in 0in 0pt; vertical-align: top; line-height: 140%"> -<span style="font-weight: normal; font-size: 8.5pt; line-height: 140%"><a href= -"javascript:CopyCode('ctl00_LibFrame_ctl17other');"><span style= -"line-height: 140%">Copy Code</span></a></span></div> -<div style= -"background: #dddddd; margin: 0in 11.25pt 0pt 0in; vertical-align: top; line-height: 140%"> -<span style= -"font-weight: normal; font-size: 10pt; line-height: 140%">SCOPE_IDENTITY 24984 -@@IDENTITY 89</span></div> -<div style="margin: 0in 0in 0pt"> </div> -<table style="width: 100%" cellspacing="0" cellpadding="0" width="100%" border= -"0"> -<tbody> -<tr> -<td style= -"border-right: #ece9d8; padding-right: 0in; border-top: #ece9d8; padding-left: 0in; background: #fda102; padding-bottom: 0in; border-left: #ece9d8; padding-top: 0in; border-bottom: #ece9d8"> -<div style="margin: 0in 0in 0pt"><span style= -"font-size: 15pt; color: white"><strong><font size="3">Know your -Identity</font></strong></span></div> -</td> -</tr> -<tr style="height: 3.75pt"> -<td style= -"border-right: #ece9d8; padding-right: 0in; border-top: #ece9d8; padding-left: 0in; background: blue; padding-bottom: 0in; border-left: #ece9d8; padding-top: 0in; border-bottom: #ece9d8; height: 3.75pt"> -<div style="margin: 0in 0in 0pt"> </div> -</td> -</tr> -<tr> -<td style= -"border-right: #ece9d8; padding-right: 0in; border-top: #ece9d8; padding-left: 0in; padding-bottom: 0in; border-left: #ece9d8; width: 100%; padding-top: 0in; border-bottom: #ece9d8; background-color: transparent" -width="100%"> -<div style="margin: 0in 0in 0pt"> </div> -<div style="margin: 0in 0in 0pt"><font size="3"><span style= -"font-weight: normal; font-size: 8pt">Often we create tables that have identity -columns. An identity column is a special column in SQL Server that can -automatically take on values when an insert operation is done on the table. -What if you want to find out whether a table contains an identity column?? SQL -Server stores this information in the</span><span style= -"font-size: 8pt"><strong>syscolumns</strong></span><span style= -"font-weight: normal; font-size: 8pt">table, but you can access this -information in many ways. In this article, we will see the various ways to -access information about whether a table has an identity -column.</span></font></div> -<div style="margin: 0in 0in 0pt"> </div> -<div style="margin: 0in 0in 0pt"><span style= -"font-weight: normal; font-size: 8pt"><font size="3">Before we start off with -the methods, let's first create a table that has an identity column, like -this:</font></span></div> -<div style="background: khaki; margin: 0in 0in 0pt"> </div> -<div style="background: khaki; margin: 0in 0in 0pt"><span style= -"font-weight: normal; font-size: 10pt; color: black"><font size="3">CREATE -TABLE samp_test</font></span></div> -<div style="background: khaki; margin: 0in 0in 0pt"><span style= -"font-weight: normal; font-size: 10pt; color: black"><font size= -"3">(</font></span></div> -<div style="background: khaki; margin: 0in 0in 0pt"><span style= -"font-weight: normal; font-size: 10pt; color: black"><font size="3"> -colA INT IDENTITY,</font></span></div> -<div style="background: khaki; margin: 0in 0in 0pt"><span style= -"font-weight: normal; font-size: 10pt; color: black"><font size="3"> -colB VARCHAR(10)</font></span></div> -<div style="background: khaki; margin: 0in 0in 0pt"><span style= -"font-weight: normal; font-size: 10pt; color: black"><font size= -"3">)</font></span></div> -<div style="background: khaki; margin: 0in 0in 0pt"> </div> -<div style="margin: 0in 0in 0pt"> </div> -<div style="margin: 0in 0in 0pt"><strong><u><span style= -"font-size: 8pt"><font size="3">Method A</font></span></u></strong></div> -<div style="margin: 0in 0in 0pt"> </div> -<div style="margin: 0in 0in 0pt"><font size="3"><span style= -"font-weight: normal; font-size: 8pt">In the first method, we will fetch this -information from the</span><span style= -"font-size: 8pt"><strong>syscolumns</strong></span><span style= -"font-weight: normal; font-size: 8pt">table. This table contains a column -called</span><span style= -"font-size: 8pt"><strong>status</strong></span><span style= -"font-weight: normal; font-size: 8pt">that contains various values. If the -value in this column contains hexadecimal</span><span style= -"font-size: 8pt"><strong>80</strong></span><span style= -"font-weight: normal; font-size: 8pt">, then it means that the corresponding -column contains an identity column. Here is how we can check -this:</span></font></div> -<div style="background: khaki; margin: 0in 0in 0pt"> </div> -<div style="background: khaki; margin: 0in 0in 0pt"><span style= -"font-weight: normal; font-size: 10pt; color: black"><font size="3">IF (EXISTS -(SELECT name FROM syscolumns WHERE</font></span></div> -<div style="background: khaki; margin: 0in 0in 0pt"><span style= -"font-weight: normal; font-size: 10pt; color: black"><font size="3"> -OBJECT_NAME(id) = 'samp_test' AND status = 0x80))</font></span></div> -<div style="background: khaki; margin: 0in 0in 0pt"><span style= -"font-weight: normal; font-size: 10pt; color: black"><font size="3"> -PRINT 'Table has an identity column'</font></span></div> -<div style="background: khaki; margin: 0in 0in 0pt"><span style= -"font-weight: normal; font-size: 10pt; color: black"><font size= -"3">ELSE</font></span></div> -<div style="background: khaki; margin: 0in 0in 0pt"><span style= -"font-weight: normal; font-size: 10pt; color: black"><font size="3"> -PRINT 'Table does not have an identity column'</font></span></div> -<div style="background: khaki; margin: 0in 0in 0pt"> </div> -<div style="margin: 0in 0in 0pt"> </div> -<div style="margin: 0in 0in 0pt"><strong><u><span style= -"font-size: 8pt"><font size="3">Method B</font></span></u></strong></div> -<div style="margin: 0in 0in 0pt"> </div> -<div style="margin: 0in 0in 0pt"><font size="3"><span style= -"font-weight: normal; font-size: 8pt">In the second method, we will see whether -a table has an identity column using the</span><span style= -"font-size: 8pt"><strong>COLUMNPROPERTY</strong></span><span style= -"font-weight: normal; font-size: 8pt">function. This function takes an -argument</span><span style= -"font-size: 8pt"><strong>IsIdentity</strong></span><span style= -"font-weight: normal; font-size: 8pt">that indicates whether the column is an -identity column. Here is how we can do the check using this -function:</span></font></div> -<div style="background: khaki; margin: 0in 0in 0pt"> </div> -<div style="background: khaki; margin: 0in 0in 0pt"><span style= -"font-weight: normal; font-size: 10pt; color: black"><font size="3">IF (EXISTS -(SELECT myTemp.IsIdentity FROM</font></span></div> -<div style="background: khaki; margin: 0in 0in 0pt"><span style= -"font-weight: normal; font-size: 10pt; color: black"><font size="3"> -(SELECT IsIdentity=COLUMNPROPERTY(id, name, 'IsIdentity')</font></span></div> -<div style="background: khaki; margin: 0in 0in 0pt"><span style= -"font-weight: normal; font-size: 10pt; color: black"><font size= -"3"> FROM syscolumns WHERE OBJECT_NAME(id) = 'samp_test') AS -myTemp</font></span></div> -<div style="background: khaki; margin: 0in 0in 0pt"><span style= -"font-weight: normal; font-size: 10pt; color: black"><font size="3"> -WHERE myTemp.IsIdentity = 1))</font></span></div> -<div style="background: khaki; margin: 0in 0in 0pt"><span style= -"font-weight: normal; font-size: 10pt; color: black"><font size="3"> -PRINT 'Table has an identity column'</font></span></div> -<div style="background: khaki; margin: 0in 0in 0pt"><span style= -"font-weight: normal; font-size: 10pt; color: black"><font size= -"3">ELSE</font></span></div> -<div style="background: khaki; margin: 0in 0in 0pt"><span style= -"font-weight: normal; font-size: 10pt; color: black"><font size="3"> -PRINT 'Table does not have an identity column'</font></span></div> -<div style="background: khaki; margin: 0in 0in 0pt"> </div> -<div style="margin: 0in 0in 0pt"> </div> -<div style="margin: 0in 0in 0pt"><font size="3"><span style= -"font-weight: normal; font-size: 8pt">This method is slightly convoluted since -the COLUMNPROPERTY function takes the name of the column as the parameter. -Thus, if we want to make a generic sort of a call, we need to iterate through -each column of a table using the</span><span style= -"font-size: 8pt"><strong>syscolumns</strong></span><span style= -"font-weight: normal; font-size: 8pt">table and then make a call to the -COLUMNPROPERTY function. Finally we then run an EXISTS check over this derived -table to check for identity.</span></font></div> -<div style="margin: 0in 0in 0pt"> </div> -<div style="margin: 0in 0in 0pt"><strong><u><span style= -"font-size: 8pt"><font size="3">Method C</font></span></u></strong></div> -<div style="margin: 0in 0in 0pt"> </div> -<div style="margin: 0in 0in 0pt"><font size="3"><span style= -"font-weight: normal; font-size: 8pt">In the final method, we will use -the</span><span style= -"font-size: 8pt"><strong>OBJECTPROPERTY</strong></span><span style= -"font-weight: normal; font-size: 8pt">function to check whether a table has an -identity column. Here is how we can do the check using this -function:</span></font></div> -<div style="background: khaki; margin: 0in 0in 0pt"> </div> -<div style="background: khaki; margin: 0in 0in 0pt"><span style= -"font-weight: normal; font-size: 10pt; color: black"><font size="3">IF -(OBJECTPROPERTY(OBJECT_ID('samp_test'), 'TableHasIdentity') = -1)</font></span></div> -<div style="background: khaki; margin: 0in 0in 0pt"><span style= -"font-weight: normal; font-size: 10pt; color: black"><font size="3"> -PRINT 'Table has an identity column'</font></span></div> -<div style="background: khaki; margin: 0in 0in 0pt"><span style= -"font-weight: normal; font-size: 10pt; color: black"><font size= -"3">ELSE</font></span></div> -<div style="background: khaki; margin: 0in 0in 0pt"><span style= -"font-weight: normal; font-size: 10pt; color: black"><font size="3"> -PRINT 'Table does not have an identity column'</font></span></div> -<div style="background: khaki; margin: 0in 0in 0pt"> </div> -<div style="margin: 0in 0in 0pt"> </div> -<div style="margin: 0in 0in 0pt"><font size="3"><span style= -"font-weight: normal; font-size: 8pt">This is the easiest of the lot. The -OBJECTPROPERTY function takes as parameter the name of the object to check (a -table in this case) and then the name of the property. We use -the</span><span style= -"font-size: 8pt"><strong>OBJECT_ID</strong></span><span style= -"font-weight: normal; font-size: 8pt">function to get the ID of the object. We -then use the</span><span style= -"font-size: 8pt"><strong>TableHasIdentity</strong></span><span style= -"font-weight: normal; font-size: 8pt">property to check if the table has an -identity column. One difference between this method and the others is that this -method does not give the name of the column which is the identity column, which -is possible using the other two methods.</span></font></div> -</td> -</tr> -</tbody> -</table> - +Hi webmaster!