ScottPletcher (12/26/2014)
Jeff Moden (12/25/2014)
ScottPletcher (12/25/2014)
Geoff A (3/3/2013)
Jeff Moden (2/28/2013)
Shifting gears a bit, I’m glad that these are essay-style questions so that I could write in what I believe is a more correct answer in for question #4 (the one on 4 part naming for distributed queries). IMHO, no Senior Developer worth his or her salt would ever make the mistake of “deciding” to use the 4 part naming convention in their code. Yes, you need the linked server to avoid the 4 part naming but you should never use 4 part naming in your stored procedures, views, or other code objects other than the script to make the linked server. If you don't know how to avoid 4 part naming or the reasons why you should do so, then you'll need to look it up or ask a Senior Developer to help you because I try not to post answers to interview questions. 😉Thomas, great article. I hope that people will read beyond the questions you included because there are some powerful lessons to be learned here.
not to dwell on this comment, but are you saying to use a synonym. That way if a database name or a linked server name changes, you don't need to find all the places in code to change it.
or is there another reason?
Jeff Moden (3/3/2013)
Geoff A (3/3/2013)
Jeff Moden (2/28/2013)
Shifting gears a bit, I’m glad that these are essay-style questions so that I could write in what I believe is a more correct answer in for question #4 (the one on 4 part naming for distributed queries). IMHO, no Senior Developer worth his or her salt would ever make the mistake of “deciding” to use the 4 part naming convention in their code. Yes, you need the linked server to avoid the 4 part naming but you should never use 4 part naming in your stored procedures, views, or other code objects other than the script to make the linked server. If you don't know how to avoid 4 part naming or the reasons why you should do so, then you'll need to look it up or ask a Senior Developer to help you because I try not to post answers to interview questions. 😉Thomas, great article. I hope that people will read beyond the questions you included because there are some powerful lessons to be learned here.
not to dwell on this comment, but are you saying to use a synonym. That way if a database name or a linked server name changes, you don't need to find all the places in code to change it.
or is there another reason?
Either a synonym or a pass-through view. And, yes, for the very reasons you stated.
I think that somewhat overstates the case against the direct use of linked servers (4-part naming), unless you've made the mistake of making your linked server names physical names, such as matching the instance name, rather than logical names, which relate to a given application or functionality. The linked server name itself should be a logical synonym (not a physical one).
Another consideration is that a linked server definition is server-wide, whereas synonyms are db by db. We have hundreds of dbs, so hundreds of changes vs. one. I suppose one could consolidate all the synonyms into one db, but I've never seen that consistently done.
Don't get me wrong, I do favor synonyms in many situations, but that's no reason to completely do away with 4-part names for any and all situations.
In code, we not only stay away from 4 part naming but we enforce the 2 part naming convention (excluding 1, 3, and 4 par naming in code) and for the very reasons you've stated. We also enforce the 2 part naming convention because of the very mistake that you've cited where the folks before the current regime did actually make the horrible mistake of using physical instance names rather than logical names in code. It's a whole lot easier to script corrections for the target server in the synonyms rather than trying to find and change such things in code.
We haven't found all of the mistakes the previous regime made in the area of using logical names rather than physical names but we're working on it.
On this...
Another consideration is that a linked server definition is server-wide, whereas synonyms are db by db. We have hundreds of dbs, so hundreds of changes vs. one. I suppose one could consolidate all the synonyms into one db, but I've never seen that consistently done.
If the synonyms are pointing to linked servers, 2 part naming shouldn't be a problem if the linked servers are logically named instead of physically named. If the synonyms are pointing to other databases, then I agree... that could be a serious problem. Still, it's easier to script a change to hundreds of synonyms rather than to find where 3 and 4 part naming has been used everywhere in code and changing that.
Why are you having to change the 4-part name?? If it's a logical name, as it should be, it should be the same no matter where it physically moves.
Wouldn't it be just as difficult to find all the synonym names to change them? And you've got tens of thousands of those, not just a few dozen linked server names.
Why isn't sys.sql_expression_dependencies helpful in finding these references?
Not sure who you're talking to at this point but we're not using the 4 part name. We use only the 2 part name and let the synonyms do the rest of the work. Do you know of a way to do a SELECT from a LinkedServer directly without using 4 part names in code other than through the use of a Synonym?
--Jeff Moden
Change is inevitable... Change for the better is not.