SQLServerCentral / Article Discussions / Article Discussions by Author / Discuss content posted by sknox / Sequences III / Latest Posts

RE: Sequences III

Dineshbabu — Wed, 09 Jan 2013 05:50:34 GMT

Still i didn't get a chance to work in 2012, But keeping property of IDENTITY and minimum value of INT in mind I answered and luckily i got it correct. I don't know what value will be returned CYCLE is mentioned.Thanks for posting 2012 feature..

RE: Sequences III

Britt Cluff — Thu, 17 May 2012 20:02:27 GMT

Good question. Thanks for submitting.

RE: Sequences III

Iulian -207023 — Tue, 08 May 2012 02:06:42 GMT

thanks, easy oneIulian

RE: Sequences III

Rose Bud — Fri, 04 May 2012 07:39:31 GMT

This series of questions has given me a good understanding of sequences. I hope I remember this if/when my company ever upgrades to SQL Server 2012. Thanks for the intro to new features!

RE: Sequences III

Koen Verbeeck — Tue, 01 May 2012 23:33:18 GMT

Nice question. A bit easy, as I know practically the syntax for Sequence by heart after the first two questions :-)

RE: Sequences III

SQLRNNR — Tue, 01 May 2012 16:27:11 GMT

Sweet:cool:

RE: Sequences III

sknox — Tue, 01 May 2012 14:06:21 GMT

[quote][b]mtassin (5/1/2012)[/b][hr][quote][b]sknox (5/1/2012)[/b][hr]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.[/quote]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.[/quote]...IF they have SSMS or another query interface available to them...

RE: Sequences III

mtassin — Tue, 01 May 2012 12:50:50 GMT

[quote][b]sknox (5/1/2012)[/b][hr]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.[/quote]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.

RE: Sequences III

Dave62 — Tue, 01 May 2012 12:24:19 GMT

[quote][b]sknox (5/1/2012)[/b][hr]... 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.[/quote]I don't mind documenting things for a coworker. Documenting for a replacement though, may be a little bit lower on my priority list. :Whistling:

RE: Sequences III

Stephen_W_Dodd — Tue, 01 May 2012 12:21:40 GMT

RE: Sequences III

sknox — Tue, 01 May 2012 11:39:23 GMT

[quote][b]mtassin (5/1/2012)[/b][hr][quote][b]marlon.seton (5/1/2012)[/b][hr]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.[/quote]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.[/quote]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.

RE: Sequences III

KWymore — Tue, 01 May 2012 11:14:13 GMT

Thanks for the question. Not sure if we will ever use this feature but it is good to know how it works anyway.

RE: Sequences III

mtassin — Tue, 01 May 2012 09:22:34 GMT

[quote][b]Toreador (5/1/2012)[/b][hr]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.[/quote]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.

RE: Sequences III

mtassin — Tue, 01 May 2012 09:19:12 GMT

[quote][b]marlon.seton (5/1/2012)[/b][hr]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.[/quote]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.

RE: Sequences III

OzYbOi d(-_-)b — Tue, 01 May 2012 08:56:23 GMT

another good question - cheers

RE: Sequences III

EL Jerry — Tue, 01 May 2012 07:59:44 GMT

Thanks for another SEQUENCEs question, I think I now understand how they work.I agree with Marlon that defaults should not be used in sequences, as it is harder to debug when there is a problem."El" Jerry

RE: Sequences III

marlon.seton — Tue, 01 May 2012 05:56:48 GMT

[quote][b]Toreador (5/1/2012)[/b][hr]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.[/quote]Our software has to run on Oracle and SQLServer. At the moment, the modules that use sequences are Oracle only but if we port those modules to SQLServer we would use sequence there, too, so as to keep the code as similar as possible.

RE: Sequences III

Toreador — Tue, 01 May 2012 05:49:03 GMT

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.

RE: Sequences III

Michael Lysons — Tue, 01 May 2012 05:38:22 GMT

I like these sequence questions, despite the fact I keep getting them wrong :-D

RE: Sequences III

Thomas Abraham — Tue, 01 May 2012 05:37:28 GMT

Argh! Blew right past the CYCLE/NO CYCLE option explanation in BOL, looking for behavior when min value exceeded. SHould have had that first cup of green tea.Thanks for the question! I've really enjoyed these questions on CREATE SEQUENCE. I look forward to using it when my company moves to 2012 ........ in 2021!

RE: Sequences III

L' Eomot Inversé — Tue, 01 May 2012 03:09:54 GMT

Nice question. Easy if one recognises the decimal representation of 2**31.

RE: Sequences III

marlon.seton — Tue, 01 May 2012 01:50:10 GMT

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.

RE: Sequences III

Revenant — Mon, 30 Apr 2012 22:51:13 GMT

Thanks for a really easy one!(I wish I came up with it...)

RE: Sequences III

bitbucket-25253 — Mon, 30 Apr 2012 20:30:19 GMT

Nice question - finally beginning to understand what a "Sequence", is and how it works ..........THANKS

Sequences III

sknox — Mon, 30 Apr 2012 20:29:04 GMT

Comments posted to this topic are about the item [B]Sequences III[/B]