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.
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.