top button
Flag Notify
    Connect to us
      Site Registration

Site Registration

Denormalization in SQL Server

+2 votes
473 views

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. Denormalization is usually done to decrease the time required to execute complex queries

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
enter image description here

PRODUCTS
enter image description here

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
enter image description here

To find the total sales write simple query:

select SUM(ORDERCOST)from orders
posted Feb 20, 2014 by Amit Kumar Pandey

  Promote This Article
Facebook Share Button Twitter Share Button LinkedIn Share Button


Related Articles

Displaying the Row Number in a SELECT Query with SQL Server 2005

Displaying the Row Number in a SELECT Query was not easy with SQL 2000 or less where you need to create a temp table to display the row numbers for a returned result set. 

Now SQL Server 2005 ROW_NUMBEWR() that is very handy in scenarios where you want the result set to have row numbers assigned to each returned row. 

For example:

Select empname 
from employees 
where city = "toronto"

If you want the row_numbers to be displayed after this query is run, then use the ROW_NUMBER()function as below:

SELECT ROW_NUMBER() OVER(ORDER BY empname) AS 'row number', empname FROM employees WHERE city = "toronto"

When you run the above query, you will see results like those below. This avoids creating temp tables.

  1 Fred
  2 Bill
  3 Jeff
  4 June

What really happens here is that the ROW_NUMBER()assigns a unique number to each row to which it is applied (either each row in the partition or each row returned by the query), in the ordered sequence of rows specified in the order_by_clause, beginning with 1.

 READ MORE
...