Sequences are a key-generating mechanism introduced in SQL Server 2012. They provide an alternative to the IDENTITY columns feature that was prevalent in previous versions of SQL Server. In this article, we will explore the concept of sequences and compare them to the identity feature.

Storage: IDENTITY vs Sequence Objects

One major difference between sequences and identity is in the way they are stored in a database. Identity relies on the existence of a table and is stored along with the properties of the table. On the other hand, sequences are stored independently of tables. SQL Server 2012 treats sequences as separate objects, which can be viewed within a given database. This separation allows for more flexibility and convenience when working with sequences.

Generating values: IDENTITY vs Sequence Objects

Sequences offer more flexibility in generating new values compared to identity. While a new increment value for identity is only generated by inserting a new row into a table, sequences allow for the generation of an increment value outside of a table. This means that sequences can be generated and stored in a variable, which can then be used in an insert statement to append a row into a table. Sequences can also be used as unique surrogate keys across multiple tables. Additionally, sequences have the advantage of generating new increment values during an UPDATE statement. Their values can be used in multiple columns within a table, providing even more flexibility.

Create Sequence Object Syntax

Creating a sequence is straightforward and requires only the sequence name as a mandatory parameter. However, there are additional arguments that can be specified to customize the sequence object. These arguments include the data type, initial value, increment value, minimum and maximum values, cycle behavior, and cache size. By utilizing these arguments, you can create sequences that meet your specific requirements.

Administering Sequence Objects

Every database in SQL Server 2012 has a system view called ‘sys.sequences’ that provides information about sequences in the database. To create a sequence object, the user must have the CREATE SEQUENCE permission. This permission was introduced in SQL Server 2012 and can be granted to users as needed. It is important to note that sequences do have limitations, such as the potential for data integrity issues and the need for unique constraints to enforce uniqueness. However, when used appropriately, sequences can be a powerful tool for generating key values in SQL Server.

Conclusion

Sequences in SQL Server 2012 offer several advantages over IDENTITY columns and make data migrations from non-Microsoft database platforms more convenient. However, it is important to consider the limitations and potential challenges associated with using sequences. They should be used as alternatives to IDENTITY columns rather than replacements. By understanding the capabilities and limitations of sequences, you can leverage them effectively in your SQL Server databases.