pkasemsant (8/8/2011)

---

As developer and still got lots to learn in SQL Server administration, can anyone tell me, which is better using temp table(s) or creating table variable(s) within these conditions,
i. Small or relatively medium size return rows
ii. Only used once to process within the working SP.

I alway thought that using variable is better, or is it? This always in back of my mind, would be great if any experience SQL Admins can reply for me.

Thanks in advance for your help.

skcadavre (8/8/2011)

---

"[i]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.

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)
);

GilaMonster (8/8/2011)

---

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.

Ninja's_RGR'us (8/8/2011)

---

Not to be too anal here, but you can't really add NC indexes, you can only add NC constraints.

pkasemsant (8/8/2011)

---

As developer and still got lots to learn in SQL Server administration, can anyone tell me, which is better using temp table(s) or creating table variable(s) within these conditions,
i. Small or relatively medium size return rows
ii. Only used once to process within the working SP.

I alway thought that using variable is better, or is it? This always in back of my mind, would be great if any experience SQL Admins can reply for me.

Thanks in advance for your help.

GilaMonster (8/8/2011)

---

And a unique constraint is enforced by a unique index, by default a nonclustered one. Other than splitting hairs was there a point?

If the column that you want to have the index on is not unique, well, I had that in the example above...