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?
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.