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
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 |
--Logically, the following two sets of SQL statements do the same thing. --However, technically, their results are different. --List out all the differences that will occur from the given SQL. --Bonus points if you can explain how this would affect a C# program. --Method #1 --To make sure the table always starts empty TRUNCATE TABLE [SOME_DB].[Some_schema].[YourTable] SELECT MAX([ModifiedDate]) as [ModifiedDate] FROM [SOME_DB].[Some_schema].[YourTable] WHERE GuidID = 'D2156C20-6448-4D22-8DEA-934D3D1F9682' --Method #2 --To make sure the table always starts empty TRUNCATE TABLE [SOME_DB].[Some_schema].[YourTable] SELECT top 1 [ModifiedDate] FROM [SOME_DB].[Some_schema].[YourTable] WHERE GuidID = 'D2156C20-6448-4D22-8DEA-934D3D1F9682' ORDER BY ModifiedDate desc ----------------------- --Answer shown in the screen capture below this code box: --Bonus points: if you're feeding these results into a C# DataReader --with the .HasRows property, the first SQL will cause it to evaluate to true --because there is a NULL in the row; the second SQL will cause the property --to evaluate to false. |
The results from the 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.
|
1 2 3 4 5 6 7 8 9 |
--Union removes duplicates select 'hello' as [Union Test] union select 'hello' --Union All does not. select 'hello' as [Union All Test] union all select 'hello' |
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:
|
1 2 3 4 |
--Was this the original SQL code? Or your new code? DELETE FROM TABLE StaleWorkOrders --Or was this the original SQL code? Or your new code? TRUNCATE TABLE StaleWorkOrders |
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:
- TRUNCATE in Microsoft SQL Server allows the action to be rolled-back if used within a transaction block.
- TRUNCATE in Oracle cannot be rolled back.
Tricky C# Interview Questions
1) C#
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 |
//Which catch-block is hit in the code below? Explain why. using System; using System.Collections.Generic; using System.Linq; using System.Text; using System.Threading.Tasks; namespace ConsoleApplication1 { class Program { static void Main(string[] args) { try { Recurse(); Console.WriteLine("No exception thrown."); } catch (OutOfMemoryException e) { Console.WriteLine("Out of memory exception thrown."); } catch (StackOverflowException e) { Console.WriteLine("Stack overflow exception thrown."); } catch (InsufficientExecutionStackException e) { Console.Write("Insufficient execution stack exception thrown."); } catch (Exception e) { Console.WriteLine("Some kind of exception thrown."); } } private static void Recurse() { Recurse(); } } } //Answer: none of the above. //The exception thrown from the CLR is actually not catchable. //This is the uncatchable exception. It actually jumps directly //outside of your try block and shuts your application down. See //screen capture just below this code block. //<a href="https://msdn.microsoft.com/en-us/library/system.stackoverflowexception.aspx " target="_blank">https://msdn.microsoft.com/en-us/library/system.stackoverflowexception.aspx</a> //"Starting with the .NET Framework 2.0, you can’t catch a StackOverflowException //object with a try/catch block, and the corresponding process is terminated by //default. Consequently, you should write your code to detect and prevent a //stack overflow." |
2) C#
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 |
//What is the output of the C# program below. //Explain why. class Program { static String theLocation; static DateTime theTime; static void Main() { Console.WriteLine(theLocation == null ? "theLocation is null" : theLocation); Console.WriteLine(theTime == null ? "theTime is null" : theTime.ToString()); } } //The output is: // // theLocation is null // 1/1/0001 12:00:00 AM // //This is because theLocation is a String, which is //a reference type; reference types are initialized to null. //theTime is a value type. As such, an uninitialized DateTime //variable is set to the default value of midnight on 1/1/1. //Yes, the year 1 A.D. |
3) C#
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 |
//This is a common scenario I encounter all the time! //Performance testing has shown the following C# code executes slowly when "ri" is not null. //The method "GetReportInstances" queries the database for ReportInstance objects and returns a list. //The method "GetReportItemInstances" queries the database for all ReportItems associated with the specified ReportInstance ID //and returns them as a list of ReportItemInstance objects. // //What change(s) do you make which results in a significant performance boost? // ReportInstance ri = null; try { ri = ReportInstance.GetReportInstances(r.ReportID).First(); } catch { ri = null; } long TotalNumberOfRecordsInReport = -1; int TotalNumberOfTablesInReport = -1; int TotalNumberOfWorksheetsInReport = -1; if (ri != null) { ReportGenerator.PublicMethods.Report_Render(ri.ReportID, ri.ReportInstanceID); if (ReportItemInstance.GetReportItemInstances(ri.ReportInstanceID).Count > 0) { TotalNumberOfRecordsInReport = ReportItemInstance.GetReportItemInstances(ri.ReportInstanceID) .Where(l => l.TableID >= 1 && l.RowCount > 0) .Sum(s => s.RowCount); TotalNumberOfTablesInReport = ReportItemInstance.GetReportItemInstances(ri.ReportInstanceID) .Select(t => t.TableID) .Count(); TotalNumberOfWorksheetsInReport = ReportItemInstance.GetReportItemInstances(ri.ReportInstanceID) .Select(t => (int)t.TableID) .Distinct() .Count() + 2; ReportGenerator.PublicMethods.DoMoreReporting(TotalNumberOfRecordsInReport ,TotalNumberOfTablesInReport ,TotalNumberOfWorksheetsInReport); } Logger.Log("Done! Rendered " + TotalNumberOfRecordsInReport.ToString("#,##0") + " records, " + TotalNumberOfTablesInReport + " tables, " + TotalNumberOfWorksheetsInReport + " worksheets."); } /******************************* ANSWER BELOW: ********************************************************/ //Here is the suggested quickest, and easiest change to look for. //You need to remove the multiple calls to the method "GetReportItemInstances". //Why hit the database 4 times?? Just query it once, save the results, and remove //the excess network connections, database calls, and wait times! ReportInstance ri = null; try { ri = ReportInstance.GetReportInstances(r.ReportID).First(); } catch { ri = null; } long TotalNumberOfRecordsInReport = -1; int TotalNumberOfTablesInReport = -1; int TotalNumberOfWorksheetsInReport = -1; if (ri != null) { ReportGenerator.PublicMethods.Report_Render(ri.ReportID, ri.ReportInstanceID); //Do the next line here so we only query the database once instead of multiple times! //It also only creates the List once to save on memory allocation operations. List<ReportItemInstance> rii = ReportItemInstance.GetReportItemInstances(ri.ReportInstanceID); if (rii != null && rii.Count > 0) //No additional database query! { TotalNumberOfRecordsInReport = rii //No additional database query! .Where(l => l.TableID >= 1 && l.RowCount > 0) .Sum(s => s.RowCount); TotalNumberOfTablesInReport = rii //No additional database query! .Select(t => t.TableID) .Count(); TotalNumberOfWorksheetsInReport = rii //No additional database query! .Select(t => (int)t.TableID) .Distinct() .Count() + 2; ReportGenerator.PublicMethods.DoMoreReporting(TotalNumberOfRecordsInReport ,TotalNumberOfTablesInReport ,TotalNumberOfWorksheetsInReport); } Logger.Log("Done! Rendered " + TotalNumberOfRecordsInReport.ToString("#,##0") + " records, " + TotalNumberOfTablesInReport + " tables, " + TotalNumberOfWorksheetsInReport + " worksheets."); } |
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(…)
5) C#
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 |
// //Question: what value is the variable //"theHandicap" set to from the code below? //Assume any version of C# up through 7 //(or .Net framework 4.7.1) // //Initialize array int i = new int[] {0, 2, 4, 7, 12, 15, 21}; //Calculate the handicap int theHandicap = CumulativeHandicap(i); //////////////////////////////////////// public static int CumulativeHandicap(int[] i) { int total = 0; int[] it = new int[] { 1, 3, 4, 5, 7, 8, 9 }; for (int x = 0; x < i.Length; x++) { switch (i[x]) { case it[1]: case it[2]: case it[3]: case it[4]: total += x; break; case it[5]: total *= x; break; default: break; } } //for-loop return total; } //CumulativeHandicap // |
Answer:
Nothing.
The code won’t compile. “A constant value is expected” will be thrown for each of the case statements except for “default”.
