Skip to main content

Posts

Showing posts from August, 2012

Disadvantages of Normalization

Dear Friends,

We allready know Normalization is the process to optimize the storage and increase the performance of system by eliminating redundancy from database.



But there is also circumstances when Normalization can reduce database performance.Due to Joins-Jonis-Joins system makes complex to select a simple record-set.

A normalized database requires much more CPU, memory
and I/O to process transactions and database queries than does a normalization database.

Ref: 
1.) advantages-disadvantages-normalizing-database
2.) www.informit.com/articles

Serial number or row number

Sometimes there would be a requirement to add serial number (or) row number to the result set.
Example: The requirement would be to select some columns from the database tables along with Serial Number like below
SNo Customer_Name Customer_Phone_Number 1 aaa xxx-xxx-xxxx 2 bbb xxx-xxx-xxxx
So, in this post we will see how we can select that using T-SQL on Microsoft SQL Server 2008
We can achieve this in several ways:

1) Using Row_Number()

Row_Number() Returns the sequential number of a row within a partition of a result set, starting at 1 for the first row in each partition. 

T-SQL Script:

SELECT ROW_NUMBER() OVER (ORDER BY CUSTOMER_ID) AS [S.No]
                        ,CUSTOMER_NAME
                        ,CUSTOMER_PHONE_NUMBER
        FROM database.dbo.tblTableName

2) Using Identity(Property)

Identity() property creates an identity column in the table. So, first we need to create a temporary table with [S.No] column on it and then we will select the required columns from the temporary table.

T-SQL…