ROLLUP in SQL Server

I learn about ROLLUP function today and I found it really usefull for generating reports. Let me give you one example where we can use ROLLUP. Consider We have a table as below

But the User wants the results as below. ie.,


User wants the Grand Total of the two columns(Offshore & Onsite) as the last row in the table. Here, we need the help of ROLLUP Keyword. Code Snippets to create table and inserting records
GO
create table #test
(roleplayer varchar(50),offshore int, onsite int)
GO
insert into #test
select 'Mobile',3,4
union
select 'Accomodation',43,4
union
select 'ID Card',30,41
union
select 'Payroll',31,42
union
select 'Library',23,42
union
select 'Bus',13,14
GO
select * from #test

Try the below query which will helps to produce the expected results

SELECT CASE
WHEN GROUPING(ROLEPLAYER)=1 THEN 'GRAND TOTAL'
ELSE ROLEPLAYER
END AS RolePlayer,
SUM(OFFSHORE) As OffShore,SUM(ONSITE) AS Onsite
FROM #TEST
GROUP BY ROLEPLAYER WITH ROLLUP

The above Query will produce the results as referred in the figure 2
Here is an another example for the ROLLUP.We have four columns for a table as below.

The User wants to display the subtotal for each roleplayer and the Grand total for all the Roleplayer as the last row. Refer the below figure


With the help of the ROLLUP Keyword we can achieve this.Code Snippet to Create table and inserting records
create table #test1
(roleplayer varchar(50),Area varchar(50),offshore int, onsite int)
GO
insert into #test1
select 'Mobile','Chennai',3,4
union
select 'Mobile','Bangalore',5,9
union
select 'Mobile','Calcutta',7,4
union
select 'Accomodation','Calcutta',43,4
union
select 'Accomodation','Cochin',43,4
union
select 'Accomodation','Chennai',43,4
union
select 'Accomodation','Mumbai',43,4
GO
select * from #test1

The below Query with ROLLUP produce the results as shown in the figure 4.SELECT CASE
WHEN GROUPING(ROLEPLAYER)=1 THEN ''
ELSE ROLEPLAYER
END AS RolePlayer,
CASE
WHEN GROUPING(Area)=1 THEN ISNULL(ROLEPLAYER +'Total','Grand Total')
ELSE Area
END AS Area,
SUM(OFFSHORE) AS OffShore,SUM(ONSITE) AS OnSite
FROM #TEST1
GROUP BY ROLEPLAYER,Area WITH ROLLUP
Thanks

I hope the ROLLUP will help you Guys.

Decimal Calculation in SQL Server

In Arithmetic calculations some developers may struggling in getting the results with specified decimals digits.
Say for Example when the user executes the below query
SELECT 1/12
The result of the query is 0
The expected result for the user is 0.08
ie., The user wants the results with two decimal points.
Solution:
The following steps will give you the expected results
Step 1: Convert/Cast the value 1 to float type
Step 2: Round off the full value ie., 1/12 to two digits
Step 1 Execution:
SELECT CAST(1 AS FLOAT)/12
The output for the above query is 0.0833333333333333

Step 2 Execution :
SELECT ROUND(CAST(1 AS FLOAT)/12,2)
The output for the above query is 0.08 as the user expected.

TVP (Table Value Parameter - SQL 2008)

This is a excellent feature provided in SQL Server 2008. Now in SQL Server 2008 we can pass a table as a parameter to stored procedure.Let me explain with an example.
I need to pass a collection of rows to a procedure. The collection of rows is the output of many joins between the tables with the select statement. Within the procedure we have to some kind of manipulation with the set of rows. In SQL 2005 there is no such facility to pass a table as a parameter to the procedure. But in SQL 2008 we have the facility to pass a table as a parameter to the procedure called Table Value Parameter.
Consider that the output of the query is stored in the below table.
CREATE TYPE CustomerType AS TABLE (Name varchar(20), Age int)
The procedure is created as below where the @Customer is table value parameter
CREATE PROCEDURE sp_MySproc(@Customer CustomerType READONLY)
Finally, call the stored procedure using the below statement. Where the @Customer is a table which is having set of rows.
exec sp_MySproc @Customer
by this way you can use the TVP as more powerful.
I hope it will help you guys..

