Click here to Skip to main content
13,513,739 members
Click here to Skip to main content
Add your own
alternative version


30 bookmarked
Posted 29 Jul 2007

SQL SERVER - Data Warehousing Interview Questions and Answers Complete List Download

, 29 Jul 2007
Rate this:
Please Sign up or sign in to vote.
I tried to accommodate all of them in the last post in the series. I hope this series is helpful to all candidates who are seeking job as well interviewers. I have combined all the questions and answers in the one PDF which is available to download and refer to at your convenience.


SQL Server Data Warehousing interview questions and answers were original published in three series. This complete series is combined in one article which is listed here in this article. This article is a PDF which is available to download for free.

Interview Questions and Answer PDF contains questions from beginner to advanced users.

Visit the complete series as well as the final link to download the PDF.

Complete Series of SQL Server Interview Questions and Answers

Samples Questions and Answers

What are Slowly Changing Dimensions (SCD)?

SCD is an abbreviation of Slowly changing dimensions. SCD applies to cases where the attribute for a record varies over time.

There are three different types of SCD:

  1. SCD1: The new record replaces the original record. Only one record exists in database - current data.
  2. SCD2: A new record is added into the customer dimension table. Two records exist in database - current data and previous history data.
  3. SCD3: The original data is modified to include new data. One record exist in database - new information are attached with old information in same row.

Differences Between Star and Snowflake Schema?

Star schema - A single fact table with N number of Dimension, all dimensions will be linked directly with a fact table. This schema is de-normalized and results in simple join and less complex query as well as faster results.

Snow schema - Any dimensions with extended dimensions are know as snowflake schema, dimensions may be interlinked or may have one to many relationship with other tables. This schema is normalized and results in complex join and very complex query as well as slower results.

What is Surrogate Key?

Surrogate key is a substitution for the natural primary key. It is just a unique identifier or number for each row that can be used for the primary key to the table. The only requirement for a surrogate primary key is that it is unique for each row in the table. It is useful because the natural primary key can change and this makes updates more difficult. Surrogated keys are always integer or numeric.

What is ER Diagram?

Entity Relationship Diagrams are a major data modeling tool and will help organize the data in your project into entities and define the relationships between the entities. This process has proved to enable the analyst to produce a good database structure so that the data can be stored and retrieved in the most efficient manner.
An entity-relationship (ER) diagram is a specialized graphic that illustrates the interrelationships between entities in a database. A type of diagram used in data modeling for relational data bases. These diagrams show the structure of each table and the links between tables.

What are the Different Methods of Loading Dimension Tables?

There are two different ways to load data in dimension tables.

Conventional (Slow)

All the constraints and keys are validated against the data before, it is loaded, this way data integrity is maintained.

Direct (Fast)

All the constraints and keys are disabled before the data is loaded. Once data is loaded, it is validated against all the constraints and keys. If data is found invalid or dirty, it is not included in index and all future processes are skipped on this data.

SQL SERVER - Data Warehousing Interview Questions and Answers Complete List Download


This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)


About the Author

India India
Pinal Dave is a Microsoft Technology Evangelist (Database and BI). He has written over 2200 articles on the subject on his blog at Along with 8+ years of hands on experience he holds a Masters of Science degree and a number of certifications, including MCTS, MCDBA and MCAD (.NET). He is co-author of two SQL Server books - SQL Server Programming, SQL Wait Stats and SQL Server Interview Questions and Answers. Prior to joining Microsoft he was awarded Microsoft MVP award for three continuous years for his contribution in community.

You may also be interested in...

Comments and Discussions

Generalproject guide Pin
mresh12-Aug-07 17:30
membermresh12-Aug-07 17:30 
AnswerRe: project guide Pin
Tomruster1-Sep-08 0:00
memberTomruster1-Sep-08 0:00 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.

Permalink | Advertise | Privacy | Terms of Use | Mobile
Web03 | 2.8.180417.1 | Last Updated 29 Jul 2007
Article Copyright 2007 by pinaldave
Everything else Copyright © CodeProject, 1999-2018
Layout: fixed | fluid