Sequences I

Question

Post reply

Sequences I

sknox

SSChampion

Points: 12388
More actions
April 16, 2012 at 10:27 pm

#267455

Comments posted to this topic are about the item Sequences I
April 17, 2012 at 12:42 am

This was removed by the editor as SPAM

Viewing 15 posts - 1 through 15 (of 36 total)

You must be logged in to reply to this topic. Login to reply

Henrico Bekker One Orange Chip Points: 27652 More actions · Answer 1

got it wrong, as the correct answer wasn't listed.

your syntax is wrong in, sequence wouldn't be created to start of with...no increment value, no 'AS'..etc.

-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
This thing is addressing problems that dont exist. Its solution-ism at its worst. We are dumbing down machines that are inherently superior. - Gilfoyle

Koen Verbeeck SSC Guru Points: 259085 More actions · Answer 2

Henrico Bekker (4/16/2012)
got it wrong, as the correct answer wasn't listed.
your syntax is wrong in, sequence wouldn't be created to start of with...no increment value, no 'AS'..etc.

When I look at BOL I see the following:

CREATE SEQUENCE [schema_name . ] sequence_name
[ AS [ built_in_integer_type | user-defined_integer_type ] ]
[ START WITH <constant> ]
[ INCREMENT BY <constant> ]
[ { MINVALUE [ <constant> ] } | { NO MINVALUE } ]
[ { MAXVALUE [ <constant> ] } | { NO MAXVALUE } ]
[ CYCLE | { NO CYCLE } ]
[ { CACHE [ <constant> ] } | { NO CACHE } ]
[ ; ]

This means that everything is optional except the first line. MSDN also lists every default value for every line.

edit: Example E in the MSDN article is exactly the same as the QotD.

Anyway, awesome question. Really like the SQL Server 2012 questions!

Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP

Carlo Romagnano SSC-Insane Points: 22597 More actions · Answer 3

I am curious to know how many developpers will use default value for "START WITH". In most cases will be a negative number.

Koen Verbeeck SSC Guru Points: 259085 More actions · Answer 4

Carlo Romagnano (4/17/2012)
I am curious to know how many developpers will use default value for "START WITH". In most cases will be a negative number.

They will all be negative, except for tinyint.

In most cases this shouldn't be a problem, unless you are using the sequence for generating surrogate keys and you have tied special meaning to some of them.

For example: 0 equals unknown, -1 equals not applicable.

Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP

Carlo Romagnano SSC-Insane Points: 22597 More actions · Answer 5

Koen Verbeeck (4/17/2012)
Carlo Romagnano (4/17/2012)
I am curious to know how many developpers will use default value for "START WITH". In most cases will be a negative number.
They will all be negative, except for tinyint.
In most cases this shouldn't be a problem, unless you are using the sequence for generating surrogate keys and you have tied special meaning to some of them.
For example: 0 equals unknown, -1 equals not applicable.

A number is a number, it doesn't matter if it is negative or positive,

but when you do maintanance on table or you should copy and paste values to/from excel, great numbers

are annoying.

Iulian -207023 SSCertifiable Points: 7639 More actions · Answer 6

Iulian -207023

SSCertifiable

Points: 7639

April 17, 2012 at 2:24 am

#1474568

Thanks for the question

Iulian

Michael Lysons SSCertifiable Points: 6490 More actions · Answer 7

Thanks for the question. I had to do a fair bit of digging to find out the answer.

Stuart Davies SSCoach Points: 19505 More actions · Answer 8

Good question today, thanks - took an educated guess and got it wrong.

Looks like I need more education.

-------------------------------Posting Data Etiquette - Jeff Moden [/url]Smart way to ask a question
There are naive questions, tedious questions, ill-phrased questions, questions put after inadequate self-criticism. But every question is a cry to understand (the world). There is no such thing as a dumb question. ― Carl Sagan
I would never join a club that would allow me as a member - Groucho Marx

DugyC Hall of Fame Points: 3804 More actions · Answer 9

Great question, and a nice feature to know about... not sure what I'd use it for mind.

At first thought it wasn't fair to ask a SQL 2012 question as its so new, but bit my lip and did a wee bit of research and the answer was there in a flash.

Relatively easy question come the end.

Cheers for the point. 🙂

_____________________________________________________________________
[font="Comic Sans MS"]"The difficult tasks we do immediately, the impossible takes a little longer"[/font]

Koen Verbeeck SSC Guru Points: 259085 More actions · Answer 10

DugyC (4/17/2012)
At first thought it wasn't fair to ask a SQL 2012 question as its so new...

Hold on to yourself, because it's a SSIS 2012 question tomorrow 😀

Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP

InvisibleCat Default port Points: 1496 More actions · Answer 11

A great questions, got me thinking so well worth it.

Thank you.

Thomas Abraham SSChampion Points: 10761 More actions · Answer 12

Great question. Simple (with a single look at BOL), clear cut, a bit surprising, and on a new version. Thanks!

[font="Verdana"]Please don't go. The drones need you. They look up to you.[/font]
Connect to me on LinkedIn

jfogel SSCarpal Tunnel Points: 4155 More actions · Answer 13

Got it right. It is nice to see the sequence feature is now in SQL Server. Mainly because developers had to use a sequence for Oracle and an identity column for SQL Server. At least now the code can move closer to being the same. Note I wrote closer.

Cheers