In a Previous Post ‘Lessons From Interviewing’ I listed a few interview questions that I had been asked in phone screens. I’ve gone back and added some additional questions, plus a short list of questions that I’ve asked interviewers.

SQL Developer Interview Questions:

1) What is the difference between a clustered and a non-clustered index / How many clustered indexes are allowed on a table?
I can almost guarantee this will be asked.
2) What is the difference between an inner join and an outer join?
3) What is a CTE? (I was surprised how popular this question was)
Common Table Expression. They’re sort of a temporary view. CTEs are useful for recursion.
4) What is the difference between count(*), count(1) and count(ColumnName)?
Using a specific column in count will return a count of rows with non-null values for that column. All three will use similar execution plans.
5) What is the difference between HAVING and WHERE?
HAVING is used with a GROUP By clause to filter values after they have been grouped.
6) Name the 5 system databases.
master, msdb, model, tempdb, resource
7) What is the difference between TRUNCATE and DELETE?
DELETE can use a WHERE clause to restrict the number of rows deleted. TRUNCATE can’t be used on a table referenced by foreign keys. TRUNCATE can be rolled back, as can a DELETE. TRUNCATE is minimally logged, the de-allocation is logged.
8) What are some differences between temp tables and table variables?
Both stored in TempDB – table variable not in memory. Temp table takes default collation from TempDB, table variables from the user database the batch runs in. TV lasts for scope of variable declaration, TT for length of session. Indexes can be created for TV in SS 2014 and later. TV don’t participate in transactions, can’t be rolled back. TVs can be used in user defined functions, unlike TT.
9) How can the identity value from an insert be returned?
a. Use @@Identity – Not scope safe.
b. Use scope_identity() – Is scope safe.
c. Use ouput Inserted.Id in insert (where id is the name of the identity column.
10) What is the difference between global and local temp tables?
A local table name is prefixed with # and the global table with ##. The local table can only be read in the session that created the table, where the global can be read by other sessions.
11) What is the difference between a cross join and a full outer join?
A cross join doesn’t specify an ON clause, so the result is every row matched with every row in the second table. The full outer join does use an ON, and will return NULLs for the rows unmatched in the other table.
12) What is the COALESCE function? (Another popular question)
It takes any number of values as parameters and returns the first not NULL value. It is ANSI compliant, unlike ISNULL (ISNULL also only takes 2 values).
13) What is the default isolation level in SQL Server?
Read Committed

Situational Questions
1) Describe how you go about troubleshooting a performance issue.
You’ll probably mention something about Profiler or Extended Events, DMVs and Execution Plans.
2) What do you find the most interesting feature of SQL Server X?
Be prepared to talk about a feature with the latest version or with an upcoming version of SQL Server.
3) Tell us about a difficult problem that you encountered and solved.

Questions for employers
1) Is this a new position? If not, what happened to the previous person that had this position?
2) Is there an on call schedule? How are after hours problems handled?
3) Does everyone work the same schedule? Are flex schedules or telecommuting available?
4) What version of SQL Server is used? Are there other databases to support? What about source control, third party tools?
5) Is there a training budget and/or travel to conferences like PASS?
6) How much travel is involved?
7) Is there any sort of development methodology used, like Agile? Are there daily stand ups or other daily meetings?
8) What is the work environment like? Is it an open office, cubes, etc?
9) What does success look like for the position? How will the employee be evaluated?