Learn more about SQL Server tools


Latest from MSSQLTips

Find Tables, Columns or Stored Procedures used in a SQL Server Reporting Services Report

Dynamically Refresh SQL Server Reporting Services Report Date Parameters

SQL Server 2016 T-SQL Syntax to Query Temporal Tables

Troubleshoot SQL Azure Bacpac Import Failures

On-Demand Webcast - Monitoring SQL Server with Mobile Devices

Configure the SQL Server Integration Services For Loop Container

On-Demand Webcast - Performance Monitoring with Uptime

On-Demand Webcast - Physical and Virtual Performance Monitoring with SQL Diagnostic Manager

On-Demand Webcast - Encrypting Data with SQL Server

On-Demand Webcast - Performance monitoring with Spotlight on SQL Server

Whitepaper - Resolving the Database Performance Blame Game

3 Tips for Managing Large Numbers of SQL Server Jobs

Accelerate SQL Server with Flash Storage

SQL Server Four part naming

MSSQLTips author Greg Robidoux By:   |   Read Comments   |   Related Tips: More > T-SQL

>>> >> > Vote for your favorite MSSQLTips.com Authors < << <<<

With some applications there may be a need to pull data from other instances of SQL Server or from other databases within the same instance.  Often this can be achieved by having multiple connections from your application pointing to each of these data sources. This is great for this one application, but what if there is a need to do this within SQL Server or for stored procedures or views that are called from several applications?

SQL Server offers the functionality to reference objects within the database you are working or to reference objects in another database or even a different instance of SQL Server.  This is referred to as four-part naming.  The reason for this name is that there can be four parts that are used to reference the object as the following shows:

For SQL Server 2000

For SQL Server 2005

In most cases, your SQL Server code probably only references the object name such as:

SELECT * FROM sysobjects

Owner or Schema
To further qualify this we can also specify the owner or schema of the object:

SELECT * FROM dbo.sysobjects

To take this a step further we can also reference the database that the object resides in such as:

SELECT * FROM master.dbo.sysobjects

And last we can reference this table on a totally different server by specifying the server name:

SELECT * FROM test1.master.dbo.sysobjects

The ability to reference an object this way opens up several possibilities whether it be within the same database or on other databases or other servers.  One example is the use of the owner or schema part.  This alone allows you to have the same object name in your database several times and with the owner or schema reference you can now signify which object you actually want to use in your code.

Also, if you have a standard database that may be used by multiple databases, by using the database part you can now have multiple databases pull in this standard data by using the database part of the four-part name.

And lastly, you can even separate your data even further by using the fourth part of the name that references the server.  One thing to note is that you will need to setup linked servers in order to use this option.  More information about linked servers can be found here for SQL Server 2000 and for SQL Server 2005.  Another thing to note is that there may be some performance issues if you are linking servers across your LAN or WAN and also if you are pulling in a lot of data.

Next Steps

  • Take a look at your code to see if there are areas that could benefit from this four-part naming structure
  • Another option to look at to retrieve data from another server or data source is OPENROWSET
  • Look at the use of SYNONYMS in SQL Server 2005 to simplify four-part naming
  • Also, take a look at ownership chaining and security/permission issues

Last Update: 11/6/2006

About the author
MSSQLTips author Greg Robidoux
Greg Robidoux is the President of Edgewood Solutions and a co-founder of MSSQLTips.com.

View all my tips

print tip Print  
Become a paid author

join MSSQLTips for free SQL Server tips     

Learn more about SQL Server tools

Post a comment or let the author know this tip helped you.

       All comments are reviewed, so stay on subject or we may delete your comment.

*Name   *Email Notify for updates

       Note: your email address is not published. Required fields are marked with an asterisk (*)

Get free SQL tips:

*Enter Code refresh code     

Sponsor Information



Get Free SQL Tips










BI Professionals


Q and A

Today's Tip







Tip Categories

Search By TipID



First Timer?


Free T-shirt



User Groups

Author of the Year

More Info








Copyright (c) 2006-2015 Edgewood Solutions, LLC All rights reserved
Some names and products listed are the registered trademarks of their respective owners.