Applicative / Development DBA Interview Questions - Part 1

If you got the chance to interview a person for applicative
(meaning development)  DBA position, you will find the
following questions helpful:

1. What is an execution plan and what for it needed?

A query execution plan is
an ordered set of steps used
to access or modify
information in a database.
An SQL statement tells
what you want and the
execution plan tells you how
the SQL Server going to
do it, meaning what
operations,operators it
will use (Table/Index
scan,Index Seek, Nested loop and etc)

SQL Server builds an execution plan in order to save
all steps it does upon submitting a query for execution
(like parsing, optimizing) and of course for reuse purpose.

2. When the execution plan created?

It created on the first execution of a SP.

3. Is an execution plan takes into an account values of SP`s input parameters?

Yes, the execution plan is built considering the value of SP`s
input parameters.(Prameters Sniffing)

4. Is an execution plan is permanent? If not, when the SQL Server changes it?

It is not permanent, the SQL Server can decide to recompile
a SP and different execution plan can be created. There are
many reasons for SP recompilation, like Schema/Statistics
changes, Temp table(s) usage and etc.

5. When you rebuild a Clustered Index are non clustered indexes rebuilt too?

Starting from SQL Server 2005, the answer is NO!
SQL Server 2005 will re-use the old uniquifier values so the
cluster keys don't change. This means that non-clustered indexes
are NOT rebuilt.

See Paul Randal Article.

6. Where does a Non Clustered index points to?

In case you have table without a Cluster index, it ponits to
data pages, once clustered index is created, non clustered
indexes will be reorganized and point to clustered index.

To be continued....