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 (https://blog.sqlauthority.com)
18 Comments. Leave new
Can you give an example on where it would be used?
It could be used for generating Alpha-numeric primary keys .
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!
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?
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
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
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.
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.
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.
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
Query on sysdepends table directly
thank u madhivanan
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..
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
SQL server stores the password in sysxlogins(master database).
Howver SQL Server creates the user’s password hash before storing it
The correct table name is syslogins
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.
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.