SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


The Sequence Object in SQL Server 2012


The Sequence Object in SQL Server 2012

Author
Message
Sanil Mhatre
Sanil Mhatre
SSC Rookie
SSC Rookie (25 reputation)SSC Rookie (25 reputation)SSC Rookie (25 reputation)SSC Rookie (25 reputation)SSC Rookie (25 reputation)SSC Rookie (25 reputation)SSC Rookie (25 reputation)SSC Rookie (25 reputation)

Group: General Forum Members
Points: 25 Visits: 85
Comments posted to this topic are about the item The Sequence Object in SQL Server 2012
Abrar Ahmad_
Abrar Ahmad_
SSCommitted
SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)

Group: General Forum Members
Points: 1726 Visits: 1326
Thanks for the brief and meaningful article!

Its long since MS-SQL Server has defended IDENTITY as a close alternative of Sequences objects, available in Oracle RDBMS.
Now what was the actual requirement to implement Sequences in SQL Server as well?
It will be great if any legitimate usefulness can be discussed here, other than the mere standards etc requirements.

Many thanks!

Koen Verbeeck
Koen Verbeeck
SSC Guru
SSC Guru (124K reputation)SSC Guru (124K reputation)SSC Guru (124K reputation)SSC Guru (124K reputation)SSC Guru (124K reputation)SSC Guru (124K reputation)SSC Guru (124K reputation)SSC Guru (124K reputation)

Group: General Forum Members
Points: 124359 Visits: 13344
This is another article about Sequences that just repeats everything on MSDN.
What I really would like to see is an article showing us what we can actuallo DO with the sequence object.


How to post forum questions.
Need an answer? No, you need a question.
What’s the deal with Excel & SSIS?
My blog at SQLKover.

MCSE Business Intelligence - Microsoft Data Platform MVP
EKD
EKD
SSC-Enthusiastic
SSC-Enthusiastic (192 reputation)SSC-Enthusiastic (192 reputation)SSC-Enthusiastic (192 reputation)SSC-Enthusiastic (192 reputation)SSC-Enthusiastic (192 reputation)SSC-Enthusiastic (192 reputation)SSC-Enthusiastic (192 reputation)SSC-Enthusiastic (192 reputation)

Group: General Forum Members
Points: 192 Visits: 126
Thank you for the article. Some real world examples would be useful. It is not clear what sequence objects are actually for.
hakkie42
hakkie42
Valued Member
Valued Member (63 reputation)Valued Member (63 reputation)Valued Member (63 reputation)Valued Member (63 reputation)Valued Member (63 reputation)Valued Member (63 reputation)Valued Member (63 reputation)Valued Member (63 reputation)

Group: General Forum Members
Points: 63 Visits: 104
Agreed with the previous posters.

It's not so hard to go through MSDN looking for how to use things, the reason why one should use things is much more important.
Real world arguments/examples would help here.
okbangas
okbangas
SSCertifiable
SSCertifiable (5.5K reputation)SSCertifiable (5.5K reputation)SSCertifiable (5.5K reputation)SSCertifiable (5.5K reputation)SSCertifiable (5.5K reputation)SSCertifiable (5.5K reputation)SSCertifiable (5.5K reputation)SSCertifiable (5.5K reputation)

Group: General Forum Members
Points: 5519 Visits: 1387
Since this is more or less a copy of a previous article, I chose to copy my comment from that article:

First of all, I don't think this article is comprehensive enough, and as pointed out, it does not explain when to use (or not use) sequences. I've seen test showing that identity is outperforming sequence, so you should obviously not use a sequence when you simply could use an identity column in your table. However, identity is limited to a single table, whereas sequences can be shared across multiple tables. There are numerous cases where you want a unique identifier across tables in the same database. Prior to SQL Server 2012 you would have to manage this yourself, use an identity column in a shared "identity-table", or use uniqueidentifiers (GUIDs), which have several drawbacks. In these cases, a sequence is a far better choice in my opinion.

What is not mentioned in the article, but the MSDN article, is that you could use the next value for statement as default value in a table. The following simple code shows two tables sharing a sequence:

use tempdb;
go

create sequence MyIDs as int
start with 1
increment by 1
no cycle;
go

