Grouping Sets in SQL Server 2008

SQL Server 2008 introduces a new feature called GROUPING SETS for SQL Server Database Developers. When a GROUP BY clause is used with the GROUPING SETS feature in SQL Server 2008 it will help you generate a result set which will be equivalent to that generated by a UNION ALL of multiple simple transact SQL Group By statements. A Grouping Sets statement will generate a result set which is equivalent to a result set generated by the use of Group By, Rollup or Cube operations. It is easier to write transact SQL statements using the Grouping Sets clause, as it avoids the overhead of writing many queries and then using UNION ALL to get the desired results. In this article you will be see how to use the new Grouping Sets feature introduced in SQL Server 2008.

Advantages of Using Grouping Sets Clause

* The Grouping Sets feature is really helpful when you want to generate a set of aggregate results and at the same time you want to group by varying columns
* It is much easier to maintain and provides better performance when compared to running different queries against the same data and then finally performing a UNION ALL to get the desired results
* It provides better performance as it is executes once against the data source
* It is much easier to program and use Grouping Sets than writing multiple
select statements

Let us assume that as per business requirement you have been asked to find the count of Cricket Teams based on the following criteria such as CricketTeamShortCode or CricketTeamCountry or CricketTeamContinent. In order to get the desired results in the previous versions of SQL Server you will end up writing as many select statements with group by clause as shown below.

a) Select statement to find the count of CricketTeams by CricketTeamShortCode among the cricket playing nations:

USE GroupingSetsDemo
GO
SELECT CricketTeamShortCode, COUNT(*) FROM CricketTeams
GROUP BY CricketTeamShortCode
GO

b) Select statement to find the count of CricketTeams by CricketTeamCountry among the cricket playing nations:

USE GroupingSetsDemo
GO
SELECT CricketTeamCountry, COUNT(*) FROM CricketTeams
GROUP BY CricketTeamCountry
GO

c) Select statement to find the count of CricketTeams by Continent among the cricket playing nations:

USE GroupingSetsDemo
GO
SELECT CricketTeamContinent, COUNT(*) FROM CricketTeams
GROUP BY CricketTeamContinent
GO

Next you need to run all the queries using the UNION ALL operator to get the desired results. The only drawback with this approach is that you end up writing many select statements. However, it is not very easy to write a statement without many select statements when you have many conditions, especially when the table has many columns. Moreover there will be a performance hit as you will end up running multiple select queries against the same data source. The following query when executed will provide you all the results as desired:

Use GroupingSetsDemo
GO

SELECT CricketTeamContinent, COUNT(*) FROM CricketTeams
GROUP BY CricketTeamContinent

UNION ALL

SELECT CricketTeamCountry, COUNT(*) FROM CricketTeams
GROUP BY CricketTeamCountry

UNION ALL

SELECT CricketTeamShortCode, COUNT(*) FROM CricketTeams
GROUP BY CricketTeamShortCode
GO




Using Grouping Sets Clause Introduced in SQL Server 2008
Now let us see how we can write a select statement using the Grouping Sets clause which was introduced in SQL Server 2008. The result set which we got by running multiple select statements with UNION ALL operator can be obtained by just executing the below mentioned piece of TSQL code:

Use GroupingSetsDemo
GO

/* Using the Grouping Sets clause introduced in SQL Server 2008 */
SELECT CricketTeamShortCode, CricketTeamCountry, CricketTeamContinent, Count(*) AS Count
FROM CricketTeams
GROUP BY GROUPING SETS (
(CricketTeamShortCode),
(CricketTeamCountry),
(CricketTeamContinent),
()
)
ORDER BY CricketTeamShortCode, CricketTeamCountry, CricketTeamContinent
GO

You could see that the syntax is much similar to the GROUP BY clause which was available in the previous versions of SQL Server. In the below snippet you can see the result set which we have obtained by executing the above TSQL code which is using the GROUPING SETS feature:


You can see in the above snippet that by using the Grouping Sets clause we have got the same result set which in previous versions of SQL Server to get similar result set you end up writing multiple queries. Using the Grouping Sets we get the same result set we got by just executing a single TSQL query which makes this feature an excellent choice for developers when developing application on SQL Server 2008.

