THE SQL Server Blog Spot on the Web

Welcome to SQLblog.com - The SQL Server blog spot on the web Sign in | |
in Search

Denis Gobo

The Sad State Of Programmers Part 2 : The In Person Interview

This is part two of a three part series. Part one was about the phone interview, this part is about the in person interview.

Part 3 is about general tips

 

The point of the in person interview is to see if the person will fit in with the rest of the people in the organization. This is where you get asked such wonderful questions like:

How many gas stations are there in the state of New Jersey?

How would you move Mount Fuji?

 

These questions are usually asked by managers to see how you handle pressure. There is usually no real answer to these questions, you have to logically come up with an answer and solve this puzzle. For example there are 8 million people in New Jersey, these people have 6 million cars. A gas station gets between 3 and 4 cars per minute; this adds up to 4320 cars per day. You would need about 2000 gas stations to serve all these people. All these numbers could be completely wrong; the point is can you logically figure out the steps to ‘solve’ this problem. It doesn’t matter how brilliant you are, if the people that interview you feel that you are a mismatch personality wise you won’t get hired.

 

Some observations about the process

Dress in business attire

One person dressed very inappropriate, in fact when picking him up at the security desk we thought he was a delivery person. This is not a way to start the face to face interview. I will get into more detail in part 3 of what to wear and not to wear.

 

You are your own worst enemy

A person was given source code after the phone interview to study. When asked about the code during the face to face interview she mentioned that she just glanced over the code and did not really look at it. Why would you say something like that if you want to get hired? Now, would you consider hiring this person?

 

Be proper

Do not grimace like you have a squirrel chewing on your big toe while thinking about an answer. Do not chew on your glasses either while thinking about the answer. When you answer the question wrong, the interviewer gives the correct answer don’t say that is what I meant. If that is what you meant then you should have said so, there are of course exceptions but I am talking about real black and white scenarios.

 

Bring good code

When you bring source code make sure it is the best code you could possible bring. One person was asked about best practices and error handling; he seemed to know this pretty good. Then he offered to show his code, we noticed that he didn’t have any of these best practices in his code. His proc was called sp_ProcName, error handling was wrong and a bunch of other things. In this case it would have been better if the person did not volunteer to show the code.

 

And now the questions.

 

 

I decided to ask every person these exact questions in the face to face interview; depending on how these were answered I would ask a bunch more questions. There are a couple of questions which you couldn’t answer wrong and I did not count them as toward the total score. Here is a list of some of the questions (Alex don’t get mad), I will comment on these questions and give the answers.

 

How many bytes can you fit in a row, do you know why
How do you enforce that only values between 1 and 10 are allowed in a column
How to check for a valid date if it is passed in as a string
Can you name the 4 isolation levels in SQL Server 2000
How would you select all rows where the date is 20061127
Name some differences between isnull and coalesce
If you create a local temp table and then call a proc is the temp table available inside the proc
What is a SQL injection

Update trigger to capture changed information
Which naming conventions did you use?

Name some best practices which you implemented

Select 3/2

Decimal(6,5)

Create table with PK symbol 10 characters, price 8 digit precision

Add clustered index to that table

What does set xact_abort on

Select * from table where ID <> 1

Favorite SQL Server book

How do you keep your SQL skills up to date?

 

 

 

How many bytes can you fit in a row, do you know why?

I am looking for one of these answers

8K

8060 bytes

Same as a page

8K + overflow column/text columns

If a person does not know the answer to this question then that is ok.


How do you enforce that only values between 1 and 10 are allowed in a column?

I am looking for check constraint as the answer. A bunch of people didn’t answer anything, some answered trigger and one said you should never check in the DB but in the application itself.


How to check for a valid date if it is passed in as a string?

The answer I am looking for is ISDATE() Some people knew this answer, some people answered “convert to datetime and then check for the error” one person said “parse the string”. A bunch of people didn’t answer anything at all


Can you name the 4 isolation levels in SQL Server 2000?

Another nice to have question, if you don’t know it then is not the end of the world. I will also ask the default transaction level


How would you select all rows where the date is 20061127?

I will draw a table on the board with values like these

2007-12-06 15:36:10.293

 

2007-12-07 00:00:00.000

2007-12-07 15:36:10.293

2007-12-07 15:36:10.293

 

2007-12-08 00:00:00.000

 

I am looking for the 3 rows which start with 2007-12-07

The answer I want is this

WHERE date >= ‘20061127’

AND date < ‘20061128’

 

I don’t want between because it will grab the 2007-12-08 value also, I don’t want convert because that causes an index scan. Less than half the people get the correct answer.

 

Some stuff to read:

Do You Know How Between Works With Dates?

http://sqlservercode.blogspot.com/2006/10/do-you-know-how-between-works-with.html

 

How Are Dates Stored In SQL Server?

http://sqlservercode.blogspot.com/2006/11/how-are-dates-stored-in-sql-server.html

 

The ultimate guide to the datetime datatypes

http://www.karaszi.com/SQLServer/info_datetime.asp



Name some differences between ISNULL and COALESCE

The main answer I am looking for is that ISNULL can only use 2 values while COALESCE can use a lot more. If the person knows other differences then that is also good. I was surprised at the number of people who never heard of COALESCE. Below is some code which shows the differences

 

There are three major differences besides being ANSI or not between COALESCE and ISNULL

1) COALESCE correctly promotes its arguments to the highest data type in the expression list, ISNULL does not

2) ISNULL can only work with 2 values while COALESCE can take a lot more

3) The alternate value takes the length of the first vale with ISNULL, with COALESCE this doesn't happen

 

 

Let's get started, run the following blocks of code

 

The result is 7, integer math

 

SELECT 15 / ISNULL(CONVERT(INT,NULL), 2.00)

 

The result is 7.5, which is correct

 

SELECT 15 / COALESCE(CONVERT(INT,NULL), 2.00)

 

You will see that the result is not the same ISNULL does integer math while COALESCE does not

 

COALESCE correctly promotes its arguments to the highest data type in the expression list.

ISNULL just looks at the first datatype, which is an integer (15) and makes everything an int

COALESCE looks at 2.00 and 15 and then promotes the integer to decimal

 

Another example is returning the first non null value, ISNULL can only take 2 values while COALESCE can take a whole lot more

Here we have 4 variables and all except for one are null

 

 

DECLARE @Var1 VARCHAR(20)

DECLARE @Var2 VARCHAR(20)

DECLARE @Var3 VARCHAR(20)

DECLARE @Var4 VARCHAR(20)

SELECT @Var4 = 'ABC'

 

--This will return ABC

SELECT COALESCE(@Var1,@Var2,@Var3,@Var4)

 

Last example.

ISNULL returns NOT while COALESCE returns Not There.

 

DECLARE @v VARCHAR(3)

SELECT  COALESCE(@v,'Not There')

SELECT  ISNULL(@v,'Not There')

 

The alternate value takes the length of the first value with ISNULL, with COALESCE this doesn't happen

 


If you create a local temp table and then call a proc is the temp table available inside the proc?

The answer is yes and the code is below

 

--create proc

CREATE PROC TestProc

AS

 

SET NOCOUNT ON

 

SELECT * FROM #temp

GO

 

--create our temp table

CREATE TABLE #temp (id int)

 

INSERT #temp VALUES(1)

INSERT #temp VALUES(2)

INSERT #temp VALUES(3)

 

--exec proc

EXEC TestProc


What is  SQL injection?

People either knew or did not know about SQL injection, the ones who knew also knew what to do to prevent it. A little less than half the people interviewed knew what it was.

 

Some stuff to read:

SQL injection cheat sheet.

http://ferruh.mavituna.com/makale/sql-injection-cheatsheet/

 

 

The Curse and Blessings of Dynamic SQL

http://www.sommarskog.se/dynamic_sql.html

 

 

Update trigger to capture changed information

In drew two tables on the whiteboard and explained to the people that I wanted to move data into a history table only when the value in a certain column changed. I was looking for several things

If the person knew about inserted and deleted tables

If the person would code the trigger for multi row statements and not assign the value to a variable

If the person knew about IF UPDATE(Column) and that this was true even if the table was updated with the same value

If  the person would join inserted and deleted to make sure no row would be inserted if the value didn’t change

 

Some stuff to read:

Fun With SQL server Update Triggers

http://sqlservercode.blogspot.com/2005/12/fun-with-sql-server-update-triggers.html



What naming conventions did you use?

Here I wanted to hear if the person had any standards, I would also ask for an example of a proc name hoping sp_ProcName wouldn’t be answered

 

Some stuff to read:

The ISO organization has a document on their site. The one that deals with naming conventions is 11179-5 The link will point to a zip file which has a pdf file in it. The TOC of this pdf file is below

Contents
Foreword
1 Scope
2 Normative references
3 Terms and definitions
4 Data Identifiers within a registry
5 Identification
6 Names
6.1 Names in a registry
6.2 Naming conventions
7 Development of naming conventions
7.1 Introduction
7.2 Scope principle
7.3 Authority principle
7.4 Semantic principle
7.5 Syntactic principle
7.6 Lexical principle
7.7 Uniqueness principle
Annex A (informative) Example naming conventions for names within an MDR registry
Annex B (informative) Example naming conventions for Asian languages

 

What best practices did you implement?

There is a whole range of possible answers here and I won’t get into detail.

 

Select 3/2

When I wrote Select 3/2 on the board and ask what this would return several people looked at me if I was crazy. The answer is of course 1 because of integer math. I ask this question because we run reports which deal with calculation with integers. Take a look at this code to see what you can do to ‘fix’ the issue

 

Run this
--Integer math
DECLARE @Val1 INT,@val2 INT
SELECT @Val1 =3, @val2 =2
SELECT @Val1/@Val2
GO

It returns 1


Now run this
DECLARE @Val1 INT,@val2 INT
SELECT @Val1 =3, @val2 =2

 

--Implicit
SELECT @Val1/(@Val2*1.0)

 

--Explicit
SELECT CONVERT(DECIMAL(18,4),@Val1)/@Val2
GO

 

Both of those return 1.50000000000000

 

 

So what does @val2*1.0 do? well run this

DECLARE @Val1 INT,@val2 INT
SELECT @Val1 =3, @val2 =2
SELECT  CAST(SQL_VARIANT_PROPERTY(@val2*1.0,'BaseType') AS VARCHAR(20)) + '(' +
             CAST(SQL_VARIANT_PROPERTY(@val2*1.0,'Precision') AS VARCHAR(10)) + ',' + 
           CAST(SQL_VARIANT_PROPERTY(@val2*1.0,'Scale') AS VARCHAR(10)) + ')'

 

As you can see it is numeric(13,1)
 

Decimal(6,5)

What is the greatest value that Decimal(6,5) can hold? A lot of people answered 999999.99999 which is wrong of course. The total number of numbers is 6 and 5 of those are after the comma. So the answer is 9.99999. Run this to try it out for yourself.

 

 

Create table with PK symbol 10 characters, price 8 digit precision

I would tell people to create a table with 2 columns

1 with a column named symbol variable character 10 in length, it would also be a primary key

Price which was decimal and can hold a number as big as 999999.99999999

They had to do this on the whiteboard in T-SQL (create table…..)

Most people got the PK syntax wrong; some people got the decimal wrong. The reason I am asking this question is because we can’t go on a production machine with Enterprise Manager and start clicking. You might not have permissions to make changes, you need to supply a script which other people might run.

 

Add clustered index to that table

My next question would be to add a clustered index to that table. The correct answer would be you can’t since the table has a primary key which is a clustered index by default

 

What does set xact_abort on do

Only one person answered this question correctly, this was another nice to know question. I did explain to people why you would use this, sometimes you cannot trap the error and this statement will enable you to rollback everything up until that point.

 

Some stuff to read:

Implementing Error Handling with Stored Procedures

http://www.sommarskog.se/error-handling-II.html

 

Error Handling in SQL Server – a Background.

http://www.sommarskog.se/error-handling-I.html

 

Select * from table where ID <> 1

I would draw a table with three rows, the values being 1, 2 and null. Then I would ask what would be returned after running this query

 

SELECT * FROM #temp WHERE id <> 1

 

A lot of people chocked on this one, the answer is 2 of course, null will not be returned. Test it out for yourself

 

 

CREATE TABLE #temp (id int)

INSERT #temp VALUES(1)

INSERT #temp VALUES(2)

INSERT #temp VALUES(null)

 

SELECT * FROM #temp WHERE id <> 1

 

Some stuff to read:

NULL trouble In SQL Server Land

http://sqlservercode.blogspot.com/2006/01/null-trouble-in-sql-server-land.html

 

NULL - The database's black hole

http://sqlblog.com/blogs/hugo_kornelis/archive/2007/07/06/null-ndash-the-database-rsquo-s-black-hole.aspx

 

The logic of three-valued logic

http://sqlblog.com/blogs/hugo_kornelis/archive/2007/07/17/the-logic-of-three-valued-logic.aspx

 

Dr. Unknown, or how I learned to stop worrying and love the NULL

http://sqlblog.com/blogs/hugo_kornelis/archive/2007/09/22/dr-unknown-or-how-i-learned-to-stop-worrying-and-love-the-null.aspx

 

What if null if null is null null null is null?

http://sqlblog.com/blogs/hugo_kornelis/archive/2007/09/30/what-if-null-if-null-is-null-null-null-is-null.aspx

 

Favorite SQL Server book

Here something bizarre happened; there were several people who did not have a SQL Server book at all. I did not understand this; how do you not have a SQL server books as a developer? A bunch of people listed Ken Henderson’s Guru book, some of them listed Inside SQL Server and one person answered SQL Server 2005 unleashed (I have the 2000 version, I should check this one out). In part three I will give you a list of my favorite books.

 

How do you keep your SQL skills up to date?

I am looking for websites, books, blogs, conferences, webinars, podcasts etc. Some people answered Books On Line. One person answered certification (I will address certification and my dislike of them in part 3). I was surprised by the difficulty that people had in answering this question. SQL Server Central was a popular website as well as DevX and MSDN. Nobody answered podcast at all. I would ask people if they played around with the latest CTP of SQL Server 2008, most of them were not aware that this was available to the general public. I did interview a bunch of SQL Server authors and I asked them what they did or would do to master SQL. You can read those interviews by clicking on the links below

  • Louis Davidson
  • Itzik Ben-Gan
  • Ken Henderson
  • Adam Machanic
  • Kalen Delaney
  •  

    That was it fort part two, I originally wanted to put more content here but I was afraid it would become too long, I moved some of that stuff to part three.

     

     

     

     

     

    Published Monday, December 10, 2007 3:54 PM by Denis Gobo

    Comment Notification

    If you would like to receive an email when updates are made to this post, please register here

    Subscribe to this post's comments using RSS

    Comments

     

    Denis Gobo : The Sad State Of Programmers Part 1 : The Phone Interview said:

    December 10, 2007 3:14 PM
     

    dmarkle said:

    Denis:

    Good set of questions.  Here's one that shocked me a while back.  One of my good friends is a EE PhD, and he was interviewing candidates for a job doing some pretty sophisticated signal processing work.  At one point, he said, something possessed him to ask, "How many bits are in a byte"?  And he was shocked by the reply.  I don't remember what the answer was exactly, but it wasn't 8 ;-).  So now this is on my question list.  You'd be amazed at how many people don't know it.  And lots of times, these people do well on the other questions.  You'll find yourself getting answers like, "It doesn't really matter - it's an implementation detail", or "we don't need to know this sort of thing with high level languages"...

    Another in my standard list:

    "I have a table, and there are a bunch of columns in it.  Of that bunch are "First Name" and "Last Name".  *Discuss* for me the performance implications of having two indexes, one on "First Name" and one on "Last Name", versus having one single index on both "Last Name" and "First Name"".

    And finally, I've found that you just have to have a short test.  I've had people absolutely ace the interview.  But then you give them a laptop with Northwind and Query Analyzer, and tell them to write simple query with a correlated subquery or with a nested GROUP BY.  I don't require that they necessarily get it right -- I've hired really good (not purely SQL) developers who haven't -- but you need to know that your developers can think in a set-based fashion if you're hiring a SQL "expert".  

    -Dave

    December 11, 2007 12:32 AM
     

    Chris Shaw said:

    I did a series of shows on this topic.  Interesting that we share some of the same thoughts.  Denis I found your article a good one and it surprises me every time I interview on the junk I see.

    December 11, 2007 2:14 AM
     

    chrissie1 said:

    As long as the squirrel doesn't move up in the world I'm fine.

    December 11, 2007 2:59 AM
     

    Madhivanan said:

    December 11, 2007 6:20 AM
     

    Alex said:

    You should be careful.  I hear that posts like this attract students, the unemployed, and homeless (and even squirrels maybe!) to *certain* sites

    December 11, 2007 12:24 PM
     

    David Burnett said:

    That's a lot of excellent questions.

    I usually ask people to use the HAVING clause. The number of people who don't know how to use it is amazing.

    To DMarkle:

    A byte is usually 8 bits but doesn't have to be. BBN machines used to have 10 bit bytes -- they were ugly monsters. A bunch of other machines had 9 bit bytes.

    December 11, 2007 3:26 PM
     

    Alexander Kuznetsov said:

    Hi Denis,

    I think it is very wrong to disclose interviewing approaches - it might enable someone to trick the way through an interview. I understand that you probably think that you are done with your interviewing, but what about other interviewers? And the fact that you writing is very clear and interesting does only makes the matter worse.

    December 11, 2007 6:28 PM
     

    Paul Nielsen said:

    Posting a list of good questions for folks to study is a good thing. Too many certification study questions don't relate to the real world. Like Thomas the Tank Engine says: "It's good to be useful."

    December 11, 2007 7:08 PM
     

    Adam Machanic said:

    Alex,

    Anyone who reads this site on a regular basis probably already has a leg up in the interview process!  Not saying that this site is special in this respect -- I'm referring to someone who takes the time to subscribe to any site to keep learning.  Those kinds of people probably already know this stuff.  The majority of people I've interviewed over the past several years never bother doing any reading outside of the bare necessity to get their job done.

    December 11, 2007 7:41 PM
     

    Denis Gobo said:

    Alexander,

    I wrote this part of this post before you replied in the other posts and I didn't feel like throwing that material away. I am actually interviewing people this week for another position. Just out of curiosity how many of the question in this post did you actually ask? Since I don't want your life to become difficult because of my posts contact me at denis.gobo At gmail and I will give you a list of additional questions and hopefully you can pick some of those.

    Denis

    December 11, 2007 7:41 PM
     

    AaronBertrand said:

    Alex, if they've learned it by reading here, then great!  That's one more thing they know.  Besides, it's usually pretty obvious (at least it has been to me) when they answer a question because they've read the answer somewhere, and not from their own thoughts and knowledge.

    In any case, hopefully you don't plan on taking Denis' question list and asking those and only those?  If the person aces this set of questions but fails everything else you've asked, again, it should be pretty obvious.

    December 12, 2007 7:48 AM
     

    andyleonard said:

    Denis,

      This is excellent work - the writing, the interview questions, the analysis, everything. Thanks for sharing all this with the community.

    :{> Andy

    December 12, 2007 7:55 AM
     

    dmarkle said:

    David:

    You're right about the definition of a byte, but let's face it, nowadays any answer to the question "how many bits are in a byte" that doesn't come out as "8" or at least have a thoughtful explanation ("9, if you count the parity bit on a RAM chip", "8 nowadays, but 10 on a BBN machine back in 1972") is pretty much wrong.  I don't see SQL Server being ported to a paper tape based machine anytime soon... ;-)

    Alex:

    There's nothing wrong with posting interview questions -- these are fine ones to ask.  But the core of any good interview is to ask "thinking" questions -- those which require the interviewee (and often the interviewer) to really understand the answers.  "How would you...?"  "Discuss why...?" "What do you love about...?"  "What do you hate about...?"  Of course, this requires that the interviewer be very confident with the subject matter him/herself.  In some cases, you could turn this "Sad state of..." post on its head, and say that many interviewers aren't good enough, either.  Any interviewer who would use a simple checklist of easily looked-up questions to determine a candidate's knowledge is playing with fire.

    -D

    December 12, 2007 8:15 AM
     

    Kevin3NF said:

    My favorite interview questions from the past:

    "Have you ever created a stored procedure?"  Me: Yes.

    "How many suits were sold in America last year?"

    "How many bytes in a bit?" (Just seeing if I was paying attention I guess)

    "How do you back up master, msdb and tempdb?"  (implies one answer fits all)

    Kevin3NF

    December 12, 2007 9:18 AM
     

    Adam Machanic said:

    Personally, I like whiteboard-based interviews.  My usual SQL Server developer interview starts with a scenario.  I ask the candidate to design, on the whiteboard, a simple database for employee data, and tell the candidate that it should include first name, last name, department, salary, and who the manager is.  Then I sit back and watch them draw something on the board.  Once they're done we can start talking about whether or not they used a key, what they used for a key, how they designed the management hierarchy, how to query the management hierarchy, whether they normalized out the department into a separate table, the appropriate data type for the salary, etc, etc, etc.  

    December 12, 2007 4:10 PM
     

    Alex Kuznetsov said:

    Hi Denis,

    I'm sorry if I sounded impolite.

    Believe me or not, but my list of questions was almost identical to yours. That was funny.

    Actually from where I sit the ability to solve problems, creativity, and the ability to think out of the box are definitely more valuable than knowledge of SQL.

    However, I am trying to estimate how much a candidate has learned, and I kind of divide that estimate by years of experience as declared in teh resume - I'm trying to estimate ability and desire to learn. This is where questions like the ones from your excellent collection come very handy.

    I'll come up with a new set of questions and I will keep them to myself - let us agree to disagree in that.

    Again, let me apologise.

    Cheers,

    AK

    December 12, 2007 9:02 PM
     

    Denis Gobo said:

    Alex,

    Don't worry....my skin is thicker than a wooly mammoth's   :-)

    The update trigger question for example was asked here yesterday

    http://groups.google.com/group/microsoft.public.sqlserver.programming/browse_thread/thread/c7402bcc51e84592/b09d661d5c84eff8#b09d661d5c84eff8

    Of course Celko had to go on his 'audit' crusade....

    Anyway, the original poster failed to account for multirow statements, null values and didn't use if update()...I am basically looking for at least these three things in the answer

    Denis

    December 13, 2007 6:47 AM
     

    Peter said:

    I enjoyed reading through this.  We don't interview too many people at this point, but they're some great questions to at least weed out those who actually have done the work from those who have read a book.

    I enjoyed the explanation of ISNULL vs. COALESCE.  I switched to COALESCE some time ago more for ANSI standards than anything else, but didn't fully understand the major differences.

    I was also happy to know that I could answer just about all of the questions correctly.  Only one or two I'd have to honestly admit that I'd have to look up the answer because I just don't know it off-hand.

    As for my interview, I spend time on SSIS/DTS and some of its ins/outs as well as T-SQL.  Manipulating data is important for my job and knowing how to work with the extra pieces of the product is important, especially because you never know what to expect from the source data.

    On the "add a clustered index" question - you can add a clustered index to a table with a PK either by dropping/re-creating the PK (which I've had to do - clustered on 10 columns with values that can change regularly, ugh) or if the PK was created as Non-Clustered to start with.  I don't know if that factors into the answers or not.

    December 16, 2007 11:25 PM
     

    Dataland said:

    Good post! I agree with much of what you said, but... I feel development methodologies are also critical, so I would add that to list.  I am a big proponent of intuitive Interative Development.  

    Crosstalk: http://dataland.wordpress.com/2007/12/17/developing-high-transaction-database-systems/

    December 17, 2007 1:14 PM
     

    Denis Gobo said:

    The Sad State Of Programmers Part 3 : General Tips This is the final part of this series. You can find

    December 27, 2007 12:29 PM

    Leave a Comment

    (required) 
    (required) 
    Submit

    About Denis Gobo

    I was born in Croatia in 1970, when I was one I moved to Amsterdam (and yes Ajax is THE team in Holland) and finally in 1993 I came to the US. I have lived in New York City for a bunch of years and currently live in Princeton, New Jersey with my wife and 3 kids. I work for Dow Jones as a Database architect in the indexes department, one drawback: since our data goes back all the way to May 1896 I cannot use smalldates ;-( I have been working with SQL server since version 6.5 and compared to all the other bloggers here I am a n00b. Some of you might know me from http://sqlservercode.blogspot.com/ or even from some of the newsgroups where I go by the name Denis the SQL Menace If you are a Tek-Tips user then you might know me by the name SQLDenis, I am one of the guys answering SQL Questions in the SQL Programming forum.

    This Blog

    Syndication

    Powered by Community Server (Commercial Edition), by Telligent Systems
      Privacy Statement