Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
Lumbago
Norsk Yak Master
3271 Posts |
 Posted - 2005-04-14 : 07:21:58
|
| Is there a point to putting an index on a table variable? The execution plan would look prettier but since it's all in memory would I see any performance gain?--Lumbago"Real programmers don't document, if it was hard to write it should be hard to understand" |
|
|
spirit1
Cybernetic Yak Master
11752 Posts |
Posted - 2005-04-14 : 07:33:58
|
AFAIK, you cant put an index on a table varaible unless you have a PK on a column when a NC index is automatically created.Go with the flow & have fun! Else fight the flow  |
 |
|
|
Lumbago
Norsk Yak Master
3271 Posts |
Posted - 2005-04-14 : 08:13:42
|
| There are no such restrictions to table variables...they work almost exactly like regular tables only they are in memory and within the current scope only.--Lumbago"Real programmers don't document, if it was hard to write it should be hard to understand" |
|
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2005-04-14 : 08:17:58
|
| You cannot create indexes or statistics on table variables. A primary key or unique constraint are the only constraints you can put on a table variable that would create an index. This is one of the areas where a temp table has a distinct advantage, especially for large rowsets. |
|
|
|
Lumbago
Norsk Yak Master
3271 Posts |
Posted - 2005-04-14 : 08:31:11
|
Ok, excellent. Sorry for "contesting" your answer spirit but I looked up "table variables" in BOL and it said nothing about indexes which I find somewhat odd. But I just tested a few options and found that you are both right...thanx alot fellas, makes my day a bit easier  --Lumbago"Real programmers don't document, if it was hard to write it should be hard to understand" |
|
|
|
|
|
|
Index on table variable