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.

Reference: Pinal Dave (http://blog.SQLAuthority.com)

About these ads

20 thoughts on “SQL SERVER – Denali – SEQUENCE is not IDENTITY

  1. 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!

  2. 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

  3. 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

  4. 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.

  5. 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.

  6. 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.

  7. 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

  8. 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..

  9. 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

  10. 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.

  11. Pingback: SQL SERVER – Understanding Identity Beyond its Every Increasing Nature – Quiz – Puzzle – 3 of 31 « SQL Server Journey with SQL Authority

  12. 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.

  13. Pingback: SQL SERVER – Weekly Series – Memory Lane – #014 « SQL Server Journey with SQL Authority

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s