SQL Four-Part Names

SQL Four Part Names

As part of the T-SQL naming convention in SQL Server every object (Table, View, etc…) has a four-part name used to reference that object.

  • For SQL Servers prior to 2005 the four-part name used the format Server.Database.Owner.Object
  • For SQL Server 2005 and later the naming convention changed to the format Server.Database.Schema.Object

Here we will break down each piece of the naming convention.

SERVER: This part of the name allows you to reference objects that may be on a different server. This part of the name is not often used and does require that linked servers have been set up to reference the object.

DATABASE: This part of the name allows you to specify which database where an object is located. This is helpful if you are writing code that may reference a separate database. However, since most code is contained within a single database if the database has been referenced by using a USE statement or by the Available Databases drop down in Management Studio it may not be necessary to include this portion of the address when referencing an object.

OWNER/SCHEMA: Prior to SQL Server 2005 the third part of the name referenced the OWNER of the object. If the owner of the object were accessing the object, they would not need to use this part of the name and would simply reference only the object name.

However, for those who were not the owner of the object, they would have to include the owner as part of the name, or the code would fail. To be more specific, if the owner part of the name was not supplied by a non-owner, SQL Server would check to see if the object were owned by dbo to see if the object existed before failing. (Also, issues would arise if the owner of an object left the company. All objects and every object that made a reference to those objects would need to be re-written.) This is the reason most companies would use the dbo object as the owner so that this part of the name would not need to be included no matter who referenced the object.

In SQL Server 2005 and later this concept changed to use the SCHEMA as the third part of the object name. This provides many benefits within SQL Server such as the better organization of objects, more fine-grained security of multiple objects, and for our purposes no longer needing to know the owner of each object that needed to be referenced. Many companies still organize objects under the dbo schema as a throwback to pre-2005 code, but this limits some of the many advantages of using schemas. Another solution for older code would be to create a synonym for object that reference dbo as part of the name.

--Pre 2005 reference by owner name
SELECT * FROM John.Employees;

--Using dbo to reference an object
SELECT * FROM dbo.Employees;

--Post 2005 reference by schema name
SELECT * FROM HR.Employees;

OBJECT: This part of the name must always be used no matter if the other three parts of the name are used or not. This is the only required part in referencing objects in your code.

Be the first to comment on "SQL Four-Part Names"

Leave a comment

Your email address will not be published.


This site uses Akismet to reduce spam. Learn how your comment data is processed.