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

RE: Sequences I

Dineshbabu — Wed, 09 Jan 2013 06:30:22 GMT

Before answering referred BOL many times, so i got it correct..

RE: Sequences I

demonfox — Fri, 03 Aug 2012 05:46:19 GMT

I don't have 2012 yet , I went through msdn [url]http://msdn.microsoft.com/en-us/library/ff878091.aspx[/url]I thought the answer would be min(bigint)+1 ; and that wasn't listed ; didn't understand the defaults ..guess need to dig out more..

RE: Sequences I

Hardy21 — Mon, 21 May 2012 02:21:40 GMT

[quote][b]Britt Cluff (4/23/2012)[/b][hr]Good question on a new feature. Thanks for submitting.[/quote] +1

RE: Sequences I

Britt Cluff — Mon, 23 Apr 2012 20:19:56 GMT

Good question on a new feature. Thanks for submitting.

RE: Sequences I

andy mack — Thu, 19 Apr 2012 21:30:59 GMT

Nice question. Got it wrong and learned something in the process :) And sequences will be SQL 2012 exam questions so this is a great help.

RE: Sequences I

codebyo — Wed, 18 Apr 2012 21:06:05 GMT

I chose "1" as the default start value because I thought it would have the same behaviour of the IDENTITY property which defaults to "start with 1, increment 1". Seems I must study more closely this welcome new feature.That means one less problem converting code to/from Oracle. :-)

RE: Sequences I

L' Eomot Inversé — Wed, 18 Apr 2012 13:44:13 GMT

Nice question. It's good to see some SQL 2012 stuff now and again.I saw 0, 1, min(int), min(bigint) presented as options and decided I would have to find it in BoL; obviously if it had been a methematician who designed it I could forget about 1, but 0 was maybe possible (although far from optimal) and the thing would probably have type-flexibility (to cater for very long sequences when needed at the same time as allowing small numbers when vl sequences weren't needed) but that flexibility meant that the choice of which type would be default wasn't really constrained beyond the requirement for an integer type. So I had to find it in BoL instead of being able to guess the answer by applying common sense, which meant I did some useful reading again, which in turn meant that I thought it was a good question.

RE: Sequences I

L' Eomot Inversé — Wed, 18 Apr 2012 13:31:22 GMT

[quote][b]Henrico Bekker (4/16/2012)[/b][hr]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.[/quote]The CREATE SEQUENCE page in Bol gives an example in which the sequence is created by [code]CREATE SEQUENCE Test.TestSequence ;[/code]so clearly MS thinks that not specifying any of those things works.

RE: Sequences I

L' Eomot Inversé — Wed, 18 Apr 2012 13:27:39 GMT

RE: Sequences I

dick.baker — Wed, 18 Apr 2012 01:33:24 GMT

for more info ..-- #1 long numbers (Carlo doesn't like for Excel)CREATE SEQUENCE SSC_ROCKS; SELECT N0 = (NEXT VALUE FOR SSC_ROCKS); -- -9223372036854775808 SELECT N1 = (NEXT VALUE FOR SSC_ROCKS); -- -9223372036854775807DROP SEQUENCE SSC_ROCKS;GO-- #2 try hex string insteadCREATE SEQUENCE SSC_ROCKX; SELECT N0 = [sys].[fn_varbintohexstr](NEXT VALUE FOR SSC_ROCKX); -- 0x8000000000000000 SELECT N1 = [sys].[fn_varbintohexstr](NEXT VALUE FOR SSC_ROCKX); -- 0x8000000000000001DROP SEQUENCE SSC_ROCKX;GO-- #3 show that ROLLBACK doesn't erase/reset sequence, ie behaves like IDENTITY()CREATE SEQUENCE SSC_ROCKT;begin tran SELECT N0 = [sys].[fn_varbintohexstr](NEXT VALUE FOR SSC_ROCKT); -- 0x8000000000000000 SELECT N1 = [sys].[fn_varbintohexstr](NEXT VALUE FOR SSC_ROCKT); -- 0x8000000000000001rollbackSELECT N2 = [sys].[fn_varbintohexstr](NEXT VALUE FOR SSC_ROCKT); -- 0x8000000000000002DROP SEQUENCE SSC_ROCKT;GO

RE: Sequences I

SQLRNNR — Tue, 17 Apr 2012 21:44:39 GMT

Nice questionThanks

RE: Sequences I

sknox — Tue, 17 Apr 2012 16:10:54 GMT

[quote][b]Schadenfreude-Mei (4/17/2012)[/b][hr]deleted due to embarrisment.[/quote]We did kind of jump on you, didn't we? Rest assured it wasn't intended (I know when I wrote my post, the other two weren't there). We've all misread questions from time to time, and every once in a while, there actually is a poorly written question. ;-)

RE: Sequences I

Cliff Jones — Tue, 17 Apr 2012 12:53:30 GMT

