SQL Server/T-SQL/Sequence/IDENTITY

Created with an IDENTITY property that is set to start at 1,000,000 and decrement by 100 for every row added

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>

Get current IDENTITY value

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>

IDENTITY

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>

IDENTITY as PRIMARY KEY

 
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>

IDENTITY [ (seed , increment ) ]

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>

Identity starting with -1000000

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>

Now you explicitly enter a value for Identity column

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>

Seed value was negative and the increment was positive

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>

SQL Server chooses the highest number as its current seed for a positive increment value or the lowest for a negative increment value

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>

SQL Server does not allow explicit values to be inserted into it

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>

Using Identity during data insert

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>

Using IDENTITY value

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>