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