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

Download a free trial of SQL Backup now

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.23 out of 5
Publish Date:
November 03, 2008
Using Indexes on Tables Variables in SQL Server Watch Video Now  Watch it later!
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



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