What is denormalization and when would you go for it?

The process of adding redundant data to get rid of complex join, in order to optimize database performance. This is done to speed up database access by moving from higher to lower form of normalization. 

In other words, we can define De-Nomalization as :-

De-normalization is the process of attempting to optimize the performance of a database by adding redundant data. It’s used To introduce redundancy into a table in order to incorporate data from a related table. The related table can then be eliminated. De-normalization can improve efficiency and performance by reducing complexity in a data warehouse schema. 

De-normalization is an application tool in SQL server model. There are three methods for de-normalization:.
• Entity inheritance
• Role expansion
• Lookup entities.

Entity Inheritance 
This method for the de-normalization should be implemented when one entity is named as another entity. This will do with the help of inheritance. Inheritance means parent child relations of entity. This will be do with making the foreign key and candidate key. This is also in notice that creation of model creates a band of relationship and if you select the inheritance this property should be automatically deleted.

Role Expansion
This type of de-normalization should be created when it is surety that one entity has the relationship to another entity or it is a part of another entity. In this storage reason is removed. It is used with the help of Expand inline function. It use the shared schema is used in from of table.

Lookup Entities
This type of de-normalization is used when entity depend on the lookup table. It is work with the help of Is Look up property. This property applies on the entity. These three will give authority to user to create a genuine and tempting report model .This model is navigation experience for the customer.

The Reason for Denormalization
Only one valid reason exists for denormalizing a relational design - to enhance performance. However, there are several indicators which will help to identify systems and tables which are potential denormalization candidates. These are:
• Many critical queries and reports exist which rely upon data from more than one table. Often times these requests need to be processed in an on-line environment.
• Repeating groups exist which need to be processed in a group instead of individually.
• Many calculations need to be applied to one or many columns before queries can be successfully answered.
• Tables need to be accessed in different ways by different users during the same timeframe.
• Many large primary keys exist which are clumsy to query and consume a large amount of disk space when carried as foreign key columns in related tables.
• Certain columns are queried a large percentage of the time causing very complex or inefficient SQL to be used.

Be aware that each new RDBMS release usually brings enhanced performance and improved access options that may reduce the need for denormalization. However, most of the popular RDBMS products on occasion will require denormalized data structures. There are many different types of denormalized tables which can resolve the performance problems caused when accessing fully normalized data. The following topics will detail the different types and give advice on when to implement each of the denormalization types.

Types of Denormalization

Pre-Joined Tables used when the cost of joining is prohibitive
Report Tables used when specialized critical reports are needed
Mirror Tables used when tables are required concurrently by two different types of environments
Split Tables used when distinct groups use different parts of a table
Combined Tables used when one-to-one relationships exist
Redundant Data used to reduce the number of table joins required
Repeating Groups used to reduce I/O and (possibly) storage usage
Derivable Data used to eliminate calculations and algorithms
Speed Tables used to support hierarchies