SqlShare.com Logo
 
Skip Navigation Links
Home
Video List
Events
Classes
About Us
Login / Register
Subscribe RSS Feed 

Confio Ignite 8

Using Indexes on Tables Variables in SQL Server

You can run into peformance issues when working with temporary table variables in SQL Server. This videos shows you how to use indexes on table variables which can help your query performance.

Duration:
1 mins 51 secs
Skill Level:
100
Rating:
4.32 out of 5
Publish Date:
November 03, 2008
Using Indexes on Tables Variables in SQL Server You must be logged in to view this video.  
Bookmark and Share
 
1=Poor, 3=Good, 5=Excellent

About the Author

Image of Plamen Ratchev
Plamen Ratchev has over two decades experience in the software architecture and development field. He has enjoyed building solutions for UNIX, DOS and Windows platforms, with main focus on SQL Server since version 4.21. Plamen is founder of Tangra, specializing in relational database applications analysis, implementation, and tuning. His particular interest is in design patterns, performance a...

References

There are no downloads or recommended reading links for this video

Comments
AEE49878AC on 12/31/2008
Fantastic. I never thought to try that.

Dustin Ryan on 12/31/2008
Nice video, very informative.

Steve Harris on 12/31/2008
I did not know that you could do this. Very good tip.

Jack Corbett on 12/31/2008
I knew about the PK, but never thought of using a unique constraint.

mbourgon on 12/31/2008
I knew about adding a primary key - but I didn't know about the constraint! Definitely something to add to my bag of tricks.

Richard Robson, Sr. on 12/31/2008
I didn't know that!

Richard Robson, Sr. on 12/31/2008
I didn't know that!

Ron on 12/31/2008
Good tip to know.

kin on 12/31/2008
able to create index on TV is cool. happy 2009.

When do the better query plans begin to pay back the cost of the constraints?

Plamen Ratchev on 1/4/2009
Satyabodhi, this depends on many parameters (data size, what type of queries you run, query complexity, etc.). In general if you have query predicates that utilize the underlying indexes you will see benefits. Best as always is to test with your data to see if it makes sense to use them.

E20CF1E7B8 on 1/5/2009
Adding indexes to table variables will definitely come in handy.

Pete Williams on 1/5/2009
Although I know this and use it a lot, it is very good. Many developers are unaware of this.

Ed Svastits on 1/6/2009
Straight and to the point. Could have added other index types or other constraints.

Plamen Ratchev on 1/7/2009
Ed, since you cannot run CREATE INDEX against a table variable, you cannot create other indexes. The techniques demonstrated in the video are based on the fact that PRIMARY KEY and UNIQUE KEY constraints are enforced via index.

khanlala on 1/8/2009
Very good & Informative

Chris Helm on 2/13/2009
Excellent video, never thought of using unique constraints before...

rupesh sharma on 2/16/2009
excellent video....very useful to implement this mostly i use in reporting services stored procs

Marc on 7/21/2009
pretty neat

Tonci on 12/1/2009
Simple, good, conceptual and to the point. Thank you.

Eric on 7/30/2010
Very helpful, especially since a view of the before and after execution plans are included.

A Alagu Ganesh on 8/4/2010
good

Todd Burrell on 11/12/2010
The first question was too tricky. I took it as you can't directly add indexes to table variables.

Philip Hickey on 11/12/2010
Thanks very much for putting this great video together - keep up the good work.

Cary on 11/12/2010
Another clever use of SQL that I hadn't thought about. Thanks.

Craig Pessano on 11/12/2010
Thanks for the video... I knew about PRIMARY KEY om table vars, but not UNIQUE index, it's good to know.

Kasey Wheeler on 11/12/2010
Good video!

Jamshid Nouri on 11/12/2010
excellent

anar on 11/12/2010
good video in a very less time.

borjonx on 11/12/2010
thick accent, but still speaks clearly - good speaker!

Great Tip!

Alexander on 11/12/2010
very nice video

Juri Kirillov on 11/13/2010
good to know

mpadigela on 11/14/2010
cool trick

Peter Voutov on 11/14/2010
It might be good to mention if a unique key can include multiple columns. My guess is that it can't in the table variable statement.

Aditya Kumar Bisoi on 11/15/2010
I want ot know more about index

Plamen Ratchev on 11/15/2010
Peter, yes, the unique key can include multiple columns. Here is example: DECLARE @Foo TABLE (col1 INT, col2 INT, UNIQUE(col1, col2));

