SQL Server 2012 Sequences in VSTS projects

  • Article

This post is intended to be a quick reference for creating and using Sequences in SQL Server 2012, as authored in a Visual Studio 2010 project. It's really pretty simple, but there are a few things to do to make it work properly. While this article discusses Visual Studio 2010, it can easily be extended to Visual Studio 2012. For reference to what Sequences are, beyond the implementation described here, refer to the MSDN documentation at https://msdn.microsoft.com/en-us/library/ff878058.aspx .

In this example, we are implementing a primary key using a Sequence as the key generator instead of Identity. As a Sequence does not generate a value for Scope_Identity, I will demonstrate an alternate technique to retrieve the value of the newly inserted key from the output clause of an insert statement, as saved to a temporary table variable.

There are plenty more scenarios to implement Sequences, such as having a unique key distributed among multiple tables. Performance improvements for large volume of inserts are a great use for Sequences. It would be a lengthy discussion to consider when to use Sequences and when not to, so I will defer that topic. I encourage readers to research on the appropriate cache size for their circumstances, I chose 1000 in the example, but that may be a bit large for most needs. Sequences are one of many reasons to upgrade to SQL Server 2012.

1. Ensure that Visual Studio 2010 supports the latest SQL Server 2012 projects.

Install SQL Server Data Tools (SSDT). A quick Bing search for SSDT should find it for you, but current location is at https://msdn.microsoft.com/en-us/data/tools.aspx

2. Create a SQL Server Project (having extension .sqlproj, not the former .dbproj).

See figures 1 and 2. After project creation, go to the properties of the project and ensure that the Target platform is "SQL Server 2012".

3. Create a sequence.

Use VSTS to import the file into the proper location. By following this method, the folder structure will automatically create the file in the proper folder.

  • Create it as a file, outside of the project, with the Sequence create definition See below for an example.
  • Right click the project, and choose Import, then Script, and specify the newly created script file.
  • Go to the Specify Options section, and ensure correct Folder Structure is specified. My preference is for "Schema\Object Type".

Code below is a simple sequence.

 CREATE SEQUENCE dbo.SeqDemoTable1
 AS int
 START WITH 1
 INCREMENT BY 1
 MINVALUE 1
 NO MAXVALUE
 NO CYCLE
 CACHE 1000

4. Associate the Sequence to a table as a default rule.

Code below will create the table and associate the primary key with the Sequence.

 CREATE TABLE dbo.DemoTable1
 (
 Field1ID int NOT NULL CONSTRAINT df_DemoTable1_Field1ID DEFAULT NEXT VALUE FOR dbo.SeqDemoTable1,
 Field2 varchar(10) NULL,
 Field3 varchar(10) NULL
 )
 GO
 ALTER TABLE dbo.DemoTable1
 ADD CONSTRAINT PK_DemoTable1_Field1ID PRIMARY KEY CLUSTERED 
 (
 Field1ID ASC
 )

 5. Insert to a table and verify Sequence values unique and get latest value inserted.

 -- Cannot use scope_identity for Sequences.
 -- Instead, trap the Primary Key (Sequence) into a table.
 DECLARE @DemoTable1 table (Field1ID int) 
 DECLARE @LatestInsertKey int
 --Insert two records in a single statement
 INSERT INTO dbo.DemoTable1(Field2, Field3)
 OUTPUT INSERTED.Field1ID INTO @DemoTable1 
 VALUES
 ('ABC','DEF')
 , ('GHI','JKL')
 -- Get latest value if multiple inserts.
 --If single insert can be assumed, not required to use MAX function.
 SELECT @LatestInsertKey = MAX(Field1ID) FROM @DemoTable1
 -- Verify results for Latest Insert Key
 SELECT @LatestInsertKey AS [@LatestInsertKey]
 -- Verify results for Sequence generation
 SELECT * FROM dbo.DemoTable1

 

FIGURE 1 - PROJECT CREATION

FIGURE 2 - PROJECT PROPERTIES

FIGURE 3 - IMPORT SQL SCRIPT TO PROJECT

FIGURE 4 - PROJECT STRUCTURE