Let’s start with sequences first. If you already worked with Oracle you should be already familiar with sequences, because they are used everywhere in the database design. For SQL Server sequences are a completely know concept. A sequence is nothing else like an INT IDENTITY column, but with the difference that a sequence is created on the database level and can be used and accessed through all your database objects (like tables, stored procedures, stored functions, etc.). Sequences are created with the new T-SQL statement CREATE SEQUENCE:
CREATE SEQUENCE MyNewSequence
AS INT
MINVALUE 1
MAXVALUE 1000
START WITH 1
GO
As you can see, the sequence get’s a name which must be unique within the current database. Then you have to specify the underlying data type of the sequence, like TINYINT, SMALLINT, INT, BIGINT. You can also specify a minimum and maximum value (MINVALUE, MAXVALUE) and which value your sequence should return, when the sequence is accessed for the first time (START WITH). To track all your sequences within a database, SQL Server provides you a new system view – sys.sequences:
SELECT * FROM sys.sequences
GO
After you have created a new sequence you can start using it through the new command NEXT VALUE FOR:
DECLARE @id INT
SET @id = NEXT VALUE FOR MyNewSequence
SELECT @id
GO
Read more: SQL Server Central
Posts
