February 23, 2011 at 8:07 am
I'm working on improving best-practices and process at my current employer. Once of the consistent coding practices (in Views and Stored Procs) is the use of three-part references to the local database. So using "DB.dbo." where "DB" is the database in which the definition is stored.
These are littered throughout the many databases we have. I've always been taught this is an unnecessary and unmanageable practice.
However, I'm always willing to learn new things... 😉
Does anyone have a good reason why this is an acceptable practice?
Thanks in advance for your input!
Dan
February 23, 2011 at 8:22 am
Dan Colbert-387424 (2/23/2011)
I've always been taught this is an unnecessary and unmanageable practice.
Me too! I've never actually seen it done on any systems I've supported.
If this is done when referencing tables, it could be extremely dangerous too!
Take a copy of a production database and restore it as a different name in order to do some investigation, execute a stored procedure and you've potentially updated the wrong database.:ermm:
February 23, 2011 at 8:55 am
Including the schema on those can improve performance, since it doesn't have to include a compile-lock to check security at runtime (allows for better concurency), but I don't think including the database name has any such improvement.
I avoid using it for the reason already mentioned: You can't easily have multiple copies of the database in a dev/test/QA environment without having to rewrite all your code for each. Even worse, if you're hosting multiple copies of the same database, with "only the DB name has been changed, to protect the customer", and you miss one of these. That only applies to a limited number of businesses, but applies very strongly to them.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
February 24, 2011 at 10:32 am
in my enviroment where i have 50 state databases that talk to each other alot I use db.dbo. alot to get to the right database
And in development, so of the developers will change the dbo to there name for testing
February 24, 2011 at 2:40 pm
danschl (2/24/2011)
in my enviroment where i have 50 state databases that talk to each other alot I use db.dbo. alot to get to the right databaseAnd in development, so of the developers will change the dbo to there name for testing
The problem with this is what happens when you change the name of one of those databases. Or, on the development system they have changed the names of all databases to nn_test where 'nn' is the state?
This is going to cause a lot of modifications to the code just to get the system to work - which is the downside of referencing the database.
Now, if I were you I would setup separate schemas for each state, create synonyms for the objects I wanted to access in the other database(s) and use the synonyms in my code. That way, when I copy/move/rename a database I just have to update the synonyms and all code will work.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply