Sequences

• domenico.delbrocco

  SSChasing Mays

  Points: 617

  More actions

  April 10, 2014 at 3:02 am

  #287515

  Hi everybody

  I'm studying Sequences and I've found something strange using them in the creation of a table. I created sequence dbo.seqTest1.

  Create sequence dbo.seqTest1 AS INT

  START WITH 1;

  If I use the following code the persistent table is created:

  create table tab11

  (num int default (next value for dbo.seqTest1),

  data datetime

  )

  If I use this other code, the temporary table is not created

  create table #tab11

  (num int default (next value for dbo.seqTest1),

  data datetime

  )

  and I receive the following message:

  Msg 208, Level 16, State 1, Line 1

  Invalid object name 'dbo.seqTest1'.

  The question is: is it true that on temporary tables I can't use sequences but I can only use Identity ?

  If it's true, where I can find any reference about this behaviour? I'm using T-SQL Fundmentals and the Training Kit but I can't find any notes about that, even on TechNet

• Gail Shaw

  SSC Guru

  Points: 1004504

  More actions

  April 10, 2014 at 3:14 am

  #1704789

  Temp tables are created in TempDB. your sequence is in your user database, not TempDB, so the 2-part reference fails as the sequence can't be found.

  Edit: Code removed.

  Gail Shaw
  Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
  SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

  We walk in the dark places no others will enter
  We stand on the bridge and no one may pass

• Koen Verbeeck

  SSC Guru

  Points: 259155

  More actions

  April 10, 2014 at 3:15 am

  #1704791

  Gail was a bit faster, but anyway...

  MSDN doesn't say it explicitly, but one of the rules of using a sequence object in a default contstraint is the following:

  The table and the sequence object must reside in the same database.

  Since the sequence object is in one database and the table another (tempdb), it is not possible.

  http://technet.microsoft.com/en-us/library/ff878370.aspx

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

• domenico.delbrocco

  SSChasing Mays

  Points: 617

  More actions

  April 10, 2014 at 4:55 am

  #1704833

  Thank you very much.