Is it possible to add index to a temp table? And what's the difference between create #t and declare @t

Question

I need to do a very complex query. At one point, this query must have a join to a view that cannot be indexed unfortunately. This view is also a complex view joining big tables.

View's output can be simplified as this:

PID (int), Kind (int), Date (date), D1,D2..DN 

where PID and Date and Kind fields are not unique (there may be more than one row having same combination of pid,kind,date), but are those that will be used in join like this

left join ComplexView mkcs on mkcs.PID=q4.PersonID and mkcs.Date=q4.date and mkcs.Kind=1 left join ComplexView mkcl on mkcl.PID=q4.PersonID and mkcl.Date=q4.date and mkcl.Kind=2 left join ComplexView mkco on mkco.PID=q4.PersonID and mkco.Date=q4.date and mkco.Kind=3 

Now, if I just do it like this, execution of the query takes significant time because the complex view is ran three times I assume, and out of its huge amount of rows only some are actually used (like, out of 40000 only 2000 are used)

What i did is declare @temptable, and insert into @temptable select * from ComplexView where Date... - one time per query I select only the rows I am going to use from my ComplexView, and then I am joining this @temptable.

This reduced execution time significantly.

However, I noticed, that if I make a table in my database, and add a clustered index on PID,Kind,Date (non-unique clustered) and take data from this table, then doing delete * from this table and insert into this table from complex view takes some seconds (3 or 4), and then using this table in my query (left joining it three times) take down query time to half, from 1 minute to 30 seconds!

So, my question is, first of all - is it possible to create indexes on declared @temptables. And then - I've seen people talk about "create #temptable" syntax. Maybe this is what i need? Where can I read about what's the difference between declare @temptable and create #temptable? What shall I use for a query like mine? (this query is for MS Reporting Services report, if it matters).

Answer 1

#tablename is a physical table, stored in tempdb that the server will drop automatically when the connection that created it is closed, @tablename is a table stored in memory & lives for the lifetime of the batch/procedure that created it, just like a local variable.

You can only add a (non PK) index to a #temp table.

create table #blah (fld int) create nonclustered index idx on #blah (fld) 

Answer 2

It's not a complete answer but #table will create a temporary table that you need to drop or it will persist in your database. @table is a table variable that will not persist longer than your script.

Also, I think this post will answer the other part of your question.

Creating an index on a table variable