December 25, 2014 at 11:13 am
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.
--Jeff Moden
Change is inevitable... Change for the better is not.
December 26, 2014 at 8:55 am
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?
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
December 26, 2014 at 9:56 am
Jeff Moden (2/28/2013)
I think the most important part of the article has been overlooked by those rightfully aghast at yet another seemingly “interview question” article. Here’s the quote. The emphasis is mine.Remember also that [font="Arial Black"]this test is just one factor in your hiring decision[/font], and that resume, past experience and [font="Arial Black"]in-person interviewing skills [/font]should also inform your hiring decisions. In my consulting practice, I have found that people who score 10 or above on this test are very [font="Arial Black"]likely [/font]senior SQL developers or supporters with several years of full time SQL experience; whereas people who score 5 or less are likely junior SQL “beginners”. Scores between 6 and 9 lie in an uncertain nether land where you may need to use other questions or interviewing skills to make a determination.
“Likely” doesn’t mean “Is”. In other words, this test is a pre-interview or phone-interview filter to separate known chaff from potential wheat and I thought the author was clear on that. The author did not intend this simple test to be all to end all in the interview process for a valuable and very expensive asset. You MUST conduct an in-person interview with questions more fitting of a “Senior Level SQL Staff”. This test is just a simple and very quick method to avoid wasting real interview time on “posers” and “wannabes”. That’s all.
Another point that the author was trying to make that may have been lost on some is how to write a test. If you notice, there are no esoteric or “trick” questions, which serve only the ego of the writer of the questions. Instead, these are basic questions that good developers will surely know the answers to most. The other important part is there are no multiple-guess possibilities to most of the questions and, when there are, there are enough possibilities to limit guessing to only 12.5%, as the author cited.
I’ll also add that if you use such a line of questions on a phone-interview, keep track of how long the person takes to answer. These questions are simple enough so that if a person doesn’t start to answer almost immediately, then they’re frantically Googling for an answer because you can’t see them.
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.
+1
Don Simpson
December 26, 2014 at 4:39 pm
I was looking for a SQL example of answer 10, HAVING clause without an AGGREGATE function. I tried but could not get it going. Please give me an example. Thank you.
December 27, 2014 at 3:31 am
Thanks for the questions. The subquery in ORDER BY clause is possible.
December 27, 2014 at 4:26 am
The HAVING clause can only be used where there is a GROUP BY or where you have an aggregation. What's more, you can only, and would only want to, use HAVING where you are testing against an aggregated value. This is because by using an aggregation without explicitly using a GROUP BY clause, it is really doing the grouping (by everything) anyway under the covers. It would take a bit of head-scratching to find a valid use for this. You might want to test to check whether a subset of a table has an average more than a particular value before executing some logic--If exists (Select ... )-- However, as a good test of a developer's knowledge of SQL, it is a complete waste. You just don't need it.
Best wishes,
Phil Factor
December 28, 2014 at 6:10 pm
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.
December 29, 2014 at 5:09 am
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?
This seems a rather better interview question to me than the original.
I reckon you've added a SQL Code Smell here 'Accessing a linked server directly via a four-part name rather than abstracting this via a Synonym'.
Best wishes,
Phil Factor
December 29, 2014 at 12:28 pm
Thanks for the good list of interview questions. I thought that the answer to question 15 might be 1 but I was really expecting an error. If you reorder the values so that a text value appears before any numeric you do get a conversion error...which seems inconsistent.
December 29, 2014 at 12:37 pm
chip.halsey (12/29/2014)
Thanks for the good list of interview questions. I thought that the answer to question 15 might be 1 but I was really expecting an error. If you reorder the values so that a text value appears before any numeric you do get a conversion error...which seems inconsistent.
If you really want to spice up the last question, make the code this:
DECLARE @byte varchar(1)
SELECT ISNULL(@byte, 'TRUE'), COALESCE(@byte, 'TRUE')
and ask if you'll get the same result ... you won't. But arguably that gets into "trickiness", which most of the rest of the test admirably does not.
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
December 29, 2014 at 7:09 pm
Phil Factor (12/29/2014)
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?
This seems a rather better interview question to me than the original.
I reckon you've added a SQL Code Smell here 'Accessing a linked server directly via a four-part name rather than abstracting this via a Synonym'.
Agreed. The fly in the ointment would be any of the 4 parts of the name. Scott's absolutely correct that if only the server you want to point to changes, that it wouldn't be a problem because, ostensibly, ALL of the code would be 4 part named to the same linked server. Just change where the linked server points to and keep it named the same (takes a drop and recreate IIRC).
The bugger is that there's no guarantee that any of the other 3 parts will remain the same on the remote server. For example, I live in one of those freakishly over cautious worlds (not my idea but I enforce it for the boss) where every database in the Development environment is suffixed with "_Devl", the DBs in Staging environment are suffixed with _Staging, the QA environment with "_QA", and the Production environment with "_Prod". Having a consistent name for the Linked Server will certainly work but not for any of the rest of the stuff. I have to have synonyms that allow the 2 part naming convention in the code to stay the same no matter what.
Anybody know how to alias the names of databases, schemas, and tables without using a synonym or pass-through view?
--Jeff Moden
Change is inevitable... Change for the better is not.
December 30, 2014 at 8:46 am
Jeff Moden (12/29/2014)
Phil Factor (12/29/2014)
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?
This seems a rather better interview question to me than the original.
I reckon you've added a SQL Code Smell here 'Accessing a linked server directly via a four-part name rather than abstracting this via a Synonym'.
Agreed. The fly in the ointment would be any of the 4 parts of the name. Scott's absolutely correct that if only the server you want to point to changes, that it wouldn't be a problem because, ostensibly, ALL of the code would be 4 part named to the same linked server. Just change where the linked server points to and keep it named the same (takes a drop and recreate IIRC).
I'm also saying to get out of the mindset of the linked server name being a physical server. Instead, name it by application or function. Only force of habit causes people to directly tie a linked server name to a specific physical server. So just don't do it.
The bugger is that there's no guarantee that any of the other 3 parts will remain the same on the remote server. For example, I live in one of those freakishly over cautious worlds (not my idea but I enforce it for the boss) where every database in the Development environment is suffixed with "_Devl", the DBs in Staging environment are suffixed with _Staging, the QA environment with "_QA", and the Production environment with "_Prod". Having a consistent name for the Linked Server will certainly work but not for any of the rest of the stuff. I have to have synonyms that allow the 2 part naming convention in the code to stay the same no matter what.
Anybody know how to alias the names of databases, schemas, and tables without using a synonym or pass-through view?
Hmm, yeah, you've created a real mess for yourself there. So I guess all your cross-database references must be by synonym only, even for utility/shared dbs that just contain common functions, etc.?! That sounds like a royal pain.
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
December 30, 2014 at 3:11 pm
ScottPletcher (12/30/2014)
Jeff Moden (12/29/2014)
Phil Factor (12/29/2014)
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?
This seems a rather better interview question to me than the original.
I reckon you've added a SQL Code Smell here 'Accessing a linked server directly via a four-part name rather than abstracting this via a Synonym'.
Agreed. The fly in the ointment would be any of the 4 parts of the name. Scott's absolutely correct that if only the server you want to point to changes, that it wouldn't be a problem because, ostensibly, ALL of the code would be 4 part named to the same linked server. Just change where the linked server points to and keep it named the same (takes a drop and recreate IIRC).
I'm also saying to get out of the mindset of the linked server name being a physical server. Instead, name it by application or function. Only force of habit causes people to directly tie a linked server name to a specific physical server. So just don't do it.
The bugger is that there's no guarantee that any of the other 3 parts will remain the same on the remote server. For example, I live in one of those freakishly over cautious worlds (not my idea but I enforce it for the boss) where every database in the Development environment is suffixed with "_Devl", the DBs in Staging environment are suffixed with _Staging, the QA environment with "_QA", and the Production environment with "_Prod". Having a consistent name for the Linked Server will certainly work but not for any of the rest of the stuff. I have to have synonyms that allow the 2 part naming convention in the code to stay the same no matter what.
Anybody know how to alias the names of databases, schemas, and tables without using a synonym or pass-through view?
Hmm, yeah, you've created a real mess for yourself there. So I guess all your cross-database references must be by synonym only, even for utility/shared dbs that just contain common functions, etc.?! That sounds like a royal pain.
Agreed. I don't use physical names for linked servers. That would be insane.
Heh.... and, no... I didn't make the mess of the insane Dev/QA/Stage/Prod naming convention. It was here when I got here and I've been fighting against it ever since. A couple of previous jobs had the same insanity and I was able to convince them otherwise. Not sure why they have such a problem making the leap here.
To make matters worse, we have special copies of the same databases in the same servers with different suffixes. Like you said, quite the mess.
They've also hog tied me a bit when it comes to utilities. I'm not allowed to have a util DB in staging or production. All functions, etc, must be in the database they're called from so that the databases are "not dependent on other databases". Yeah... I know. Doesn't make any sense in light of the synonyms.
The good part is, they let me lock down the production databases/server (couldn't have done that without full management support). Developers cannot promote their own code and all code is peer reviewed by me (not a huge shop). If I'm not available (which is rare), there are two other people that have been trained up to do the reviews. With that in mind, I don't complain so much about the Dev/QA/Staging environments.
--Jeff Moden
Change is inevitable... Change for the better is not.
May 7, 2015 at 1:12 pm
Looks like a good list and I've only looked at the top few. I'm going to go through them all now and see what trips me up.
Viewing 14 posts - 31 through 43 (of 43 total)
You must be logged in to reply to this topic. Login to reply