в 13:46, 26 мая 2010

2010-05-26T13:46:06Z

Admin: 1 версия

2010-05-26T10:20:03Z

1 версия

Новая страница

==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>

← Предыдущая	Версия 13:46, 26 мая 2010
(нет различий)

SQL Server/T-SQL/Sequence/IDENTITY - История изменений

в 13:46, 26 мая 2010

Admin: 1 версия