SQL Server 2008 - Interview Questions and Answers

A dirty read occurs when two operations say, read and write occurs together giving the incorrect or unedited data. Suppose, A has changed a row, but has not committed the changes. B reads the uncommitted data but his view of the data may be wrong so that is Dirty Read.

Aggregate functions perform a calculation on a set of values and return a single value. Aggregate functions ignore NULL values except COUNT function. HAVING clause is used, along with GROUP BY, for filtering query using aggregate values.

Following functions are aggregate functions.

AVG, MIN CHECKSUM_AGG, SUM, COUNT, STDEV, COUNT_BIG, STDEVP, GROUPING, VAR, MAX. VARP

TABLESAMPLE allows you to extract a sampling of rows from a table in the FROM clause. The rows retrieved are random and they are not in any order. This sampling can be based on a percentage of number of rows. You can use TABLESAMPLE when only a sampling of rows is necessary for the application instead of a full result set.

1. UNION The UNION command is used to select related information from two tables, much like the JOIN command. However, when using the UNION command all selected columns need to be of the same data type. With UNION, only distinct values are selected.
2. UNION ALL The UNION ALL command is equal to the UNION command, except that UNION ALL selects all values.

The database server uses a B-tree structure to organize index information. B-Tree generally has following types of index pages or nodes:

1. root node: A root node contains node pointers to branch nodes which can be only one.
2. branch node: A branch node contains pointers to leaf nodes or other branch nodes which can be two or more.
3. leaf nodes: A leaf node contains index items and orizantal pointers to other leaf nodes which can be many.