SQL Server 2008 | SQL with Manoj

Using GROUPING SETS | SQL Server 2008 and above

November 20, 2014 2 comments

In my previous posts (long back) I discussed about using [CUBE and ROLLUP] operators introduced in SQL Server 2008. I also discussed about COMPUTE & COMPUTE BY operators, but there two were removed from the SQL Server 2012 version.

Here in this post I’ll talk about GROUPING SETS (few days back my friend asked me about this and I thought its better to document this here).

Just like CUBE and ROLLUP, GROUPING SETS are used to GROUP and calculate Sub Totals and Totals within a set of records. Compared to ROLLUP with GROUPING SETS you have more flexibility to show Sub Totals on selected columns.

-- ROLLUP
SELECT class, section, rollno, sum(marks) [sum]
FROM #tempTable
GROUP BY class, section, rollno WITH ROLLUP

Output:
class		section	rollno	sum
HighSchool	a	1	80
HighSchool	a	2	70
HighSchool	a	3	80
HighSchool	a	NULL	230
HighSchool	b	4	90
HighSchool	b	5	90
HighSchool	b	6	50
HighSchool	b	NULL	230
HighSchool	NULL	NULL	460
Intermediate	a	1	60
Intermediate	a	2	50
Intermediate	a	3	80
Intermediate	a	NULL	190
Intermediate	b	4	90
Intermediate	b	5	50
Intermediate	b	6	70
Intermediate	b	NULL	210
Intermediate	NULL	NULL	400
NULL		NULL	NULL	860

–> Now, if you want to just Sub Total on Class level, you just need to use GROUPING SET operator like this:

-- GROUPING SETS on selective columns
SELECT class, section, rollno, sum(marks) [sum]
FROM #tempTable
GROUP BY GROUPING SETS (
	 (class, section, rollno)
	,(class)
)

This will give you more controlled Sub Totaling and grouping on selected columns:

Output:
class		section	rollno	sum
HighSchool	a	1	80
HighSchool	a	2	70
HighSchool	a	3	80
HighSchool	b	4	90
HighSchool	b	5	90
HighSchool	b	6	50
HighSchool	NULL	NULL	460
Intermediate	a	1	60
Intermediate	a	2	50
Intermediate	a	3	80
Intermediate	b	4	90
Intermediate	b	5	50
Intermediate	b	6	70
Intermediate	NULL	NULL	400

–> You can also get the same output like ROLLUP operator, but you have to provide columns in following groups:

-- GROUPING SETS
SELECT class, section, rollno, sum(marks) [sum]
FROM #tempTable
GROUP BY GROUPING SETS (
	 (class, section, rollno)
	,(class, section)
	,(class)
	,()
)

Note: You can also use GROUPING SETS with combination of ROLUP and/or CUBE operators, check more on regarding this on MS BoL, here.

–> Grouping Sets equivalent for SQL Server 2005 and below, check here.

Categories: SQL Server 2008 Tags: CUBE, GROUP BY, GROUPING, GROUPING SETS, ROLLUP, SQL SERVER 2008

Passing multiple/dynamic values to Stored Procedures & Functions | Part 4 – by using TVP

September 10, 2012 2 comments

This is the last fourth part of this series, in previous posts we talked about passing multiple values by following approaches: CSV, XML, #table. Here we will use a new feature introduced in SQL Server 2008, i.e. TVP (Table Valued Parameters).

As per MS BOL, TVPs are declared by using user-defined table types. We can use TVPs to send multiple rows of data to Stored Procedure or Functions, without creating a temporary table or many parameters. TVPs are passed by reference to the routines thus avoiding copy of the input data.

Let’s check how we can make use of this new feature (TVP):

-- First create a User-Defined Table type with a column that will store multiple values as multiple records:
CREATE TYPE dbo.tvpNamesList AS TABLE 
(
	Name NVARCHAR(100) NOT NULL,
    PRIMARY KEY (Name)
)
GO

-- Create the SP and use the User-Defined Table type created above and declare it as a parameter:
CREATE PROCEDURE uspGetPersonDetailsTVP (
	@tvpNames tvpNamesList READONLY
)
AS
BEGIN
	
	SELECT BusinessEntityID, Title, FirstName, MiddleName, LastName, ModifiedDate
	FROM [Person].[Person] PER
	WHERE EXISTS (SELECT Name FROM @tvpNames tmp WHERE tmp.Name  = PER.FirstName)
	ORDER BY FirstName, LastName

