SQL Server Performance

Index on TABLE Variable

Discussion in 'General Developer Questions' started by georgedamien, Nov 2, 2006.

  1. georgedamien New Member


    I wanted to know if I can create Index on a TABLE variable

    I am using the following code

    declare @order_status_codes Table
    (
    [status] [varchar] (15)
    )

    create clustered index idx_status on @order_status_codes(status)

    I am getting the following error message

    : Incorrect syntax near '@order_status_codes'.

  2. Adriaan New Member

    Easy enough:

    declare @order_status_codes Table ([status] [varchar] (15) primary key clustered)

    In case you need a multi-column unique index, I don't think you can add that to a table variable.
  3. georgedamien New Member


    Hey Thanks Adriaan

    but can i create an index using the create index command

  4. FrankKalis Moderator

    No, table variables only support PRIMARY KEYS and UNIQUE CONSTRAINTs, which are implemented by indexes in SQL Server. You cannot create additional nonclustered indexes, for example. That's one major drawback of table variables as compared to temp tables.

    --
    Frank Kalis
    Moderator
    Microsoft SQL Server MVP
    Webmaster:http://www.insidesql.de
  5. Roji. P. Thomas New Member

    quote:Originally posted by Adriaan

    Easy enough:
    In case you need a multi-column unique index, I don't think you can add that to a table variable.
    You can.
    declare @tab Table (id int, name varchar(10), PRIMARY KEY(id, name))

    Roji. P. Thomas
    http://toponewithties.blogspot.com
  6. Adriaan New Member

    As far as I know, CREATE INDEX only works for permanent and temporary tables. Table variables support constraints only.

    I did some tests with the syntax, and you actually can add a multi-column unique constraint to a table variable - the syntax is just not explained very clearly in BOL:

    DECLARE @MyTable TABLE (col1 VARCHAR(1), col2 VARCHAR(1) UNIQUE (col1, col2))

    INSERT INTO @MyTable VALUES ('a', 'a')
    INSERT INTO @MyTable VALUES ('a', 'b')
    INSERT INTO @MyTable VALUES ('a', 'a') >>> "VIOLATION OF UNIQUE KEY CONSTRAINT"

    BOL says: "Creating a PRIMARY KEY or UNIQUE constraint automatically creates a unique index on the specified columns in the table." This could be understood as: adding a unique key to a table variable also creates an index on the table variable.

    The only problem then is that you can't have a non-unique index.

Share This Page