SQL Server/T-SQL Tutorial/Sequence Indentity/Identity
Содержание
- 1 Altering the Table to Add an Identity Column
- 2 A table could also be created so that the seed value was negative and the increment was positive:
- 3 Cannot insert explicit value for identity column in table "T" when IDENTITY_INSERT is set to OFF.
- 4 Check @@IDENTITY value
- 5 Create table with IDENTITY column
- 6 Generating IDENTITY values
- 7 IDENTITY(
- 8 Identity: A numerical value automatically assigned to a row any time a new row is added.
- 9 Identity column
- 10 IDENTITY/NOT NULL/PRIMARY KEY
- 11 Query a view with four table join
- 12 Query that Returns Result Row Numbers, Starting with 1 and Incrementing by 3
- 13 Retrieving the Maximum Value of key_col from MyTable
- 14 Saving the @@IDENTITY Value in a Variable
- 15 SELECT Statement and IDENTITY Property
- 16 SQL Server provides the @@IDENTITY global variable to hold that value.
- 17 SQL Server will choose the highest number as its current seed for a positive increment value or the lowest for a negative increment value.
- 18 Storing the Results in a Temporary Table Using the IDENTITY() Function
- 19 Supported data types for the IDENTITY property are TinyInt, SmallInt Int, BigInt, Decimal, and Numeric.
- 20 Syntax for Using the IDENTITY() Function
- 21 The IDENTITY property must be temporarily turned off when inserting a specific value.
- 22 Using the IDENTITY Property and the IDENTITY() Function
Altering the Table to Add an Identity Column
<source lang="sql">
7> CREATE TABLE Dupes( 8> ID int NOT NULL, 9> Txt char (10) NOT NULL 10> ) 11> GO 1> INSERT Dupes (ID, Txt) VALUES (1, "x") 2> INSERT Dupes (ID, Txt) VALUES (1, "a") 3> INSERT Dupes (ID, Txt) VALUES (1, "x") 4> INSERT Dupes (ID, Txt) VALUES (1, "x") 5> INSERT Dupes (ID, Txt) VALUES (2, "b") 6> INSERT Dupes (ID, Txt) VALUES (2, "x") 7> INSERT Dupes (ID, Txt) VALUES (2, "b") 8> INSERT Dupes (ID, Txt) VALUES (3, "c") 9> GO (1 rows affected) (1 rows affected) (1 rows affected) (1 rows affected) (1 rows affected) (1 rows affected) (1 rows affected) (1 rows affected) 1> 2> 3> ALTER TABLE Dupes 4> ADD 5> Ident int NOT NULL IDENTITY (1, 1) 6> GO 1> 2> 3> --Deleting Duplicates with a Correlated Subquery 4> DELETE D1 5> FROM 6> Dupes AS D1 7> WHERE 8> D1.Ident > 9> ( 10> SELECT 11> MIN (D2.Ident) 12> FROM 13> Dupes AS D2 14> WHERE 15> D2.ID = D1.ID 16> AND 17> D2.Txt = D1.Txt 18> ) 19> 20> 21> drop table dupes; 22> GO (3 rows affected) 1></source>
A table could also be created so that the seed value was negative and the increment was positive:
<source lang="sql">
4> CREATE TABLE MyTable (MyID Int IDENTITY(-1000000, 100) NOT NULL 5> ,MyDescription NVarChar(50) NOT NULL) 6> GO 1> 2> 3> drop table MyTable; 4> GO 1> By default, once a column has been assigned the IDENTITY property, SQL Server does not allow explicit values to be inserted into it.</source>
Cannot insert explicit value for identity column in table "T" when IDENTITY_INSERT is set to OFF.
<source lang="sql">
4> CREATE TABLE T ( 5> int1 int IDENTITY PRIMARY KEY, 6> bit1 bit NOT NULL DEFAULT 0 7> ) 8> GO 1> 2> INSERT T (bit1) VALUES (1) 3> INSERT T (bit1) VALUES (0) 4> INSERT T DEFAULT VALUES 5> INSERT T (int1, bit1) VALUES (4,1) 6> GO (1 rows affected) (1 rows affected) (1 rows affected) Msg 544, Level 16, State 1, Server J\SQLEXPRESS, Line 5 Cannot insert explicit value for identity column in table "T" when IDENTITY_INSERT is set to OFF. 1> 2> select * from t; 3> GO int1 bit1
----
1 1
2 0
3 0
(3 rows affected) 1> 2> drop table t; 3> GO</source>
Check @@IDENTITY value
<source lang="sql">
3> 4> CREATE TABLE OrderDetails ( 5> OrderID int NOT NULL , 6> ProductID int NOT NULL , 7> UnitPrice money NOT NULL DEFAULT (0), 8> Quantity smallint NOT NULL DEFAULT (1), 9> Discount real NOT NULL DEFAULT (0) 10> ) 11> GO 1> INSERT OrderDetails VALUES(10248,11,14,12,0) 2> INSERT OrderDetails VALUES(10248,42,9.8,10,0) 3> INSERT OrderDetails VALUES(10248,72,34.8,5,0) 4> INSERT OrderDetails VALUES(10249,14,18.6,9,0) 5> INSERT OrderDetails VALUES(10249,51,42.4,40,0) 6> INSERT OrderDetails VALUES(10250,41,7.7,10,0) 7> INSERT OrderDetails VALUES(10250,51,42.4,35,0.15) 8> INSERT OrderDetails VALUES(10250,65,16.8,15,0.15) 9> INSERT OrderDetails VALUES(10251,22,16.8,6,0.05) 10> INSERT OrderDetails VALUES(10251,57,15.6,15,0.05) 11> go (1 rows affected) (1 rows affected) (1 rows affected) (1 rows affected) (1 rows affected) (1 rows affected) (1 rows affected) (1 rows affected) (1 rows affected) (1 rows affected) 1> 2> 3> CREATE TABLE Orders ( 4> OrderID int IDENTITY (1, 1) NOT NULL , 5> CustomerID nchar (5) NULL , 6> EmployeeID int NULL , 7> OrderDate datetime NULL , 8> RequiredDate datetime NULL , 9> ShippedDate datetime NULL , 10> ShipVia int NULL , 11> Freight money NULL DEFAULT (0), 12> ShipName nvarchar (40) NULL , 13> ShipAddress nvarchar (60) NULL , 14> ShipCity nvarchar (15) NULL , 15> ShipRegion nvarchar (15) NULL , 16> ShipPostalCode nvarchar (10) NULL , 17> ShipCountry nvarchar (15) NULL 18> ) 19> GO 1> 2> 3> DECLARE @Ident int 4> 5> INSERT INTO Orders 6> (CustomerID,OrderDate) 7> VALUES 8> ("ALFKI", DATEADD(day,-1,GETDATE())) 9> 10> SELECT @Ident = @@IDENTITY 11> 12> INSERT INTO OrderDetails 13> (OrderID, ProductID, UnitPrice, Quantity) 14> VALUES 15> (@Ident, 1, 50, 25) 16> 17> SELECT "The OrderID of the INSERTed row is " + CONVERT(varchar(8),@Ident) 18> GO (1 rows affected) (1 rows affected)
The OrderID of the INSERTed row is 1 (1 rows affected) 1> 2> drop table orders; 3> drop table OrderDetails; 4> GO</source>
Create table with IDENTITY column
<source lang="sql">
The basic syntax for creating a table is as follows: CREATE TABLE [database_name].[owner].table_name
(column_name data_type [length] [IDENTITY(seed, increment)] [NOT NULL])
9> CREATE TABLE Groups ( 10> Id int IDENTITY (1, 1) NOT NULL 11> ) 12> GO 1> 2> drop table Groups 3> GO</source>
Generating IDENTITY values
<source lang="sql">
4> IF EXISTS (SELECT name 5> FROM sysobjects 6> WHERE name = N"Players" 7> AND type = "U" 8> ) 9> DROP TABLE Players 10> GO 1> 2> create table Players ( 3> Id int IDENTITY (1, 1) NOT NULL 4> ) 5> GO 1> 2> drop table Players 3> GO 1></source>
IDENTITY(
<source lang="sql">
2> CREATE TABLE customer 3> ( 4> cust_id smallint IDENTITY(100, 20) NOT NULL, 5> cust_name varchar(50) NOT NULL 6> ) 7> GO 1> 2> SELECT IDENT_CURRENT("customer") 3> GO
100
(1 rows affected) 1> 2> drop table customer; 3> GO 1></source>
Identity: A numerical value automatically assigned to a row any time a new row is added.
<source lang="sql">
4> 5> CREATE TABLE MyTable ( 6> MyID Int IDENTITY(1, 1) NOT NULL 7> , MyDescription nVarChar(50) NOT NULL) 8> GO 1> 2> drop table MyTable; 3> GO 1> The actual syntax for the IDENTITY property is as follows: IDENTITY [ (seed , increment ) ] 4></source>
Identity column
<source lang="sql">
3> CREATE TABLE customer 4> ( 5> cust_id int IDENTITY NOT NULL, 6> cust_name varchar(30) NOT NULL, 7> CONSTRAINT customer_PK PRIMARY KEY (cust_id) 8> ) 9> GO 1> 2> EXEC sp_helpconstraint customer 3> GO Object Name
customer
constraint_type constraint_name
delete_action update_action status_enabled status_for_replication constraint_keys
-----------------------------------------------------
------------- ------------- -------------- ---------------------- ----------------------------------------------------------
PRIMARY KEY (clustered) customer_PK
(n/a) (n/a) (n/a) (n/a) cust_id
No foreign keys reference table "customer", or you do not have permissions on referencing tables.
1> drop table customer;
2> GO</source>
IDENTITY/NOT NULL/PRIMARY KEY
<source lang="sql">
12> CREATE TABLE customer 13> ( 14> cust_id int IDENTITY NOT NULL PRIMARY KEY, 15> cust_name varchar(30) NOT NULL 16> ) 17> GO 1> 2> EXEC sp_helpconstraint customer 3> GO Object Name
customer
constraint_type constraint_name
delete_action update_action status_enabled status_for_replication constraint_keys
-----------------------------------------------------
------------- ------------- -------------- ---------------------- ----------------------------------------------------------
PRIMARY KEY (clustered) PK__customer__383021B8
(n/a) (n/a) (n/a) (n/a) cust_id
No foreign keys reference table "customer", or you do not have permissions on referencing tables.
1>
2> drop table customer;
3> GO</source>
Query a view with four table join
<source lang="sql">
4> CREATE TABLE Customers ( 5> CustomerID nchar (5) NOT NULL , 6> CompanyName nvarchar (40) NOT NULL , 7> ContactName nvarchar (30) NULL , 8> ContactTitle nvarchar (30) NULL , 9> Address nvarchar (60) NULL , 10> City nvarchar (15) NULL , 11> Region nvarchar (15) NULL , 12> PostalCode nvarchar (10) NULL , 13> Country nvarchar (15) NULL , 14> Phone nvarchar (24) NULL , 15> Fax nvarchar (24) NULL 16> ) 17> GO 1> 2> INSERT Customers VALUES("1","A","Maria", "Sales", "Str. 57", "Berlin" ,NULL,"12209", "Germany","111-1111111","111-1111111") 3> INSERT Customers VALUES("2","M","Joe", "Owner", "Ave. 231","Vancouver" ,NULL,"05023", "Mexico", "(222) 222-3332",NULL) 4> INSERT Customers VALUES("3","H","Thomas", "Sales", "Sq. 111","London" ,NULL,"1D00P", "UK", "(444) 444-4444","(444) 444-4444") 5> INSERT Customers VALUES("4","B","Berg", "Order", "Blv 8","Toronto" ,NULL,"00222", "Sweden", "4444-55 55 65","5555-55 55 55") 6> INSERT Customers VALUES("5","S","Moos", "Sales", "Fort 57","New York" ,NULL,"68306", "Germany","6666-66666","6666-77777") 7> INSERT Customers VALUES("6","F","Cite", "Manager","24 ","Dalles" ,NULL,"67000", "France", "88.60.15.31","88.60.15.32") 8> INSERT Customers VALUES("7","C","Sommer", "Owner", "Araq, 67","Paris" ,NULL,"28023", "Spain", "(91) 555 22 82","(91) 555 91 99") 9> INSERT Customers VALUES("8","P","Leb", "Owner", "12 ","Beijing" ,NULL,"13008", "France", "91.24.45.40","91.24.45.41") 10> INSERT Customers VALUES("9","D","Elizabeth","Manager","23 Blvd.","Tsawassen","BC", "T2F8M4","Canada", "(604) 555-4729","(604) 555-3745") 11> go (1 rows affected) (1 rows affected) (1 rows affected) (1 rows affected) (1 rows affected) (1 rows affected) (1 rows affected) (1 rows affected) (1 rows affected) 1> 2> 3> CREATE TABLE Orders ( 4> OrderID int IDENTITY (1, 1) NOT NULL , 5> CustomerID nchar (5) NULL , 6> EmployeeID int NULL , 7> OrderDate datetime NULL , 8> RequiredDate datetime NULL , 9> ShippedDate datetime NULL , 10> ShipVia int NULL , 11> Freight money NULL DEFAULT (0), 12> ShipName nvarchar (40) NULL , 13> ShipAddress nvarchar (60) NULL , 14> ShipCity nvarchar (15) NULL , 15> ShipRegion nvarchar (15) NULL , 16> ShipPostalCode nvarchar (10) NULL , 17> ShipCountry nvarchar (15) NULL 18> ) 19> GO 1> 2> CREATE TABLE Products ( 3> ProductID int NOT NULL , 4> ProductName nvarchar (40) NOT NULL , 5> SupplierID int NULL , 6> CategoryID int NULL , 7> QuantityPerUnit nvarchar (20) NULL , 8> UnitPrice money NULL, 9> UnitsInStock smallint NULL, 10> UnitsOnOrder smallint NULL, 11> ReorderLevel smallint NULL, 12> Discontinued bit NOT NULL 13> ) 14> GO 1> INSERT Products VALUES(1,"F",15,4,"10 - 999 g pkgs.",61.5,66,6,6,6) 2> INSERT Products VALUES(2,"M",14,4,"24 - 888 g pkgs.",34.8,74,7,7,7) 3> INSERT Products VALUES(3,"R",17,8,"24 - 777 g jars",17,171,0,5,0) 4> INSERT Products VALUES(4,"L",4,7,"5 kg pkg.",10,4,20,5,0) 5> INSERT Products VALUES(5,"R",12,1,"24 - 0.5 l bottles",1.23,445,0,25,0) 6> INSERT Products VALUES(6,"L",23,1,"500 ml",18,57,1,20,0) 7> INSERT Products VALUES(7,"O",12,2,"12 boxes",13,23,0,15,0) 8> go (1 rows affected) (1 rows affected) (1 rows affected) (1 rows affected) (1 rows affected) (1 rows affected) (1 rows affected) 1> 2> 3> CREATE TABLE OrderDetails ( 4> OrderID int NOT NULL , 5> ProductID int NOT NULL , 6> UnitPrice money NOT NULL DEFAULT (0), 7> Quantity smallint NOT NULL DEFAULT (1), 8> Discount real NOT NULL DEFAULT (0) 9> ) 10> GO 1> INSERT OrderDetails VALUES(10248,11,14,12,0) 2> INSERT OrderDetails VALUES(10248,42,9.8,10,0) 3> INSERT OrderDetails VALUES(10248,72,34.8,5,0) 4> INSERT OrderDetails VALUES(10249,14,18.6,9,0) 5> INSERT OrderDetails VALUES(10249,51,42.4,40,0) 6> INSERT OrderDetails VALUES(10250,41,7.7,10,0) 7> INSERT OrderDetails VALUES(10250,51,42.4,35,0.15) 8> INSERT OrderDetails VALUES(10250,65,16.8,15,0.15) 9> INSERT OrderDetails VALUES(10251,22,16.8,6,0.05) 10> INSERT OrderDetails VALUES(10251,57,15.6,15,0.05) 11> go (1 rows affected) (1 rows affected) (1 rows affected) (1 rows affected) (1 rows affected) (1 rows affected) (1 rows affected) (1 rows affected) (1 rows affected) (1 rows affected) 1> 2> 3> CREATE VIEW CustomerOrders_vw 4> AS 5> SELECT cu.rupanyName, 6> o.OrderID, 7> o.OrderDate, 8> od.ProductID, 9> p.ProductName, 10> od.Quantity, 11> od.UnitPrice, 12> od.Quantity * Od.UnitPrice AS ExtendedPrice 13> FROM Customers AS cu 14> INNER JOIN Orders AS o 15> ON cu.CustomerID = o.CustomerID 16> INNER JOIN OrderDetails AS od 17> ON o.OrderID = od.OrderID 18> INNER JOIN Products AS p 19> ON od.ProductID = p.ProductID 20> GO 1> SELECT CompanyName, ExtendedPrice 2> FROM CustomerOrders_vw 3> WHERE OrderDate = "9/3/1996" 4> GO CompanyName ExtendedPrice
---------------------
(0 rows affected) 1> 2> drop VIEW CustomerOrders_vw; 3> GO 1> drop table Customers; 2> drop table orders; 3> drop table orderdetails; 4> drop table Products; 5> GO</source>
Query that Returns Result Row Numbers, Starting with 1 and Incrementing by 3
<source lang="sql">
4> 5> CREATE TABLE MyTable ( 6> key_col int NOT NULL IDENTITY (1,1), 7> abc char(1) NOT NULL 8> ) 9> INSERT INTO MyTable VALUES ("a") 10> INSERT INTO MyTable VALUES ("b") 11> INSERT INTO MyTable VALUES ("c") 12> SELECT * FROM MyTable ORDER BY key_col 13> GO (1 rows affected) (1 rows affected) (1 rows affected) key_col abc
---
1 a
2 b
3 c
(3 rows affected) 1> 2> SELECT 3> 1 + 4> 3 * 5> (SELECT 6> count(*) 7> FROM 8> MyTable AS T2 9> WHERE 10> T2.abc < T1.abc) AS rownum, 11> abc 12> FROM 13> MyTable AS T1 14> ORDER BY 15> abc 16> 17> drop table MyTable 18> GO rownum abc
---
1 a
4 b
7 c
(3 rows affected) 1></source>
Retrieving the Maximum Value of key_col from MyTable
<source lang="sql">
5> CREATE TABLE MyTable ( 6> key_col int NOT NULL IDENTITY (1,1), 7> abc char(1) NOT NULL 8> ) 9> INSERT INTO MyTable VALUES ("a") 10> INSERT INTO MyTable VALUES ("b") 11> INSERT INTO MyTable VALUES ("c") 12> SELECT * FROM MyTable ORDER BY key_col 13> 14> 15> SELECT 16> MAX (key_col) AS max_key_col 17> FROM 18> MyTable 19> 20> drop table MyTable 21> GO (1 rows affected) (1 rows affected) (1 rows affected) key_col abc
---
1 a
2 b
3 c
(3 rows affected) max_key_col
3
(1 rows affected) 1></source>
Saving the @@IDENTITY Value in a Variable
<source lang="sql">
4> 5> CREATE TABLE MyTable ( 6> key_col int NOT NULL IDENTITY (1,1), 7> abc char(1) NOT NULL 8> ) 9> INSERT INTO MyTable VALUES ("a") 10> INSERT INTO MyTable VALUES ("b") 11> INSERT INTO MyTable VALUES ("c") 12> SELECT * FROM MyTable ORDER BY key_col 13> 14> 15> DECLARE @mylastident AS int 16> SET @mylastident = @@IDENTITY 17> PRINT @mylastident 18> 19> drop table MyTable 20> GO (1 rows affected) (1 rows affected) (1 rows affected) key_col abc
---
1 a
2 b
3 c
(3 rows affected) 3 1></source>
SELECT Statement and IDENTITY Property
<source lang="sql">
The IDENTITYCOL variable can be used instead of the name of the column with the IDENTITY property. 8> CREATE TABLE product 9> (product_no INTEGER IDENTITY(10000,1) NOT NULL, 10> product_name CHAR(30) NOT NULL, 11> price MONEY) 12> GO 1> 2> SELECT IDENTITYCOL 3> FROM product 4> WHERE product_name = "Soap" 5> GO product_no
(0 rows affected) 1> drop table product; 2> GO</source>
SQL Server provides the @@IDENTITY global variable to hold that value.
<source lang="sql">
4> CREATE TABLE MyTable (MyID Int IDENTITY(1, 10) NOT NULL 5> , MyDescription nVarChar(50) NOT NULL) 6> 7> INSERT MyTable (MyDescription) VALUES ("Auto Record 1") 8> INSERT MyTable (MyDescription) VALUES ("Auto Record 2") 9> 10> SET IDENTITY_INSERT MyTable ON 11> 12> INSERT MyTable (MyID, MyDescription) VALUES (5, "Manual Record 1") 13> 14> SET IDENTITY_INSERT MyTable OFF 15> 16> INSERT MyTable (MyDescription) VALUES ("Auto Record 3") 17> 18> SELECT * FROM MyTable 19> GO (1 rows affected) (1 rows affected) (1 rows affected) (1 rows affected) MyID MyDescription
--------------------------------------------------
1 Auto Record 1
11 Auto Record 2
5 Manual Record 1
21 Auto Record 3
(4 rows affected) 1> 2> SELECT @@IDENTITY AS LastIdentity 3> GO LastIdentity
21
(1 rows affected) 1> drop table MyTable; 2> GO</source>
SQL Server will choose the highest number as its current seed for a positive increment value or the lowest for a negative increment value.
<source lang="sql">
4> CREATE TABLE MyTable (MyID Int IDENTITY(1, 10) NOT NULL 5> , MyDescription nVarChar(50) NOT NULL) 6> 7> INSERT MyTable (MyDescription) VALUES ("Auto Record 1") 8> INSERT MyTable (MyDescription) VALUES ("Auto Record 2") 9> 10> SET IDENTITY_INSERT MyTable ON 11> 12> INSERT MyTable (MyID, MyDescription) VALUES (5, "Manual Record 1") 13> 14> SET IDENTITY_INSERT MyTable OFF 15> 16> INSERT MyTable (MyDescription) VALUES ("Auto Record 3") 17> 18> SELECT * FROM MyTable 19> GO (1 rows affected) (1 rows affected) (1 rows affected) (1 rows affected) MyID MyDescription
--------------------------------------------------
1 Auto Record 1
11 Auto Record 2
5 Manual Record 1
21 Auto Record 3
(4 rows affected) 1> 2> drop table MyTable; 3> GO</source>
Storing the Results in a Temporary Table Using the IDENTITY() Function
<source lang="sql">
4> 5> CREATE TABLE MyTable ( 6> key_col int NOT NULL IDENTITY (1,1), 7> abc char(1) NOT NULL 8> ) 9> INSERT INTO MyTable VALUES ("a") 10> INSERT INTO MyTable VALUES ("b") 11> INSERT INTO MyTable VALUES ("c") 12> SELECT * FROM MyTable ORDER BY key_col 13> GO (1 rows affected) (1 rows affected) (1 rows affected) key_col abc
---
1 a
2 b
3 c
(3 rows affected) 1> SELECT 2> IDENTITY (int , 1, 1) AS rownum, 3> abc 4> INTO 5> #temp 6> FROM 7> MyTable 8> ORDER BY 9> abc 10> 11> --Retrieving the Results of Using the IDENTITY() Function from the Temporary Table 12> SELECT 13> * 14> FROM 15> #temp 16> ORDER BY 17> Abc 18> 19> drop table MyTable 20> GO (3 rows affected) rownum abc
---
1 a
2 b
3 c
(3 rows affected) 1></source>
Supported data types for the IDENTITY property are TinyInt, SmallInt Int, BigInt, Decimal, and Numeric.
<source lang="sql">
8> 9> CREATE TABLE MyTable ( 10> MyID Int IDENTITY(1000000, -100) NOT NULL 11> ,MyDescription NVarChar(50) NOT NULL ) 12> GO 1> 2> drop table MyTable; 3> GO</source>
Syntax for Using the IDENTITY() Function
<source lang="sql">
The IDENTITY() function is used in a SELECT INTO statement to generate a result column with automatically generated values. The IDENTITY() function works in the same way that the IDENTITY property generates them when you use it in a table.
SELECT IDENTITY(<data_type> [, <seed>, <increment>]) AS column_name, <other_columns> INTO <new_table_name> FROM <table_name> WHERE <search_criteria> Query that Returns Result Row Numbers, Starting with 1 and Incremented by 1
22> 23> CREATE TABLE MyTable ( 24> key_col int NOT NULL IDENTITY (1,1), 25> abc char(1) NOT NULL 26> ) 27> INSERT INTO MyTable VALUES ("a") 28> INSERT INTO MyTable VALUES ("b") 29> INSERT INTO MyTable VALUES ("c") 30> SELECT * FROM MyTable ORDER BY key_col 31> GO (1 rows affected) (1 rows affected) (1 rows affected) key_col abc
---
1 a
2 b
3 c
(3 rows affected) 1> 2> SELECT 3> (SELECT 4> COUNT (*) 5> FROM 6> MyTable AS T2 7> WHERE 8> T2.abc <= T1.abc) AS rownum, 9> abc 10> FROM 11> MyTable AS T1 12> ORDER BY 13> abc 14> 15> drop table MyTable 16> GO rownum abc
---
1 a
2 b
3 c
(3 rows affected) 1></source>
The IDENTITY property must be temporarily turned off when inserting a specific value.
<source lang="sql">
9> CREATE TABLE MyTable (MyID Int IDENTITY(-1000000, 100) NOT NULL 10> ,MyDescription NVarChar(50) NOT NULL) 11> GO 1> 2> SET IDENTITY_INSERT MyTable ON 3> 4> INSERT MyTable (MyID, MyDescription) 5> VALUES (5, "This will work") 6> 7> SET IDENTITY_INSERT MyTable OFF 8> 9> select * from MyTable; 10> GO (1 rows affected) MyID MyDescription
--------------------------------------------------
5 This will work
(1 rows affected) 1> 2> drop table MyTable; 3> GO</source>
Using the IDENTITY Property and the IDENTITY() Function
<source lang="sql">
The IDENTITY property enables you to assign an auto-number to a numeric column. Values for a column with an IDENTITY property are generated automatically. The IDENTITY column must be of datatype int, bigint, smallint, tinyint, decimal, or numeric with a scale of 0. It must be non-NULLable.</source>
