SQL Server/T-SQL Tutorial/Sequence Indentity/Identity — различия между версиями

Версия 13:46, 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.

SQL Server/T-SQL Tutorial/Sequence Indentity/Identity — различия между версиями

Версия 13:46, 26 мая 2010

Содержание

Altering the Table to Add an Identity Column

A table could also be created so that the seed value was negative and the increment was positive:

Cannot insert explicit value for identity column in table "T" when IDENTITY_INSERT is set to OFF.

Check @@IDENTITY value

Create table with IDENTITY column

Generating IDENTITY values

IDENTITY(

Identity: A numerical value automatically assigned to a row any time a new row is added.

Identity column

IDENTITY/NOT NULL/PRIMARY KEY

Query a view with four table join

Query that Returns Result Row Numbers, Starting with 1 and Incrementing by 3

Retrieving the Maximum Value of key_col from MyTable

Saving the @@IDENTITY Value in a Variable

SELECT Statement and IDENTITY Property

SQL Server provides the @@IDENTITY global variable to hold that value.

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

Storing the Results in a Temporary Table Using the IDENTITY() Function

Supported data types for the IDENTITY property are TinyInt, SmallInt Int, BigInt, Decimal, and Numeric.

Syntax for Using the IDENTITY() Function

The IDENTITY property must be temporarily turned off when inserting a specific value.

Using the IDENTITY Property and the IDENTITY() Function

Навигация

Персональные инструменты

Пространства имён

Варианты

Просмотры

Ещё

Поиск

Разделы

Навигация

Инструменты

Текущая версия на 10:24, 26 мая 2010 (просмотреть исходный код) Admin (обсуждение \| вклад) м (1 версия)	Версия 13:46, 26 мая 2010 (просмотреть исходный код) (обсуждение) Следующая правка →
(нет различий)