What is a transaction and what
are ACID properties?
A transaction is a logical unit of work in which, all the steps
must be performed or none. ACID stands for Atomicity,
Consistency, Isolation, Durability. These are the properties of
a transaction. For more information and explanation of these
properties, see SQL Server books online or any RDBMS
fundamentals text book.
Explain different isolation levels
An isolation level determines the degree of isolation of data
between concurrent transactions. The default SQL Server
isolation level is Read Committed. Here are the other isolation
levels (in the ascending order of isolation): Read Uncommitted,
Read Committed, Repeatable Read, Serializable. See SQL Server
books online for an explanation of the isolation levels. Be sure
to read about SET TRANSACTION ISOLATION LEVEL, which lets you
customize the isolation level at the connection level.
CREATE INDEX myIndex ON myTable(myColumn)
What type of Index will get created after executing the above
statement?
Non-clustered index. Important thing to note: By default a
clustered index gets created on the primary key, unless
specified otherwise.
What's the maximum size of a row?
8060 bytes. Don't be surprised with questions like 'what is
the maximum number of columns per table'. Check out SQL Server
books online for the page titled: "Maximum Capacity
Specifications".
Explain Active/Active and Active/Passive cluster
configurations
Hopefully you have experience setting up cluster servers.
But if you don't, at least be familiar with the way clustering
works and the two clusterning configurations Active/Active and
Active/Passive.
Explain the architecture of
SQL Server
This is a very important question and you better be able to
answer it if consider yourself a DBA. SQL Server books online is
the best place to read about SQL Server architecture. Read up
the chapter dedicated to SQL Server Architecture.
What is lock escalation?
Lock escalation is the process of converting a lot of low
level locks (like row locks, page locks) into higher level locks
(like table locks). Every lock is a memory structure too many
locks would mean, more memory being occupied by locks. To
prevent this from happening, SQL Server escalates the many
fine-grain locks to fewer coarse-grain locks. Lock escalation
threshold was definable in SQL Server 6.5, but from SQL Server
7.0 onwards it's dynamically managed by SQL Server.
What's the difference between DELETE TABLE and TRUNCATE TABLE
commands?
DELETE TABLE is a logged operation, so the deletion of each
row gets logged in the transaction log, which makes it slow.
TRUNCATE TABLE also deletes all the rows in a table, but it
won't log the deletion of each row, instead it logs the
deallocation of the data pages of the table, which makes it
faster. Of course, TRUNCATE TABLE can be rolled back.
Explain the storage models of OLAP
What are the new features introduced in SQL Server 2000 (or
the latest release of SQL Server at the time of your interview)?
What changed between the previous version of SQL Server and the
current version?
What are constraints? Explain different types of constraints.
Constraints enable the RDBMS enforce the integrity of the
database automatically, without needing you to create triggers,
rule or defaults. Types of constraints: NOT NULL, CHECK,
UNIQUE, PRIMARY KEY, FOREIGN KEY
Whar is an index? What are the types of indexes? How many
clustered indexes can be created on a table? I create a separate
index on each column of a table. what are the advantages and
disadvantages of this approach?
Indexes are of two types. Clustered indexes and non-clustered
indexes. When you craete a clustered index on a table, all the
rows in the table are stored in the order of the clustered index
key. So, there can be only one clustered index per table.
Non-clustered indexes have their own storage separate from the
table data storage. Non-clustered indexes are stored as B-tree
structures (so do clustered indexes), with the leaf level nodes
having the index key and it's row locater. The row located could
be the RID or the Clustered index key, depending up on the
absence or presence of clustered index on the table.
If you create an index on each column of a table, it improves
the query performance, as the query optimizer can choose from
all the existing indexes to come up with an efficient execution
plan. At the same t ime, data modification operations (such as
INSERT, UPDATE, DELETE) will become slow, as every time data
changes in the table, all the indexes need to be updated.
Another disadvantage is that, indexes need disk space, the more
indexes you have, more disk space is used.
|
