Home
> SQL Server 2012 > SQL Server 2012 (DENALI) TSQL – New Feature | SEQUENCES
SQL Server 2012 (DENALI) TSQL – New Feature | SEQUENCES
As per MS BOL 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.
--// Create a simple Sequence CREATE SEQUENCE CountBy1 START WITH 1 INCREMENT BY 1 ; GO -- Assign new ID sorted by First Name: SELECT BusinessEntityID, FirstName, MiddleName, LastName, NEXT VALUE FOR CountBy1 OVER(ORDER BY FirstName) as New_ID FROM Person.Person; GO -- If you run the above query again, it won't start from 1 but starts after the max value it ended in its first run. --// Create a Cyclic Sequence CREATE SEQUENCE CountBy5 AS tinyint START WITH 1 INCREMENT BY 1 MINVALUE 1 MAXVALUE 5 CYCLE ; GO -- Assign Persons to 5 different sections: SELECT BusinessEntityID, FirstName, MiddleName, LastName, NEXT VALUE FOR CountBy5 OVER(ORDER BY FirstName, LastName) as New_Section FROM Person.Person; GO --// Final cleanup DROP SEQUENCE CountBy1 DROP SEQUENCE CountBy5 GO
More on MSDN: http://technet.microsoft.com/en-us/library/ff878058%28SQL.110%29.aspx
Advertisement
Categories: SQL Server 2012
Tags: CREATE SEQUENCE, Cyclic Sequence, Denali, NEXT VALUE FOR, OVER, SQL Server 2012
Comments (0)
Trackbacks (2)
Leave a comment
Trackback
-
November 19, 2012 at 12:36 amPassed 70-461 Exam : Querying Microsoft SQL Server 2012 « SQL with Manoj
-
April 23, 2013 at 5:48 pmIssue with the new SEQUENCE feature in SQL Server 2012 … and its workaround | SQL with Manoj
ETL with Azure: Buy here
Apache Spark books:
- Spark: The Definitive Guide
- Learning Spark 2Ed
SQL Server - Certification exam books:
- TSQL: 70-761 , 70-762
- DBA: 70-764 , 70-765
- DW/BI: 70-767
Apache Spark books:
- Spark: The Definitive Guide
- Learning Spark 2Ed
SQL Server - Certification exam books:
- TSQL: 70-761 , 70-762
- DBA: 70-764 , 70-765
- DW/BI: 70-767
SQL Tags
annual report Apache Spark APPLY Operator Azure Databricks BCP Clustered Index ColumnStore Index Cpp CROSS APPLY Databricks Denali Download SQL Server Excel Exception Handling FileTables Graphics in Cpp Hekaton Hekaton 2014 IDENTITY In-Memory Tables Install SQL Server Java Applet Programs Java Basic Programs Java File Handling Java IO Programs Java OO Programs JSON JSON SQL Linked Server msdb MSDN TSQL forum multiple values to Stored Procedures PRIMARY KEY Python Runnable Interface sp_configure SQL Agent SQL Server 2012 SQL Server 2014 SQL Server 2016 SQL Server 2017 SQL Server Certification SQL Server Interview Questions SQL Server on Linux Stored Procedure Temporary Tables TRY-CATCH WordPress Annual Report XML XML SQLCategories
- Big Data (13)
- Apache Spark (6)
- Hadoop (5)
- DBA Stuff (37)
- Interview Q (8)
- Microsoft Azure (37)
- Azure Data Factory (1)
- Azure DevOps (2)
- Cosmos DB (5)
- Databricks (7)
- MS BI (9)
- Analysis Services (1)
- DW and BI (1)
- Integration Services (1)
- Power BI (4)
- Reporting Services (2)
- Tabular Model (1)
- Others (205)
- Blockchain (1)
- Certifications (9)
- Cloud Computing (1)
- Cpp (32)
- Cpp Graphics (21)
- Excel (10)
- Informatica (5)
- Java (56)
- Linux (8)
- Microsoft (15)
- MS.net (2)
- Oracle (1)
- Powershell (3)
- Python (6)
- Reviews (5)
- Security (1)
- Spark SQL (3)
- SQL Server (9)
- SQLwithManoj (12)
- Uncategorized (7)
- VBA Macro (1)
- Visual Studio (1)
- Windows (6)
- SQL Server Conferences (1)
- SQL Server Internals (85)
- Datatypes (5)
- DB Concepts (28)
- Indexes (11)
- JOINS (4)
- ML Python (2)
- SQL DB Engine (8)
- SQL Server Migration (1)
- SQL Server Upgrade (1)
- Stored Procedures (11)
- tempdb (3)
- XML (16)
- SQL Server Questions (41)
- SQL Server Versions (137)
- SQL Azure (4)
- SQL Server 2005 (7)
- SQL Server 2008 (5)
- SQL Server 2012 (33)
- SQL Server 2014 (18)
- SQL Server 2016 (50)
- SQL Server 2017 (20)
- SQL Server 2019 (2)
- SQL Server 2022 (1)
- SQL Server Express (1)
- T SQL (184)
- Differences (31)
- JSON (6)
- Misconception (7)
- Optimization Performance (14)
- SQL Basics (15)
- SQL Built-in Functions (6)
- SQL Errors (45)
- SQL Tips (67)
- SQL Trivia (9)
Archives
Top Posts
- SQL Error - The server may be running out of resources, or the assembly may not be trusted with PERMISSION_SET = EXTERNAL_ACCESS or UNSAFE
- Reading JSON string with Nested array of elements | SQL Server 2016 - Part 3
- Python error: while converting Pandas Dataframe or Python List to Spark Dataframe (Can not merge type)
- Using IDENTITY function with SELECT statement in SQL Server
- SQL Error - "SELECT TOP 100" throws error in SparkSQL - what's the correct syntax?
- Databricks Notebook error: Your administrator has only allowed sql and scala commands on this cluster.
- SQL Error - SQL Server blocked access to procedure 'dbo.sp_send_dbmail' of component 'Database Mail XPs'
- Spark - Cannot perform Merge as multiple source rows matched...
- SQL DBA - Windows could not start the SQL Server... refer to service-specific error code 17051 - SQL Server Evaluation period has expired
- Difference between Index and Primary Key - MSDN TSQL forum
Blog Stats, since Aug 2010
- 4,986,652 hits
StatCounter …since April 2012

Leisure blog: Creek & Trails
- An error has occurred; the feed is probably down. Try again later.
Disclaimer
This is my personal blog site.
The opinions expressed here represent my own and not those of my employer. For accuracy and official reference refer to MS Books On Line and/or MSDN/TechNet.
My employer do not endorse any tools, applications, books, or concepts mentioned on the blog. I have documented my personal experience on this blog.
Join 802 other subscribers