GilaMonster (8/8/2011)

---

skcadavre (8/8/2011)

---

"Table variables are not the same as temporary tables. The biggest differences are that: -

•Temporary tables can be altered with DDL statements whereas table variables cannot.

Essentially, this means a table variable is a heap, so every access to the table variable requires a table scan. This quickly becomes extremely costly when looking at a large dataset.

The first statement is correct, but the conclusion is not. I can easily create a table variable that has a clustered index and a couple nonclustered indexes. The trick is that the table has to be created with them, not altered afterwards.

DECLARE @SomeTable TABLE (

ID INT IDENTITY PRIMARY KEY, -- clustered index

SomeStringColumn VARCHAR(20), -- I want to index this too, soo...

ARandomDate DATETIME,

UNIQUE (SomeStringColumn, ID)

);

Table variable with a clustered and a nonclustered index. SQL will seek on those indexes too quite happily.