Dynamically calling indexes in SQL Server 2005?

Question

As most people working with Sql Server know, you can declare a temporary table as follows:

create table #foo ( row_id int identity not null primary key, bar varchar(50) not null default 'YoMomma' ); 

... this will create a primary key and a default constraint for this temporary table; the names of these objects will be unique, dynamically generated by Sql Server.

Is it possible to create a dynamically named index for a table after creating it? I have a case where a stored procedure using temporary tables can run multiple instances at the same time, and I would like to increase performance without the risk of conflict between identically named objects in the tempdb database. The CREATE INDEX command requires an explicit index name.

I am looking for a solution that does not include dynamic SQL, just dynamic names.

Answer 1

It's not a problem. Index names do not have to be unique. Only constraint names have.

So, for example, you can run this in multiple parallel connections without any problems

 CREATE TABLE #T ( C INT ) CREATE UNIQUE CLUSTERED INDEX ix on #T(C) 

But it will not work under concurrency

 ALTER TABLE #T ADD CONSTRAINT UQ UNIQUE NONCLUSTERED (C) 

Answer 2

It should be possible:

 CREATE INDEX #foo1 ON #foo(bar);