Conclusion
The Grouping Sets feature is an enhancement to the existing Group By clause of SQL Server. The Grouping Sets feature allows database developers to merge multiple Group By transact SQL queries into a single query. This feature is very useful when you need to collect summary data for different criteria’s as per business requirements. With the introduction of Grouping Sets clause in SQL Server 2008 you don’t have to write multiple select queries to collect summary data. Developers can use this new enhancement if your application has many Group By queries. Using this feature will make your queries much simpler to write and it will also improve the performance of your queries as they are running against the data source once.

Using Views to Enforce Business Rules

A view is most commonly thought of as a SELECT statement. Most developers will simply create a view to "group" complex SELECT statements for reuse within another view or stored procedures. It makes typing easier! But the really power of views is their ability to implement business rules.

To fully understand how this is possible, we must go back to basics. A view in the relational model has as much standing as a relation (table). This means it can be treated in EXACTLY the same way. You can insert, update and delete data from a view, add or remove columns and most importantly add constraints to the view.

The relational model has four types of constraints that can be used to implement business models and rules.

1. Domain/Type (Attribute/column)
2. Tuple (Row)
3. Relation (Table)
4. Database (Multiple tables)

SQL Server handles the first 3 fairly well but is limited to only one type of Database constraint: The "Foreign Key".

While extremely useful, the foreign key is the simplest form of database constraint. Practically, most business models will need much more complexity than a simple Parent-Child relationship. This is where views can be used.

Let's use an example where a business has clients that generate invoices. Each invoice belongs to a particular client. The business categorizes their clients based on spending limits. They want to restrict the total of each invoice to ensure that certain clients do not exceed a limit.

This gives us 3 tables:

* Client
* SpendingType
* ClientInvoice

create table SpendingType(
SpendingType varchar(25) NOT NULL primary key,
InvoiceLimit money not null)
go
create table Client(
ClientID int not null primary key,
ClientName varchar(50)
, SpendingType varchar(25) not null references SpendingType (SpendingType))
go
create table ClientInvoice(
InvoiceID int not null,
ClientID int not null,
TotalInvoice money not null
, primary key (InvoiceID, ClientID),
foreign key (ClientID) References Client (ClientID))
go

This yields us this entity-relationship diagram (ERD):

Given this ERD, we can see there is nothing to enforce our "Spending Limit" rule.

Enter the view...

create view ClientInvoice_SpendingConstraint
as
select InvoiceID,
ClientID,
TotalInvoice
from dbo.ClientInvoice CI
where exists
(Select 1
from dbo.Client C
inner join dbo.SpendingType ST on C.SpendingType = ST.SpendingType
where C.ClientID = CI.ClientID
and TotalInvoice <= ST.InvoiceLimit)
with check option

Notice the "with check option". This tells SQL Server to enforce the constraints defined by the view. There are several limitations to creating update-able views which practically can be summarised into 2 golden rules.

1. Express the entire table. Declare all columns in the underlying table in the view definition.
2. Don't touch yourself. Never reference the primary table (in our example the ClientInvoice table) in the constraint (WHERE).

When this view is presented to the user, any INSERT or UPDATE into this view must satisfy our rule. Failure to do so will result in an exception being thrown by SQL Server. Because the constraint is expressed as a set, the view can handle multiple row insert and updates effortlessly thus ensuring the ACID principle. Watch the execution plan for the successful insert/update to see how efficiently SQL Server processes the rule.

We can test the view using these statements:

insert SpendingType values ('Standard', 1000)
insert SpendingType values ('Premium', 5000)
insert Client values (1, 'David', 'Standard')
insert Client values (2, 'Peter', 'Premium')
go

--David is under 1000
insert ClientInvoice_SpendingConstraint values (1,1,600)
/* Result: (1 row(s) affected) */

--David is over 1000 (bad)
insert ClientInvoice_SpendingConstraint values (2,1,1600)

/* Result: The attempted insert or update failed because the target view either
specifies WITH CHECK OPTION or spans a view that specifies WITH CHECK OPTION and
one or more rows resulting from the operation did not qualify under the CHECK OPTION constraint. */


--David is 1000 (good)
insert ClientInvoice_SpendingConstraint values (2,1,1000)
/* Result: (1 row(s) affected) */
Go

-- Update that violates the rule.
update ClientInvoice_SpendingConstraint set TotalInvoice = 1001 where InvoiceID = 2

/* Result: The attempted insert or update failed because the target view either
specifies WITH CHECK OPTION or spans a view that specifies WITH CHECK OPTION and
one or more rows resulting from the operation did not qualify under the CHECK OPTION constraint. */

go
--Peter is under 5000
insert ClientInvoice_SpendingConstraint values (3,2,2600)
/* Result: (1 row(s) affected) */
go
Select * from ClientInvoice
Go

Most developers would choose a stored procedure or a trigger to implement this rule. But consider the advantages using the view gives:

1. Set based and Optimised. The view is compiled and BCP and BULK INSERT friendly.
2. Abstraction. The view provides the possibility to change business rules very quickly with minimal physical impact.
3. Tool friendly. Extracting view metadata is a very common feature.

The only downside I see is the error that SQL Server throws. The error message is ugly without any detailed information.

Using BULK INSERT to Load a Text File

his example combines dynamic SQL, BULK INSERT and the proper handling of double-quotes to solve a client's problem with loading various text file formats into a database. (This article has been updated through SQL Server 2005.)

One of my clients contacted me recently and said they needed some help creating a stored procedure that imported data from a text file. They wanted the procedure to accept three parameters: PathFileName, OrderID, and FileType. The PathFileName is simply the name and physical location of the source file on the hard drive, the OrderID is generated in the program that calls the procedure and the FileType indicates the format of the data in the source file. The two possible formats for the source data are shown here:

FileType=1 (TxtFile1.txt)

"Kelly","Reynold","kelly@reynold.com"
"John","Smith","bill@smith.com"
"Sara","Parker","sara@parker.com"

FileType=2 (TxtFile2.txt)

Kelly,Reynold,kelly@reynold.com
John,Smith,bill@smith.com
Sara,Parker,sara@parker.com

BULK INSERT

I decided to use BULK INSERT to implement the solution. The BULK INSERT statement was introduced in SQL Server 7 and allows you to interact with bcp (bulk copy program) via a script. In pre-7 versions the only way you could access bcp functionality was from a command prompt. I am not going to list the full syntax of BULK INSERT here (but you can find it here), because it is a little long and most of it does not apply to the problem I am solving. Instead, I will show the valid BULK INSERT statements used to load the data shown above.

BULK INSERT TmpStList FROM 'c:\TxtFile1.txt' WITH (FIELDTERMINATOR = '","')

TmpStList is the target table and TxtFile1.txt is the source data file. The source file is located in the root of the C drive. The FIELDTERMINATOR argument allows you to specify the delimeter used to discern column values.

The valid statement for FileType=2 is shown here:

BULK INSERT tmpStList FROM 'c:\TxtFile2.txt' WITH (FIELDTERMINATOR = ',')

The only difference is the value of the FIELDTERMINATOR argument.
The Solution

The stored procedure used to implement the solution is fairly straight forward once you master the BULK INSERT statement. The only real trick is loading the data that comes to you in FileType=1 format. Because a double-quote starts and ends a data row, it too is loaded in the table. The FIELDTERMINATOR works between columns, not at the beginning or end of a row. To workaround this I simply load the data into a temporary table and then use a CASE statement and the SUBSTRING and DATALENGTH functions to load the correct data in the final table. The FileType=2 data will load as-is, but I still put in the temporary table for consistency (easier programming).

The SQL statements that create the temporary and final table are shown here.

CREATE TABLE StudentList
(
StID int IDENTITY NOT NULL,
StFName varchar(50) NOT NULL,
StLName varchar(50) NOT NULL,
StEmail varchar(100) NOT NULL,
OrderID int NOT NULL
)
go
CREATE TABLE TmpStList
(
stFName varchar (50) NOT NULL,
stLName varchar (50) NOT NULL,
stEmail varchar (100) NOT NULL
)
go

