Sql-server – Should you use the schema name in WHERE, ON, and other clauses

sql-server-2008-r2t-sql

I know it is good practice, and a performance boost, in SQL Server to prefix the table name with the schema (dbo.TableName) in the FROM clause of a SELECT statement but should you also use it on other clauses like the WHERE or ON? Should you also specify the TABLE name for columns in the where clause if the column name is unique?

Take two scenarios:

SELECT Col1, Col2, Col3
FROM dbo.Table
WHERE Col1 = Value;

and

SELECT Col1, Col2, Col3,
FROM dbo.Table
INNER JOIN dbo.Table2
ON Col1 = Col5
WHERE Col6 = Value;

In the first scenario should I add dbo.Table to each column name?

IN the second scenario if I added an alias to each table would that automatically include the schema data too or would I still need to pre-fix the column names with the schema.

And in both scenarios do I even NEED to prefix the columns with schema data or is that pointless and gives no performance boost at all?

Best Answer

SELECT Col1, Col2, Col3
FROM dbo.Table
WHERE Col1 = Value;

In the first scenario should I add dbo.Table to each column name?

For single tables, table aliases are OK to be excluded. 

SELECT Col1, Col2, Col3,
FROM dbo.Table
INNER JOIN dbo.Table2
ON Col1 = Col5
WHERE Col6 = Value;

IN the second scenario if I added an alias to each table would that automatically include the schema data too or would I still need to pre-fix the column names with the schema.

Table alias will include the FULLY qualified table name.

e.g. dbo.Table as T1 will reference the dbo.Table.

And in both scenarios do I even NEED to prefix the columns with schema data or is that pointless and gives no performance boost at all?

First your query example is wrong. It should be (carefully see the aliases I have used, else it will be syntactically wrong - SQL Server will throw errors).

 SELECT T1.Col1, T1.Col2, T2.Col3,
    FROM dbo.Table T1
    INNER JOIN dbo.Table2 T2
    ON T1.Col1 =  T2.Col5
    WHERE T2Col6 = Value;

Hopefully, the above query will answer your 3rd question as well.

Related Question