April 12, 2012 at 9:39 pm
Comments posted to this topic are about the item Sequences in SQL Server 2012
April 13, 2012 at 12:46 am
What would have been nice is some real world examples why I should use sequences.
For now it just seems I can use an IDENTITY column as well.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
April 13, 2012 at 2:55 am
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
April 13, 2012 at 7:22 am
You can use them as default values? oh that's cool - I was wondering where this feature would be usable. I can see that coming in handy for sure. Thanks!
April 13, 2012 at 7:24 am
I too was wondering why the sequence thing was such a big deal. Thanks okbangas for your example and explanation. That looks like it could be really useful in certain situations.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
April 13, 2012 at 8:09 am
Yes, this article was just an introduction. I will write a new one named sequences vs IDENTITY.
Personally, I hate the IDENTITY. 😛
April 13, 2012 at 8:12 am
calbimonte.daniel (4/13/2012)
Yes, this article was just an introduction. I will write a new one named sequences vs IDENTITY.Personally, I hate the IDENTITY.
What is wrong with IDENTITY? It is super easy to use and the performance is good. Curious why you feel so strongly about it.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
April 13, 2012 at 8:43 am
prima facie this seems a good way to produce a tally table
I'm also going to have a play to see whether you can have a formula as the increment field. I'm not sure yet just how useful it will be but being able to increment in units of 10, not 1 would certainly give an old school feel to numerical increments.
April 13, 2012 at 9:06 am
Sequences are used to deterministic programming.
A simple Example: EDI documents outbound have unique numbers that are in sequence for EACH customer.
Customer1 receives document 5010, 5011, etc...
Customer2 receives document 2010, 2011, etc...
Each customer would therefore have a sequence when set up for EDI.
No, sequences aren't the only way to do this.
*****
Give a man a fish, and you've given him a meal. Teach a man to fish and he'll ask to borrow your boat.:-P
April 13, 2012 at 9:13 am
I'm also going to have a play to see whether you can have a formula as the increment field. I'm not sure yet just how useful it will be but being able to increment in units of 10, not 1 would certainly give an old school feel to numerical increments.
I don't see why you couldn't just change the INCREMENT BY statement to whatever value you like.
Can you explain more about what you mean by a "tally table"?
April 13, 2012 at 9:23 am
Scott D. Jacobson (4/13/2012)
I'm also going to have a play to see whether you can have a formula as the increment field. I'm not sure yet just how useful it will be but being able to increment in units of 10, not 1 would certainly give an old school feel to numerical increments.
I don't see why you couldn't just change the INCREMENT BY statement to whatever value you like.
Can you explain more about what you mean by a "tally table"?
To coin a phrase from one of the other threadizens, "the tally table is the swiss army knife for sql server".
Jeff Moden has an article that explains it The "Numbers" or "Tally" Table: What it is and how it replaces a loop.[/url]
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
April 13, 2012 at 9:24 am
stephanie.sullivan (4/13/2012)
prima facie this seems a good way to produce a tally tableI'm also going to have a play to see whether you can have a formula as the increment field. I'm not sure yet just how useful it will be but being able to increment in units of 10, not 1 would certainly give an old school feel to numerical increments.
Are you saying you would use this in a loop to create your tally table? See the article I posted above from Jeff. The first part talks about creating the table with no loops.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
April 13, 2012 at 10:08 am
I was more thinking as a way of producing the tally table itself - Jeff uses an identity and cross joins sys columns. This method may or may not be faster but it would read more clearly, which I'll happily trade some performance for.
April 13, 2012 at 11:24 am
stephanie.sullivan (4/13/2012)
I was more thinking as a way of producing the tally table itself - Jeff uses an identity and cross joins sys columns. This method may or may not be faster but it would read more clearly, which I'll happily trade some performance for.
It would definitely be slower but for just creating a tally table it probably doesn't matter all that much. I don't create them frequently because I just keep it permanently.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
April 13, 2012 at 12:12 pm
stephanie.sullivan (4/13/2012)
I was more thinking as a way of producing the tally table itself - Jeff uses an identity and cross joins sys columns. This method may or may not be faster but it would read more clearly, which I'll happily trade some performance for.
The only time I'll make a trade on performance is for accuracy. Comments can be used to explain anything else.
Can you post how you'd make a Tally Table using a Sequence? Seems like a bit of extra work especially considering how comparatively long the code for the WHILE loop would be.
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 15 posts - 1 through 15 (of 19 total)
You must be logged in to reply to this topic. Login to reply