Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase ««123»»

Sequences III Expand / Collapse
Author
Message
Posted Tuesday, May 01, 2012 8:56 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Thursday, January 31, 2013 8:53 AM
Points: 1,176, Visits: 778
another good question - cheers
Post #1293208
Posted Tuesday, May 01, 2012 9:19 AM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Yesterday @ 12:05 PM
Points: 3,566, Visits: 72,411
marlon.seton (5/1/2012)
I think this question indicates that using default values with a new item like CREATE SEQUENCE isn't a great idea. I, for one, will be defining everything (AS, START WITH, INCREMENT BY, MINVALUE, MAXVALUE, CYCLE, CACHE) if I get round to using sequences in SQLServer. I know it might look wordy and take up a few more lines but for the sake of clarity, and potential help in solving problems, I would think it worth it.


Defaults aren't bad... a default sequence is a bigint starting at -9,223,372,036,854,775,808 and counting up.

In this case the qotd started at the bottom end of the int and counted down.




--Mark Tassin
MCITP - SQL Server DBA
Proud member of the Anti-RBAR alliance.
For help with Performance click this link
For tips on how to post your problems
Post #1293224
Posted Tuesday, May 01, 2012 9:22 AM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Yesterday @ 12:05 PM
Points: 3,566, Visits: 72,411
Toreador (5/1/2012)
Does anyone have any suggestions for possible uses for sequences? Once upon a time I'd have used them for populating primary keys instead of using identities (ie the way I used to in Oracle). But now that we have the Output clause to easily read back the generated values there doesn't seem much advantage in using a sequence instead.


a loosely coupled multi-sql server system could use these....

define the sequence on each server in a way that they never collide. Then you could write your own "merge" code to replicate changes in the manner you describe.

Other than something like that.... no clue... personally I prefer identities.




--Mark Tassin
MCITP - SQL Server DBA
Proud member of the Anti-RBAR alliance.
For help with Performance click this link
For tips on how to post your problems
Post #1293229
Posted Tuesday, May 01, 2012 11:14 AM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Yesterday @ 5:35 PM
Points: 3,148, Visits: 1,899
Thanks for the question. Not sure if we will ever use this feature but it is good to know how it works anyway.
Post #1293338
Posted Tuesday, May 01, 2012 11:39 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Today @ 6:34 AM
Points: 1,242, Visits: 1,591
mtassin (5/1/2012)
marlon.seton (5/1/2012)
I think this question indicates that using default values with a new item like CREATE SEQUENCE isn't a great idea. I, for one, will be defining everything (AS, START WITH, INCREMENT BY, MINVALUE, MAXVALUE, CYCLE, CACHE) if I get round to using sequences in SQLServer. I know it might look wordy and take up a few more lines but for the sake of clarity, and potential help in solving problems, I would think it worth it.


Defaults aren't bad... a default sequence is a bigint starting at -9,223,372,036,854,775,808 and counting up.

In this case the qotd started at the bottom end of the int and counted down.


This QotD (and Sequences I and II) were written to highlight specific defaults for sequences. No, defaults in and of themselves aren't bad -- indeed with sequences Microsoft has done an OK job of ensuring that if you rely on defaults, things won't break too badly.

The problem comes in when you specify some values but leave others at default, as in this scenario, or when you haven't used a feature for a while, and think a default is A when it's actually B.

If you explicitly define each value, you not only can't easily make that kind of mistake, but you've also documented the structure completely in one place rather than relying on documentation elsewhere. This reduces the chance that someone else (a coworker or even a replacement) could misunderstand what the sequence is doing.
Post #1293359
Posted Tuesday, May 01, 2012 12:21 PM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Friday, October 18, 2013 6:12 AM
Points: 951, Visits: 552
+1
Post #1293391
Posted Tuesday, May 01, 2012 12:24 PM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Yesterday @ 5:58 AM
Points: 1,882, Visits: 1,859
sknox (5/1/2012)
... you've also documented the structure completely in one place rather than relying on documentation elsewhere. This reduces the chance that someone else (a coworker or even a replacement) could misunderstand what the sequence is doing.


I don't mind documenting things for a coworker. Documenting for a replacement though, may be a little bit lower on my priority list.
Post #1293393
Posted Tuesday, May 01, 2012 12:50 PM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Yesterday @ 12:05 PM
Points: 3,566, Visits: 72,411
sknox (5/1/2012)


The problem comes in when you specify some values but leave others at default, as in this scenario, or when you haven't used a feature for a while, and think a default is A when it's actually B.

If you explicitly define each value, you not only can't easily make that kind of mistake, but you've also documented the structure completely in one place rather than relying on documentation elsewhere. This reduces the chance that someone else (a coworker or even a replacement) could misunderstand what the sequence is doing.


Or they can open up SSMS right cilck on the sequence and go to properties and see more information than you'll get anywhere.

Edit: I should qualify that... you could run a series of queries to get the info that right clicking and selecting properties does. Near as I can tell it takes 3 separate queries. It would be nice if sp_help would give all the information. Kind of sad that it doesn't.




--Mark Tassin
MCITP - SQL Server DBA
Proud member of the Anti-RBAR alliance.
For help with Performance click this link
For tips on how to post your problems
Post #1293426
Posted Tuesday, May 01, 2012 2:06 PM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Today @ 6:34 AM
Points: 1,242, Visits: 1,591
mtassin (5/1/2012)
sknox (5/1/2012)


The problem comes in when you specify some values but leave others at default, as in this scenario, or when you haven't used a feature for a while, and think a default is A when it's actually B.

If you explicitly define each value, you not only can't easily make that kind of mistake, but you've also documented the structure completely in one place rather than relying on documentation elsewhere. This reduces the chance that someone else (a coworker or even a replacement) could misunderstand what the sequence is doing.


Or they can open up SSMS right cilck on the sequence and go to properties and see more information than you'll get anywhere.

Edit: I should qualify that... you could run a series of queries to get the info that right clicking and selecting properties does. Near as I can tell it takes 3 separate queries. It would be nice if sp_help would give all the information. Kind of sad that it doesn't.


...IF they have SSMS or another query interface available to them...
Post #1293495
Posted Tuesday, May 01, 2012 4:27 PM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Today @ 6:25 AM
Points: 20,455, Visits: 14,065
Sweet



Jason AKA CirqueDeSQLeil
I have given a name to my pain...
MCM SQL Server


SQL RNNR

Posting Performance Based Questions - Gail Shaw
Posting Data Etiquette - Jeff Moden
Hidden RBAR - Jeff Moden
VLFs and the Tran Log - Kimberly Tripp
Post #1293544
« Prev Topic | Next Topic »

Add to briefcase ««123»»

Permissions Expand / Collapse