CDC in SQL Server 2008

CDC - Change Data Capture, it will capture the changes on both data and schema in a server. This is a new and powerful facility provided by Microsoft in SQL Server 2008. This feature is very useful when the developer is working with many servers.
Say for example a developer DevA is working with the server Srv1 and Srv2. He wants to pull the records from the Srv1 to Srv2. There is a table called tab1 is available in Srv1 and he wants to pull the records from the tab1 to Srv2 table tab2 for reporting purposes.
In SQL 2005:
Consider both servers (Srv1 & Srv2 ) in SQL 2005. The developer written a job from Srv2 to pull the full records from tab1 in Srv1. Every time he is truncating the table tab2 in Srv2 and pushing all the records from the tab1 in Srv1.
Drawbacks:

• It takes more time to pull all the records from tab1.
• If something happens while pulling there is no records are available in tab2 (Srv2) because of truncation.

In SQL 2008:
Consider both the server (Srv1 & Srv2) are in the SQL Server 2008. In SQL Server 2008 we have a facility to track a table what are the changes done for the data and schema. There is a tracking table is maintained on each table which keep tracks of the changes in the table. If you enabling the CDC for the tab1 in the Srv1 which will keep track the changes on the data in a seperate table.
From the tracking table the developer can easily find the which records is inserted or updated or deleted newly in the table tab1. With the help of the tracking table, the developers can easily pull the countable records from the table tab1(Srv1) to the table tab2(Srv2).

I hope it will helpful for you guys... If you not clear Please give your feedback...

Perfomance Tunning (Update Statement)

How to increase the perfomance for an update statement. Suppose we have a table Tab1 which is having more than 5 crores of records. The user want to update a single column in the table Tab1 using update statement. Now the update statement will take few hours to update the column for all the records.
Hints to improve the performance of the update statement

• Check the corresponding table is having any triggers, if triggers are found then disable the triggers while updating.
• Check Is there any constraints for all the columns in the table, if so then make the constraints disable while updating the columns.
• Check is there any Clustered or Non Clustered Index for the table, if so then drop the indexes while updating and recreate the indexes once you completes the update.

I hope the above hints will helps you to improve the perfomance of an update statement.

Covering Index

Covering Index
Everyone is familiar with Clustered and non clustered index. In SQL Server 2005 there is an additional keyword provided for the non clustered index called Covering Index. Covering Index is an Index which is only applicable for non clustered Index, which will cover all the columns (columns mentioned in the query eg., Select col1,col2... from T1) in the Index itself. Actually it will improve the perfomance of the query by keeping all the columns in the Index lookup itself.

In the absence of the Covering Index, the Index will have only the Index column (only one column col1). Inorder to fetch other columns again it will use the Index column col1 lookup. This lookup search step (for other columns) you can avoid when you start using the covering Index.

I hope its helps..

Thiyagarajan

Index Seek Vs Index Scan

Index Seek Vs Index Scan
Many developers exactly knows the different between Index seek and Index scan. But few developers may be in the confusion to find the difference between Index Seek and Index scan.

• When the table is created with the Index then the query will not do the table scan. It will go for Index scan or Index seek it depends.
• For example, the table T1 have an index called Ix1. It is having more than 5 Cr of records.
• When the developer fires an query with a where condition on the Index column. Then the Query will for Index seek or Index Scan.
• Index Seek or Index Scan is purely depends on the density of the rows which the query returns.
• If the query returns less rows eg., only 5 to 10 rows then Index Seek will happen
• If the query return more than 10000 rows then Index Scan will happen

I hope you will get some idea about the difference between Index seek and Index Scan