|
|
[ ABOUT THE AUTHOR ]
Introduction
Having identified three T-SQL enhancements in SQL Server 2008 in the previous article, this article will exploit the remaining features.
This article is based on the February CTP or CTP 6 of SQL Server 2008 and there might be some changes when Microsoft releases final RTM. (Microsoft has announced that CTP 6 is a feature completed version and will not add new features to it)
Grouping Set
WITH ROLLUP and CUBE operators are available with prior SQL Server 2008 versions. However, there are few modifications and few additional functionalities for these operators. However, previous syntax is also valid with the SQL Server 2008 hence you don’t have to modify your previously written T-SQL scripts if you have used WITH ROLLUP and CUBE operators.
First let us create a simple data tables.
CREATE TABLE TBL_GROUPING_SET
(GROUP1 INT,
GROUP2 VARCHAR(1),
VALUE INT)
GO
Then insert some records to the created tables.
INSERT INTO TBL_GROUPING_SET VALUES (1, 'A', 10)
INSERT INTO TBL_GROUPING_SET VALUES (1, 'A', 20)
INSERT INTO TBL_GROUPING_SET VALUES (1, 'A', 30)
INSERT INTO TBL_GROUPING_SET VALUES (1, 'B', 60)
INSERT INTO TBL_GROUPING_SET VALUES (2, 'A', 70)
INSERT INTO TBL_GROUPING_SET VALUES (2, 'A', 80)
INSERT INTO TBL_GROUPING_SET VALUES (3, 'C', 70)
INSERT INTO TBL_GROUPING_SET VALUES (3, 'C', 90)
GO
If you can remember the ROLLUP command in SQL Server 2005, it is like following.
SELECT GROUP1, GROUP2, SUM (VALUE) AS TOTAL_VAL
FROM TBL_GROUPING_SET
GROUP BY GROUP1, GROUP2 WITH ROLLUP
Results of this will looks like following.
While the above syntax is still valid, new syntax is introduced in SQL Server 2008.
SELECT GROUP1, GROUP2, SUM(VALUE) AS TOTAL_VAL
FROM TBL_GROUPING_SET
GROUP BY ROLLUP (GROUP1, GROUP2)
Above syntax will result nothing but the same result as the previous syntax. However, new syntax is much easier than the previous and as new syntax follows famous GROUP BY syntax.
Like ROLLUP, CUBE operator also has experienced the same change.
|
Previous CUBE Syntax |
SQL Server 2008 CUBE syntax |
|
SELECT GROUP1, GROUP2, SUM(VALUE) AS TOTAL_VAL
FROM TBL_GROUPING_SET
GROUP BY GROUP1, GROUP2 WITH CUBE |
SELECT GROUP1, GROUP2, SUM(VALUE) AS TOTAL_VAL
FROM TBL_GROUPING_SET
GROUP BY CUBE (GROUP1, GROUP2) |
Like ROLLUP, both the syntaxes will return same identical results but SQL Server 2008 Syntax is much clearer. Following is the results you will get from both of these queries.
If you have worked on CUBE and ROLLUP operators you know that there is a function called GROUPING to identify the grouped records. Nothing has changed in the function. However, for the sake of completeness I have listed the syntax and the results.
SELECT GROUP1, GROUP2, SUM(VALUE) AS TOTAL_VAL, GROUPING(GROUP1) AS GRP1_VALUE,GROUPING(GROUP2) AS GRP2_VALUE
FROM TBL_GROUPING_SET
GROUP BY ROLLUP (GROUP1, GROUP2)
Simply, GROUPING will return whether the given column is grouped for the given record. GRP2_VALUE = 1 indicates rows generated by ROLLUP operator.
GROUP_ID is a new function in SQL Server 2008 where you can get the level of aggregation.
SELECT GROUP1, GROUP2, SUM(VALUE) AS TOTAL_VAL, GROUPING(GROUP1) AS GRP1_VALUE,GROUPING(GROUP2) AS GRP2_VALUE, GROUPING_ID(GROUP1, GROUP2) AS GRP_ID_VALUE
FROM TBL_GROUPING_SET
GROUP BY ROLLUP (GROUP1, GROUP2)
GRP_ID_VALUE 3 indicates that it particular column is grouped by two columns.
Let us apply GROUP_ID for syntax with CUBE operator.
SELECT GROUP1, GROUP2, SUM(VALUE) AS TOTAL_VAL,GROUPING_ID(GROUP1, GROUP2) AS GRP_ID_VALUE
FROM TBL_GROUPING_SET
GROUP BY GROUP1, GROUP2 WITH CUBE
From the above result set you can see that GRP_ID_VALUE has got different values. 0 indicates non-rollup column, 1 indicates rollup by GROUP2 column, 2 indicates rollup by GROUP1 and 3 indicates rollup by both columns.
Next important addition in SQL Server 2008 T-SQL is Grouping Sets. The GROUPING SETS operator can generate the same result set as that generated by using a simple GROUP BY, ROLLUP, or CUBE operator. When all the groupings that are generated by using a full ROLLUP or CUBE operator are not required, you can use GROUPING SETS to specify only the groupings that you want. The GROUPING SETS list can contain duplicate groupings and when GROUPING SETS is used with ROLLUP and CUBE, it might generate duplicate groupings. Duplicate groupings are retained as they would be by using UNION ALL.
SELECT GROUP1, GROUP2, COUNT(*) AS CNT_RECORD, SUM(VALUE) TOTAL_VAL, AVG(VALUE* 1.0) AVG_VAL
FROM TBL_GROUPING_SET
GROUP BY GROUPING SETS
(
(GROUP1, GROUP2),
(GROUP1),
()
)
The result of the above script is shown in following image.
Basically Grouping set is collection of several Group by clauses. In SQL Server 2005, you have to use following T-SQL script to get the above results.
SELECT GROUP1,GROUP2, COUNT(*) AS CNT_RECORD, SUM(VALUE) TOTAL_VAL, AVG(VALUE* 1.0) AVG_VAL
FROM TBL_GROUPING_SET
GROUP BY GROUP1,GROUP2
UNION
SELECT GROUP1,NULL, COUNT(*) AS CNT_RECORD, SUM(VALUE) TOTAL_VAL, AVG(VALUE* 1.0) AVG_VAL
FROM TBL_GROUPING_SET
GROUP BY GROUP1
UNION
SELECT NULL,NULL, COUNT(*) AS CNT_RECORD, SUM(VALUE) TOTAL_VAL, AVG(VALUE* 1.0) AVG_VAL
FROM TBL_GROUPING_SET
You can see that how you can do the same thing with SQL Server 2008 GROUPINGSETS operator.
Force Seek
Most of the time you may have experienced that indexes in the table are not being used. ForceSeek command is to force query to use the indexes.
To demonstrate forceseek command I created simple table and inserted 1000,000 records from following scripts.
CREATE TABLE FORCE_SEEK_TABLE
(
COL1 INT IDENTITY NOT NULL,
COL2 TINYINT NOT NULL
)
GO
DECLARE @I INT
SET @I = 1
WHILE (@I <= 1000000)
BEGIN
IF (@I <=100000)
INSERT INTO FORCE_SEEK_TABLE (COL2) VALUES (10)
IF (@I > 100000 AND @I <= 1000000)
INSERT INTO FORCE_SEEK_TABLE (COL2) VALUES (90)
SET @I = @I + 1
END
GO
CREATE NONCLUSTERED INDEX FORCE_SEEK_TABLE ON FORCE_SEEK_TABLE (COL2)
From the above script, there will be 100000 records of COL2 = 10 and 900000 records of COL2 = 90
Let us query records whose COL2 = 10.
SELECT * FROM FORCE_SEEK_TABLE WHERE COL2 = 10
From the above execution plan you can see that it is using table scan. Table scan is inefficient as there are less number of records where COL2 = 10.
Now we will see what will happen with using forceseek command.
Instead of table scan you can see that it is using index seek as we force script to use index. However, this does not say that forceseek always gives you much performance. So you need to verify according to your query.
Conclusion
Even though most of these T-SQL enhancements are helpful, from the blogs and forums it is understood that merge and forceseek are mostly used commands. Due to the limited features in table parameters are not used much. GROUPING SETS are also not much used, mainly because there are workarounds to it.
We have discussed three features of new T-SQL functionalities in SQL Server 2008 namely Merge, Table Value Parameters and Row constructors. We will discuss further two T-SQL functions in the next article.
References
[ RATE this ARTICLE, and COMMENT on it ] [ ABOUT THE AUTHOR ]
|