table object - create index

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Man T - 21 May 2008 01:12 GMT

declare @summaryEmp table (ID int,
Code varchar(20),
Paid money)
create index idx_summaryEmp on @summaryEmp (Code)

I got error:
Incorrect syntax near '@summaryEmp'

Reply to this Message

Aaron Bertrand [SQL Server MVP] - 21 May 2008 01:30 GMT

You can't create an explicit index on a table variable, unless you do so
indirectly via a constraint (e.g. Primary key). As a workaround, use a
#temp table.

On 5/20/08 8:12 PM, in article e6H0ObtuIHA.4560@TK2MSFTNGP03.phx.gbl, "Man
T" <alanpltse_NOSPAM@yahoo.com.au> wrote:

> declare @summaryEmp table (ID int,
> Code varchar(20),
[quoted text clipped - 3 lines]
> I got error:
> Incorrect syntax near '@summaryEmp'

Reply to this Message

Man T - 23 May 2008 08:10 GMT

Thanks, I will define the primary key then.

> You can't create an explicit index on a table variable, unless you do so
> indirectly via a constraint (e.g. Primary key). As a workaround, use a
[quoted text clipped - 7 lines]
>> I got error:
>> Incorrect syntax near '@summaryEmp'

Reply to this Message

Omid Golban - 31 Jul 2008 23:54 GMT

We were doing that in our routines and I had a little trouble with these temp
tables not going away. Something about SQL server had to be restarted...
Data from previous run was intermixed with our next run...

so we thought about two choices and picked choice 2:
1. check if #myTable exists
then: DELETE #myTable
else: create the table & its index

2. CREATE TABLE MyTable
CREATE INDEX MyIndex ON MyTable ...
do our processing
DROP INDEX MyIndex
DROP TABLE MyTable

> Thanks, I will define the primary key then.
>
[quoted text clipped - 9 lines]
> >> I got error:
> >> Incorrect syntax near '@summaryEmp'

Reply to this Message