January 14, 2014 at 12:27 pm
below86 (1/13/2014)
Almost ever query we write will span more than one database, so that's why I prefer it.:-)
If your system consists of multiple databases - and there is never the possibility that one or more of those databases grows to a point where it needs to be moved to another server, then 3-part naming makes sense.
However, if the possibility exists that at some point in time one or more of those databases can be moved to a different server - then using synonyms makes sense.
In either case, if someone changes the name of an object - drops an object - etc... then both will generate a failure.
Synonyms are ideal if you have linked servers in your organization. On a test system, you would have linked server 'OtherSystemTest' and on the production system your linked server would be 'OtherSystem'. If you are using 4-part naming in your code - then promoting that code to production *requires* a code change be made as it is being applied.
Using synonyms in this situation avoids any issues with making code changes between test and production and still allows for code comparisons between the environments.
Synonyms are also ideal in situations where the names of the databases on a test system are different than the names in production. For example, some sites like to put a post-fix of _test on the database names in their test environment. In that situation, using 3-part naming doesn't work because you cannot promote that code to production without code changes - however, using synonyms means no changes to the code need to be made.
There are good reasons to use either method - and both are valid.
Note: I use both methods, but will only use 3-part naming when referencing objects outside the scope of the database where the procedure will live. This allows me to move that procedure to another database if needed - without worrying about accessing data in the 'old' database.
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
January 15, 2014 at 11:19 am
[font="Comic Sans MS"]What would prevent a user from scripting all stored procs and views in a common file and then just use a text editor to find and re-place the old-new databasename and rerun the scripts after dropping all the stored procs and views (you will of course create a backup before starting any of it) ?
So why should it be a lot of work for someone who has the authority to rename or relocate a databse ?[/font]
Viewing 2 posts - 16 through 16 (of 16 total)
You must be logged in to reply to this topic. Login to reply