When to use fully qualified names in SQL Server

In Sql Server each and every object name has four parts.
1. Server Name
2. Database Name
3. Owner Name
4. Object Name

If you quality Object Name with Server Name, Database Name and Owner then it is referred as Fully Qualified Name.

Some people believes that if you fully qualify the object name it will improve the performance. But its not true. Depends on the need you need to make a right choice. As an example if your application is using one/single database always then you can write queries as shown below:

SELECT * FROM HumanResources.Shift

Sometimes owner name is also referred as Schema. If you take AdventureWorks2008 database, then there are various schemas like HumanResources, Sales, Production, Person etc.

If you have multiple databases and your queries always joins two or more tables from two or more different databases then it will be good if you qualify with database name as shown below:

SELECT * FROM
AdventureWorks2008.HumanResources.Shift

SELECT * FROM
VJ_LCL.dbo.emp

VJ_LCL is my local database and the schema owner of emp table is dbo. Similarly AdventureWorks2008 is database and HumanResources is the schema name and Shift is the table name. Rememeber, object name has to be unique within each schema.

On the other hand, if you have queries that has joins between two or more tables from two or more different servers then you need to fully qualify the object name as show below:

SELECT * FROM
VJMSSQLServer.AdventureWorks2008.HumanResources.Shift

If you fully qualify each and every object even when you have single database then you might get into following problems:

1) If you are planning to change the database, your code will break
2) If you are planning to change the server or deploying it on another server then your code will break.

Reference: Vijaya Kadiyala (http://DotNetVJ.com)