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...