April 10, 2014 at 3:02 am
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
April 10, 2014 at 3:14 am
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
April 10, 2014 at 3:15 am
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
April 10, 2014 at 4:55 am
Thank you very much.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply