Denormalization in Sqlserver

Introduction

Before we understand denormalization Lets understand the table design . Table can be designed in 2 ways as below.

Normalization
Denormalization

see the below Fig(Fig-1).

Fig-1

Lets Get Started

Normalization is used to Disassociate the large table into a small tables(simpler format) having related data.It takes part in relational database.

Denormalization is just opposite to it. Smaller table converted to a larger table. So it does not take part in relational database.

There is an obvious question comes to our mind , if there is a process of making a table simpler with the related data then "why we need denormalization which again keep redundant data which is difficult to understand by the user?"

The question is really appreciated, but there is some scenario where we can use denormalization in stead of Normalization.

Lets understand why?

Lets take an example of a query which includes "Join" along with some manipulation.

Lets create three normalized tables Employee,Dept and Contact.I wanted some kind of manipulation.

Lets say joining. What happen when we try to manipulate huge numbers records(lac) and we know joining make the execution slower because of comparing from one table to another table. There is a chance of application timeout.

What is the solution then?

The Obvious choice is Denormalization.

see the below image.The left hand side called Normalized table where as right side table is called denormalized table(combination of 3 normalized table).

When retrieve complex calculation from multiple table and store into a single table which should show data with simple select statement which out any complex query like Joining or sub query or any other.

For example in the above case we can write as below.

Select Empid,EmpName from <Denormalized Table> where <condition>

Few Points to remember about Denomalization

It used to improve performance of the database.

If the query is not much complex then we can prefer normalized table. If the query is complex then we follow the denormalized process.

Always 1st preference is Normalized table . If that does not work and taking too much time to execute then we should go for De-normalization.

De-Normalization contains redundant data unlike normalization.

Thanks.......