How to Create Sequence in SQL Server 2012 using SQL Server Management Studio
One answer of what is new in SQL Server 2012, Denali CTP1 is the SQL Sequence objects.
SQL Sequence objects generate a sequence of numbers in SQL Server 2012 databases.
T-SQL developers and SQL Server database administrators can find detailed information and t-sql sample codes on how to create sequence of numbers in SQL Server 2012, Denali at t-sql tutorial SQL Sequences and Create Sequence of Numbers in SQL Server 2012
In this SQL Server tutorial, I try to demonstrate how to create sequence of numbers using Microsoft SQL Server Managemement Studio without using Transact-SQL Create Sequence command.
Open SQL Server Management Studio client tool.
Connect to a Microsoft SQL Server 2012 CTP 1, Denali instance.
Drill through a SQL Server database.
Open Programmability node.
Right click on Sequences node as shown in below screenshot.
Select New Sequence... menu item.
When SQL Server Management Studio (SSMS) New Sequence screen is displayed, sql developers can configure a numerous number sequence properties.
The Sequence name has a default value with date, time and seconds like "Sequence-20101113-230829".
You can change Sequence Name as you wish.
A Sequence Schema should be selected. "dbo" schema is default.
Data Type of numbers in sequence is also a required parameter. "int" data type is default.
For sequence of numbers, the number types can be one of the following data types :
tinyint,
smallint,
int,
bigint,
decimal, and
numeric
Increment By value is 1 by default. But you can alter increment by value according to your requirements.
Also Minimum value and Maximum value of a sequence can be specified during sequence creation process.
Minimum value and Maximum value of a sequence of numbers is shown in below table.
Note that for decimal and numeric data types, sql developers can change precision value which affects the minimum and maximum values of sequence of numbers.
| Data Type | Minimum Value | Maximum Value |
| tinyint | 0 | 255 |
| smallint | -32768 | 32767 |
| int | -2147483648 | 2147483647 |
| bigint | -9223372036854775808 | 9223372036854775807 |
| decimal | -999999999999999999 | 999999999999999999 |
| numeric | -999999999999999999 | 999999999999999999 |
For sql developers and database administrators who has to CREATE Sequence using t-sql and looking for how to use number sequences in SQL Server, please refer to Create Sequence, NEXT VALUE FOR and sp_sequence_get_range in SQL Server 2012