Results 1 to 4 of 4
  1. #1
    Join Date
    Dec 2003
    Posts
    22

    Unanswered: Table datatype and indexes?

    I'm trying to add an index to a Table datatype, inside of a stored procedure, but I receive an error 'Incorrect syntax near '@temp1''.

    We were originally using standard temp tables(#temp1) in our stored procedures, until we discovered a huge amount of lock timeouts on tempdb. We changed the SPs to use the Table datatype instead, which cured the locking problem, but we couldn't figure out how to add the indexes.

    Any ideas?

    Thanks,
    Rob

  2. #2
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    149
    You cannot create any indexes on table variables. The only idexes allowed are the ones created because of the Primary Key and Unique key constraints. This is one of the limitations of table variable Vs temp table.

  3. #3
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    I don't know if you can...

    YOu can do this, which I think might create an index

    DECLARE @myTable99 TABLE (Col1 int IDENTITY(1,1) NOT NULL PRIMARY KEY, Col2 char(1), Col3 int)

    But a temp table's data is usually scanned...so you wouldn't incur any benefit anyway..

    Besides, temp tables shouldn't be holding large volumes of data

    Can't you make a physical work table if you're pushing around that much data?
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  4. #4
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    You can't create an index, but you can define PRIMARY KEY at the time of declaring the table.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •