CREATE SEQUENCE Statement

Using the IDENTITY property has several significant disadvantages, the most important of which are the following:

• You can use it only with the specified table.
• You cannot obtain the new value before using it.
• You can specify the IDENTITY property only when the column is created.

For these reasons, SQL Server 2012 introduces sequences, which has the same semantics as the IDENTITY property but don’t have the limitations from the preceding list. Therefore, a sequence is an independent database feature that enables you to specify a counter of values for different database objects, such as columns and variables.

Sequences are created using the CREATE SEQUENCE statement. The CREATE SEQUENCE statement is specified in the SQL standard and is implemented in other relational database systems, such as IBM DB2 and Oracle. Example 6.39 shows how sequences can be specified in SQL Server.Example 6.39

USE sample;
CREATE SEQUENCE dbo.Sequence1
	AS INT
	START WITH 1 INCREMENT BY 5
	MINVALUE 1 MAXVALUE 256
	CYCLE;Code language: PHP (php)

The values of the sequence called Sequence1 in Example 6.39 are automatically generated by the system, beginning with 1 and incrementing by 5 for every subsequent value. Therefore, the START clause specifies the initial value, while the INCREMENT clause defines the incremental value. (The incremental value can be positive or negative.)

The following two optional clauses, MINVALUE and MAXVALUE, are directives, which specify a minimal and maximum value for a sequence object. (Note that MINVALUE must be less than or equal to the start value, while MAXVALUE cannot be greater than the upper boundary for the values of the data type used for the specification of the sequence.)

The CYCLE clause specifies that the object should restart from the minimum value (or maximum value, for descending sequence objects) when its minimum (or maximum) value is exceeded. The default value for this clause is NO CYCLE, which means that an exception will be thrown if its minimum or maximum value is exceeded.

The main property of a sequence is that it is table-independent; that is, it can be used with any database object, such as a table’s column or variable. (This property positively affects storage and, therefore, performance. You do not need storage for a specified sequence; only the last-used value is stored.) To generate new sequence values, you can use the NEXT VALUE FOR expression. Example 6.40 shows the use of this expression.

USE sample;
SELECT NEXT VALUE FOR dbo.sequence1;
SELECT NEXT VALUE FOR dbo.sequence1;Code language: CSS (css)

The result is

You can use the NEXT VALUE FOR expression to assign the results of a sequence to a variable or to a column. Example 6.41 shows how you can use this expression to assign the results to a table’s column.

USE sample;
CREATE TABLE dbo.table1
	(column1 INT NOT NULL PRIMARY KEY,
	column2 CHAR(10));
	INSERT INTO dbo.table1 VALUES (NEXT VALUE FOR dbo.sequence1, 'A');
	INSERT INTO dbo.table1 VALUES (NEXT VALUE FOR dbo.sequence1, 'B');Code language: PHP (php)

Example 6.41 first creates a table called table1 with two columns. The following two INSERT statements insert two rows in this table. The first column has values 11 and 16, respectively. (These two values are subsequent values, following the generated values in Example 6.40.)

Example 6.42 shows how you can use the catalog view called sys.sequences to check the current value of the sequence, without using it. (Catalog views are described in detail in Chapter “System Catalog”.)

USE sample;
SELECT current_value
	FROM sys.sequences
	WHERE name = 'Sequence1';Code language: PHP (php)

The ALTER SEQUENCE statement modifies the properties of an existing sequence. One of the most important uses of this statement is in relation to the RESTART WITH clause, which “reseeds” a given sequence. Example 6.43 shows the use of the ALTER SEQUENCE statement to reinitialize (almost) all properties of the existing sequence called Sequence1.

USE sample;
ALTER SEQUENCE dbo.sequence1
	RESTART WITH 100
	INCREMENT BY 50
	MINVALUE 50
	MAXVALUE 200
	NO CYCLE;Code language: PHP (php)

To drop a sequence, use the DROP SEQUENCE statement.