Grouping Set (SQL Server 2008)

People cannot help appreciating GROUP BY clause whenever they have to get a DISTINCT from any result set. Additionally whenever any aggregate function is required GROUP BY clause is the only solution. There has always been requirement get these aggregate function based on different set of columns in the same result set. It is also safe to use this feature as this is an ISO standard.

Though the same result could be achieved earlier but we have to write different queries and would have to combine them using UNION operator. The result set returned by GROUPING SET is the union of the aggregates based on the columns specified in each set in the Grouping set.

To understand it completely first we create a table tbl_Employee.



Now we populate table with some the following rows:



After populating with the rows, we select some rows using Grouping Sets.



The result of this statement is as follows:



You can see that the result set contains rows grouping by each set in the specified Grouping Sets. You can see the average salary of employees for each region and department. You can also appreciate the average salary of employees for the organization (NULL for both Region and Department). This was the result of empty Grouping Set i.e. ().

Before 2008, if you had to get the same result set, following query had to be written:



By looking at the above query, you can appreciate the ease provided by Grouping Set to the developer.

CUBE Sub Clause for Grouping
This is used to return power ‘n’ to 2 for ‘n’ elements.



The above query is equivalent to the following query:



ROLLUP operator for Grouping
This is used to return ‘n+1’ grouping sets for ‘n’ elements in the hierarchy scenario.



This is equivalent to the following query:

Change Data Capture (SQL Server 2008)

You can view my article about Change Data Capture on Code Project.

Click: Change Data Capture

Batch Update ADO .net OPENXML

I wanted to use the Batch Update in one of my project and I explored the following information. This might be of interest to some of you.

I thought that using the Batch update option in .net 2.0 command, I can go with this. This is achieved using the rows in a dataset which are updated using a DataAdapter object. In reality one has to set the UpdateBatch size property to a specific value. But this is not the same. When i started the profiler, the trace showed that each statements are executed individually instead a single Update / Insert statement. When i studied the documentation, it became clear that Microsoft never claimed that a single statement would be executed. It says that a batch of statements are passed to SQL Server instead of individual statement. You can study the following article for further details:

http://msdn.microsoft.com/en-us/library/kbbwt18a(VS.80).aspx

But those who are like me and want that a single Update/ Insert statement is executed instead, have an alternate option. This is through the use of OPENXML feature of SQL Server. All the rows are provided to a stored procedure as a single parameter of XML. The parameter datatype is generally set as NText. For further details, please read the following:

http://en.csharp-online.net/XML_and_ADO.NET%E2%80%94OPENXML

After using this option, when you start your profiler trace, you should be satisfied that there is only a single Update statement being executed.

Are you happy now??