Here is a selection of interview questions and answers that would be of use for both SQL Server developer and DBA positions. I prefer fairly short questions that allow candidates to talk around the subject. I can then gauge the experience of a candidate by the depth of their answer – there’s no right or wrong answer and I wouldn’t expect a candidate to necessarily come up with complete answers to all questions. As I say it’s about understanding a candidate’s depth of knowledge and experience, rather than being a test of memory or recall. There also a couple of BI questions for Data Warehouse developers and designers. If you are preparing for an interview I would recommend reminding yourself of the issues around these subjects as in my experience they do come up in interviews quite frequently. I’ve listed some pointers to the answers.

What do you understand by "Collation" ?

Microsoft define this as “a set of rules that determines how data is compared, ordered, and presented”. In practice each string based column (varchar, char, text, nvarchar, nchar, ntext) can have a collation defined. Collations can also be defined at the database and instance level. If there is no column collation defined then the database collation is used. When creating databases if no database collation is specified then the instance collation is used. The instance collation is defined at installation. The collation defines how string based columns are sorted and compared. For instance Western European strings might have a collation of “Latin1_General” where as a string containing Greek or Russian characters would have a different collation. If you compare strings with different collations in a SQL query then it can give rise to an error.

What different types of backup are available in SQL Server ?

Full (full backup of the database), transaction log (backup of all transactions since last transaction log backup, only available for databases which use the full recovery model), differential (a backup of all data that has changed since last full backup), file or filegroup (a backup of an individual file or file group), partial (a backup of all filegroups usually excepting read-only ones, mainly used for data warehousing).

What is the difference between “TRUNCATE TABLE” and “DELETE FROM” ?

They both achieve a similar outcome (removing all rows from a table), but there are differences as follows :
1. TRUNCATE TABLE removes data by deallocating data pages while DELETE FROM removes rows one row at a time.
2. TRUNCATE TABLE is usually faster and typically uses fewer locks.
3. TRUNCATE TABLE does not fire triggers.
4. TRUNCATE TABLE cannot be used on tables that participate in indexed views, are referenced by foreign key constraints or participate in some forms of replication.
5. TRUNCATE TABLE resets identity values back to the seed values but DELETE FROM doesn’t.
6. Different permissions are required.
7. Optionally a where clause can be added to the DELETE FROM statement.

What is the difference between blocking and deadlocking ?

First of all it’s important to realise that these concepts are not the same, sometimes they are referred as if they are synonymous. Blocking is a natural part of using locks to control concurrency. If one query is updating a table then another query that is trying to read from the same table is blocked from reading until the update query is complete. This ensures that the read query doesn’t access partially updated data. A deadlock is a specific form of blocking where two queries are waiting on each other to complete, also known as a “deadly embrace”. Unlike blocking SQL Server has a mechanism which intervenes in this situation and cancels one query (the victim query). If that didn’t occur then both queries would wait indefinitely.

What do you understand by the term “lock escalation” ?

A lock uses a finite amount of resources. Lock escalation is the concept where many fine grained locks such as row level locks get escalated to a smaller number of courser page or table level locks. This uses less resources (which may become necessary as more and more rows are locked), but does mean that more data than necessary might be locked.

What is the difference between a primary key and a unique key ?

They’re very similar. They both uniquely identify a row. However there can only be one primary key, and it doesn’t allow nulls. There can be many unique keys and it can have a single null value.

What are the main differences between the full and simple recovery model ?

Some of the key points here are :
1. The full recovery model allows “point in time” database restores, simple recovery only allows restores to the time of the last backup.
2. Full recovery is more appropriate for OLTP production databases, Simple recovery for test databases and data warehouses.
3. Full recovery is used by SQL Server to implement some of the high availability features such as log shipping and mirroring.
4. Full recovery requires regular transaction log backups otherwise the transaction log file will keep growing.

Some extra questions on Data Warehousing

What is the difference between and type 1 and type 2 dimensions ?

Type 1 dimensions overwrite old data with new data (so there is no history), type 2 dimensions have a time attribute so will have a history and a new row every time a change is made. Type 2 are more common but use a lot more storage space especially if there are lots of changes. Also (for a bonus point !) type 3 is similar to type 1 except that it has extra columns to store some limited history.

What is a conformed dimension ?

It’s a dimension which contains common values used by several other dimensions. For instance a conformed dimension used for both a mortgage dimension and a pension dimension might contain account number and name and address details, but not mortgage interest rate as that is specific to pensions. They are most useful when there are many attributes which are common to two or more dimensions.

Good luck !