30

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).

rob
  • 8,134
  • 8
  • 58
  • 68
Istrebitel
  • 533
  • 3
  • 6
  • 8

5 Answers5

23

#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)
Alex K.
  • 171,639
  • 30
  • 264
  • 288
  • 3
    Will it mean that if two persons run the query at once, and this query uses temp tables, they will access the same #tablename but different @tablenames, so using #tablename for temp queries in a script is unsafe? in other words, what happens when two queries are ran simultaneoncely, each having create statement with same #temptable identifier? Do they each get their own #temptable or they both access same table? – Istrebitel Jun 17 '11 at 12:13
  • ah, i found the answer inthe link below – Istrebitel Jun 17 '11 at 12:18
  • 5
    @DanzaiVer the link is from a deleted answer; http://www.sqlteam.com/article/optimizing-performance-indexes-on-temp-tables – Alex K. Sep 13 '13 at 10:10
  • Table variables are not necessarily "stored in memory." They are handled just like #temp tables: they will start in memory, but be spooled out to physical disk in tempdb as they grow. The main differences are syntactic, and scope (table variables are scoped to the batch / function / procedure in which they are created: #temp tables to the session. – Curt Jan 15 '19 at 17:37
7

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

Community
  • 1
  • 1
Brian Dishaw
  • 5,767
  • 34
  • 49
  • 17
    The temp table will go away after the server connection is dropped and is only in the scope of that connection. Not that dropping things you no longer need is a bad habit, it's just not required. – JeffO Jun 17 '11 at 11:55
  • Interesting, I wasn't aware of that. Thanks for the clarification. – Brian Dishaw Jun 17 '11 at 12:03
  • 2
    Actually it is required. For example when i'm running my query in sql management studio, it doesnt close the session, and thus create statement gives an error that object is already there! So i think it is better to drop it manually. Articles on the web also suggest it. – Istrebitel Jun 17 '11 at 13:29
  • Even though you actualy do not need to drop, the link in this answer has a link that directly answers 100% of my question, so i'll mark this as an answer – Istrebitel Jun 17 '11 at 13:30
  • @Istrebitel JeffO did specify the scope of the connection so he was correct. In SSMS if you right-click on the window and hit disconnect, that will be sufficient. Since you are not doing that, the connection is still active. – Shiv Feb 23 '15 at 03:17
  • @JeffO Why is it a bad practice? I thought it's the opposite - even though the temp table will be dropped when the connection drops, it's a good practice to do it yourself. – Apostrofix May 22 '15 at 06:16
  • 1
    Sorry my wording was confusing with too many negatives. It is a good habit; just not required. – JeffO Jul 23 '15 at 03:13
  • Please fix your answer to describe how `#table` is different from global temporary tables like `##table` and how scoping of temporary tables and index names works. – binki Sep 03 '19 at 16:50
4

The @tableName syntax is a table variable. They are rather limited. The syntax is described in the documentation for DECLARE @local_variable. You can kind of have indexes on table variables, but only indirectly by specifying PRIMARY KEY and UNIQUE constraints on columns. So, if your data in the columns that you need an index on happens to be unique, you can do this. See this answer. This may be “enough” for many use cases, but only for small numbers of rows. If you don’t have indexes on your table variable, the optimizer will generally treat table variables as if they contain one row (regardless of how many rows there actually are) which can result in terrible query plans if you have hundreds or thousands of rows in them instead.

The #tableName syntax is a locally-scoped temporary table. You can create these either using SELECT…INTO #tableName or CREATE TABLE #tableName syntax. The scope of these tables is a little bit more complex than that of variables. If you have CREATE TABLE #tableName in a stored procedure, all references to #tableName in that stored procedure will refer to that table. If you simply reference #tableName in the stored procedure (without creating it), it will look into the caller’s scope. So you can create #tableName in one procedure, call another procedure, and in that other procedure read/update #tableName. However, once the procedure that created #tableName runs to completion, that table will be automatically unreferenced and cleaned up by SQL Server. So, there is no reason to manually clean up these tables unless if you have a procedure which is meant to loop/run indefinitely or for long periods of time.

You can define complex indexes on temporary tables, just as if they are permanent tables, for the most part. So if you need to index columns but have duplicate values which prevents you from using UNIQUE, this is the way to go. You do not even have to worry about name collisions on indexes. If you run something like CREATE INDEX my_index ON #tableName(MyColumn) in multiple sessions which have each created their own table called #tableName, SQL Server will do some magic so that the reuse of the global-looking identifier my_index does not explode.

Additionally, temporary tables will automatically build statistics, etc., like normal tables. The query optimizer will recognize that temporary tables can have more than just 1 row in them, which can in itself result in great performance gains over table variables. Of course, this also is a tiny amount of overhead. Though this overhead is likely worth it and not noticeable if your query’s runtime is longer than one second.

binki
  • 7,754
  • 5
  • 64
  • 110
4

To extend Alex K.'s answer, you can create the PRIMARY KEY on a temp table

IF OBJECT_ID('tempdb..#tempTable') IS NOT NULL
 DROP TABLE #tempTable

CREATE TABLE #tempTable 
(
   Id INT PRIMARY KEY
  ,Value NVARCHAR(128)
)

INSERT INTO #tempTable
VALUES 
     (1, 'first value')
    ,(3, 'second value')
    -- will cause Violation of PRIMARY KEY constraint 'PK__#tempTab__3214EC071AE8C88D'. Cannot insert duplicate key in object 'dbo.#tempTable'. The duplicate key value is (1).
    --,(1, 'first value one more time')


SELECT  * FROM #tempTable
svonidze
  • 151
  • 8
4

Yes, you can create indexes on temp tables or table variables. http://sqlserverplanet.com/sql/create-index-on-table-variable/

JeffO
  • 7,957
  • 3
  • 44
  • 53