END
GO

-- Now, create a Table Variable of type created above:
DECLARE @tblPersons AS tvpNamesList

INSERT INTO @tblPersons
SELECT Names FROM (VALUES ('Charles'), ('Jade'), ('Jim'), ('Luke'), ('Ken') ) AS T(Names)

-- Pass this table variable as parameter to the SP:
EXEC uspGetPersonDetailsTVP @tblPersons
GO
-- Check the output, objective achieved 🙂


-- Final Cleanup
DROP PROCEDURE uspGetPersonDetailsTVP
GO

So, we saw how we can use TVPs with Stored Procedures, similar to this they are used with UDFs.

TVPs are a great way to pass array of values as a single parameter to SPs and UDFs. There is lot of know and understand about TVP, their benefits and usage, check this [link].

Categories: SQL Server 2008, SQL Tips, Stored Procedures Tags: multiple values to Stored Procedures, Stored Procedure, Table Valued Parameters, TVP

OUTPUT clause and MERGE statement in SQL Server

November 25, 2010 1 comment

Just responded to a post in MSDN forum, link: http://social.msdn.microsoft.com/Forums/en-US/transactsql/thread/c06e1db4-7fd6-43c4-8569-5335d555dac8

Accroding to MS-BOL, OUTPUT clause returns information from, or expressions based on, each row affected by an INSERT, UPDATE, DELETE, or MERGE statement. These results can be returned to the processing application for use in such things as confirmation messages, archiving, and other such application requirements. The results can also be inserted into a table or table variable. Additionally, you can capture the results of an OUTPUT clause in a nested INSERT, UPDATE, DELETE, or MERGE statement, and insert those results into a target table or view.

Lets go with a self descriptive example:

–> OUTPUT with INSERT

create table manoj (sn int, ename varchar(50))
insert into manoj
OUTPUT INSERTED.*
values (1,'manoj'), (2,'hema'), (3,'kanchan'), (4,'pankaj')

This gives me following output instead of normal message (N row(s) affected):
sn      ename
1	manoj
2	hema
3	kanchan
4	pankaj

select * from manoj

This gives me the same output as above:
sn      ename
1	manoj
2	hema
3	kanchan
4	pankaj

–> OUTPUT with DELETE

delete from manoj
OUTPUT DELETED.*
where sn = 4

This gives me following output:
sn      ename
4	pankaj

select * from manoj

Now the result set is changed to:
sn      ename
1	manoj
2	hema
3	kanchan

–> OUTPUT with UPDATE

update manoj
set ename = 'pankaj'
OUTPUT DELETED.*, INSERTED.*
from manoj
where sn = 2

This gives me following output:
sn      ename   sn     ename
2	hema	2	pankaj

select * from manoj

Now the result set is changed to:
sn      ename
1	manoj
2	pankaj
3	kanchan

–> OUTPUT with MERGE

According to MS-BOL, MERGE performs insert, update, or delete operations on a target table based on the results of a join with a source table. For example, you can synchronize two tables by inserting, updating, or deleting rows in one table based on differences found in the other table.

create table manoj2 (sn int, ename varchar(50))

insert into manoj2
values (1,'manoj'), (2,'hema'), (3,'kanchan'), (4,'pankaj'), (5,'saurabh')

select * from manoj2

This gives me following output instead of normal message (N row(s) affected):
sn      ename
1	manoj
2	hema
3	kanchan
4	pankaj

MERGE manoj AS TARGET
USING (SELECT sn, ename FROM manoj2) AS SOURCE
ON (TARGET.sn = SOURCE.sn)
WHEN MATCHED THEN
	UPDATE SET TARGET.ename = SOURCE.ename
WHEN NOT MATCHED BY TARGET THEN
	INSERT (sn, ename) VALUES (sn, ename)
OUTPUT $action, DELETED.*, INSERTED.*;

This gives me following output:
$action sn      ename   sn      ename
INSERT	NULL	NULL	4	pankaj
INSERT	NULL	NULL	5	saurabh
UPDATE	1	manoj	1	manoj
UPDATE	2	pankaj	2	hema
UPDATE	3	kanchan	3	kanchan

select * from manoj

Now the result set is changed to:
sn      ename
1	manoj
2	hema
3	kanchan
4	pankaj
5	saurabh

–> Final cleanup

drop table manoj
drop table manoj2

Plz note: An UPDATE, INSERT, or DELETE statement that has an OUTPUT clause will return rows to the client even if the statement encounters errors and is rolled back. The result should not be used if any error occurs when you run the statement.

MS BOL:-

– On MERGE: http://technet.microsoft.com/en-us/library/bb510625.aspx

– On OUTPUT: http://technet.microsoft.com/en-us/library/ms177564.aspx

Categories: SQL Server 2008 Tags: $action, DELETED, INSERTED, MERGE, OUTPUT

CUBE, ROLLUP, COMPUTE, COMPUTE BY, GROUPING SETS

November 12, 2010 10 comments

The CUBE and ROLLUP operators are useful in generating reports that contain subtotals and totals. There are extensions of the GROUP BY clause.

–> Difference b/w CUBE and ROLLUP:

– CUBE generates a result set that shows aggregates for all combinations of values in the selected columns.

– ROLLUP generates a result set that shows aggregates for a hierarchy of values in the selected columns.

Let’s check this by a simple example:

select 'A' [class], 1 [rollno], 'a' [section], 80 [marks], 'manoj' stuName
into #tempTable
UNION
select 'A', 2, 'a', 70 ,'harish'
UNION
select 'A', 3, 'a', 80 ,'kanchan'
UNION
select 'A', 4, 'b', 90 ,'pooja'
UNION
select 'A', 5, 'b', 90 ,'saurabh'
UNION
select 'A', 6, 'b', 50 ,'anita'
UNION
select 'B', 1, 'a', 60 ,'nitin'
UNION
select 'B', 2, 'a', 50 ,'kamar'
UNION
select 'B', 3, 'a', 80 ,'dinesh'
UNION
select 'B', 4, 'b', 90 ,'paras'
UNION
select 'B', 5, 'b', 50 ,'lalit'
UNION
select 'B', 6, 'b', 70 ,'hema'

select class, rollno, section, marks, stuName 
from #tempTable

Output:
class	rollno	section	marks	stuName
A	1	a	80	manoj
A	2	a	70	harish
A	3	a	80	kanchan
A	4	b	90	pooja
A	5	b	90	saurabh
A	6	b	50	anita
B	1	a	60	nitin
B	2	a	50	kamar
B	3	a	80	dinesh
B	4	b	90	paras
B	5	b	50	lalit
B	6	b	70	hema

–> WITH ROLLUP:

select class, section, sum(marks) [sum]
from #tempTable
group by class, section with ROLLUP

Output:
class	section	sum
A	a	230
A	b	230
A	NULL	460  -- 230 + 230  = 460
B	a	190
B	b	210
B	NULL	400  -- 190 + 210 = 400
NULL	NULL	860  -- 460 + 400 = 860

–> WITH CUBE:

select class, section, sum(marks) [sum]
from #tempTable
group by class, section with CUBE

Output:
class	section	sum
A	a	230
A	b	230
A	NULL	460  -- 230 + 230  = 460
B	a	190
B	b	210
B	NULL	400  -- 190 + 210 = 400
NULL	NULL	860  -- 460 + 400 = 860
NULL	a	420  -- 230 + 190 = 420
NULL	b	440  -- 230 + 210 = 440

–> COMPUTE & COMPUTE BY: (this feature is no longer supported and discontinued with SQL Server 2012 and next versions)

A COMPUTE BY clause allows you to see both detail and summary rows with one SELECT statement. You can calculate summary values for subgroups, or a summary value for the whole result set.

The COMPUTE clause takes the following information:
– The optional BY keyword. This calculates the specified row aggregate on a per column basis.
– A row aggregate function name. This includes SUM, AVG, MIN, MAX, or COUNT.
– A column upon which to perform the row aggregate function.

select class, section, marks
from #tempTable
COMPUTE SUM(marks), AVG(marks)

select class, section, marks
from #tempTable
order by class
COMPUTE SUM(marks), AVG(marks) by class

select class, section, marks
from #tempTable
order by class, section
COMPUTE SUM(marks), AVG(marks) by class, section

Final Cleanup, drop the temp tables:

drop table #tempTable

–> GROUPING SETS:

SQL Server 2008 has a new GROUPING SETS operator which can generate the same result set as that generated by using a simple GROUP BY, ROLLUP, or CUBE operator.

–> Grouping Sets for SQL Server 2008 and above, check here.

–> Grouping Sets equivalent for SQL Server 2005 and below, check here.

>> Check & Subscribe my [YouTube videos] on SQL Server.

Categories: SQL Server 2008 Tags: COMPUTE, COMPUTE BY, CUBE, GROUPING SETS, ROLLUP

MS SQL SERVER 2008 – New Features | GROUPING SETS, ROLLUP, CUBE, Row Constructors, etc

August 7, 2009 3 comments

Compatibility Level:

ALTER DATABASE SET COMPATIBILITY_LEVEL replaces sp_dbcmptlevel for setting the database compatibility level.

ALTER DATABASE database_name
SET COMPATIBILITY_LEVEL = { 80 90 100 }

COMPATIBILITY_LEVEL Is the version of SQL Server with which the database is to be made compatible. The value must be one of the following:
80 = SQL Server 2000
90 = SQL Server 2005
100 = SQL Server 2008

Compound Operators:
Operators that perform an operation and set a variable to the result, for example SET @x += 2, are available.

DECLARE @x1 int = 27;
SET @x1 += 2;
SELECT @x1 AS Added_2;

CONVERT Function:
The CONVERT function is enhanced to allow conversions between binary and character hexadecimal values.
CAST ( expression AS data_type [ (length ) ])
CONVERT ( data_type [ ( length ) ] , expression [ , style ] )

Date and Time Functionality:
SQL Server 2008 introduces four new date and time data types:
1. DATE
2. TIME
3. DATETIME2
4. DATETIMEOFFSET

It also includes support for the ISO week-date system.

DATEPART ( datepart , date )

SELECT DATEPART (TZoffset, 2007-05-10 00:00:01.1234567 +05:10);
SELECT DATEPART(millisecond, '00:00:01.1234567'); -- Returns 123
SELECT DATEPART(microsecond, '00:00:01.1234567'); -- Returns 123456
SELECT DATEPART(nanosecond, '00:00:01.1234567'); -- Returns 123456700

GROUPING SETS:

The GROUPING SETS, ROLLUP, and CUBE operators are added to the GROUP BY clause. There is a new function, GROUPING_ID(), that returns more grouping-level information than the existing GROUPING() function. The non-ISO compliant WITH ROLLUP, WITH CUBE, and ALL syntax is deprecated.

For more info: http://msdn.microsoft.com/en-us/library/bb522495.aspx

MERGE Statement:

This new Transact-SQL statement performs INSERT, UPDATE, or DELETE operations on a target table based on the results of a join with a source table. The syntax allows you to join a data source with a target table or view, and then perform multiple actions based on the results of that join.

For moe info: http://msdn.microsoft.com/en-us/library/bb510625.aspx

SQL Dependency Reporting:

SQL Server 2008 introduces a new catalog view and system functions to provide consistent and reliable SQL dependency reporting. You can use sys.sql_expression_dependencies, sys.dm_sql_referencing_entities, and sys.dm_sql_referenced_entities to report on cross-server, cross-database, and database SQL dependencies for both schema-bound and non-schema-bound objects.

Table-Valued Parameters:

The Database Engine introduces a new parameter type that can reference user-defined table types. Table-valued parameters can send multiple rows of data to a SQL Server statement or routine (such as a stored procedure or function) without creating a temporary table.

For more info: http://msdn.microsoft.com/en-us/library/bb510489.aspx

Transact-SQL Row Constructors:

Transact-SQL is enhanced to allow multiple value inserts within a single INSERT statement.

INSERT INTO dbo.Customers(custid, companyname, phone, address)
VALUES
(1, 'cust 1', '(111) 111-1111', 'address 1'),
(2, 'cust 2', '(222) 222-2222', 'address 2'),
(3, 'cust 3', '(333) 333-3333', 'address 3'),
(4, 'cust 4', '(444) 444-4444', 'address 4'),
(5, 'cust 5', '(555) 555-5555', 'address 5');

--- AND ---

SELECT * FROM (VALUES
(1, 'cust 1', '(111) 111-1111', 'address 1'),
(2, 'cust 2', '(222) 222-2222', 'address 2'),
(3, 'cust 3', '(333) 333-3333', 'address 3'),
(4, 'cust 4', '(444) 444-4444', 'address 4'),
(5, 'cust 5', '(555) 555-5555', 'address 5')) AS C(custid, companyname, phone, address);

Source: MSDN

Categories: SQL Server 2008 Tags: Row Constructors