Introduction
An interview is a very important event for any person. A good interview leads to a good career if the candidate is willing to learn. I always enjoy interview question and answer series. This is my very humble attempt to write SQL Server 2008 interview questions and answers. SQL Server is a very large subject and not everything is usually asked in an interview. In an interview, what matters the most is conceptual knowledge and learning attitude. I have listed the entire series in this post so that it can be easily downloaded and used. All the questions are collected and listed in one PDF which is here to download.
If you have any questions or if you want to add to any of the questions, please send me an email or write a comment.
Sample of Questions Specific to SQL Server 2008
What is Data Compression?
In SQL SERVER 2008, Data Compression comes in two flavors:
- Row Compression
- Page Compression
Row Compression
Row compression changes the format of physical storage of data. It minimizes the metadata (column information, length, offsets, etc.) associated with each record. Numeric data types and fixed length strings are stored in variable-length storage format, just like Varchar. (Read more in the download)
Page Compression
Page compression allows common data to be shared between rows for a given page. It uses the following techniques to compress data:
- Row compression
- Prefix Compression
For every column in a page, duplicate prefixes are identified. These prefixes are saved in compression information headers (CI) which reside after page headers. A reference number is assigned to these prefixes and that reference number is replaced wherever those prefixes are being used.
Dictionary Compression
Dictionary compression searches for duplicate values throughout the page and stores them in CI. The main difference between prefix and dictionary compression is that prefix is only restricted to one column while dictionary is applicable to the complete page.
Reference