[quote][b]Schadenfreude-Mei (4/17/2012)[/b][hr]deleted due to embarrisment.[/quote]Thanks, made me laugh. :-)

RE: Sequences I

sknox — Tue, 17 Apr 2012 10:30:53 GMT

[quote][b]Schadenfreude-Mei (4/17/2012)[/b][hr]Sorry guys but I took a guess, got it wrong so ran it through my local 2k8r2 and get the below:[quote]Msg 343, Level 15, State 1, Line 1Unknown object type 'SEQUENCE' used in a CREATE, DROP, or ALTER statement.Msg 102, Level 15, State 1, Line 3Incorrect syntax near ';'.Msg 343, Level 15, State 1, Line 5Unknown object type 'SEQUENCE' used in CREATE, DROP, or ALTER statement.[/quote]SO what gives?[/quote]From the question: [quote]In a default installation of SQL Server 2012...[/quote]From the answer:[quote]Sequences, a new feature in SQL Server 2012,...[/quote]

RE: Sequences I

jfogel-1131029 — Tue, 17 Apr 2012 10:30:20 GMT

Yes, note the wording of the question.

RE: Sequences I

Cliff Jones — Tue, 17 Apr 2012 10:27:43 GMT

RE: Sequences I

Schadenfreude-Mei — Tue, 17 Apr 2012 10:25:14 GMT

deleted due to embarrisment.

RE: Sequences I

KWymore — Tue, 17 Apr 2012 09:39:51 GMT

Thanks for the 2012 question. Keep 'em coming.

RE: Sequences I

EL Jerry — Tue, 17 Apr 2012 09:15:18 GMT

Good one. Good to know since I still don't have access to SQL Server 2012, but eventually will."El" Jerry.

RE: Sequences I

OzYbOi d(-_-)b — Tue, 17 Apr 2012 08:56:30 GMT

tks for the questions - +1 for learned something new today. cheers

RE: Sequences I

Cliff Jones — Tue, 17 Apr 2012 08:07:54 GMT

[quote][b]jfogel (4/17/2012)[/b][hr]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.[/quote]Yes, I agree. After all these years it is good to see it being implemented in SQL Server.

RE: Sequences I

SQLDCH — Tue, 17 Apr 2012 08:06:08 GMT

Thanks, learned something new.

RE: Sequences I

jfogel — Tue, 17 Apr 2012 07:11:15 GMT

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.

RE: Sequences I

Thomas Abraham — Tue, 17 Apr 2012 05:32:58 GMT

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

RE: Sequences I

Nic-306421 — Tue, 17 Apr 2012 04:39:07 GMT

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

RE: Sequences I

Koen Verbeeck — Tue, 17 Apr 2012 03:01:01 GMT

[quote][b]DugyC (4/17/2012)[/b][hr]At first thought it wasn't fair to ask a SQL 2012 question as its so new...[/quote]Hold on to yourself, because it's a SSIS 2012 question tomorrow :-D

RE: Sequences I

DugyC — Tue, 17 Apr 2012 02:58:09 GMT

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. :-)

RE: Sequences I

Stuart Davies — Tue, 17 Apr 2012 02:54:25 GMT

Good question today, thanks - took an educated guess and got it wrong.Looks like I need more education.

RE: Sequences I

Michael Lysons — Tue, 17 Apr 2012 02:33:12 GMT

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

RE: Sequences I

Iulian -207023 — Tue, 17 Apr 2012 02:24:15 GMT

Thanks for the questionIulian

RE: Sequences I

Carlo Romagnano — Tue, 17 Apr 2012 02:06:47 GMT

[quote][b]Koen Verbeeck (4/17/2012)[/b][hr][quote][b]Carlo Romagnano (4/17/2012)[/b][hr]I am curious to know how many developpers will use default value for "START WITH". In most cases will be a negative number.[/quote]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.[/quote]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 numbersare annoying.

RE: Sequences I

Koen Verbeeck — Tue, 17 Apr 2012 01:21:58 GMT

[quote][b]Carlo Romagnano (4/17/2012)[/b][hr]I am curious to know how many developpers will use default value for "START WITH". In most cases will be a negative number.[/quote]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.

RE: Sequences I

Carlo Romagnano — Tue, 17 Apr 2012 01:17:59 GMT

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

RE: Sequences I

Stewart "Arturius" Campbell — Tue, 17 Apr 2012 00:42:54 GMT

Interesting question, thanks...

RE: Sequences I

Koen Verbeeck — Tue, 17 Apr 2012 00:41:54 GMT

[quote][b]Henrico Bekker (4/16/2012)[/b][hr]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.[/quote]When I look at BOL I see the following:[quote]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 } ] [ ; ][/quote]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!

RE: Sequences I

Henrico Bekker — Mon, 16 Apr 2012 22:28:48 GMT

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.

Sequences I

sknox — Mon, 16 Apr 2012 22:27:47 GMT

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