1

I'm trying to create an index on a table variable, is this possible?

DECLARE @RDTABLE TABLE (TLCDE VARCHAR(12), TLTYP VARCHAR(2), TLEFFDAT DATETIME, TLRATFCT DECIMAL(29,6))

INSERT INTO  
    @RDTABLE
SELECT
    TLCDE
,   TLTYP
,   TLEFFDAT
,   TLRATFCT
FROM    
    BT_TR
WHERE
    TLTYP = 'TX'

Normally I would create the index like so:

CREATE NONCLUSTERED INDEX IX_RDTABLE ON RDTABLE ([TLTYP],[TLEFFDAT]) 
INCLUDE ([TLCDE],[TLRATFCT])
nowYouSeeMe
  • 935
  • 6
  • 11
  • 21

1 Answers1

6

yes, you can create index directly in table variable's script like this:

DECLARE @RDTABLE TABLE (TLCDE VARCHAR(12), TLTYP VARCHAR(2), TLEFFDAT DATETIME, TLRATFCT DECIMAL(29,6), INDEX [index_name] [NONCLUSTERED/CLUSTERED](column_name))

considers if it's the case of using temporary tables instead table variable, because depending on the case could be better to handle the indexes

this features is available only from sqlserver 2014 version

i've found this article on the internet:

http://www.brentozar.com/archive/2014/04/table-variables-good-temp-tables-sql-2014/

rdenisi
  • 139
  • 2
  • 4