The procedure used to implement the data loading is shown here.

SET QUOTED_IDENTIFIER OFF
go
CREATE PROCEDURE ps_StudentList_Import
@PathFileName varchar(100),
@OrderID integer,
@FileType tinyint
AS

--Step 1: Build Valid BULK INSERT Statement
DECLARE @SQL varchar(2000)
IF @FileType = 1
BEGIN
-- Valid format: "John","Smith","john@smith.com"
SET @SQL = "BULK INSERT TmpStList FROM '"+@PathFileName+"' WITH (FIELDTERMINATOR = '"",""') "
END
ELSE
BEGIN
-- Valid format: John,Smith,john@smith.com
SET @SQL = "BULK INSERT TmpStList FROM '"+@PathFileName+"' WITH (FIELDTERMINATOR = ',') "
END

--Step 2: Execute BULK INSERT statement
EXEC (@SQL)

--Step 3: INSERT data into final table
INSERT StudentList (StFName,StLName,StEmail,OrderID)
SELECT CASE WHEN @FileType = 1 THEN SUBSTRING(StFName,2,DATALENGTH(StFName)-1)
ELSE StFName
END,
SUBSTRING(StLName,1,DATALENGTH(StLName)-0),
CASE WHEN @FileType = 1 THEN SUBSTRING(StEmail,1,DATALENGTH(StEmail)-1)
ELSE StEmail
END,
@OrderID
FROM tmpStList

--Step 4: Empty temporary table
TRUNCATE TABLE TmpStList
go

The first thing you need to know is that SET QUOTED_IDENTIFIER is set to OFF because double-quotes are used to set the value of a variable. Dynamic SQL is used to create the BULK INSERT statement on-the-fly, and double-quotes are required to do this. The final BULK INSERT statement is a function of both the @PathFileName and @FileType parameters. Once built, it is executed with the EXEC() statement and the source data is loaded into the temporary table.

Once the data is in TmpStList, the next step is to load it into the final table. I use the CASE statement to determine the value in the @FileType parameter and manipulate accordingly. When @FileType=1, the SUBSTRING and DATALENGTH functions are used to remove the double-quotes from the StFName and StEmail columns. When FileType=2, the data is loaded as is and no manipulation is required.

After the data is loaded I empty the temporary table with the TRUNCATE TABLE statement. I could have used DELETE to accomplish this, but TRUNCATE TABLE has less of an impact on the transaction log.

The following shows the way to call the procedure specifying a different FileType value for each call.

EXEC ps_StudentList_Import 'c:\TxtFile1.txt',1, 1
EXEC ps_StudentList_Import 'c:\TxtFile2.txt',1, 2

Tips for tuning SQL Server 2005 to improve reporting performance

There are a few things you can do to configure SQL Server for improved reporting performance. The first two are generic, and the rest pertain to SQL Server Reporting Services:

1. Create plenty of indexes to support your queries. In OLTP systems, we need to be more careful, since updating rows also requires updating all indexes. Therefore, we usually try to keep a balance with the number of indexes we put in place. But since these are reporting tables, it is highly desirable to create indexes to support your report queries; otherwise, the performance might be far from desirable.

2. Create a few "decision support" tables and populate them with data aggregated from the big tables. I often see reports processing millions of rows and then showing just a few rows with a high level summary of data grouped by a specific criteria. In many cases, you can just run that query once (or maybe daily or weekly) and store the results in small aggregate tables. In your reports, you can then use these small tables, and the execution time is much faster. If your reports use aggregations, give this technique serious consideration.

3. Reporting Services supports report caching. How does it work? When you configure a report to use caching (go to Report Properties and click on Execution), Reporting Services only executes the query once. After that, it caches the rendered report for the length of time you specified for caching. On any subsequent report requests, the report is served from the cached copy. Reporting Services creates a cached copy for each unique combination of parameters, so typically, the first person to run that combination of parameters has to wait longer. Any future requests are then returned much faster, and without going to the data source. This can be a huge time saver for reports with queries that take several minutes to execute.

