Interview Questions


June 8, 2011 by Mike Hillwig

I’ve mentioned that I’m going to be moving on in my career. My company has started the search for my replacement. Just in case I’m not able to interview potential candidates for my job, I’ve prepared a list of interview questions for my boss. He’s not a database guy, so I thought I’d give him some things to look for.

When is it a good idea to enable auto shrink on a database?

When you don’t care about performance. Other than that, never. Auto shrink puts a lock on the entire database, fragments the hell out of your indexes, and you have no control over when it runs. On top of that, the next time you rebuild indexes, the file will just grow again.

What is the difference between a primary key and a clustered index?

These go hand-in-hand but are frequently mistaken as one and the same. A primary key will be used to as a unique identifier to define a single record in a database. The clustered index is used to define how the records are laid out in the data pages. You almost always create the clustered index on the primary key. Almost. But not always.

When would you disable auto growth on a data file?

When a data file has reached a certain size and you want to prevent it from getting bigger. In that case, you would add additional data files to the database and new data would flow into those files.

Another case, although unlikely, is when a database isn’t growing.

What you’re looking for is to make sure you don’t grow data files too large and that your files don’t outgrow available disk space.

Microsoft’s default for data file growth is 1 MB. When would you change this?

Always. Not changing this fragments your disks because your data files won’t be contiguous. This is a setting that DBAs hate. The problem is that nobody can come up with a better number. It always depends on the specific instance

Fizz Buzz

Using pseudo code, list all integers between 0 and 100. If the number is divisible by 3, replace the number with the word FIZZ. If it’s divisible by 5, replace the number with the word BUZZ. If the number is divisible by both 3 and 5, replace the number with FIZZBUZZ.

Refer to

The goal here is to see how the person thinks. There is no best correct answer. You’re looking more for analytical and creative thinking.

When would you use SIMPLE recovery mode on a database?

Simple recovery is used when you don’t need to restore to a specific point in time. Simple recovery doesn’t back up transaction logs, so you only do full database backups.

This is useful for static databases and for databases where the data can be recreated with minimal effort. An example in our environment is the installbase on DBWarehouse. All of the data comes from other data sources, so transaction logging isn’t necessary.

Why would you use a GUID (goo-id) for a primary key? Why wouldn’t you?

A GUID is a string of 36 characters. It appears to be random, but SQL Server has a methodology for creating them. A GUID works well for a distributed application that will need to be consolidated later. This is exactly what Sharepoint does. For most applications, it’s a very bad design. Sequential integers almost always make a better primary key because the data will be laid out in data pages sequentially. By using a 36-character non-sequential primary key , this will cause page splits, and those are very expensive for performance.

If you don’t have the SA password, how else could you get administrative access to a database server?

Any other user with the sysadmin role.

I have an account that is a domain administrator that keeps getting Access Denied messages connecting to a SQL Server. Any idea what the problem could be?

With the introduction of SQL 2005, Microsoft removed the local administrators group from automatically being SQL system administrators. If you need your local administrators group to be SQL system administrators, you can do that at installation. Otherwise, you can create a login that corresponds to your AD Domain Admins group.

This is just my first few to start with. Any suggestions?

3 thoughts on “Interview Questions

  1. Denis Gobo says:

    Nice list,

    In regards to “What is the difference between a primary key and a clustered index?” by default when you create a PK it creates a clustered index behind the scenes

    There are however some differences that you did not mention.
    A primary key can’t have a null value a clustered index can
    A pk cannot have duplicates a clustered index can have duplicates(a uniqueifier will be created)

    create table bla(id int)
    create clustered index ix_bla on bla(id)

    insert bla values (null)
    insert bla values (1)
    insert bla values (1)
    insert bla values (null)

    select * from bla


    A guid can be sequential, just make sure to use the NEWSEQUENTIALID() function as the default instead
    that said I don’t like GUIDs either

  2. […] Interview Questions – Mike Hilwig (Blog|Twitter) shares with us some of the interview questions that he puts to candidates. […]

  3. chris says:

    Ask them to very specifically write out the syntax to use a cursor to iterate through a table.

    If they get all the syntax right, don’t hire them.

Leave a Reply

Your email address will not be published. Required fields are marked *