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 setup 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 as long as 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 was 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 was 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 possibly 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 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.

OBJECT: This part of the name must always be used no matter if the other three parts of the name is 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.


*