sql - Creating an index on a table variable -

can create index on table variable in sql server 2000?

i.e.

declare @temptable table (         [id] [int] not null primary key         ,[name] [nvarchar] (255) collate database_default null  ) 

can create index on name?

the question tagged sql server 2000 benefit of people developing on latest version i'll address first.

sql server 2014

in addition methods of adding constraint based indexes discussed below sql server 2014 allows non unique indexes specified directly inline syntax on table variable declarations.

example syntax below.

/*sql server 2014+ compatible inline index syntax*/ declare @t table ( c1 int index ix1 clustered, /*single column indexes can declared next column*/ c2 int index ix2 nonclustered,        index ix3 nonclustered(c1,c2) /*example composite index*/ ); 

filtered indexes , indexes included columns can not declared syntax sql server 2016 relaxes bit further. ctp 3.1 possible declare filtered indexes table variables. rtm may case included columns allowed current position "will not make sql16 due resource constraints"

/*sql server 2016 allows filtered indexes*/ declare @t table ( c1 int null index ix unique c1 not null /*unique ignoring nulls*/ ) 

---

sql server 2000 - 2012

can create index on name?

short answer: yes.

declare @temptable table (   [id]   [int] not null primary key,   [name] [nvarchar] (255) collate database_default null,   unique nonclustered ([name], [id])    )  

a more detailed answer below.

traditional tables in sql server can either have clustered index or structured heaps.

clustered indexes can either declared unique disallow duplicate key values or default non unique. if not unique sql server silently adds uniqueifier duplicate keys make them unique.

non clustered indexes can explicitly declared unique. otherwise non unique case sql server adds row locator (clustered index key or rid heap) index keys (not duplicates) again ensures unique.

in sql server 2000 - 2012 indexes on table variables can created implicitly creating unique or primary key constraint. difference between these constraint types primary key must on non nullable column(s). columns participating in unique constraint may nullable. (though sql server's implementation of unique constraints in presence of nulls not per specified in sql standard). table can have 1 primary key multiple unique constraints.

both of these logical constraints physically implemented unique index. if not explicitly specified otherwise primary key become clustered index , unique constraints non clustered behavior can overridden specifying clustered or nonclustered explicitly constraint declaration (example syntax)

declare @t table ( int null unique clustered, b int not null primary key nonclustered ) 

as result of above following indexes can implicitly created on table variables in sql server 2000 - 2012.

+-------------------------------------+-------------------------------------+ |             index type              | can created on table variable? | +-------------------------------------+-------------------------------------+ | unique clustered index              | yes                                 | | nonunique clustered index           |                                     | | unique nci on heap                | yes                                 | | non unique nci on heap            |                                     | | unique nci on clustered index     | yes                                 | | non unique nci on clustered index | yes                                 | +-------------------------------------+-------------------------------------+ 

the last 1 requires bit of explanation. in table variable definition @ beginning of answer non unique non clustered index on name simulated unique index on name,id (recall sql server silently add clustered index key non unique nci key anyway).

a non unique clustered index can achieved manually adding identity column act uniqueifier.

declare @t table ( int null, b int null, c int null, uniqueifier int not null identity(1,1), unique clustered (a,uniqueifier) ) 

but not accurate simulation of how non unique clustered index implemented in sql server adds "uniqueifier" rows. not require it.