4. Similarly, you can configure a report to be cached and rendered from a snapshot. You can think of it as a pre-processed report. Snapshots can be scheduled to be created off hours, and then the report would be served from the snapshot. This is suitable for reports with no parameters, long execution time, and when your report does not need real-time data.

Super Sizing Columns in SQL Server

SQL Server 2005, columns can also be Super Sized due to the introduction of the MAX Specifier. In previous versions of SQL Server, if an application allowed for the storage of string data that would exceed 8000 bytes, the only option available was to use the TEXT or NTEXT data type. By using either one of these data types, common operators were unable to be used, meaning that tasks such as searching and updating data was a complex process. With the introduction of the MAX Specifier it is now possible to work with large objects in SQL Server in ways that were previously not possible.

With the introduction of the MAX Specifier there is no longer a need to perform the complex manipulation of large objects that requires the use of the TEXTPTR operator to determine a the pointer to the value before using a set of specialized commands. The following examples show the complexity involved with working with the TEXT and NTEXT data types as a result of having to use a different set of operators. The example below illustrates how to find the first 10 characters for the pr_info column in the pub_info table:

DECLARE @ptrval varbinary(16);

SELECT @ptrval = TEXTPTR(pr_info)
FROM pubs.dbo.pub_info
WHERE pub_id = '0736'

READTEXT pub_info.pr_info @ptrval 0 10;

Whereas the following example illustrates how to update the first 10 characters for the pr_info column which is a TEXT data type:

DECLARE @ptrval binary(16)

SELECT @ptrval = TEXTPTR(pr_info)
FROM pubs.dbo.pub_info
WHERE pub_id = '0736'

UPDATETEXT pub_info.pr_info @ptrval 0 4 'This'

The introduction of the MAX Specifier in SQL Server 2005 provides the ability for variable length columns that previously were limited to 8000 bytes to store large amounts of data. The introduction also means that the NTEXT, TEXT and IMAGE data types are candidates for not being supported in future versions of SQL Server. Hence, it is recommended that the MAX Specifier is used for the storage of large amounts of data.

The MAX Specifier increases the maximum storage capabilities of the VARCHAR and VARINARY data types up to 2^31-1 bytes and up to 2^30-1 bytes for NVARCHAR. VARCHAR(MAX), NVARCHAR(MAX) and VARBINARY(MAX) are collectively called large-value data types. The MAX Specifier is in effect, a very large variable length column. Although the maximum size of the MAX specifier is approximately 2GB, the size is actually the maximum size that SQL Server supports. This means that in future versions of the product if the maximum size supported increases, the MAX specifier will automatically be able to support the size increase without any modifications. To use the MAX Specifier the word MAX is used in place of a size when you define a column or variable. In the following example the LargeColumn in the LargeDataType table is created using the VARCHAR data type and the MAX Specifier as the size.

CREATE TABLE dbo.LargeDataType
(
LargeColumn VARCHAR(MAX)
)

There are two ways in SQL Server that columns that are defined with the MAX Specifier may be stored. They can be stored either in a page with the other columns in a row or alternatively off-page. When the MAX Specifier is used for a column, SQL Server uses its own algorithms to determine whether to keep the value in line within the physical record or store the value externally to the base record and to keep track of the value by using a pointer. SQL Server will store the data as either a VARCHAR, NVARCHAR, VARBINARY or as a Large Object (LOB). If the length of the column is less than or equal to 8000 bytes, SQL Server will store the data in-page and where it is greater than 8000 bytes SQL Server will check the row size to determine the appropriate storage. If the row size is less than the size of a page (8060 bytes), the data values will be stored as in-row data whereas if the row size is greater than 8060 bytes, the data values are stored as LOB data with only a 16 byte pointer stored in the row. It is possible to override this default behaviour by using the new table option called large value type out of row so that columns defined with the MAX Specifier are always stored as a LOB. The following example illustrates how this option can be enabled by using the system Stored Procedure sp_tabeloption.

