1.1 IDENTITY- IDENTITY [ (seed , increment) ] - In create table id_num int IDENTITY(1,1), - SET IDENTITY_INSERT Students ON; -- manually we can insert - SET IDENTITY_INSERT Students OFF; -- normal identity column functionality resume - The identity property on a column does not guarantee the o Uniqueness of the value - Uniqueness must be enforced by using a PRIMARY KEY or UNIQUE constraint or UNIQUE index. o Consecutive values within a transaction o Consecutive values after server restart or other failures o Reuse of values - SET IDENTITY_INSERT tablename OFF /ON; - SCOPE_IDENTITY() - Returns the last identity value inserted into an identity column in 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. - DBCC CHECKIDENT (table Name) - returns the current identity value and the current maximum value of the identity column. If the two values are not the same, you should reset the identity value to avoid potential errors or gaps in the sequence of values. - SELECT IDENT_INCR('Person.Address') AS Identity_Increment; returns the increment value - Forcing the current identity value to a new value o DBCC CHECKIDENT ('Person.AddressType', RESEED, 10); - Returning SEED Values USE AdventureWorks2012; GO SELECT TABLE_SCHEMA, TABLE_NAME, IDENT_SEED(TABLE_SCHEMA + '.' + TABLE_NAME) AS IDENT_SEED FROM INFORMATION_SCHEMA.TABLES WHERE IDENT_SEED(TABLE_SCHEMA + '.' + TABLE_NAME) IS NOT NULL; GO - SCOPE_IDENTITY, IDENT_CURRENT, and @@IDENTITY are similar functions because they return values that are inserted into identity columns. - IDENT_CURRENT is not limited by scope and session; it is limited to a specified table. IDENT_CURRENT returns the value generated for a specific table in any session and any scope. For more information, see IDENT_CURRENT (Transact-SQL). - SCOPE_IDENTITY and @@IDENTITY return the last identity values that are generated in any table in the current session. However, SCOPE_IDENTITY returns values inserted only within the current scope; @@IDENTITY is not limited to a specific scope. |
