November 2, 2010 at 11:21 am
I am trying to create a temp table with a non-clustered index.
Originally I tried to create the index after I created the table.
This seemed to work fine, so I added my stored procedure to our Production environment.
However, when two users called the stored procedure at once I got the following error:
There is already an object named 'IX_tmpTableName' in the database. Could not create constraint. See previous errors.
I then found that SQL Server does generate unique names for the temp table but not all the objects associated with the temp table if they are explicitly named.
This is easy enough to solve for a PRIMAY KEY or UNIQUE constraint because the do not have to be named.
Is there a way to create an non-clustered index on a temp table without naming it?
November 2, 2010 at 11:33 am
I do that in prod no problem. Can you post the create table and index scripts you are using?
November 2, 2010 at 11:36 am
I would create a table with a unique name, eg MY_TEMP_2010_11_02, create the index on it, use the table and drop it after use.
November 2, 2010 at 11:42 am
Just to make sure they didn't change something on me in 2k8...
From: http://msdn.microsoft.com/en-us/library/ms188783.aspx
index_name
Is the name of the index. Index names must be unique within a table or view but do not have to be unique within a database. Index names must follow the rules of identifiers.
So, with a local temporary table name being unique by default from the system you can re-use the index name simultaneously with the same name, no issue.
So, this begs the question, which temp table are you using? # or ##?
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
November 2, 2010 at 11:43 am
Ninja's_RGR'us (11/2/2010)
I do that in prod no problem. Can you post the create table and index scripts you are using?
All 3 of these fail:
CREATE TABLE #Table1
(
TeacherID BIGINT
, StudentIDBIGINT
, CONSTRAINT table1_key PRIMARY KEY CLUSTERED (TeacherID, StudentID)
)
CREATE TABLE #Table2
(
TeacherIDBIGINT
,StudentIDBIGINT
,CONSTRAINT [PK_#Table2] PRIMARY KEY CLUSTERED
(
TeacherID ASC,
StudentID ASC
)ON [PRIMARY]
) ON [PRIMARY]
CREATE TABLE #Table3
(
StudentID BIGINT
, CourseID BIGINT
, FinalGrade DECIMAL(18,0)
, CONSTRAINT PK_Table3
PRIMARY KEY CLUSTERED(StudetnID,CourseID)
)
November 2, 2010 at 11:51 am
That version works. Not using PK but same effect.
CREATE TABLE #Table1
(
TeacherID BIGINT
, StudentID BIGINT
)
CREATE UNIQUE CLUSTERED INDEX #IX_Tbl1 on #Table1 (TeacherID, StudentID)
CREATE TABLE #Table2
(
TeacherID BIGINT
,StudentID BIGINT
) ON [PRIMARY]
CREATE UNIQUE CLUSTERED INDEX #IX_Table2 on #Table2 (TeacherID, StudentID)
CREATE TABLE #Table3
(
StudentID BIGINT
, CourseID BIGINT
, FinalGrade DECIMAL(18,0)
)
CREATE UNIQUE CLUSTERED INDEX #IX_Table3 on #Table3 (StudentID,CourseID)
November 2, 2010 at 11:56 am
Goldie Lesser (11/2/2010)
Ninja's_RGR'us (11/2/2010)
I do that in prod no problem. Can you post the create table and index scripts you are using?All 3 of these fail:
CREATE TABLE #Table1
(
TeacherID BIGINT
, StudentIDBIGINT
, CONSTRAINT table1_key PRIMARY KEY CLUSTERED (TeacherID, StudentID)
)
CREATE TABLE #Table2
(
TeacherIDBIGINT
,StudentIDBIGINT
,CONSTRAINT [PK_#Table2] PRIMARY KEY CLUSTERED
(
TeacherID ASC,
StudentID ASC
)ON [PRIMARY]
) ON [PRIMARY]
CREATE TABLE #Table3
(
StudentID BIGINT
, CourseID BIGINT
, FinalGrade DECIMAL(18,0)
, CONSTRAINT PK_Table3
PRIMARY KEY CLUSTERED(StudetnID,CourseID)
)
Your script works perfectly fine for me except for the misspelling of studetn in the last one. Works with a fix to the typo.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
November 2, 2010 at 11:58 am
Ninja, the CREATE INDEX #idx_1 ...
also works as CREATE INDEX idx_1...
Don't need the # for the index name there. Just fyi. Works either way.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
November 2, 2010 at 12:00 pm
Craig Farrell (11/2/2010)
Ninja, the CREATE INDEX #idx_1 ...also works as CREATE INDEX idx_1...
Don't need the # for the index name there. Just fyi. Works either way.
Tx, I used to think it was required way back 5 minutes ago. But I love to see that the create is on tmp object so that's why I keep it there.
November 2, 2010 at 12:05 pm
Craig Farrell (11/2/2010)
Goldie Lesser (11/2/2010)
Ninja's_RGR'us (11/2/2010)
I do that in prod no problem. Can you post the create table and index scripts you are using?All 3 of these fail:
CREATE TABLE #Table1
(
TeacherID BIGINT
, StudentIDBIGINT
, CONSTRAINT table1_key PRIMARY KEY CLUSTERED (TeacherID, StudentID)
)
CREATE TABLE #Table2
(
TeacherIDBIGINT
,StudentIDBIGINT
,CONSTRAINT [PK_#Table2] PRIMARY KEY CLUSTERED
(
TeacherID ASC,
StudentID ASC
)ON [PRIMARY]
) ON [PRIMARY]
CREATE TABLE #Table3
(
StudentID BIGINT
, CourseID BIGINT
, FinalGrade DECIMAL(18,0)
, CONSTRAINT PK_Table3
PRIMARY KEY CLUSTERED(StudetnID,CourseID)
)
Your script works perfectly fine for me except for the misspelling of studetn in the last one. Works with a fix to the typo.
I know the script works (apologies for the typo)
The problem is that when I put this script inside a stored procedure, and the stored procedure gets called by two users at once, I get the error mentioned above.
November 2, 2010 at 12:11 pm
Goldie Lesser (11/2/2010)
The problem is that when I put this script inside a stored procedure, and the stored procedure gets called by two users at once, I get the error mentioned above.
Yes it will, because you've explicitly named the constraints (not indexes) and those names must be unique in a database. Indexes just have to be unique on a table
This will work because it allows SQL to auto create the constraint names
CREATE TABLE #Table1
(
TeacherID BIGINT
, StudentID BIGINT
, PRIMARY KEY CLUSTERED (TeacherID, StudentID)
)
CREATE TABLE #Table2
(
TeacherID BIGINT
,StudentID BIGINT
,PRIMARY KEY CLUSTERED
(
TeacherID ASC,
StudentID ASC
)ON [PRIMARY]
) ON [PRIMARY]
CREATE TABLE #Table3
(
StudentID BIGINT
, CourseID BIGINT
, FinalGrade DECIMAL(18,0)
, PRIMARY KEY CLUSTERED(StudentID,CourseID)
)
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
November 2, 2010 at 12:18 pm
GilaMonster (11/2/2010)
Yes it will, because you've explicitly named the constraints (not indexes) and those names must be unique in a database. Indexes just have to be unique on a table
This will work because it allows SQL to auto create the constraint names
Are you saying I won't have any issues if I create an index as follows:
CREATE TABLE #Table4
(
StudentIDBIGINT
,CourseIDBIGINT
)
CREATE NONCLUSTERED INDEX #IX_Table4 ON #Table4 (CourseID ASC)
November 2, 2010 at 12:27 pm
Correct. Index names only have to be unique within a table, while constraint names have to be unique in the DB
CREATE TABLE #t1 (id int)
CREATE TABLE #t2 (id int)
CREATE INDEX idx_id ON #t1 (id)
CREATE INDEX idx_id ON #t2 (id)
So name the indexes and don't worry, and do the constraints as I showed in my last post so that SQL can generate its own names for them.
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
November 2, 2010 at 12:29 pm
GilaMonster (11/2/2010)
Correct. Index names only have to be unique within a table, while constraint names have to be unique in the DB
CREATE TABLE #t1 (id int)CREATE TABLE #t2 (id int)
CREATE INDEX idx_id ON #t1 (id)
CREATE INDEX idx_id ON #t2 (id)
So name the indexes and don't worry, and do the constraints as I showed in my last post so that SQL can generate its own names for them.
Thanks Gail.
I knew about not naming the constraints, but I didn't realize indexes would be different.
Thanks so much for your help!
November 2, 2010 at 12:37 pm
GilaMonster (11/2/2010)
Correct. Index names only have to be unique within a table, while constraint names have to be unique in the DB
I so rarely create constraints I'd forgotten that. Sorry about that Goldie, I guess I had a brain fart. Thanks for the reminder Gail.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
Viewing 15 posts - 1 through 15 (of 25 total)
You must be logged in to reply to this topic. Login to reply