I will be sharing a series of interview questions and answers. The topics will be focused on:
  1. Data Modeling
  2. Transact SQL
  3. Data Warehouse Design
  4. SQL Server Integration Services (SSIS)
  5. SQL Server Reporting Services (SSRS)
  6. SQL Server Analysis Services (SSAS)
Please feel free to share your ideas with me on how to improve this further. I will also update this as I come up with more questions and answers.
Data Modeling
Describe a primary key.
  • A primary key is a column that uniquely identifies a row in a table. 
  • A table can only have one primary key
  • A primary key cannot contain any NULL value
  • You can combine multiple keys together to generate a single primary key
  • A unique clustered index is created when a primary key is created
Describe a unique key
  • A unique key is a column that uniquely identifies a row in a table
  • Unlike a primary key, a unique key can accept up to one NULL value
  • Unlike a primary key, a table can have more than one unique key
  • Unique non-clustered index is created when a unique key is created
Describe a foreign key
  • A foreign key helps to enforce referential integrity between related tables
  • It is a column in a table that references a primary key in another table
  • A foreign key can reference itself, that is the foreign key is acting as its own primary key (in the case of a unary degree relationship)
  • NULLs are allowed in a foreign key (not recommended)
What are the types of relationships in database design?
  • One-to-one
  • One-to-many
  • Many-to-many
  • One-to-fixed
What is Normalization?

Normalization is simply a step by step process of efficiently organizing data in a database based on some normalization rules.
Advantages of normalizing include:
·         Getting rid of insert, update and delete anomalies
·         Helps to improve the performance of DML operations (insert, update, delete)
·         Eliminating data redundancy
Normalization comes with some drawbacks like reducing the performance of read operations (SELECT)

What are the most common degrees of normalization?

·         First Normal Form (1NF): First normal form sets the very basic rules for an organized database. It is the most basic form of normalization
o   There should be no repeating rows. To ensure this, it means the table must have a primary key.
o   There should be no repeating columns. This means there cannot be two different columns with the same information.
o   All the attributes should be single-valued
o   All composite attributes should be broken down into its minimal component

·         Second Normal Form (2NF): A table is in its second normal form when
o   It satisfies the conditions for a first normal form
o   There are no partial dependencies. If any exist, they must be removed.

·         Third Normal Form (3NF): A table is in its third normal form when
o   It satisfies the conditions for a second normal form
o   There are no transitive dependencies. It any exist, they must be removed.