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.

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.

mtassin (5/1/2012)

---

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.

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.

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.

mtassin (5/1/2012)

---

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.