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 |
|
cjpsx
Starting Member
34 Posts |
 Posted - 2006-06-08 : 14:20:19
|
| I have declared a table in TSQL: DECLARE @tempTable1 TABLEI'm trying to create an index on this "table" but do not know if it is even possible:CREATE INDEX idx1 ON @tempTable1 (invoiceid)Is this possible?Thanks,cj |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2006-06-08 : 14:25:39
|
| You can not add an index to the table variable, however you can add a primary key constraint to the table variable, that is if invoiceid is unique. You get an index with a PK constraint.DECLARE @tempTable1 TABLE (invoiceid int PRIMARY KEY CLUSTERED)Tara Kizeraka tduggan |
 |
|
|
cjpsx
Starting Member
34 Posts |
Posted - 2006-06-08 : 14:37:42
|
| Thank you. I will try that. Are there any performance issues between a table variable and a #temp table? |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2006-06-08 : 14:42:14
|
| Yes. But you'll need to google that as it has been discussed at length already. SQL Server Books Online indicates that you should use table variables usually. Other people have argued against that. After doing your research, I'd recommend testing both scenarios to determine which will work better for you in your particular situation. Each situation will different. You might have some that perform better with table variables and some with temporary tables.Tara Kizeraka tduggan |
|
|
|
madhivanan
Premature Yak Congratulator
22864 Posts |
Posted - 2006-06-09 : 10:19:21
|
quote:
Originally posted by cjpsx
Thank you. I will try that. Are there any performance issues between a table variable and a #temp table?
Read thishttp://www.aspfaq.com/show.asp?id=2475MadhivananFailing to plan is Planning to fail |
|
|
|
X002548
Not Just a Number
15586 Posts |
|
|
|
|
|
Creating Index on @table