Sequences II

Question

Post reply

Sequences II

sknox

SSChampion

Points: 12388
More actions
April 23, 2012 at 8:27 pm

#254630

Comments posted to this topic are about the item Sequences II
April 24, 2012 at 1:56 am

This was removed by the editor as SPAM

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

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

Britt Cluff SSCertifiable Points: 5083 More actions · Answer 1

Guess I need to learn more about the new features in 2012. Good question.

http://brittcluff.blogspot.com/

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

Nice question, thanks.

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

tommyh SSCertifiable Points: 6302 More actions · Answer 3

Now i cant test this. But is that answer really accurate. Reading the link... it doesnt sound so.

Since its an int the values defaults become

MinValue : -2,147,483,648

MaxValue : 2,147,483,647

Startvalue will be MinValue (since its not given).

So add -1 to -2,147,483,648 and it should loop... but only if option is CYCLE and the default is NO CYCLE... so shouldnt it give an error instead?

/T

dawryn SSCertifiable Points: 5481 More actions · Answer 4

tommyh (4/23/2012)
Startvalue will be MinValue (since its not given).

Check reference again. Start value is different whether sequence is ascending or descending.

tommyh SSCertifiable Points: 6302 More actions · Answer 5

dawryn (4/24/2012)
tommyh (4/23/2012)
Startvalue will be MinValue (since its not given).
Check reference again. Start value is different whether sequence is ascending or descending.

You are so right.

/T

Stephen E. Cook SSCrazy Points: 2275 More actions · Answer 6

I fell into the same trap as tommyh... I should have looked it up.

-- Stephen Cook

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

Stephen E. Cook (4/24/2012)
I fell into the same trap as tommyh... I should have looked it up.

Me too... trapped, hook, line and sinker... doh! :blush:

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

sqlmunkee SSCommitted Points: 1949 More actions · Answer 8

So, the key bit here is that SQL will *decide* whether the sequence is ascending or descending based on the INCREMENT BY argument. That's a bit misleading if your just starting out with sequences and SQL 2012 (like me) 🙂 Good question !

[font="Courier New"]sqlmunkee[/font]
[font="Courier New"]Bringing joy and happiness via SQL Server since 1998[/font]

Thomas Abraham SSChampion Points: 10761 More actions · Answer 9

Excellent question. On a new feature, minimum code to make your point, clear cut answer, and several issues demonstrated at once. Bravo! (and Thanks!)

I think if you give it some thought, this feature behaves as you would design it. The defaults seem the way I would hope they would - for a pleasant change.

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

Ron McCullough SSC Guru Points: 63877 More actions · Answer 10

I am some what confused and perplexed for when I run

(in Denalli CTP3)

SELECT name, start_value FROM sys.sequences;

the return values are:

name start_value

SSC_ROCKS2147483647

and that start value is a POSITIVE VALUE.

If everything seems to be going well, you have obviously overlooked something.

Ron

Please help us, help you -before posting a question please read[/url]
Before posting a performance problem please read[/url]

Thomas Abraham SSChampion Points: 10761 More actions · Answer 11

bitbucket-25253 (4/24/2012)
I am some what confused and perplexed for when I run
(in Denalli CTP3)
SELECT name, start_value FROM sys.sequences;
the return values are:
name start_value
SSC_ROCKS2147483647
and that start value is a POSITIVE VALUE.

MAX for INT is 214748647, which it uses as start value because INCREMENT BY -1 indicates the sequence should be DESCENDING.

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

Toreador SSChampion Points: 11365 More actions · Answer 12

Thomas Abraham (4/24/2012)
this feature behaves as you would design it.

Which is the reason I guessed wrong 😉

Koen Verbeeck SSC Guru Points: 259105 More actions · Answer 13

sqlmunkee (4/24/2012)
So, the key bit here is that SQL will *decide* whether the sequence is ascending or descending based on the INCREMENT BY argument.

That only seems logical to me.

If the INCREMENT BY is negative, the sequence will automatically be a descending one, as you will always "take a step back".

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