SQL Server/T-SQL/Sequence/IDENTITY
Содержание
- 1 Created with an IDENTITY property that is set to start at 1,000,000 and decrement by 100 for every row added
- 2 Get current IDENTITY value
- 3 IDENTITY
- 4 IDENTITY as PRIMARY KEY
- 5 IDENTITY [ (seed , increment ) ]
- 6 Identity starting with -1000000
- 7 Now you explicitly enter a value for Identity column
- 8 Seed value was negative and the increment was positive
- 9 SQL Server chooses the highest number as its current seed for a positive increment value or the lowest for a negative increment value
- 10 SQL Server does not allow explicit values to be inserted into it
- 11 Using Identity during data insert
- 12 Using IDENTITY value
Created with an IDENTITY property that is set to start at 1,000,000 and decrement by 100 for every row added
<source lang="sql">
1> -- Created with an IDENTITY property that is set to start at 1,000,000 and decrement by 100 for every row added 2> 3> CREATE TABLE MyTable (MyID Int IDENTITY(1000000, -100) NOT NULL 4> ,MyDescription NVarChar(50) NOT NULL ) 5> GO 1> insert mytable (mydescription) values ("a"); 2> GO (1 rows affected) 1> insert mytable (mydescription) values ("b"); 2> GO (1 rows affected) 1> insert mytable (mydescription) values ("c"); 2> GO (1 rows affected) 1> select * from mytable 2> GO MyID MyDescription
--------------------------------------------------
1000000 a
999900 b
999800 c
(3 rows affected) 1> drop table MyTable 2> GO 1> 2>
</source>
Get current IDENTITY value
<source lang="sql">
17> 18> -- Save the original @@IDENTITY value 19> 20> CREATE TABLE TestIdent 21> ( 22> IDCol int IDENTITY PRIMARY KEY 23> ) 24> GO Msg 2714, Level 16, State 6, Server sqle\SQLEXPRESS, Line 20 There is already an object named "TestIdent" in the database. 1> CREATE TABLE TestChild1 ( 2> IDcol int PRIMARY KEY FOREIGN KEY REFERENCES TestIdent(IDCol) 3> ) 4> GO 1> CREATE TABLE TestChild2 ( 2> IDcol int PRIMARY KEY FOREIGN KEY REFERENCES TestIdent(IDCol) 3> ) 4> GO 1> DECLARE @Ident int 2> 3> INSERT INTO TestIdent DEFAULT VALUES 4> 5> SET @Ident = @@IDENTITY 6> PRINT "The value we got originally from @@IDENTITY was " + CONVERT(varchar(2),@Ident) 7> PRINT "The value currently in @@IDENTITY is " + CONVERT(varchar(2),@@IDENTITY) 8> 9> INSERT INTO TestChild1 VALUES (@@IDENTITY) 10> 11> PRINT "The value we got originally from @@IDENTITY was " + CONVERT(varchar(2),@Ident) 12> 13> IF (SELECT @@IDENTITY) IS NULL 14> PRINT "The value currently in @@IDENTITY is NULL" 15> ELSE 16> PRINT "The value currently in @@IDENTITY is " + CONVERT(varchar(2),@@IDENTITY) 17> 18> PRINT "" 19> 20> INSERT INTO TestChild2 VALUES (@Ident) 21> GO (1 rows affected) (1 rows affected) The value we got originally from @@IDENTITY was 2 (1 rows affected) The value currently in @@IDENTITY is 2 (1 rows affected) (1 rows affected) The value we got originally from @@IDENTITY was 2 (1 rows affected) The value currently in @@IDENTITY is NULL (1 rows affected)
(1 rows affected) 1> 2> select * from testchild1 3> go IDcol
2
(1 rows affected) 1> 2> select * from testchild2 3> GO IDcol
2
(1 rows affected) 1> 2> drop table TestChild2; 3> drop table TestChild1; 4> GO 1> 2>
</source>
IDENTITY
<source lang="sql">
1> 2> create table Players ( 3> Id int IDENTITY (1, 1) NOT NULL , 4> myGroup nvarchar (10) 5> ) 6> GO 1> 2> INSERT INTO Players(myGroup) VALUES ("g1") 3> GO (1 rows affected) 1> 2> select * from Players 3> GO Id myGroup
----------
1 g1
(1 rows affected) 1> 2> drop table Players 3> GO 1>
</source>
IDENTITY as PRIMARY KEY
<source lang="sql">
3> CREATE TABLE customer 4> ( 5> cust_id int NOT NULL IDENTITY PRIMARY KEY, 6> cust_name varchar(50) NOT NULL 7> ) 8> GO 1> 2> CREATE TABLE orders 3> ( 4> order_id int NOT NULL IDENTITY PRIMARY KEY, 5> cust_id int NOT NULL REFERENCES customer(cust_id) 6> ON UPDATE NO ACTION ON DELETE NO ACTION 7> ) 8> GO 1> 2>
</source>
IDENTITY [ (seed , increment ) ]
<source lang="sql">
1> CREATE TABLE MyTable (MyID Int IDENTITY NOT NULL 2> , MyDescription nVarChar(50) NOT NULL) 3> GO 1> 2> -- IDENTITY [ (seed , increment ) ] 3> 4> insert mytable (mydescription) values ("a"); 5> GO (1 rows affected) 1> insert mytable (mydescription) values ("b"); 2> GO (1 rows affected) 1> insert mytable (mydescription) values ("c"); 2> GO (1 rows affected) 1> select * from mytable 2> GO MyID MyDescription
--------------------------------------------------
1 a
2 b
3 c
(3 rows affected) 1> 2> drop table MyTable 3> GO 1>
</source>
Identity starting with -1000000
<source lang="sql">
1> CREATE TABLE MyTable (MyID Int IDENTITY(-1000000, 100) NOT NULL 2> ,MyDescription NVarChar(50) NOT NULL) 3> 4> insert mytable (mydescription) values ("a"); 5> insert mytable (mydescription) values ("b"); 6> insert mytable (mydescription) values ("c"); 7> 8> select * from mytable 9> GO (1 rows affected) (1 rows affected) (1 rows affected) MyID MyDescription
--------------------------------------------------
-1000000 a -999900 b -999800 c
(3 rows affected) 1> 2> 3> SELECT @@IDENTITY AS LastIdentity 4> 5> drop table MyTable 6> GO LastIdentity
-999800
(1 rows affected) 1> 2>
</source>
Now you explicitly enter a value for Identity column
<source lang="sql">
1> 2> CREATE TABLE MyTable (MyID Int IDENTITY(1, 10) NOT NULL 3> , MyDescription nVarChar(50) NOT NULL) 4> 5> 6> INSERT MyTable (MyDescription) VALUES ("Auto Record 1") 7> INSERT MyTable (MyDescription) VALUES ("Auto Record 2") 8> 9> 10> -- Now you explicitly enter a MyID value with the following script: 11> 12> SET IDENTITY_INSERT MyTable ON INSERT MyTable (MyID, MyDescription) 13> VALUES (5, "Manual Record 1") SET IDENTITY_INSERT MyTable OFF 14> 15> select * from MyTable 16> GO (1 rows affected) (1 rows affected) (1 rows affected) MyID MyDescription
--------------------------------------------------
1 Auto Record 1
11 Auto Record 2
5 Manual Record 1
(3 rows affected) 1> 2> drop table MyTable 3> GO 1> 2>
</source>
Seed value was negative and the increment was positive
<source lang="sql">
1> 2> -- Seed value was negative and the increment was positive: 3> 4> CREATE TABLE MyTable (MyID Int IDENTITY(-1000000, 100) NOT NULL 5> ,MyDescription NVarChar(50) NOT NULL) 6> GO 1> insert mytable (mydescription) values ("a"); 2> GO (1 rows affected) 1> insert mytable (mydescription) values ("b"); 2> GO (1 rows affected) 1> insert mytable (mydescription) values ("c"); 2> GO (1 rows affected) 1> select * from mytable 2> GO MyID MyDescription
--------------------------------------------------
-1000000 a -999900 b -999800 c
(3 rows affected) 1> 2> drop table MyTable 3> GO 1>
</source>
SQL Server chooses the highest number as its current seed for a positive increment value or the lowest for a negative increment value
<source lang="sql">
1> 2> CREATE TABLE MyTable (MyID Int IDENTITY(1, 10) NOT NULL 3> , MyDescription nVarChar(50) NOT NULL) 4> 5> INSERT MyTable (MyDescription) VALUES ("Auto Record 1") 6> INSERT MyTable (MyDescription) VALUES ("Auto Record 2") 7> 8> 9> -- Now you explicitly enter a MyID value with the following script: 10> 11> SET IDENTITY_INSERT MyTable ON INSERT MyTable (MyID, MyDescription) 12> VALUES (5, "Manual Record 1") SET IDENTITY_INSERT MyTable OFF 13> 14> -- SQL Server will always choose the highest number as its current seed for a positive -- increment value or the lowest for a negative increment value 15> 16> INSERT MyTable (MyDescription) 17> VALUES ("Auto Record 3") 18> 19> SELECT * FROM MyTable 20> 21> drop table MyTable 22> 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>
</source>
SQL Server does not allow explicit values to be inserted into it
<source lang="sql">
1> -- SQL Server does not allow explicit values to be inserted into it 2> 3> CREATE TABLE MyTable (MyID Int IDENTITY(-1000000, 100) NOT NULL 4> ,MyDescription NVarChar(50) NOT NULL) 5> 6> 7> INSERT MyTable (MyID, MyDescription) 8> VALUES (5, "This will not work") 9> GO Msg 544, Level 16, State 1, Server sqle\SQLEXPRESS, Line 7 Cannot insert explicit value for identity column in table "MyTable" when IDENTITY_INSERT is set to OFF. 1> 2> select * from MyTable 3> GO MyID MyDescription
--------------------------------------------------
(0 rows affected) 1> 2> drop table MyTable 3> GO 1> 2>
</source>
Using Identity during data insert
<source lang="sql">
18> 19> -- Identity 20> 23> CREATE TABLE MyTable (MyID Int IDENTITY(1, 1) NOT NULL 24> , MyDescription nVarChar(50) NOT NULL) 25> 26> insert mytable (mydescription) values ("a"); 27> insert mytable (mydescription) values ("b"); 28> insert mytable (mydescription) values ("c"); 29> 30> select * from mytable 31> GO (1 rows affected) (1 rows affected) (1 rows affected) MyID MyDescription
--------------------------------------------------
1 a
2 b
3 c
(3 rows affected) 1> 2> drop table MyTable 3> GO 1>
</source>
Using IDENTITY value
<source lang="sql">
20> 21> -- Using @@IDENTITY 22> CREATE TABLE TestIdent( 23> IDCol int IDENTITY PRIMARY KEY 24> ) 25> GO Msg 2714, Level 16, State 6, Server sqle\SQLEXPRESS, Line 22 There is already an object named "TestIdent" in the database. 1> CREATE TABLE TestChild1 ( 2> IDcol int PRIMARY KEY FOREIGN KEY REFERENCES TestIdent(IDCol) 3> ) 4> GO 1> CREATE TABLE TestChild2 ( 2> IDcol int PRIMARY KEY FOREIGN KEY REFERENCES TestIdent(IDCol) 3> ) 4> 5> DECLARE @Ident int 6> INSERT INTO TestIdent DEFAULT VALUES 7> SET @Ident = @@IDENTITY 8> 9> PRINT "The value we got originally from @@IDENTITY was " + CONVERT(varchar(2),@Ident) 10> PRINT "The value currently in @@IDENTITY is " + CONVERT(varchar(2),@@IDENTITY) 11> 12> INSERT INTO TestChild1 VALUES (@@IDENTITY) 13> 14> PRINT "The value we got originally from @@IDENTITY was " + CONVERT(varchar(2),@Ident) 15> 16> IF (SELECT @@IDENTITY) IS NULL 17> PRINT "The value currently in @@IDENTITY is NULL" 18> ELSE 19> PRINT "The value currently in @@IDENTITY is " + CONVERT(varchar(2),@@IDENTITY) 20> 21> -- The next line is just a spacer for our print out 22> PRINT "" 23> 24> INSERT INTO TestChild2 25> VALUES (@@IDENTITY) 26> GO (1 rows affected) (1 rows affected) The value we got originally from @@IDENTITY was 3 (1 rows affected) The value currently in @@IDENTITY is 3 (1 rows affected) (1 rows affected) The value we got originally from @@IDENTITY was 3 (1 rows affected) The value currently in @@IDENTITY is NULL (1 rows affected) Msg 515, Level 16, State 2, Server sqle\SQLEXPRESS, Line 24 Cannot insert the value NULL into column "IDcol", table "master.dbo.TestChild2"; column does not allow nulls. INSERT fails. The statement has been terminated. 1> select * from testchild1 2> go IDcol
3
(1 rows affected) 1> 2> select * from testchild2 3> GO IDcol
(0 rows affected) 1> 2> drop table testchild1 3> drop table testchild2 4> GO 1>
</source>
