CODEWITHSUNDEEP
sqlsql-serverdatabase
bornfromanegg
bornfromanegg

Reputation: 2918

How should table names be qualified in SQL Server 2012?

Let's say I have a database called MyDB, containing a table called MyTable. I can reference this as

MyDb.dbo.MyTable

However, if I do this in a stored procedure, and then I deploy the database with a different name, the stored procedure will no longer work.

If I qualify it as

dbo.MyTable

then it will work, and I can name the database anything I want. But if I no longer know what my databases are going to be called when deployed, I can't reference a table in another database. i.e. I have no way of specifying that the table I actually want is in another database (on the same server) i.e.

MyOtherDb.dbo.MyTable

This: http://www.sql-server-performance.com/2001/sql-best-practices/ says

Prefix the table names with the owner’s name, as this improves readability and avoids any unnecessary confusion. Microsoft SQL Server Books Online even states that qualifying table names with owner names helps in execution plan reuse, further boosting performance.

I'm new to SQL Server and am not entirely sure what an owner is. Also, many of the other resources dictate that the "fully qualified name" should be used, without saying exactly what they mean by "fully qualified name".

Sorry if this is a dumb question, but what is best practice here? I'd rather know this now, than work it out for myself three months down the line and have a lot of refactoring to do. Thanks!

Upvotes: 2

Views: 6312

Answers (2)

Martin Smith
Martin Smith

Reputation: 453406

Use two part names for objects in the same database.

This means that you can create a copy of the database with a different name and don't have to go through and alter the database names in three part naming format.

For objects in different databases you need to use three part names (or four if it is a linked server).

Often you can use synonyms to centralise these external references. This makes it easier to redirect them to a different location in development. Though synonyms don't quite have all the functionality that referencing the object directly would have (e.g. TRUNCATE TABLE dbo.YourSynonym or CREATE INDEX referencing a synonym does not work).

Upvotes: 3

user4109297
user4109297

Reputation: 11

An owner in Sybase(another DBMS) is similar to Schema in SQL Server. By fully qualified name they mean following:

ServerName.DatabaseName.SchemaName.TableName (even your local computer you can try this with simple SQL statement)

So, Unless your query is going against linked servers all you need is the later 3 (DatabaseName.SchemaName.TableName) and your query will never have problems with duplicate table names.

To clear up your mind a few points to notice are:

  1. Stored Procedures can be and usually are stored within user database it is going against so it know the first part (unless of-course it is going against multiple databases or even worse against multiple linked servers)

  2. Stored procedures are schema bound objects (uses default schema of the user creating, if not specified)

Upvotes: 0

Related Questions