SQL Server Interview Questions

The following interview questions have been used on multiple interviewees and seem to work out well in finding the different areas of expertise for an individual. The rating part at the beginning gives you an understanding of where the interviewer thinks they are at in the listed categories. You can then judge yourself after you ask the questions.

1. On a scale of 1-10,
   rate yourself in the following categories:
1. T-SQL
2. Optimization
   (SQL Server Internals)
3. Data Warehouse
4. Database Administration
5. Database Architecture
6. Database slowdown
   troubleshooting

T-SQL

1. Name all the different kinds of Joins.
1. OUTER
   JOIN – LEFT, RIGHT, CROSS, FULL
2. INNER
   JOIN
2. On a scale of 1 to 10, how important would you consider cursors or while loops for a transactional database?
1. As close to zero as possible, mainly only used for maintenance or warehouse operations.
3. What is a correlated sub query?
1. When a sub query is tied to the outer query. Mostly used in self joins.
4. What is faster, a correlated sub query or an inner join?
1. Correlated
   sub query.
5. What is faster, a correlated sub query or an exists?
1. Exists
6. What is the having clause and when is it used.
1. Used to further filter a group by.
7. What are the pros and cons of putting a scalar function in a queries select list or in the where clause?
1. Trick question, mostly just cons. Scalar functions in these places make the query slow down dramatically.

Internals

1. Describe lock escalation
1. A query first takes the lowest level lock possible with the smallest footprint
   (row-level). When too many rows are locked (requiring too much RAM) the lock is escalated to a range or page lock. If too many pages are locked, it may escalate to a table lock.
2. What are the internal differences between #temp tables and @table variables
1. SQL Server can create column statistics on #temp tables
2. Indexes can be created on #temp tables
3. @table variables are stored in memory up to a certain threshold.
3. How does a clustered index differ from a non-clustered?
1. Clustered index physically sorts the table data in that order.
2. Non clustered index is a duplication of the data in the table with a pointer to the clustered index key.
4. What are some of the join algorithms used when SQL Server joins tables.
1. Loop Join (indexed keys unordered)
2. Merge Join (indexed keys ordered)
3. Hash Join (non-indexed keys)
5. Name some possible warning signals you may see in an execution plan indicating the query
   is not optimized.
1. Index Scan or Table Scan
2. Hash Joins
3. Thick arrows (indicating large work tables)
4. Parallel streams (Parallelism)
5. Bookmark lookup (or key lookup)
6. What is a bookmark lookup? (Or key lookup)?
1. When a non clustered index is used for the seek and the data needed was not
   at the leaf level.
7. Which is faster a Table Scan, or a Clustered Index Scan?
1. Trick question. Same speed.
8. Describe recompilation. What causes it to happen and what are the differences between 2000 and 2005?
1. When the cached execution plan for a query cannot be used so the procedure
   recompiles. It may happen because (1) underlying statistics change. (2) DDL changes within the procedure. (3) The parameters the procedure was compiled with vary from the recently passed in parameters. (4) The query plan was flushed from cache.

Data Warehouse

1. If moving a larger number of rows from one server to another using T-SQL with a linked-server,
   how would you limit the size of the batches so the transaction log does not fill up?
1. Create a looping mechanism using SET ROWCOUNT and a while loop
2. Use the new SQL 2005 GO statement to set batch sizes.
2. In DTS or SSIS, name the three types of precedence constraints used in moving from one
   task to another.
1. Success
2. Failure
3. Completion

Slowdown Troubleshooting

1. When there is a whole system slowdown that is verified to be the database, describe
   the steps you take to investigate it.
1. Run sp_who2 and see if any blocking is taking place, or any queries are
   eating too much CPU or disk IO. Also check the # of connections.
2. Locking.
3. Log into the box, see if the box is slow. Check CPU, memory usage, page
   file usage.
4. Open perfmon and check the page life expectancy and disk latency.
2. If there are no physical indicators for the system slowdown, what is the first thing you should do?
1. Update Statistics
3. When one particular query is slow, describe the steps you take to investigate it.
1. Run SQL profiler and determine if abnormal amounts of IO or CPU is used.
2. Run profiler to determine if recompilation is a factor.
3. Update the statistics.
4. Check the execution plan.
4. Name all the potential points of contention that can cause a database slowdown:
1. CPU
   bottleneck
2. Memory
   bottleneck
3. Network
   IO bottleneck
4. Disk
   IO bottleneck
5. Paging
   File (process trimming)
6. Lock
   contention
7. Corrupt
   index
8. Recompilation

Database Administration

1. What is the difference between a truncate and a delete and what is the minimum fixed-server role needed to truncate?
1. Truncates are not logged and cannot be undone. Truncate requires dbo.
2. What is the default number of worker threads in SQL 2000?
1. 255
3. What is parallelism?
   What is the default query threshold for parallelism?
1. The optimizer decides to utilize multiple SPIDS running on different processors
   to query / transfer data. Default threshold is 5 seconds.
4. Describe Log Shipping vs. Mirroring vs. Transactional Replication. What are the pros and cons?
1. Log Shipping is asynchronous and sends transaction log file updates at a
   minimal interval of 2 minutes.
2. Mirroring replicates the entire database and is synchronous. By default, it commits the data on both sides prior to releasing the transaction.
3. Transactional replication is asynchronous and can isolate specific tables.
5. How many transaction logs can you create?
1. As many as you want.
6. What is the optimal Disk configuration for a database server and what RAID configurations would you use?
1. RAID
   1 for the OS / Applications
2. RAID
   1 for the page file
3. RAID
   10 for the Data file (possibly RAID 5 for few writes)
4. RAID
   1 (or 10) for the transaction log
7. What do the following commands do?
8. DBCC SHOWCONTIG
1. Shows fragmentation within tables / indexes
9. DBCC FREEPROCCACHE
1. Clears the procedure cache removing all execution plans, all procedures are
   recompiled.
10. DBCC DBREINDEX
1. Performs a complete reorganization of the index. (Intrusive process)
11. DBCC DROPCLEANBUFFERS
1. Drops
   all the data that was cached in memory.
12. What is AWE and
    what are the requirements?
1. Address Windowing Extensions – Allows SQL to utilize greater than 4 GB of RAM. When used with 32 bit Windows PAE (Physical Address Extension) needs to be turned on.

Queries
1) Write a query to return the firstname, lastname and the most recent OrderID for all customers. The orders table contains all the orders for each customerID and the Customers table contains the customer personal information. Each order in the Orders table has an OrderDate.
Answer

[cc lang=”sql”]
SELECT
o.OrderID
,c.FirstName
,c.LastName
FROM Orders o
JOIN Customers c
ON o.CustomerID = c.CustomerID
WHERE OrderDate = (SELECT MAX(OrderDate)
FROM Orders
WHERE CustomerID = o.CustomerID
[/cc]

OR

[cc lang=”sql”]
SELECT
c.FirstName,
c.LastName,
o.OrderNumber
FROM Orders o
JOIN
(
SELECT MAX(OrderDate) AS MaxOrderDate,
custid
FROM orders
GROUP BY custid

) o_2
ON o.custid = o_2.custid
AND o.orderdate = o_2.MaxOrderDate
JOIN customers c
ON c.CustID = o.CustID
[/cc]

2. If this was the only query on used for these two tables, how would you create the clustered indexes?
Customers – CustomerID
Orders – CustomerID, OrderDate