Table Variable vs Parallelism

There are many myths surrounding table variables and one of the most common is probably the 'in memory' story. There are however a couple of other interesting facts about temp variables which you should also know about. The guys from the Storage Engine have an excellent post about table variables so you definitely have to read it.

One of the things that caught my eye in the post was the fact that queries do not go parallel when table variables are involved. This was actually something I had never come across so I decided to put it to the test. I borrowed a query from Craig Freedman who has an excellent series on parallelism. Here we go:

CREATE TABLE T (A INT, B INT IDENTITY, C INT, D INT)
CREATE CLUSTERED INDEX TA ON T(A)

SELECT COUNT(*) FROM T OPTION (MAXDOP 0)

UPDATE STATISTICS T WITH ROWCOUNT = 1000000, PAGECOUNT = 100000

SELECT COUNT(*) FROM T OPTION (RECOMPILE, MAXDOP 0)

DECLARE @t AS TABLE
(NumberOfRows int)

INSERT INTO @t
SELECT COUNT(*) FROM T OPTION (RECOMPILE, MAXDOP 0)

For those of you who want to try it with a temp table:

CREATE TABLE #t
(NumberOfRows int)

INSERT INTO #t
SELECT COUNT(*) FROM T OPTION (RECOMPILE, MAXDOP 0)

DROP TABLE #t

EXEC AT

With every release there are really some features that you seem to miss, maybe because they are small or maybe because you just do not use that functionality that often.

That is how I recently stumbled upon a nice feature that has been added in SQL Server 2005 but I had never seen before.

EXEC AT allows you to execute queries on a linked server just like OPENQUERY and OPENROWSET but with a little less limitations. OPENQUERY and OPENROWSET for example do not accept variables for their parameters and they act like a table and thus limit you from executing certain statements like DDL.

EXEC AT on the other hand can take parameters:

EXEC sp_addlinkedserver [MyLinkedServer], 'SQL Server';

EXEC
(
'SELECT [LanguageID], [Description]
FROM myDB.dbo.Translations
WHERE TranslationID = ?;', 1000
) AT [MyLinkedServer]

It can do DDL:

EXEC
(
'USE myDB;
CREATE TABLE myTable
(myId int NOT NULL PRIMARY KEY,
myVarChar varchar(100) NULL
)'
) AT [MyLinkedServer]

It can take a username:

EXEC
(
'SELECT [LanguageID], [Description]
FROM myDB.dbo.Translations
WHERE TranslationID = ?;', 1000
) AS USER = 'WesleyB' AT [MyLinkedServer]

And last but not least it can take a variable:
DECLARE @SQLStmt nvarchar(max)
SET @SQLStmt = 'SELECT [LanguageID], [Description] FROM myDB.dbo.Translations'

EXEC(
@SQLStmt
) AT [MyLinkedServer]

It may not be the most funky feature in SQL Server 2005 but it really has potential when you are working with linked servers.

Insufficient result space to convert uniqueidentifier value to char.

I needed a char representation of a GUID field but couldn't retrieve it because of an obscure error :-s

Insufficient result space to convert uniqueidentifier value to char.

This was caused by a CAST as varchar without specifying the length (or a too short length).

Just using CAST(field as char(36)) solved the problem.