How to: Use Microsoft SQL Server 2012 Objects in Your Project
In this example, you will add a sequence object to a database project targeting Microsoft SQL Server 2012.
Sequences are introduced in Microsoft SQL Server 2012. A sequence is a user-defined schema-bound object that generates a sequence of numeric values according to the specification with which the sequence was created. The sequence of numeric values is generated in an ascending or descending order at a defined interval and may cycle (repeat) as requested. For more information on sequence objects, see Sequence Numbers. For information on what’s new in Microsoft SQL Server 2012, see What's New in SQL Server 2012.
|The following procedures utilize entities created in previous procedures in the Connected Database Development and Project-Oriented Offline Database Development sections.|
To add a new Sequence object to your project
Right-click the TradeDev database project in Solution Explorer , select Add , then New Item .
Click Programmability on the left pane, and select Sequence . Click Add to add the new object to the project.
Replace the default code with the following.
CREATE SEQUENCE [dbo].[Seq1] AS INT START WITH 1 INCREMENT BY 1 MAXVALUE 1000 NO CYCLE CACHE 10
If your project’s target platform is not set to Microsoft SQL Server 2012, the Error List will show a syntax error for the
CREATE SEQUENCEstatement. To correct this, follow the How to: Change Target Platform and Publish a Database Project topic to change the target platform accordingly.
Follow the How to: Change Target Platform and Publish a Database Project topic to publish the project to a database in your connected Microsoft SQL Server 2012 server.
To use the new Sequence object
In SQL Server Object Explorer, right-click database you have published to in the previous procedure, and select New Query .
Paste the following code to the query window.
DECLARE @counter INT SET @counter=0 WHILE @counter<10 BEGIN SET @counter = @counter +1 INSERT dbo.Products (Id, Name, CustomerId) VALUES (NEXT VALUE FOR dbo.Seq1, 'ProductItem'+cast(@counter as varchar), 1) END GO
Press the Execute Query button.
In SQL Server Object Explorer , navigate to the Products table in the database. Right-click and select View Data to examine the newly added rows.