Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

share|improve this question
2  
Include the schema name in the FROM clause, and use table aliases. –  Jon Seigel Oct 31 '13 at 19:41
    
Can you elaborate on why I should use aliases? –  Matthew Verstraete Oct 31 '13 at 19:42
    
Refer to Aaron's write up on using table aliases (sqlblog.com/blogs/aaron_bertrand/archive/2009/10/08/…) –  Kin Oct 31 '13 at 19:48
    
@Kin I understand why you should use aliases but in a single table select why should I use an alias? Do aliases also include the schema if you include it? A bit more reasoning behind the use of aliases as it pertains to my question would be nice. –  Matthew Verstraete Oct 31 '13 at 19:55
    
Using Aliases is a matter of code readability. For eg. select column1, column2 from table1 would not generally need an alias vs select t1.column1, t2.column1, t2.column2 from dbo.table1 as t1 inner join dbo.table2 as t2 on t1.ID = t2.ID is where it makes sense to use Aliases. –  Kin Oct 31 '13 at 19:58
show 3 more comments

1 Answer

up vote 2 down vote accepted
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.

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.