none
Using the fully qualified names in T-SQL

    Question

  • Hi,

    I just want to know is there is any deference in performance or other benefits that I’m going to gain from using the full-qualified name of a table or column or it’s just a matter of following or applying a standard.

    For example what is the deference using the first or second T-SQL select statement (Assuming that I have database named HR, Schema named dbo, Table named Employee and some columns exists in Employee table “EmployeeId, EmployeeName”).

    1. SELECT EmployeeId, EmployeeName FROM Employee

    2. SELECT dbo.Employee.EmployeeId, dbo.Employee.EmployeeName From HR.dbo.Employee

    Thanks.


    • Edited by Samijf Sunday, May 19, 2013 10:24 PM
    Sunday, May 19, 2013 10:06 PM

Answers

  • I would choose a third choice.  First, I would not specify the database name unless necessary.  In particular, I would never specify the database name if I meant the current database.  The reason for this is sometimes you want to restore a database as some other name for testing (for example TESTHR instead of HR).  If you have the database name embedded in the queries, that is difficult to do.  But if you do not, then for testing, all you need to do is connect to the TESTHR database.  Even if you must do cross database queries, I would normally create synonyms for the objects in the foreign database.   That way, if you need to use a test database, you only need to drop and add the synonym, not change the database name everywhere in your code.

    Second, why I recommend qualifying column names (even when you have only one table - that makes it easier if/when the query is later changed to reference multiple tables - I would always use table alias's not the fully qualified table name when referencing the table columns.  For me, anyway, this makes the query more readable.

    But in the FROM/JOIN clauses, do specify the schema name for the table.  That will allow more plan reuse, so it is a (slight) improvement to performance.  So

    SELECT e.EmployeeID, e.EmployeeName FROM dbo.Employee e;

    Tom

    Monday, May 20, 2013 2:24 AM

All replies

  • In addition to avoiding ambiguity, schema-qualifying object names is a best practice because to promotes reuse of execution plans.


    Dan Guzman, SQL Server MVP, http://www.dbdelta.com

    Monday, May 20, 2013 1:48 AM
  • Columns need not have three part naming convention as there is only one table in the SELECT clause.

    If multiple tables are used in the query(JOINs , three part naming conventions looks good. Use alias names for the tables and mention that for columns instead of fully qualified name.

    The three part naming is for better readability and understanding of the code to us as well as for optimizer. It may not give any noticeable performance benefits, but helps optimizer to resolve the conflicts with user defined objects names.


    Thanks
    Sarat

    Please use Marked as Answer if my post solved your problem and use Vote As Helpful if a post was useful.

    Monday, May 20, 2013 1:55 AM
  • The 1st statement means you are selecting from the current schema used. While you can retrieve the list by 2nd one within whatever schemas in the current DB.

    Many Thanks & Best Regards, Hua Min

    Monday, May 20, 2013 1:56 AM
  • I would choose a third choice.  First, I would not specify the database name unless necessary.  In particular, I would never specify the database name if I meant the current database.  The reason for this is sometimes you want to restore a database as some other name for testing (for example TESTHR instead of HR).  If you have the database name embedded in the queries, that is difficult to do.  But if you do not, then for testing, all you need to do is connect to the TESTHR database.  Even if you must do cross database queries, I would normally create synonyms for the objects in the foreign database.   That way, if you need to use a test database, you only need to drop and add the synonym, not change the database name everywhere in your code.

    Second, why I recommend qualifying column names (even when you have only one table - that makes it easier if/when the query is later changed to reference multiple tables - I would always use table alias's not the fully qualified table name when referencing the table columns.  For me, anyway, this makes the query more readable.

    But in the FROM/JOIN clauses, do specify the schema name for the table.  That will allow more plan reuse, so it is a (slight) improvement to performance.  So

    SELECT e.EmployeeID, e.EmployeeName FROM dbo.Employee e;

    Tom

    Monday, May 20, 2013 2:24 AM