Table Variable Name as Column Prefix

See also, "Qualifying a Table Variable's Columns" and "Jump Start: Table Variables."

Since the introduction of table variables with SQL Server 2000, until recently, I believed you couldn’t prefix a column name directly with a table variable name, as in @T1.col1. You need to table qualify a column name when referring to a column with an ambiguous name, e.g., in a query involving multiple tables with the same column name. When trying to use a table variable name as a column prefix directly, as in @T1.col1, you get an error. Until recently, I thought that the only way to get around this limitation was to assign an alias to the table, e.g., @T1 AS A, and then when referring to the ambiguous column name, to use the alias as the prefix, as in A.col1.

Recently, to my great surprise, I learned from my friend SQL Server MVP Erland Sommarskog that all you need to do is delimit the table variable name (including the @ sign) just like you delimit irregular identifiers with square brackets, as in [@T1].col1, or with double quotes, as in “@T1”.col1.

To test this, first try running the following code which uses non-delimited table variable names as column prefixes, and see that it fails:

DECLARE @T1 TABLE(col1 INT);

INSERT INTO @T1(col1) VALUES(1);

INSERT INTO @T1(col1) VALUES(2);

DECLARE @T2 TABLE(col1 INT);

INSERT INTO @T2(col1) VALUES(2);

INSERT INTO @T2(col1) VALUES(3);

-- Query 1

SELECT @T1.col1

FROM @T1 JOIN @T2

  ON @T1.col1 = @T2.col1;

-- Query 2

SELECT col1

FROM @T1

WHERE EXISTS

  (SELECT *

   FROM @T2

   WHERE @T2.col1 = @T1.col1);

You will get an error for each reference to the non-delimited table variable name as a prefix:

Msg 137, Level 16, State 1, Line 10

Must declare the scalar variable "@T1".

Msg 137, Level 16, State 1, Line 12

Must declare the scalar variable "@T1".

Msg 137, Level 16, State 1, Line 12

Must declare the scalar variable "@T2".

Msg 137, Level 16, State 1, Line 20

Must declare the scalar variable "@T2".

Msg 137, Level 16, State 1, Line 20

Must declare the scalar variable "@T1".

Then replace Query 1 and Query 2 with corresponding queries, but ones that use delimited table names, and rerun the code. Here’s an example using square brackets as delimiters:

-- Query 1

SELECT [@T1].col1

FROM @T1 JOIN @T2

  ON [@T1].col1 = [@T2].col1;

-- Query 2

SELECT col1

FROM @T1

WHERE EXISTS

  (SELECT *

   FROM @T2

   WHERE [@T2].col1 = [@T1].col1);

And here’s one using double quotes:

-- Query 1

SELECT "@T1".col1

FROM @T1 JOIN @T2

  ON "@T1".col1 = "@T2".col1;

-- Query 2

SELECT col1

FROM @T1

WHERE EXISTS

  (SELECT *

   FROM @T2

   WHERE "@T2".col1 = "@T1".col1);

Both delimited versions run successfully.

Even though it’s a small thing, I wish I had known about it nine years ago! :)

Cheers, and thanks Erland!

BG