Kenneth Wymore on 11/15/2010
Good information. I wasn't aware that you could do this with table variables but it makes perfect sense since the creation statement is similar to creating a standard table.

nilesh bilimoria on 11/17/2010
nice

nilesh bilimoria on 11/17/2010
scripts would be good to have

Brent Wells on 12/16/2010
I like it

Ludwig on 5/23/2011
Great stuff

Emre on 6/14/2011
what an accent

Eric Moreau on 6/14/2011
excellent tip

Jeya on 6/14/2011
Good one

SDAS on 6/14/2011
Simple and precise.

Omair on 6/14/2011
I would like to say why is there a key nlookip? This is worst than a table scan.

Sneha on 6/14/2011
Very good

Daniel Wolford on 6/14/2011
Short and sweet. Love adding new tricks to my arsenal.

Dennis Allen on 6/14/2011
I would like more discussion on when the cost of creating the index on a table-variable presents a higher cost than running the query that uses it without an index. How to measure this and rule-of-thumb for knowing when to index and when not to index.

Dennis Allen on 6/14/2011
I would like more discussion on when the cost of creating the index on a table-variable presents a higher cost than running the query that uses it without an index. How to measure this and rule-of-thumb for knowing when to index and when not to index.

Dennis Allen on 6/14/2011
I would like more discussion on when the cost of creating the index on a table-variable presents a higher cost than running the query that uses it without an index. How to measure this and rule-of-thumb for knowing when to index and when not to index.

Vladimir on 6/14/2011
Great tip!

Paul McCurdy on 6/14/2011
Short & sweet

Gerry Doan on 6/14/2011
Well focused and good presentation

Michael Neymit on 6/14/2011
Nice tip, thank you!

Bobby on 6/14/2011
Learned something new, thanks!

Keith Badeau on 6/14/2011
Great information. I didn't know that a table variable could contain a primary key. Thanks for all the videos.

Dana Kee on 6/14/2011
Great tip!

John O'Sullivan on 6/15/2011
excellent tip

Jacob on 6/15/2011
I did not know this.

John Barone on 6/15/2011
The title was a bit misleading. Yes, there are indexes used here, but I was looking to see if one could create a non-unique index here.

Matthew White on 6/16/2011
Genius! Best tip I have seen for a long time.

Anil Babu on 6/16/2011
good

Carlos B. Vasquez on 6/18/2011
Great job!!

Carlos B. Vasquez on 6/19/2011
this is very practical, thank you

Plamen Ratchev on 6/21/2011
Dennis Allen, as usual with performance questions the best is to look at the execution plan. There is no rule of thumb, you have to test in your particular scenario and see what works best.

Ron on 7/7/2011
Direct and to the point. Excellent.

revanth on 7/8/2011
good,

siva on 7/11/2011
I never tried this one! excellent

domingo asuncion on 8/18/2011
I Love it... - Domingo M. Asuncion

MyDoggieJessie on 8/22/2011
Great video. Just to note, if you make a subtle change you will get rid of the Key Lookup that takes a hit in "WHERE emp_nbr = 2" If you create the PRIMARY KEY as clustered on the first col, leaving the 3rd col as a UNIQUE NONCLUSTERED - you can kill both birds with the same stone :) Ex: DECLARE @Beer TABLE ( beer_no int PRIMARY KEY CLUSTERED, beer_name varchar(25), beer_id varchar(5) UNIQUE NONCLUSTERED ) INSERT INTO @Beer VALUES (1, 'Molson', '00001') INSERT INTO @Beer VALUES (2, 'Labatts', '00002') INSERT INTO @Beer VALUES (3, 'Guiness', '00003') SELECT Beer_no, Beer_name, beer_id FROM @Beer WHERE beer_no = 3 SELECT Beer_no, Beer_name, beer_id FROM @Beer WHERE beer_id LIKE '%0002'

MyDoggieJessie on 8/22/2011
I think another important thing to note is when you use this method and are inserting a lot of data into the temp table...there is a small performance hit when inserting each row (because the indexes need to be updated)

Olu on 10/24/2011
Brilliant - I never knew that you could do that

Maurice Ivory on 11/9/2011
Good video

Les Weaving on 5/3/2012
I find it really hard to understand what Plamen is saying



Must Be Logged In
 

How Do I Become a Video Author? |  Newsletter History

Copyright © Fourdeuce, Inc., 2005-2009. All Rights Reserved | Privacy Policy | Terms & Conditions