SQL Server interview questions.

Question

So I have an interview next week for a shop that makes heavy use of SQL Server 2008. I also use it on a daily basis and feel quite comfortable with it however there is always the nagging question: What don't I know?

So I humbly ask, what can a C# developer expect an interviewer to ask with regards to an SQL Server 2008 for a mid-level developer position interview?

Thanks.

Answer 1

At the consulting shop I work at, we use exclusively SQL Server 2005-2008 for our web app backends...stuff I deal with on a day-to-day basis involves:

• Full-text indexing and queries using CONTAINS/FREETEXT or CONTAINSTABLE/FREETEXTTABLE
• Using and implementing SQL Server reporting services
• Occasionally, SOAP endpoints built directly into SQL Server rather than setting up a separate web service

That's probably the most "advanced" stuff I get into...setting up replication is always fun as well, but I don't know that they'd ask you to know that for a mid-level developer position. And knowing T-SQL in and out is obviously a huge plus. :)

Answer 2

What is a clustered index scan in the execution plan? Is it a good thing? If not, what would you do differently?

Answer 3

Sql Server is a massive topic. You're not going to know everything, especially as a developer. You'll be expected to know about PKeys, foreign keys, design etc. Just be honest when you don't know something. Don't guess and don't try to b*lls*&t your way through anything. It never plays well. I've seen it from both angles (interviewee and interviewer.)

Honesty gets a lot of respect from the interviewer. It makes you a real person and it makes their job easier. Not necessarily to say yes or no to your qualifications but because they have less guessing to do and they will remember that when it comes time to cull through the applicants.

Answer 4

For a generic database developer position, I would focus on

• T-SQL constructs, sprocs, triggers, etc. Look at table of contents of some book like SQL Queries for dummies and you should have a nice idea of what you need.
• Table structures - Indexes, heap, triggers (instead of etc.)
• Basic understanding of SQL - system databases, tools, SSMS, sql profiler. You don't need to be proficient at this but having some knowledge would be helpful.
• CTE happens to be favorite topic of some of the interviewers. Simple example from BOL would suffice.

Since you mentioned C# developer specifically in your question, I would also focus on these -

1. Data Access - Simple CRUD operations, advanced topics include bulk operations using SQL
2. DLinq - Some people really like this since they seem to get best of both worlds - C# and SQL like syntax. I suggest playing around with some samples from MSDN videos.
3. Latest SQL - New functionality in SQL 2008 - Table valued params, spatial support etc.
4. SQL Azure - there is lot of interest in this and having some basic understanding can't hurt. Having said that retry logic required for using Azure is a nice topic.

Answer 5

Here are some SQL Server interview questions with answers. Have a look!

http://www.prepare4interview.com/ssis-interview-questions-and-answers