SQL Server/T-SQL Tutorial/Sequence Indentity/Identity — различия между версиями
Admin (обсуждение | вклад) м (1 версия) |
|
(нет различий)
| |
Текущая версия на 10:24, 26 мая 2010
Содержание
- 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
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>
A table could also be created so that the seed value was negative and the increment was positive:
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.
Cannot insert explicit value for identity column in table "T" when IDENTITY_INSERT is set to OFF.
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
Check @@IDENTITY value
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
Create table with IDENTITY column
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
Generating IDENTITY values
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>
IDENTITY(
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>
Identity: A numerical value automatically assigned to a row any time a new row is added.
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>
Identity column
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
IDENTITY/NOT NULL/PRIMARY KEY
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
Query a view with four table join
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
Query that Returns Result Row Numbers, Starting with 1 and Incrementing by 3
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>
Retrieving the Maximum Value of key_col from MyTable
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>
Saving the @@IDENTITY Value in a Variable
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>
SELECT Statement and IDENTITY Property
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
SQL Server provides the @@IDENTITY global variable to hold that value.
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
SQL Server will choose the highest number as its current seed for a positive increment value or the lowest for a negative increment value.
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
Storing the Results in a Temporary Table Using the IDENTITY() Function
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>
Supported data types for the IDENTITY property are TinyInt, SmallInt Int, BigInt, Decimal, and Numeric.
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
Syntax for Using the IDENTITY() Function
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>
The IDENTITY property must be temporarily turned off when inserting a specific value.
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
Using the IDENTITY Property and the IDENTITY() Function
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.