• 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


    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

  • 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
  • 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.

    Need an answer? No, you need a question
    My blog at
    MCSE Business Intelligence - Microsoft Data Platform MVP

  • Thank you very much.

Viewing 4 posts - 1 through 4 (of 4 total)

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