Google search turns up some links to tsql questions. I was wondering what would SO experts would ask in an interview for TSQL.
+11
A:
Here are some of the most common questions I've been asked as an ASP.Net developer with strong SQL Server skills:
- Name and describe the different kinds of JOINs
- What is COALESCE?
- Explain primary and foreign keys
- What would you do to optimize slow-running queries?
- What is the difference between DELETE and TRUNCATE?
DOK
2009-04-19 18:46:06
+1 This is a good step to filter out those who have no clue. What about distinguishing among advanced final applicants?
Joel Coehoorn
2009-04-19 19:33:01
OK, Joel, I almost never get questions about any of the newer features in SQL Server 2005 such as RANK, PIVOT, ROW_NUMBER, APPLY, CHECKSUM, using the dedicated admin connection, using CTE's, writing CLR stuff.
DOK
2009-04-19 19:37:20
And, if you're looking for someone who keeps their skills current, ask them what new features of SQL Server 2008 they are looking forward to using, and if they've downloaded and experimented with the beta versions.
DOK
2009-04-19 19:40:15
I was surprised by how many DBA candidates couldn't explain the different joins or what a PK / FK was.
JoshBerke
2009-04-20 13:54:15
If you'd like to, you can edit your question to include this amplification. People do that to guide the answers in directions they want. There's a little "edit" link right below the question. Then you can delete this answer.
DOK
2009-04-19 21:05:05
+2
A:
In SQL query section (to extend DOK):
- How would you handle NULL problems? (ie. NULL - 25 = ?)
- Variations for CASE in SELECTs (pros/cons)
- User function vs. stored procedure performance
- .NET/SQL integration (pros/cons)
- How would you and why chain SQL queries? (via god-query, view, stored procedures etc.)
boj
2009-04-19 19:13:34
What's the answer to the question about user function v.s. stored procedure performance?
Andomar
2009-04-20 07:16:33
@John: in our SQL2k based applications it's always problem how to create testable and composite queries. If we need a report we break down it into discrete steps and chains as View1->View2->View3->Sp1.With this architecture we are able to deep-control our dataflow - not as in a 2000 line lonq T-SQL query.
boj
2009-04-20 09:31:24
@Andomar: in MSSQL? In that case user function's performance can be far from stored procedures (let's say in sQL2k).
boj
2009-04-20 09:36:38
A:
I always ask programmers (not really db programmers, but programmers that shoud be able to write a DAL)
"Epxlain the difference between a subquery that is correlated and one that is not"
I know for people who know a thing or 2 about sql this sounds very easy, but to my amazement, much less than 50% gets it right.
Peter
2009-04-19 19:24:28
Are you looking for something beyond just the fact that a correlated subquery uses the results from the outer query and is executed repeatedly, or is that it?
John Dibling
2009-04-19 19:39:29
nono, that's it indeed! Further info was only required after they could tell me what it was in the first place! We even ended up hiring so who fail this actual question... Trouble finding IT people in Belgium =-) But still not that bad as a story I heard from so else : looking for a DB2 expert, one candidate, with 10y experience as he claimed, did not know what a stored procedure was..
Peter
2009-04-19 20:25:54
I like Belgian Chocolate a lot. I'll come work for you. I've heard of stored procedures once or twice.
2009-04-20 03:18:27
Hey man, forget about the (indeed very nice) chocolate, try the beer : http://en.wikipedia.org/wiki/Belgian_beer. Even twice!! Come on over!
Peter
2009-04-20 09:36:19
A:
Here are some of questions I can come up with.
- How to implement Row_Number without using Row_Number() function.
- How to calculate running total
- How to transpose records - pivoting.
- Filtering NULL records in
WHEREclause - Conditional record ordering and filtering
Sung Meister
2009-04-20 12:16:16
+4
A:
There are a bunch of questions here: SQL Server Quiz, Can You Answer All These?
A biggie is of course how can you code to minimize deadlocks
Take the code below for example, 80% of people get that wrong
What will be the output of the following?
SELECT 3/2
SQLMenace
2009-04-20 13:16:52
That's one of those "Doh!" moments when you think about the inferred types. For those same readers lacking SQL Server, you can get the answer you expected if you do SELECT 3.0/2.0 so that it doesn't infer that you want an integer.The Oracle equivalent would be select 3/2 from dual and it does return 1.5Must...fight... urge to... install MySQL and PostgreSQL to test...
jeffa00
2010-05-07 18:14:05
+1
A:
Name the TRANSACTION ISOLATION LEVEL's Explain each of them, Which is the default ?
Nick Kavadias
2009-04-20 13:51:38
just to add to that, what is a phantom read, dirty read, repeatable read, lost update and which isolation levels can help
SQLMenace
2009-04-20 14:16:15
+1
A:
A partial list of suggestions:
get the most awful SQL you've run across and get the candidates to explain as many things wrong with it as they can
ask them how they would get the query plan for the tsql they write and what are some of the things to avoid in query plans (and how they would rectify them if the did pop up); see if they know what a full table or index scan is
ask them if they understand how the server's optimizer works and how that might have bearing on the tsql they write
ask them for a examples of when it is good to use tempdb
see if they know which sql clauses will force the use of tempdb behind the scenes; see if they know why this is important
ask them for examples of when it is better not to use a cursor
ask them how to ensure updates in place; ask them which vendors support that and which implement updates as delete/insert
have them explain why it is bad practice to use select * in code
yetanotherdave
2009-04-22 05:06:20
A:
What's the difference between a WHERE clause and a HAVING clause?
It's a seemingly easy question that many people get wrong, and listening to them try to answer will give you some insight into their understanding of grouping.
msulis
2010-05-20 09:07:45