Stoopid Tech Interviewer Tricks
OK, so you’ve got your suit fresh from the drycleaners. Your shirt is pressed and your shoes are shined. You’ve gone over the job requirements a dozen times, and even took the time to scan BOL for obscure tips the interviewer might try to turn into trick questions. All that hard work and now you find yourself on the losing end of a conversation like this:
Interviewer: “Tell me about your favorite new feature of SQL Server 2005.”
You: “I like the new XML data type, I’ve found that...”
Interviewer: “That’s not a new feature. That was around in SQL 2000.”
[‘Nuff said.]
So what do you do? As I told my friend (the job applicant) my first instinct would have been to stand up, shake the interviewer’s hand, and politely tell him that the interview was over. My friend, however, has a much higher threshold for mental anguish than I do.
Just for grins, here are some more recent gems from various friends on tech. interviews:
Interviewer: “What’s the difference between a temporary variable and a temporary table?”
Applicant: “The temporary variable has well-defined scope, they cause fewer stored procedure recompilations than temporary tables, and they are not affected by transaction rollbacks.”
Interviewer: “You left out the most important part – table variables are always stored in memory, they don’t clog up tempdb!”
[Yes, he used the highly technical term "clog up". Microsoft weighs into the discussion with the following from http://support.microsoft.com/default.aspx/kb/305977: “A table variable is not a memory-only structure. Because a table variable might hold more data than can fit in memory, it has to have a place on disk to store data. Table variables are created in the tempdb database similar to temporary tables. If memory is available, both table variables and temporary tables are created and processed while in memory (data cache).”]
Interviewer: “Why do we normalize our tables?”
Applicant: “To preserve data integrity.”
Interviewer: “No, that’s not right.”
[No further explanation was apparently necessary or forthcoming from this idiot. Dr. Codd had a few words on this in his paper "Further Normalization of the Data Base Relational Model". One of his objectives of normalization was to free the database from certain insert, update, and delete anomalies. Sounds like a data integrity issue to me, but I ain't no hiring manager...]
Interviewer: “What’s the difference between using DELETE to delete all rows from a table and using TRUNCATE?”
Applicant: “DELETE logs every row as it’s being deleted, TRUNCATE logs page deallocations so TRUNCATE executes much faster. Oh, and TRUNCATE reseeds the IDENTITY column if the table has one.”
Interviewer: “No. TRUNCATE is not logged.”
[Microsoft’s take on the situation is at http://technet.microsoft.com/en-us/library/ms177570.aspx: “TRUNCATE TABLE removes the data by deallocating the data pages used to store the table data and records only the page deallocations in the transaction log.” You be the judge.]
Interviewer: “Why does BULK INSERT provide better performance than individual INSERT statements?”
Applicant: “For one thing if you’re loading 1,000,000 rows you’re not running 1,000,000 individual INSERT statements. Also BULK INSERT queues up batches of rows and commits them in batches. BULK INSERT can also take advantage of minimal logging if your database is configured appropriately.”
Interviewer: “Wrong, BULK INSERT doesn’t write to the transaction log!”
[Hmmm, Microsoft seems to think it does. In reference to BULK INSERT with minimal logging “...the Database Engine still logs extent allocations each time a new extent is allocated to the table.” If your database is in full recovery mode, “all row-insert operations that are performed by bulk import are fully logged in the transaction log.”]