Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase «««1234»»

Sequences I Expand / Collapse
Author
Message
Posted Tuesday, April 17, 2012 10:25 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Tuesday, March 4, 2014 10:03 AM
Points: 1,106, Visits: 1,334
deleted due to embarrisment.

Adam Zacks

-------------------------------------------

Be Nice, Or Leave
Post #1285091
Posted Tuesday, April 17, 2012 10:27 AM
Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Yesterday @ 12:39 PM
Points: 3,900, Visits: 3,632
Schadenfreude-Mei (4/17/2012)
Sorry guys but I took a guess, got it wrong so ran it through my local 2k8r2 and get the below:

Msg 343, Level 15, State 1, Line 1
Unknown object type 'SEQUENCE' used in a CREATE, DROP, or ALTER statement.
Msg 102, Level 15, State 1, Line 3
Incorrect syntax near ';'.
Msg 343, Level 15, State 1, Line 5
Unknown object type 'SEQUENCE' used in CREATE, DROP, or ALTER statement.


SO what gives?

This feature requires SQL 2012.
Post #1285095
Posted Tuesday, April 17, 2012 10:30 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Thursday, May 10, 2012 7:58 AM
Points: 19, Visits: 64
Yes, note the wording of the question.
Post #1285098
Posted Tuesday, April 17, 2012 10:30 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Today @ 8:48 AM
Points: 1,304, Visits: 1,672
Schadenfreude-Mei (4/17/2012)
Sorry guys but I took a guess, got it wrong so ran it through my local 2k8r2 and get the below:

Msg 343, Level 15, State 1, Line 1
Unknown object type 'SEQUENCE' used in a CREATE, DROP, or ALTER statement.
Msg 102, Level 15, State 1, Line 3
Incorrect syntax near ';'.
Msg 343, Level 15, State 1, Line 5
Unknown object type 'SEQUENCE' used in CREATE, DROP, or ALTER statement.


SO what gives?


From the question:
In a default installation of SQL Server 2012...


From the answer:
Sequences, a new feature in SQL Server 2012,...

Post #1285099
Posted Tuesday, April 17, 2012 12:53 PM
Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Yesterday @ 12:39 PM
Points: 3,900, Visits: 3,632
Schadenfreude-Mei (4/17/2012)
deleted due to embarrisment.

Thanks, made me laugh.
Post #1285203
Posted Tuesday, April 17, 2012 4:10 PM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Today @ 8:48 AM
Points: 1,304, Visits: 1,672
Schadenfreude-Mei (4/17/2012)
deleted due to embarrisment.


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.

Post #1285343
Posted Tuesday, April 17, 2012 9:44 PM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Today @ 10:59 AM
Points: 21,619, Visits: 15,275
Nice question

Thanks




Jason AKA CirqueDeSQLeil
I have given a name to my pain...
MCM SQL Server


SQL RNNR

Posting Performance Based Questions - Gail Shaw
Posting Data Etiquette - Jeff Moden
Hidden RBAR - Jeff Moden
VLFs and the Tran Log - Kimberly Tripp
Post #1285406
Posted Wednesday, April 18, 2012 1:33 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Monday, June 25, 2012 4:09 AM
Points: 44, Visits: 91
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); -- -9223372036854775807
DROP SEQUENCE SSC_ROCKS;
GO

-- #2 try hex string instead
CREATE SEQUENCE SSC_ROCKX;
SELECT N0 = [sys].[fn_varbintohexstr](NEXT VALUE FOR SSC_ROCKX); -- 0x8000000000000000
SELECT N1 = [sys].[fn_varbintohexstr](NEXT VALUE FOR SSC_ROCKX); -- 0x8000000000000001
DROP 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); -- 0x8000000000000001
rollback
SELECT N2 = [sys].[fn_varbintohexstr](NEXT VALUE FOR SSC_ROCKT); -- 0x8000000000000002

DROP SEQUENCE SSC_ROCKT;
GO
Post #1285455
Posted Wednesday, April 18, 2012 1:27 PM


SSCrazy Eights

SSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy Eights

Group: General Forum Members
Last Login: Today @ 9:14 AM
Points: 8,679, Visits: 9,205
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.

Only if they don't specify a negative increment (if they do, they will be positive).


Tom
Post #1285996
Posted Wednesday, April 18, 2012 1:31 PM


SSCrazy Eights

SSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy Eights

Group: General Forum Members
Last Login: Today @ 9:14 AM
Points: 8,679, Visits: 9,205
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.

The CREATE SEQUENCE page in Bol gives an example in which the sequence is created by
CREATE SEQUENCE Test.TestSequence ;

so clearly MS thinks that not specifying any of those things works.


Tom
Post #1286005
« Prev Topic | Next Topic »

Add to briefcase «««1234»»

Permissions Expand / Collapse