title | description | author | ms.author | ms.date | ms.service | ms.subservice | ms.topic |
---|---|---|---|---|---|---|---|
SQL Server 2012 Objects in Your Project |
Become familiar with SQL Server 2012 sequences. See how to add these objects to database projects and use them in queries. |
markingmyname |
maghan |
02/09/2017 |
sql |
ssdt |
conceptual |
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.
Warning
The following procedure uses entities created in previous procedures in the Project-Oriented Offline Database Development sections.
-
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 SEQUENCE
statement. 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.
-
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.