June 3, 2012 at 10:23 pm
Comments posted to this topic are about the item The Sequence Object in SQL Server 2012
June 4, 2012 at 12:02 am
[font="Verdana"]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![/font]
June 4, 2012 at 12:05 am
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.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
June 4, 2012 at 12:23 am
Thank you for the article. Some real world examples would be useful. It is not clear what sequence objects are actually for.
June 4, 2012 at 3:34 am
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.
June 4, 2012 at 3:45 am
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
June 4, 2012 at 3:47 am
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
June 4, 2012 at 4:38 am
As many posters have said above, the article doesn't really provide the real world example on where to use sequences.
June 4, 2012 at 4:54 am
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
[font="Verdana"]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 😀
Thanks ![/font]
June 4, 2012 at 7:52 am
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.
June 4, 2012 at 7:53 am
danl-971512 (6/4/2012)
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.
And IDENTITY is better in this respect how...?
June 4, 2012 at 11:33 am
sknox (6/4/2012)
danl-971512 (6/4/2012)
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.And IDENTITY is better in this respect how...?
Identitiy is for a single table only, not shared between tables. Secondly, if you use caching to reduce IO, you will get more (or bigger) gaps of values each time the instance is restarted, eg when the server is rebooted.
June 4, 2012 at 11:38 am
Abrar Ahmad_ (6/4/2012)
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 😀
Updated my post to include output.
June 4, 2012 at 5:52 pm
okbangas (6/4/2012)
sknox (6/4/2012)
danl-971512 (6/4/2012)
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.And IDENTITY is better in this respect how...?
Identitiy is for a single table only, not shared between tables. Secondly, if you use caching to reduce IO, you will get more (or bigger) gaps of values each time the instance is restarted, eg when the server is rebooted.
For one, you don't HAVE to share a sequence between tables. You can if you'd like, or you can create a sequence for each table.
For two, you do not lose any values when the instance is restarted properly. You only lose values when the server shuts down abnormally, or when an application reserves a range but doesn't use all of it. Read the cache management section of http://msdn.microsoft.com/en-us/library/ff878091.aspx for more detail.
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply