0

I'm currently doing some maintenance on an application and I've come across a big issue regarding the qualified name in tsql. I wondering if someone could clear my confusion up for me.

From my understanding you want to use USE [DatabaseName] to declare which database you are using. I notice if u "rename" the databse it automatically updates these references in your code.

However, the developer who originally wrote this code used the USE [DatabaseName]. Then in later statements he wrote: SELECT * FROM [DatabaseName].[dbo].[Table]. Well this obviously breaks if I change the Database's name. From what i've read you want to qualify names only to the owner such as: [dbo].[TableName] so it knows where to look which increases performance.

Is there a reason he included the Database name in each statement?

MisterIsaak
  • 3,882
  • 6
  • 32
  • 55

1 Answers1

1

From what i've read you want to qualify names only to the owner such as: [dbo].[TableName] so it knows where to look which increases performance.

Not that I'm aware of, rather it looks like someone is lazy.
I always use the three name format (unless accessing a linked server instance, then it's four).

The benefit is that the correct table from the correct database & schema will be used without concern for an errant USE [appropriate database] statement. As long as the object exists, and the permissions are valid based on the need, you can recreate a stored procedure, function, view, etc in other databases without needing to address the USE [appropriate database] statement each time.

But I'm working with data spread over numerous databases on the same instance. I wouldn't have necessarily designed it that way, but it wouldn't change that I use three (or four) part qualified name format.

OMG Ponies
  • 325,700
  • 82
  • 523
  • 502
  • So do u create synonyms ? Because if I use a 3 or 4 part qualifier and then rename the Database, I'll have to go through and fix all the naming myself. I'm just saying this because when I renamed the db it automatically corrected the "USE" statements but didn't update any of the 3 part naming inline. – MisterIsaak Sep 23 '10 at 16:07
  • @Jisaak: No, haven't used synonyms on SQL Server. Updating a database name is easy - SSMS's Find (Ctrl-F) has a Find & Replace function for regex like, global replacement. – OMG Ponies Sep 23 '10 at 16:12
  • Hmm. Well with a 100 sprocs that still seems painful. Thanks for the help. – MisterIsaak Sep 23 '10 at 16:25
  • @Jisaak: Understood, but how often are you changing database names? – OMG Ponies Sep 23 '10 at 16:37
  • It gets renamed every for each step in the dev cycle. DbName_Const -> DbName_Dev -> DbName_QA -> etc, etc. Sadly enough I dun make the rules. – MisterIsaak Sep 23 '10 at 17:38
  • @Jisaak: Something that could make Life easier would be to execute the [scripts via SQLCMD](http://msdn.microsoft.com/en-us/library/ms162773.aspx), and use a variable for the database name. – OMG Ponies Sep 23 '10 at 19:59