UnPivoting data in SQL Server

There are some situations when we need to do the data comparison row level. One of the solution of this kind of situation is to do the "Unpivoting" of data which will apparently move the records under a specific column and then we can do the filter of the data by using the "Where" clause.

Here is an example.
Lets take the below sample code

declare @Test as table(id int, col1 int, col2 int, col3 int, col4 int)
insert into @Test values(1, 100, 500, 300, 200)
insert into @Test values(2, 600, 300, 500, 400)
insert into @Test values(3, 400, 100, 800, 300)

select * from @Test

The expected output from the above table is to get the each rows max value. This means the output should be
ID MaxVal
1 500
2 600
3 800

Since in this case we need to do the comparison at row, the first thing which should be clicked is unpivoting of data and then do the normal filtering. To get this output we will use the SQL Server Unpivot command as below.

SELECT id,Max(colVal) as MaxVal
FROM
(SELECT * FROM @Test) T
UNPIVOT
(
ColVal FOR columnnames in(col1,col2,col3,col4)
) as UnPvtTable
Group by ID

Finding missing Indexes in SQL Server

As you might already know SQL Server DMV's (Dynamic Management Views) started from SQL Server 2005 are very helpful in analysing the SQL Server performance (finding locks, missing indexes, indexes which are not in use and causing overhead, long running t-sql queries, sp's etc...). I enjoy reading the Brent Ozar's blog in SQL Server performance and they have been very helpful personally to me in understanding the SQL Server performance tuning.
The SQL Server performance tuning used to be a task of DBA's to work on in the time of SQL Server 2000, but going forward starting from SQL Server 2005 with the help of DMV's a normal SQL developer also can contribute a lot in this area.
In this article I am going to talk about finding the missing indexes in SQL Server 2005/2008. Indexes are very critical if we talk about SQL Server performance and can impact a lot. If the transactional system is used for reporting purpose also the index can play a major role in improving performance of the reports. One thing we need to be very carefuly before adding new indexes is to make sure we analyse them thoroughly before adding. If any erp system/app is ruuning on top of SQL Server we need to make sure and understand adding a new index is not going to effect the performance of erp system/app since adding new index means more time in insertion and update activities.
I have copied the script below which is very helpful and gives top missing indexes with their impact. Adding these top index can definitely improve the SQL Server performance but once again we should very careful before adding these. We should be analysing these first in the staging/dev. environment before adding in production if required.


SELECT sys.objects.name
, (avg_total_user_cost * avg_user_impact) * (user_seeks + user_scans) AS Impact
, 'CREATE NONCLUSTERED INDEX ix_IndexName ON ' + sys.objects.name COLLATE DATABASE_DEFAULT + ' ( ' + IsNull(mid.equality_columns, '') + CASE WHEN mid.inequality_columns IS NULL
THEN ''
ELSE CASE WHEN mid.equality_columns IS NULL
THEN ''
ELSE ',' END + mid.inequality_columns END + ' ) ' + CASE WHEN mid.included_columns IS NULL
THEN ''
ELSE 'INCLUDE (' + mid.included_columns + ')' END + ';' AS CreateIndexStatement
, mid.equality_columns
, mid.inequality_columns
, mid.included_columns
FROM sys.dm_db_missing_index_group_stats AS migs
INNER JOIN sys.dm_db_missing_index_groups AS mig ON migs.group_handle = mig.index_group_handle
INNER JOIN sys.dm_db_missing_index_details AS mid ON mig.index_handle = mid.index_handle AND mid.database_id = DB_ID()
INNER JOIN sys.objects WITH (nolock) ON mid.OBJECT_ID = sys.objects.OBJECT_ID
WHERE (migs.group_handle IN
(
SELECT TOP (500) group_handle
FROM sys.dm_db_missing_index_group_stats WITH (nolock)
ORDER BY (avg_total_user_cost * avg_user_impact) * (user_seeks + user_scans) DESC))
AND OBJECTPROPERTY(sys.objects.OBJECT_ID, 'isusertable')=1
ORDER BY 2 DESC , 3 DESC