1

Tricky C# and SQL Interview Questions

Tricky C# and SQL Interview Questions for mid-to-senior level Positions

A few readers have left private comments asking me what kinds of interview questions I’ve asked potential candidates. Personally, I have a lot more fun with mid-to-senior level positions as it opens the door to asking tricky C# and SQL interview questions just to see how well they know their stuff, and if they don’t know, how they handle it.

Feel free to use any of them!
Tricky SQL Interview Questions
Tricky C# Interview Questions

 

1) SQL

 

2) SQL

Describe two actions which can be undertaken with tempdb files to increase SQL Server’s performance.

Primary answers I look for:

i) tempdb files should be moved to a different physical drive from the server’s log files and production database(s) log files because of how active it is and how much I/O occurs with it.

ii) Create multiple tempdb files. It increases the number of physical I/O operations that SQL Server can push to the disk at any one time. The more I/O SQL Server can push down to the disk level, the faster the database will run.

See Increase SQL Server tempdb Performance for more information.

 

3) SQL

What the difference between UNION and UNIONALL?

UNION will remove the duplicate rows from the result set; UNIONALL does not.

SQL UNION vs UNIONALL

 

4) SQL

You have been tasked with increasing the speed of a stored procedure that runs once a month, deleting approximately 25 million records of stale data from a table called “StaleWorkOrders”.

 

Your sole job is to increase the speed at which it runs: you don’t care about any sort of logging and there’s zero transaction blocks that need to be rolled back.

 

You’ve made an important change. One of the SQL statements below was the original code; the other is your new code:

a) Which SQL statement was originally there? And which one did you change it to?
b) Why did you make the change?

 

Answers:
a) DELETE FROM was the original statement which you replaced with the TRUNCATE statement.
b) TRUNCATE TABLE quickly deletes all records in a table by deallocating the data pages used by the table. It completely bypasses the transaction log. This reduces the resource overhead of logging the deletions, as well as the number of locks acquired, thus increasing performance. In addition, the TRUNCATE statement also resets any Identity Seeds that may be in the table.

 

1) C#

 

2) C#

 

3) C#

 

 

  • Stephen

    Good questions. I’ll be adding these to my study guide!