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 |
|
uberbloke
Yak Posting Veteran
67 Posts |
 Posted - 2002-09-24 : 07:11:07
|
| Is there a way to create an index on a column in a table variable?DECLARE @results TABLE( [uid] INTEGER IDENTITY PRIMARY KEY CLUSTERED, [clientcount] INTEGER, ....lots more stuff...)CREATE INDEX pleasework ON @results(clientcount) This falls over... not sure yet if I need the extra index (playing around with stuff) but it is hard to investigate the effects of something you cant get to work....I have searched BoL and google (and here) for a pointer ya/nay but have yet to find anything conclusive (but given that it doesn't seem to work anyway I suspect the prognosis is poor). |
|
|
AndrewMurphy
Master Smack Fu Yak Hacker
2916 Posts |
Posted - 2002-09-24 : 07:37:33
|
| I think the issue you have to contend with....is what's the point....a TABLE variable is stored in memory.....and as such access should be impressive enough not to require ANY index.If on the otherhand, you are looking to enforce uniqueness (which often goes hand in hand with an index), you MAY be able to add CONSTRAINTS to the table definition....As you are experiencing, if the CREATE INDEX statement isn't working, it probably isn't allowed. |
 |
|
|
Page47
Master Smack Fu Yak Hacker
2878 Posts |
Posted - 2002-09-24 : 08:21:38
|
quote:
and as such access should be impressive enough not to require ANY index.
- I dunno about that ...quote:
Indexes or other contraints applied to the table must be defined as part of the DECLARE variable or CREATE FUNCTION statement. They cannon be applied later, because the CREATE INDEX or ALTER TABLE statements cannont reference table variables and user-defined functions.-SQL 2K, Books Online
Your PRIMARY KEY designation does infact create a clustered unique index on uid. However, I don't believe there is a way to create a non-unique index as part of the CREATE TABLE ddl, so I think you are out of luck. You can do a CREATE INDEX statement on a temp table. I would try the temp table with the index vs. the table variable without and see which is faster in your environment ....Jay White{0} |
|
|
|
jasper_smith
SQL Server MVP & SQLTeam MVY
846 Posts |
Posted - 2002-09-24 : 08:32:51
|
You can only add column/tabale constrainsts, you cannot run a create index statement against a table variable e.g.declare @t table(col1 int NOT NULL PRIMARY KEY CLUSTERED , col2 char(5) NOT NULL, col3 char(5) NOT NULL)declare @t table(col1 int NOT NULL, col2 char(5) UNIQUE NONCLUSTERED NOT NULL, col3 char(5) NOT NULL)declare @t table(col1 int NOT NULL, col2 char(5) NOT NULL, col3 char(5) NOT NULL, PRIMARY KEY CLUSTERED(col1,col2)) HTHJasper Smith |
|
|
|
uberbloke
Yak Posting Veteran
67 Posts |
Posted - 2002-09-24 : 09:00:52
|
| Thanks for the replies.... |
|
|
|
AndrewMurphy
Master Smack Fu Yak Hacker
2916 Posts |
Posted - 2002-09-24 : 11:32:13
|
| If the point of an index is to speed up access to data, and the data is stored directly in memory....then it's already addressable....there is (little or) no 'seek-time' because everything in the table is already in the fastest-accessible form going...memory.The only (main) issue to contend with is on systems with memory contention problems, when the particular area of interest in the table could be paged out to disk.I would have thought that anything that is always in memory, would be a quantum level faster than something stored on disk (or even cache which can expire/be paged out) |
|
|
|
|
|
|
|
|
Index on Table Variable