Table of Contents

Page 1 of 44 SQL Server 2008 All Blog Posts till 04 30 2008 Page 2 of 44 Copyright Decipher Information Systems 2008 All rights reserved Last revised Jan 2008 Page 3 of 44 Table of Contents Table of Contents 3 Server Consolidation things to look for in SQL Server 2008 4 GROUP BY and CUBE ROLLUP GROUPING and GROUPING ID functions 4 GROUPING SETS in SQL Server 2008 8 SQL Server 2008 Table Valued Parameters 10 New Data types in SQL Server 2008 I 12 Row Value Constructor Support in SQL Server 2008 12 New Features in SQL Server 2008 I 13 MSFT learning portal for SQL Server 2008 14 Intellisense in SQL Server 2008 14 SQL Server 2008 INSERT over a DML statement 16 MERGE command in SQL Server 2008 18 Change Data Capture Feature in SQL Server 2008 21 Data Collector in SQL Server 2008 26 Compound Assignment Operators in SQL Server 2008 29 Back Up Compression Option in SQL Server 2008 31 FORCESEEK Table Hint in SQL Server 2008 33 HierarchyID Data Type in SQL Server 2008 36 FILESTREAM Data in SQL Server 2008 39 XML DML Enhancement in SQL Server 2008 41 Lock Escalation Changes in SQL Server 2008 42 SQL Server 2008 Deployment Guides from Dell 42 Filtered Indexes and Statistics in SQL Server 2008 42 Virtual Labs 43 Page 4 of 44 Server Consolidation things to look for in SQL Server 2008 We had talked about virtualization in the database world in a previous post In SQL Server 2008 there are a lot more features available for doing server consolidation here is a link to the MSFT whitepaper that breaks this down into the sections of flexibility manageability and scalability CREATE TABLE PRODUCT SALE PRODUCT NAME NVARCHAR 100 NOT NULL SALE QTY INT NOT NULL INSERT INTO PRODUCT CATALOG PRODUCT NAME QTY VALUES TOY1 100 INSERT INTO PRODUCT CATALOG PRODUCT NAME QTY VALUES TOY2 50 INSERT INTO PRODUCT SALE PRODUCT NAME SALE QTY VALUES TOY1 100 INSERT INTO PRODUCT SALE PRODUCT NAME SALE QTY VALUES TOY2 10 INSERT INTO PRODUCT SALE PRODUCT NAME SALE QTY VALUES TOY3 500 GO If you read that post you will see that we had used the MERGE command to do the DML Delete Insert Update logic on the PRODUCT CATALOG table This was the first example of the MERGE command that we had used in that post MERGE PRODUCT CATALOG PC target table USING PRODUCT SALE PS source table ON PC PRODUCT NAME PS PRODUCT NAME WHEN MATCHED AND Qty SALE QTY 0 THEN DELETE WHEN MATCHED THEN update stock if you still hold some stock UPDATE SET Qty Qty SALE QTY WHEN NOT MATCHED THEN insert a row if the stock is newly acquired INSERT VALUES PRODUCT NAME SALE QTY output details of INSERT UPDATE DELETE operations made on the target table OUTPUT action inserted PRODUCT NAME inserted QTY deleted PRODUCT NAME deleted QTY Page 17 of 44 Now suppose that you want the output of this to be put into a separate audit or processing table but only when the update is done or when a certain other condition is met It is possible to be able to now to a select atop the MERGE command We will first need to create a table for storing that data CREATE TABLE TEST INSERT OVER DML ACTION CODE NVARCHAR 50 I PRODUCT NAME NVARCHAR 100 I QTY INT D PRODUCT NAME NVARCHAR 100 D QTY INT GO And now the insert over DML statement INSERT INTO TEST INSERT OVER DML ACTION CODE I PRODUCT NAME I QTY D PRODUCT NAME D QTY select Action Code Inserted Product Name Inserted QTY Deleted Product Name Deleted Qty from MERGE PRODUCT CATALOG PC target table USING PRODUCT SALE PS source table ON PC PRODUCT NAME PS PRODUCT NAME WHEN MATCHED AND Qty SALE QTY 0 THEN DELETE WHEN MATCHED THEN update QTY if you still hold some QTY for that PRODUCT UPDATE SET Qty Qty SALE QTY WHEN NOT MATCHED THEN insert a row if the PRODUCT is newly acquired INSERT VALUES PRODUCT NAME SALE QTY output details of INSERT UPDATE DELETE operations made on the target table OUTPUT action inserted PRODUCT NAME inserted QTY deleted PRODUCT NAME deleted QTY Change Action Code Inserted Product Name Inserted QTY Deleted Product Name Deleted Qty And after executing it let s take a look at the data in the PRODUCT CATALOG and the TEST INSERT OVER DML tables PRODUCT CATALOG PRODUCT NAME QTY TOY2 40 TOY3 500 Page 18 of 44 TEST INSERT OVER DML ACTION CODE I PRODUCT NAME I QTY D PRODUCT NAME D QTY INSERT TOY3 500 NULL NULL DELETE NULL NULL TOY1 100 UPDATE TOY2 40 TOY2 50 As you can see from above the action history log has been captured and the DELETE INSERT and UPDATE actions took place on the PRODUCT CATALOG table since the product TOY1 got deleted the QTY for TOY2 got decremented by 10 and TOY3 record got added This new feature will help a lot in data warehousing ETLM operations and also for preparing audit logs for DML operations though CDC Change Data Capture would be a better option for auditing MERGE command in SQL Server 2008 MERGE command also known as UPSERT in Oracle circles is nothing new to Oracle It has existed since quite some time We have also blogged about it on our site including the enhancements to that command that were made in Oracle 10g It gets introduced in SQL Server 2008 as well which is a welcome addition for all the data warehousing data mining community as well as those writing complicated feeds to their OLTP systems This command is way more powerful than just thinking of it as an UPDATE DELETE INSERT combined into one single statement Let s set up two tables and then start going through the features of this command to see how one can utilize it We are setting up two tables One for the Product Catalog and the other one for the sales of the products SET NOCOUNT ON GO CREATE TABLE PRODUCT CATALOG PRODUCT NAME NVARCHAR 100 NOT NULL QTY INT NOT NULL CHECK QTY 0 CREATE TABLE PRODUCT SALE PRODUCT NAME NVARCHAR 100 NOT NULL SALE QTY INT NOT NULL INSERT INTO PRODUCT CATALOG PRODUCT NAME QTY VALUES TOY1 100 INSERT INTO PRODUCT CATALOG PRODUCT NAME QTY VALUES TOY2 50 INSERT INTO PRODUCT SALE PRODUCT NAME SALE QTY VALUES TOY1 100 INSERT INTO PRODUCT SALE PRODUCT NAME SALE QTY VALUES TOY2 10 INSERT INTO PRODUCT SALE PRODUCT NAME SALE QTY VALUES TOY3 Page 19 of 44 500 GO Apply changes to the PRODUCT CATALOG table based on daily sales that are tracked in the incoming Product Sale table from different stores Delete the record from the PRODUCT CATALOG table if all the items for that Product Name have been sold Update and decrement the quantity if quantity after the sale is not 0 and Insert a new record if there was a new item introduced at one of the regional stores MERGE PRODUCT CATALOG PC target table USING PRODUCT SALE PS source table ON PC PRODUCT NAME PS PRODUCT NAME WHEN MATCHED AND Qty SALE QTY 0 THEN DELETE WHEN MATCHED THEN update QTY if you still hold some QTY for that product UPDATE SET Qty Qty SALE QTY WHEN NOT MATCHED THEN insert a row if the PRODUCT is newly acquired INSERT VALUES PRODUCT NAME SALE QTY output details of INSERT UPDATE DELETE operations made on the target table OUTPUT action inserted PRODUCT NAME inserted QTY deleted PRODUCT NAME deleted QTY action PRODUCT NAME QTY PRODUCT NAME QTY INSERT TOY3 500 NULL NULL DELETE NULL NULL TOY1 100 UPDATE TOY2 40 TOY2 50 SELECT FROM PRODUCT CATALOG GO PRODUCT NAME QTY TOY2 40 TOY3 500 This is a very simple example to illustrate the functionality of the MERGE command The comments given above are self explanatory Let s move on to other advanced features of this wonderful enhancement In the example above you saw that we are joining between two tables There is no reason why we cannot join the PRODUCT CATALOG table with another sub query Page 20 of 44 Suppose we had another table called SALE ORDER in which we kept track of the sales of all the products per store and the quantity that was sold In that scenario we will get the total Sale Quantity by summing it up over the PRODUCT NAME for a given date CREATE TABLE SALE ORDER STORE NBR INT NOT NULL PRODUCT NAME NVARCHAR 100 NOT NULL SALE QTY INT NOT NULL ORDER DATE DATETIME NOT NULL DEFAULT GETDATE INSERT INTO SALE ORDER STORE NBR PRODUCT NAME SALE QTY VALUES 1 TOY1 10 INSERT INTO SALE ORDER STORE NBR PRODUCT NAME SALE QTY VALUES 2 TOY1 20 INSERT INTO SALE ORDER STORE NBR PRODUCT NAME SALE QTY VALUES 3 TOY1 50 GO And let us drop and re create the two tables from above Please note that in the query below we are MERGE PRODUCT CATALOG PC USING SELECT PS PRODUCT NAME SUM SO SALE QTY AS SALE QTY FROM PRODUCT SALE PS INNER JOIN SALE ORDER SO ON SO PRODUCT NAME PS PRODUCT NAME WHERE SO ORDER DATE CONVERT VARCHAR 10 GETDATE 101 AND SO ORDER DATE CONVERT VARCHAR 10 GETDATE 1 101 GROUP BY PS PRODUCT NAME AS IV PRODUCT NAME SALE QTY ON PC PRODUCT NAME IV PRODUCT NAME WHEN MATCHED AND Qty SALE QTY 0 THEN DELETE WHEN MATCHED THEN update QTY if you still hold some QTY for that product UPDATE SET Qty Qty SALE QTY WHEN NOT MATCHED THEN insert a row if the PRODUCT is newly acquired INSERT VALUES PRODUCT NAME SALE QTY output details of INSERT UPDATE DELETE operations made on the target table OUTPUT action inserted PRODUCT NAME inserted QTY deleted PRODUCT NAME deleted QTY action PRODUCT NAME QTY PRODUCT NAME QTY UPDATE TOY1 20 TOY1 100 SELECT FROM PRODUCT CATALOG GO Page 21 of 44 PRODUCT NAME QTY TOY1 20 TOY2 50 In the next post we will go over another T SQL enhancement in SQL Server 2008 in that one we will cover the enhancement to the INSERT statement one can insert over a DML and one can also have minimally logged insert operations same as direct path inserts in the case of Oracle Change Data Capture Feature in SQL Server 2008 We have been blogging for quite some time now on the new feature sets of the upcoming release of SQL Server SQL Server 2008 You can search for SQL Server 2008 on this blog and get to those posts In this post we wil cover another new feature called Change Data Capture Using this feature one can capture the DML activities insert update and delete that take place against the tables There are two steps that need to happen in order for this to take place A login that has sysadmin rights first needs to enabled the database for change data capture and then a member of the db owner database role needs to enable the tables for which the DML activities need to be captured Let s first enable the database and then a table for demonstrating this and then we will go over how the capture process works internally On our instance currently none of the databases is configured for CDC Change Data Capture You can see that from the IS CDC ENABLED column of SYS DATABASES USE MASTER GO SELECT IS CDC ENABLED NAME FROM SYS DATABASES GO IS CDC ENABLED NAME 0 master 0 tempdb 0 model 0 msdb 0 MDW 0 ReportServer 0 ReportServerTempDB 0 AdventureWorks 0 DECIPHER TEST Now in order to enable it for the database DECIPHER TEST we will have to run the stored procedure sys sp cdc enable db change data capture in the context of DECIPHER TEST database Page 22 of 44 USE DECIPHER TEST GO EXEC sys sp cdc enable db change data capture GO Now if we execute the first query we will see that DECIPHER TEST has been enabled for CDC IS CDC ENABLED NAME 0 master 0 tempdb 0 model 0 msdb 0 MDW 0 ReportServer 0 ReportServerTempDB 0 AdventureWorks 1 DECIPHER TEST So what exactly did SQL Server do when we enabled the CDC option It went ahead and created the cdc schema the cdc user the meta data tables and other system objects that will be used for this process You can look at the image below to see some of those objects that were created Page 23 of 44 Now that we have enabled the database we will enable the table TEST TBL in the dbo schema for the CDC process In order to do so a member of the db owner fixed database role needs to create a capture instance for the TEST TBL table We need to first ensure that the SQL Server Agent is running and then use the sys sp cdc enable table change data capture procedure USE DECIPHER TEST GO EXECUTE sys sp cdc enable table change data capture source schema N dbo source name N TEST TBL role name N cdc Admin GO This execution will then create two jobs that you will see in the informational messages as part of this execution Job cdc DECIPHER TEST capture started successfully Job cdc DECIPHER TEST cleanup started successfully Page 24 of 44 In addition if you want to track which tables have been marked for the CDC process you can use this SQL select is tracked by cdc name type from sys tables where type u go The ones marked with 1 are the ones that are tracked is tracked by cdc name type 0 ddl history U 0 lsn time mapping U 0 captured columns U 0 index columns U 0 dbo TEST TBL CT U 1 TEST TBL U 0 systranschemas U 0 change tables U By creating a capture instance a change table is also created by this process There are a couple of options that can be utilized when specifying the capture process for a table a You can specify which columns you want to capture b You can specify an index to help uniquely identify the rows in the change table c Name of a fileroup in which that change table should be created d There is a concept of net change tracking with CDC By default only one UDF is created to access the data in the change table cdc fn cdc get all changes In our example from above the name of the UDF is cdc fn cdc get all changes dbo TEST TBL You can also use the parameter supports net changes and assign it a value of 1 to create another UDF which will return only one change for each row that was changed in a given interval So essentially when the DML operations are made against the source tables these are then recorded into the transaction log of the database The CDC process that we just set up then reads the log records and inserts modifications made to captured columns in the corresponding change table s in the CDC schema Now let us follow it up with an example of DML operations against the TEST TBL table If you recall from a previous posts in which we had covered some Page 25 of 44 other T SQL features of SQL 2008 here and here this table has three columns COL1 COL2 and COL3 and this set of data SELECT FROM TEST TBL GO COL1 COL2 COL3 1 A 10 1 A 20 1 A 30 1 B 90 2 A 30 2 A 100 3 C 110 3 C 120 Now we will execute these DML statements against this table delete from TEST TBL insert into TEST TBL values 1 A 1000 Update dbo TEST TBL set COL2 X where col1 1 And this should have created the log enteries into the table in the CDC schema Let s query that using the UDF that was created for us when we had set up the CDC process DECLARE start time datetime end time datetime from lsn binary 10 to lsn binary 10 SET start time GETDATE 1 SET end time GETDATE SELECT from lsn sys fn cdc map time to lsn smallest greater than or equal start time SELECT to lsn sys fn cdc map time to lsn largest less than or equal end time Show the changes that happened during that query window SELECT start lsn seqval case operation when 1 then Delete when 2 then Insert when 3 then Update Before when 4 then Update After end as Operation Col1 Col2 Col3 Page 26 of 44 FROM cdc fn cdc get all changes dbo test tbl from lsn to lsn all update old order by 1 2 GO start lsn seqval Operation Col1 Col2 Col3 0 x00000019000001CF000B 0 x00000019000001CF0002 Delete 1 A 10 0 x00000019000001CF000B 0 x00000019000001CF0003 Delete 1 A 20 0 x00000019000001CF000B 0 x00000019000001CF0004 Delete 1 A 30 0 x00000019000001CF000B 0 x00000019000001CF0005 Delete 1 B 90 0 x00000019000001CF000B 0 x00000019000001CF0006 Delete 2 A 30 0 x00000019000001CF000B 0 x00000019000001CF0007 Delete 2 A 100 0 x00000019000001CF000B 0 x00000019000001CF0008 Delete 3 C 110 0 x00000019000001CF000B 0 x00000019000001CF0009 Delete 3 C 120 0 x00000019000001D20004 0 x00000019000001D20002 Insert 1 A 1000 0 x00000019000001D30006 0 x00000019000001D30002 Update Before 1 A 1000 0 x00000019000001D30006 0 x00000019000001D30002 Update After 1 X 1000 You can imagine the benefits of the CDC feature fine grained auditing enhanced functionality for an ETL application that needs to incrementally populate a data mart There are of course a lot of other things that need to be discussed pertaining CDC what happens when the source table gets modified by a DDL command change of data type addition deletion of a column etc the overhead of having a CDC process is it any different than using a DML trigger the answer is Yes we will cover it in a future blog post Data Collector in SQL Server 2008 This is a wonderful new feature in SQL Server 2008 Data Collector in SQL Server 2008 is a component that is installed in a SQL instance and which can either be configured to run on your defined schedule or can run all the time and collect different sets of data pertaining to performance diagnostics historical data for baseline comparisons policy based data etc It can then be used to store this data into a relational database that is called the management data warehouse So how is it different than just using SQL Trace and perfmon It actually provides a central point for data collection across servers and across applications and allows you to control what gets collected allows you to report over it allows you to create a data warehouse of this data for baseline and trend analysis it allows you to extend it further using an API Page 27 of 44 Let s first see where it appears in SSMS and then how do we configure it After that in subsequent posts we will cover the different terminologies and different usage bits for this wonderful feature In the image below you can see that once you connect to the instance you will see Data Collection icon under the Management tree Since this is the first time we doing it we will need to configure the management data warehouse So right click and select Configure Management Data Warehouse It will bring up the wizard and on the second image below you will see that you will need to mention the instance the new database we are calling it CMDB and then a cache directory location Page 28 of 44 The third image below shows the place where you will need to map an existing login you can create a new one from this screen to the roles within the database You can see the descriptions of those roles in the image below Page 29 of