EXEC sp_tableoption
'dbo.LargeDataType', 'large value types out of row', 1

When the option is set to 1 (enabled) the data in columns that have been defined with the MAX specifier will always be stored out of row as a LOB with only a 16-byte text pointer stored in the row. Text pointers point to the root node of a tree built of internal pointers that map to the pages in which string fragments are actually stored.

The advantage of storing large-value data types in-row with the other columns in a table is that SQL Server can return a single row with only one I/O operation. If the bulk of SQL Server statements do not return large-value data type columns then the data should be stored out of row. This allows for a greater number of rows to be stored on a data page allowing a greater number of rows to be returned for each I/O operation.

Unlike TEXT and NTEXT data types that store the data off page, it does not matter where the data is stored for columns defined with the MAX Specifier. As no matter where the data is stored the column can be treated as a standard variable length data type. Hence, different operators do not need to be used and all of the standard operators can be used. This means that in SQL Server, there is now a unified programming model for working with regular types and large objects. The restrictions that previously existed for the use of TEXT and NTEXT as variables in Stored Procedures and Functions also no longer exists with large-value data types.

The following example illustrates just a few of the ways that standard operators can now be used with large-value data types.

String concatenation can now be used with large data types. The thing to note with this example is the use of the REPLICATE function. The REPLCIATE function returns a character expression of the same type as the supplied character expression. So if the supplied expressions is not CAST as a large data type the MAXIMUM length of the expression returned would be 8000 characters.

INSERT INTO dbo.LargeDataType(LargeColumn)
SELECT 'There is lots of data in this row ' +
REPLICATE(CAST('x' AS VARCHAR(MAX)), 100000)

Updates can be made directly to large data types without the need to use the UPDATETEXT operator:

UPDATE dbo.LargeDataType
SET LargeColumn = REPLACE(LargeColumn, 'lots', 'lots and lots')

Standard string operators such as SUBSTRING can now be used with large data types

SELECT SUBSTRING(LargeColumn, 10, 4)
FROM dbo.LargeDataType

The support for large data types is a valuable new addition to SQL Server. So if you are designing a new application that needs to store vales that are greater than 8000 bytes you should use the new large-value data types. As not only will the use of large-value data types assist by providing a unified programming model, it will also ensure that your application can take advantage of additional storage in future versions of SQL Server, allowing you to really Super Size your columns.

Performance Tuning Tips for SQL Server Backup and Restore

If you suspect that your backup or restore operations to disk are running at sub-optimal speeds, you can help verify this by using one or more of the following Performance Monitor counters to measure I/O activity during a backup or restore:

* SQL Server Backup Device Object: Device Throughput Bytes/sec: This counter measures how much data is being backed up or restored. While there is no absolute value this counter should show, it should give you an idea of how fast your backups or restores are occurring. If this value appears to be small in relation to how fast you think your I/O system is, then perhaps there is some bottleneck preventing your backups or restores from occurring faster.

* Physical Disk: % Disk Time: As a rule of thumb, the % Disk Time counter should run less than 55%. If this counter exceeds 90% for continuous periods when performing backups or restores (over 10 minutes or so) then your SQL Server may be experiencing an I/O bottleneck. If you suspect a physical disk bottleneck, you may also want to monitor the % Disk Read Time counter and the % Disk Write Time counter in order to help determine if the I/O bottleneck is being mostly caused by reads or writes.

* Physical Disk Object: Avg. Disk Queue Length: If the Avg. Disk Queue Length exceeds 2 for continuous periods when performing backups or restores (over 10 minutes or so) for each disk drive in an array, then you probably have an I/O bottleneck for that array. You will need to calculate this figure because Performance Monitor does not know how many physical drives are in arrays.

If you find that you do have an I/O bottleneck during backups or restores, your options to correct this include increasing the speed of your disk I/O system, reducing the load on your current system by performing backups or restores on less busy times, or backing up to a local tape device or over the network (assuming you are not doing that now). [6.5, 7.0, 2000, 2005]