We were interviewing people for developer and architect positions. The developer position required about 5 years C# and SQL Server. We got a couple of good candidates but some of them were really terrible. They claim SQL server experience since 1998 but don't know what a clustered index is or they can not name even one isolation level. There were people who had 10 years experience in web development but could not explain what MVC (Model-view-controller) is or name a design pattern. People created webservices but did not know what a WSDL is?????
You see this is the problem I have with WDP (Wizard Driven Programming); It's okay to use wizards but you HAVE to know what is going on behind the scenes. It's okay to use Enterprise Manager but you have to know how to do the same thing in Query Analyzer. If you never write your own scripts you will sooner or later come to a shop where they don't use these tools and changes have to be submitted via scripts. Not only do you have to script everything you also have to provide back-out scripts in case something goes wrong.
So here are some of the questions I asked; with 5 years experience I think you should not miss more than two (probably traceflag 1204 and parameter sniffing questions)
Let me know how many you couldn't get without looking them up
What is normalization
What is the fastest way to empty a table
what is a deadlock
Can you give an example of creating a deadlock
How do you detect deadlocks
What is an audit trail
what is an identity column
How do you return an identity value from a table
How do you return an identity value from a table with a trigger
How many bytes can you fit in a row, do you know why
What is a clustered index
How many clustered indexes per table
How many nonclustered indexes per table
what is an execution plan
Between index scan, index seek and table scan; which one is fastest and which one is slowest
How do you return a value from a proc
How do you return a varchar value from a proc
If I have a column that will only have values between 1 and 250 what data type should I use
How do you enforce that only values between 1 and 10 are allowed in a column
How to check for a valid date
Which date format is the only safe one to use when passing dates as strings
How do you suppress rows affected messages when executing an insert statement
Can you name the 4 isolation levels in SQL Server 2000
How would you select all last names that start with S
How would you select all rows where the date is 20061127
What is horizontal partitioning
What does schemabinding do
How do you test for nulls
Name some differences between isnull and coalesce
What is a temp table
what is the difference between a local and global temporary table
If you create a local temp table and then call a proc is the temp table available inside the proc
What is referential integrity
what is the fastest way to populate a table (performance wise)
using the method above what can you do to make it even faster
What data type should you use to store monetary values
What is a cascade delete
Name a couple of types of joins
What is a SQL injection
What is parameter sniffing
Name 2 differences between a primary key and UNIQUE Constraints
How do you ensure that SQL server will use an index
What does option fast (10) do
What is the difference between union and union all
What does trace flag 1204 do
Just to be totally pedantic:
ReplyDelete"How do you return an identity value from a table"
I think you mean, "How do you return the identity value that resulted from an INSERT operation"
... Because the way you have it phrased right now, one possible correct answer is:
SELECT TOP(1) your_identity_column
FROM YourTable
I've just retrieved "an" identity value :)
yes of course ;-)
ReplyDeleteThese are the short questions, I was to lazy to type the whole question.
the next question (How do you return an identity value from a table with a trigger) is also not as short, I usually draw it on a whiteboard with table 1 and table 2, trigger on table 1 that inserts into table 2
I will draw some rows with different values and then ask how to return the identity if they answer @@identity I will tell them that thaw will return the value from table 2 not table 1. 80% of people never heard of SCOPE_IDENTITY()...sad but true
You know, I am quite lucky I dont have to know most of those things, as long as I know where to look for the answers :)
ReplyDeleteBut then again, I am not a DBA or SQL Developer claiming to have years of experience with that stuff...
But I do agree it is good to ask questions which are good to get a feel for the problems and tasks the candidate had encountered, the scope_id is a good question.
However it only tells you that candidate had used only clean applications without strange triggers *G* - i dont know if that is a good or bad thing :)
I was able to answer 75% of your questions. But it would be useful if you could include answers as well to your questions. And I know people who can answer only 25 % of this list are my bosses, boasting of experience of 7-8 yrs. But as someone posted above, to be a good developer, you just need to know how to update urself and where to look for answers. Unless u r interviewing for projects in NASA or other mission critical stuff or real hard core product development or systems programming, ur list is too comprehensive :)
ReplyDelete