Programmer's Rules

SQL Server

Using Sequences In SQL Server

How to create sequences for coordinating values across tables in SQL Server

SQL Serverjob interview70-761

When we need to increment value within one table the best solution is to use IDENTITY property.

But when we want to coordinate incrementation within many tables we should use CREATE (ALTER, DROP) SEQUENCE statement.

A sequence is an object since SQL Server 2012 that provides a pool of sequential values to be referenced by multiple tables within a database.

The example below shows how to create a sequence with initial value (seed) of 100 and step of 2:

Let's create a table:

and insert 3 rows, getting value from the sequence:

The expected result is:

Id Name
100 it1
102 it2
104 it3
Tutorial statistics