sql - Derived table with an index -

sql - Derived table with an index -

please see tsql below:

declare @testtable table (reference int identity, testfield varchar(10), primary key (reference)) insert @testtable values ('ian') select * @testtable testtable inner bring together livetable on livetable.reference=testtable.reference

is possible create index on @test.testfield? next webpage suggests not. however, read on webpage possible.

i know create physical table instead (for @testtable). however, want see if can derived table first.

you can create index on table variable described in top voted reply on question:

sql server : creating index on table variable

sample syntax post:

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

alternately, may want consider using temp table, persist during scope of current operation, i.e. during execution of stored procedure table variables. temp tables structured , optimized regular tables, stored in tempdb, hence can indexed in same way regular table.

temp tables offer improve performance table variables, it's worth testing dataset.

more in depth details can found here:

when should utilize table variable vs temporary table in sql server?

you can see sample of creating temp table index from:

sql server planet - create index on temp table

one of valuable assets of temp table (#temp) ability add together either clustered or non clustered index. additionally, #temp tables allow auto-generated statistics created against them. can help optimizer when determining cardinality. below illustration of creating both clustered , non-clustered index on temp table.

sample code site:

create table #users ( id int identity(1,1), userid int, username varchar(50) ) insert #users ( userid, username ) select userid = u.userid ,username = u.username dbo.users u create clustered index idx_c_users_userid on #users(userid) create index idx_users_username on #users(username)

sql sql-server tsql