SQL SERVER – Denali – SEQUENCE is not IDENTITY

Yesterday I posted blog post on the subject SQL SERVER – 2011 – Introduction to SEQUENCE – Simple Example of SEQUENCE and I received comment where user was not clear about difference between SEQUENCE and IDENTITY.

The reality is that SEQUENCE not like IDENTITY. There is very clear difference between them. Identity is about single column. Sequence is always incrementing and it is not dependent on any table.

Here is the quick example of the same.

USE AdventureWorks2008R2
GO
CREATE SEQUENCE [Seq] AS [int] START WITH 1
INCREMENT
BY 1
MAXVALUE 20000
GO
-- Run five times
SELECT NEXT VALUE FOR Seq AS SeqNumber;
SELECT NEXT VALUE FOR Seq AS SeqNumber;
SELECT NEXT VALUE FOR Seq AS SeqNumber;
SELECT NEXT VALUE FOR Seq AS SeqNumber;
SELECT NEXT VALUE FOR Seq AS SeqNumber;
GO
-- Clean Up
DROP SEQUENCE [Seq] GO

Here is the resultset.

SQL SERVER - Denali - SEQUENCE is not IDENTITY seq1

Reference: Pinal Dave (https://blog.sqlauthority.com)

SQL Scripts
Previous Post
SQL SERVER – Denali – Introduction to SEQUENCE – Simple Example of SEQUENCE
Next Post
SQL SERVER – What is Fill Factor and What is the Best Value for Fill Factor

Related Posts

18 Comments. Leave new

  • Can you give an example on where it would be used?

    Reply
  • It could be used for generating Alpha-numeric primary keys .

    Reply
  • Hi, I am a newbie with SQL 2008, I can’t figure out how to sort a varchar column that has numbers and alpha mixed. I need the alpha to be listed first which contains CO1, CO2, A, B, C, then 1,2,3,
    I tried “order by” and the numbers are showing as 1, 10, 100 and the alpha is listed last. Thank you for any help with this!

    Reply
    • RaviShankarKota
      February 9, 2011 3:19 pm

      Can you provide an example of the same?By the way this concept is not there in earlier versions of sql server.Am i right?

      Reply
  • Srikanth Nallamothu
    January 31, 2011 9:27 am

    hello pinal

    could u please solve my problem

    i declared local variable with VARCHAR(MAX).
    But it stores only 8000 characters.what to do to store large amount of text into a varable.

    ex:
    DECLARE @VAR VARCHAR(MAX)
    SET @X = ‘give more than 8000 chars’
    PRINT @X

    Reply
  • Animesh Upadhyay
    January 31, 2011 2:26 pm

    Hi Srikanth,

    Varchar data type can be defined in two ways:
    1) VARCHAR(n) where max value of “n” can be 8000
    2) VARCHAR(max) where “max” indicates that the maximum storage size is 2^31-1 bytes. The storage size is the actual length of data entered + 2 bytes.

    For ex:
    — Created temporary table #test in temp database
    create table #test (test varchar(max))

    –Inserted 8010 character in column test
    insert into #test values (REPLICATE(‘a’,8010))

    — Stores more than 8000 char
    select * from #test

    Drop table #test

    Regards,
    Animesh

    Reply
  • Good to know that SEQUENCE has been introduced in Denali, i think this concept existed in oracle before. The SEQUENCE can be used for key generation type scenarios.

    Reply
  • We had a postgres database once upon a time and it had the sequence ability. I always wondered why SQL never had it. With all that said, could we see sequence as an identity replacement in certain databases? It offers a unique id for a row not only across the table itself but across all tables. That may provide for some benefits on the application side.

    Reply
  • Hi Pinal,
    I am facing an issues in Bulk Insert with Stored Procedure. I am comparing two xml documents, based on comparison i am creating a valid xml document. After all comparisons the resultant xml should be places on sql server. here i am dealing with llarge xml files and application performance also important. please give some idea.

    Reply
  • Srikanth Nallamothu
    February 8, 2011 3:39 pm

    CAN ANY ONE GIVE THE SOLUTION FOR THIS…
    After alter the column in table or drop the table AND RECREATE IT .. sp_depends doesn’t show THE DEPENDES PLEASE GIVE ME THE SOLUTION

    Reply
  • Srikanth_Nallamothu
    February 11, 2011 5:05 pm

    but i want sp name because i want to execute the query with short cut. goto tools -> options -> keyboard -> place ths sp name on the corresponding short cut and click ok..

    advantage of this short cut is u can write the table name and select it and hold u r shortcut key..

    Reply
  • Hello sir,
    Yesterday i was in a interview & she asked me question that where the SQL Server save the password ?? Kindly answer this sir.

    Thanks in advance.

    vijay sharma

    Reply
  • SQL server stores the password in sysxlogins(master database).
    Howver SQL Server creates the user’s password hash before storing it

    Reply
  • julian castiblanco
    May 18, 2011 3:01 am

    You’re my master PinalDave. How do you think about HADR in denali??

    For me, It’s like HA with cluster mixed with HA with Database Mirroring.

    Reply
  • Hi,

    CREATE SEQUENCE [Seq]
    AS [int]
    START WITH 1
    INCREMENT BY 1
    MAXVALUE 20000
    GO
    While trying to run the above sql I got the following error..
    Msg 343, Level 15, State 1, Line 1
    Unknown object type ‘SEQUENCE’ used in a CREATE, DROP, or ALTER statement.

    Reply

Leave a Reply