Using the identity column property and the Sequence object

Using the Identity column property

1. Create a table Sales.myorders

 UseTSQL2012;IF object_id(N'sales.myorders'N'U') is  not NULL DROP TABLEsales.myorders;GOCREATE TABLEsales.myorders (OrderIDINT  not NULL IDENTITY(1,1)    CONSTRAINTPk_myorders_orderidPRIMARY KEY, CustIDINT  not NULL    CONSTRAINTChk_myorders_custidCHECK(CustID> 0), EmpidINT  not NULL    CONSTRAINTChk_myorders_empidCHECK(Empid> 0), OrderDate DATE not NULL);

2. Insert some Records

INSERT   intosales.myorders (CustID, Empid, OrderDate)VALUES(1,2,'20120620' ),        ( 1,3,'20120620' ),        ( 2,2,'20120620');

3. IDENTITY-related functions

SELECT  scope_identity() as scope_identity,/*returns the last identity value in an identity column that is inserted into the same scope.                                                      A scope is a module: a stored procedure, trigger, function, or batch. Therefore, if two statements are in the same stored procedure, function, or batch, they are in the same scope. */  @ @IDENTITY                       as [@ @IDENTITY],--returns the last identifier within the same scope as above, but with an unlimited scope. Ident_current ('sales.myorders') asIdent_current;--returns the last identity value generated for the specified table or view. 

4. If the TRUNCATE table, the value of the identity of the table will be reset, just delete the record will not.

TRUNCATE TABLE sales.myorders; SELECT Ident_current ('sales.myorders'as[ident_current ];

5. Using DBCC CHECKINDENT to continue to grow

DBCC Checkident ('sales.myorders'4); INSERT  into VALUES (22'20120620'); SELECT *  from Sales.myorders;

Using the Sequence object

SQL Server 2012 introduces a new database object, sequence, which does not have as many restrictions as the identity column property (for example, the identity attribute can only be applied to a column of a table.) Sometimes you want multiple table keys to have no conflict, but the identity cannot operate across tables. You wish the gentleman to use it as a value, or not.   You cannot update the identity column. IDENTITY cannot be reused. Truncate will reset the identity value)

1. Create sequence

--Create sequenceIF object_id(N'Sales.seqorderids'N' So') is  not NULL DROPSEQUENCE Sales.seqorderids;--Partial Parameters--INCREMENT by increment value, default = 1--MINVALUE minimum value, which defaults to the minimum value of the data type, for example, the minimum value of int is -2147483648--MAXVALUE Maximum, the default value is the maximum value of the data type--Cycle|no cycle, default to No loop--start with, which sets a starting value, the default starting value is the maximum value of the ascending sequence object's minimum and descending sequence objects. CREATESEQUENCE Sales.seqorderids as INTMINVALUE1CYCLE;

2. Sequence in the query system

SELECT   as type,        start_value,        minimum_value,        current_value,        increment,        is_cyclingfrom      sys.sequencesWHERE   object_id=object_id( n'sales.seqorderids', n'so');

3. Get a new value

--SELECTNEXT for Sales.seqorderids;

4. All properties of the sequence can be modified by the alter sequence command. such as changing the current value

ALTER SEQUENCE sales.seqorderids    with 1;

5. Practical use

A) Create a sales.myorders table and insert the data

--Recreate sales.myorders TableIF object_id(N'sales.myorders'N'U') is  not NULL DROP TABLEsales.myorders;GOCREATE TABLEsales.myorders (OrderIDINT  not NULL    CONSTRAINTPk_myorders_orderidPRIMARY KEY, CustIDINT  not NULL    CONSTRAINTChk_myorders_custidCHECK(CustID> 0), EmpidINT  not NULL    CONSTRAINTChk_myorders_empidCHECK(Empid> 0), OrderDate DATE not NULL);--Use in INSERT VALUESINSERT  intoSales.myorders (OrderID, CustID, Empid, OrderDate)VALUES  (NEXTVALUE forSales.seqorderids,1,2,'20120620'),  (NEXTVALUE forSales.seqorderids,1,3,'20120620'),  (NEXTVALUE forSales.seqorderids,2,2,'20120620');--Use in INSERT SELECTINSERT  intosales.myorders (OrderID, CustID, Empid, OrderDate)SELECT    NEXTVALUE forSales.seqorderids Over(ORDER  byOrderID), CustID, Empid, OrderDate fromsales.ordersWHERECustID= 1;

b) Set the sequence directly to the default constraint

ALTER TABLE sales.myorders   ADD CONSTRAINT Dft_myorders_orderid     DEFAULT (NEXT for for OrderID;

Test

INSERT  into sales.myorders (CustID, Empid, OrderDate)   SELECT     CustID, Empid, OrderDate  from  sales.orders  WHERE= 2;

Reference documents

SCOPE_IDENTITY (Transact-SQL)

Https://msdn.microsoft.com/zh-cn/library/ms190315.aspx

@ @IDENTITY (Transact-SQL)

Https://msdn.microsoft.com/zh-cn/library/ms187342.aspx

IDENT_CURRENT (Transact-SQL)

Https://msdn.microsoft.com/library/ms175098.aspx

CREATE SEQUENCE (Transact-SQL)

Https://msdn.microsoft.com/zh-cn/library/ff878091.aspx

Using the identity column property and the Sequence object