create table MyTable (
id int default next value for MyIDs,
value varchar(50)
);

create table MySecondTable (
id int default next value for MyIDs,
value varchar(50)
);

insert into MyTable (value) values ('Test');
insert into MySecondTable (value) values ('Test2');

select * from MyTable
select * from MySecondTable
go

drop table MyTable;
drop table MySecondTable;
drop sequence MyIDs



output:
id          value
----------- --------------------------------------------------
1 Test

id value
----------- --------------------------------------------------
2 Test2





Ole Kristian Velstadbråten Bangås - Virinco - Facebook - Twitter

Concatenating Row Values in Transact-SQL
danl-971512
danl-971512
SSC Journeyman
SSC Journeyman (95 reputation)SSC Journeyman (95 reputation)SSC Journeyman (95 reputation)SSC Journeyman (95 reputation)SSC Journeyman (95 reputation)SSC Journeyman (95 reputation)SSC Journeyman (95 reputation)SSC Journeyman (95 reputation)

Group: General Forum Members
Points: 95 Visits: 26
My two cents: Microsoft has a lot of conversions that happen throughout the year from Oracle to SQLServer. The transition for Oracle folks to get to SQLServer is made easier by having a sequence object.

However, in my opinion, this is a travesty. Separate Sequence Objects have long been the bane of existence for high speed, fully parallel bulk & batch loading paradigms. To introduce this to SQLServer raises serious concerns in these areas.

It also makes it easier to "blow the lid" off of big-int based sequences especially if a single sequence is used in large or huge tables. I hope Microsoft thought about mitigation strategies for all of these things, including clusters, and parallel servers, and bulk loaders. If they didn't think about this, then those projects that take on the use of Sequence Objects rather than identity columns will struggle to adapt as their volumes grow, and their parallelism needs increase.

I've long thought that Oracle should be the one to change, to add identity columns, rather than the other way around. If you look at other major RDBMS players, they pretty much all have identity columns these days: MySQL, Teradata, DB2 UDB, and so on.

But this is just my opinion, however the article is good coverage on a Sequence Object.

Thank-you,
Dan Linstedt
Divine Flame
Divine Flame
SSCrazy Eights
SSCrazy Eights (8.1K reputation)SSCrazy Eights (8.1K reputation)SSCrazy Eights (8.1K reputation)SSCrazy Eights (8.1K reputation)SSCrazy Eights (8.1K reputation)SSCrazy Eights (8.1K reputation)SSCrazy Eights (8.1K reputation)SSCrazy Eights (8.1K reputation)

Group: General Forum Members
Points: 8127 Visits: 2816
As many posters have said above, the article doesn't really provide the real world example on where to use sequences.


Sujeet Singh
Abrar Ahmad_
Abrar Ahmad_
SSCommitted
SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)

Group: General Forum Members
Points: 1726 Visits: 1326
okbangas (6/4/2012)


use tempdb;
go

create sequence MyIDs as int
start with 1
increment by 1
no cycle;
go

create table MyTable (
id int default next value for MyIDs,
value varchar(50)
);

create table MySecondTable (
id int default next value for MyIDs,
value varchar(50)
);

insert into MyTable (value) values ('Test');
insert into MySecondTable (value) values ('Test2');

select * from MyTable
select * from MySecondTable
go

drop table MyTable;
drop table MySecondTable;
drop sequence MyIDs



If possible kindly share the output of the code segment and explain a bit, because we are still on the place of having no sequences in SQL SERVER :-D

Thanks !

Sanil Mhatre
Sanil Mhatre
SSC Rookie
SSC Rookie (25 reputation)SSC Rookie (25 reputation)SSC Rookie (25 reputation)SSC Rookie (25 reputation)SSC Rookie (25 reputation)SSC Rookie (25 reputation)SSC Rookie (25 reputation)SSC Rookie (25 reputation)

Group: General Forum Members
Points: 25 Visits: 85
Thank You for all of your feedback. I do see how real life usage example for Sequences can prove useful here. I will try to post some examples here or may be write a new article covering this particular area of interest. I really appreciate all of the constructive criticism here and it will definitely help me write better articles in the future. Thanks.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum







































































































































































SQLServerCentral


Search