2

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

1) SQL

The results from the SQL:
Select MAX vs Select Top 1

 

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. This reduces the resource overhead of logging the deletions, as well as the number of locks acquired, thus increasing performance. It also does not fire any triggers. In both SQL Server and Oracle Identity Columns will be reset to their starting values but sequences will not be automatically reset – this must still be done manually as a sequence is not connected to a table.

Bonus points if the interviewee knows this difference between Oracle and SQL Server when using TRUNCATE:

 


Tricky C# Interview Questions

1) C#

Stackoverflow Exception

 

2) C#

 

3) C#

 

4) C#

Describe the difference between an abstract and a virtual function/method.

Answer:

  • An abstract function/method cannot have functionality. You’re basically saying any child class MUST give their own version of this method – it always has to be overridden. As such, it can only be declared inside an abstract class.
  • A virtual function provides a default implementation and it can exist on either an abstract class or a non-abstract class.
    It’s basically saying, “here’s the functionality that may or may not be good enough for the child class. If it is good enough, use it; if not, override me, and provide your own functionality.” If you override the virtual method, you can always reference the parent method by base.Foo(…)

 

 

  • rd

    Hi, this is a good article in general but what you write regarding the TRUNCATE statement in SQL Server (“It completely bypasses the transaction log”) is false.
    A way of checking it would be to actually try to truncate a sample table and immediately check the transaction log row count. Please refer to the following article by Paul Randal:
    https://www.sqlskills.com/blogs/paul/a-sql-server-dba-myth-a-day-1930-truncate-table-is-non-logged/
    Regards!

  • Stephen

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