IDENTITY & SEQUENCE

IDENTITY

DECLARE @new_key AS INT; 
INSERT INTO dbo.T1(datacol) VALUES('AAAAA'); 
SET @new_key = SCOPE_IDENTITY(); 
SELECT @new_key AS new_key

　　

Remember that both @@identity and SCOPE_IDENTITY return the last identity value produced by the current session. Neither is affected by inserts issued by other sessions. However, if you want to know the current identity value in a table (the last value produced) regardless of session, you should use the IDENT_CURRENT function and provide the table name as input.

SELECT SCOPE_IDENTITY() AS [SCOPE_IDENTITY], @@identity AS [@@identity], IDENT_CURRENT('dbo.T1') AS [IDENT_CURRENT];

SET IDENTITY_INSERT dbo.T1 ON;
INSERT INTO dbo.T1(keycol, datacol) VALUES(5, 'FFFFF'); SET IDENTITY_INSERT dbo.T1 OFF;

 

you can reseed the current identity value in the table by using the DBCC CHECKIDENT command.

Sequence

Sequence To create a sequence object, use the CREATE SEQUENCE command. The minimum required information is just the sequence name, but note that the defaults in such a case might not be what you want. If you don’t indicate the type, SQL Server will use BIGINT by default. If you want a different type, indicate AS <type>. The type can be any numeric type with a scale of zero. For example, if you need your sequence to be of an INT type, indicate AS INT. Like identity, the sequence object allows you to specify the starting value (START WITH <val>) and the increment (INCREMENET BY <val>). If you don’t indicate the starting value, the default will be the same as the minimum value (MINVALUE). If you don’t indicate the increment value, it will be 1 by default.

CREATE SEQUENCE dbo.SeqOrderIDs AS INT MINVALUE 1 CYCLE;

ALTER SEQUENCE dbo.SeqOrderIDs NO CYCLE;

SELECT NEXT VALUE FOR dbo.SeqOrderIDs;

DECLARE @neworderid AS INT = NEXT VALUE FOR dbo.SeqOrderIDs; INSERT INTO dbo.T1(keycol, datacol) VALUES(@neworderid, 'a');

INSERT INTO dbo.T1(keycol, datacol) VALUES(NEXT VALUE FOR dbo.SeqOrderIDs, 'b'); SELECT * FROM dbo.T1;

 

To get information about your sequences, query a view called sys.sequences. For example, to find the current sequence value in the SeqOrderIDs sequence, you would use the following code.

SELECT current_value FROM sys.sequences WHERE OBJECT_ID = OBJECT_ID('dbo.SeqOrderIDs');

 

Another extension allows the use of the NEXT VALUE FOR function in a default constraint. Here’s an example.

ALTER TABLE dbo.T1 ADD CONSTRAINT DFT_T1_keycol DEFAULT (NEXT VALUE FOR dbo.SeqOrderIDs) FOR keycol;

 

Finally, another extension allows you to allocate a whole range of sequence values at once by using a stored procedure called sp_sequence_get_range. The idea is that if the application needs to assign a range of sequence values, it is easiest to update the sequence only once, incrementing it by the size of the range. You call the procedure, indicate the size of the range you want, and collect the first value in the range, as well as other information, by using output parameters. Here’s an example of calling the procedure and asking for a range of 1,000 sequence values.

DECLARE @first AS SQL_VARIANT;
EXEC sys.sp_sequence_get_range @sequence_name = N'dbo.SeqOrderIDs', @range_size = 1000, @range_first_value = @first OUTPUT ; 
SELECT @first; 

If you run the code twice, you will find that the returned first value in the second call is greater than the first by 1,000.