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 

  Modified On Nov-30-2017 05:50:39 AM

Leave Comment