Home > DeveloperSection > Articles > Denormalization in SQL Server

Denormalization in SQL Server


Database Database 
Ratings:
0 Comment(s)
 13806  View(s)
Rate this:

Denormalization in SQL Server

What is Denormalization ?


The intentional introduce of redundancy in a table in order to improve performance is called Denormalization”. Denormalization is a technique to move from higher to lower normal forms of database modeling in order to speed up database access.De-normalization is the reverse process of Normalization i.e, to combine two or more tables into a single table. De-normalization increases the performance (Searching data from one table is quiet faster than searching data from multiple tables). Good for OLAP systems. Denormalization is usually done to decrease the time required to execute complex queries. Drawbacks of a normalized database are mostly in performance. In a normalized database, more joins are required to gather all the information from multiple entities, as data is divided and stored in multiple entities rather than in one large table. Queries that have a lot of complex joins will require more CPU usage and will adversely affect performance. Sometimes, it is good to denormalize parts of the database. Examples of design changes to denormalize the database and improve performance are:

ORDERS

denormalization in sql server

PRODUCTS

denormalization in sql server

If you have calculated the total cost of each order placed as the cost of the product plus a tax of 10% of the product cost, the query to calculate the total cost sales as follows:

select sum((cost*qty)+(0.10*cost*qty)) from orders join products on orders.ProductId =products.ProductId

If there are thousands of rows, the server will take a lot of time to process the query and return the results as there is a join and computation involved.

ORDERS

denormalization in sql server

To find the total sales write simple query:

select SUM(ORDERCOST)from orders 


Don't want to miss updates? Please click the below button!

Follow MindStick