SQL Server 2008 Archives - Page 2 of 2

Tag Archive for: SQL Server 2008

Filtered Indexes

April 20, 2008/0 Comments/in SQL Server/by Plamen Ratchev

Filtered indexes are another welcome addition in SQL Server 2008. They allow creating an index filtered with WHERE clause. While this was doable in SQL Server 2005 using indexed views on the table, the natural approach of directly defining a filtered index is much more appealing.

Below are some examples of using filtered indexes. The first one demonstrate how filtered index will assist on commonly searched values allowing to use INDEX SEEK for those, and a TABLE SCAN for less frequently searched values. The other example implements a very common request to define unique values for column by allowing multiple NULL values (using UNIQUE index allows only a single NULL value).

CREATE TABLE Regions (
 region_cd CHAR(2),
 region_name VARCHAR(35),
 region_phone VARCHAR(12) NULL);
 
INSERT INTO Regions VALUES ('NE', 'North East', NULL),
                           ('SW', 'South West', NULL),
                           ('SE', 'South East', '902-202-1234');
 
-- Index to filter on frequently queried values
CREATE NONCLUSTERED INDEX ix_SouthEastRegion
  ON Regions (region_cd)
  INCLUDE(region_name, region_phone)
  WHERE region_cd = 'SE';
 
GO
SET SHOWPLAN_TEXT ON;
GO
 
-- Typical user query
SELECT region_cd, region_name, region_phone
FROM Regions
WHERE region_cd = 'SE';
 
/*
 
-- Execution plan
StmtText
--------------------------------------------------------------
  |--Index Seek(OBJECT:([Testing].[dbo].[Regions].[ix_SouthEastRegion]), 
           SEEK:([Testing].[dbo].[Regions].[region_cd]='SE') ORDERED FORWARD)
 
*/
 
-- Less frequent user query
SELECT region_cd, region_name, region_phone
FROM Regions
WHERE region_cd = 'NE';
 
/*
 
-- Execution plan
StmtText
--------------------------------------------------------------
  |--Table Scan(OBJECT:([Testing].[dbo].[Regions]), 
           WHERE:([Testing].[dbo].[Regions].[region_cd]='NE'))
 
*/
 
GO
SET SHOWPLAN_TEXT OFF;
GO
 
-- Guarantee unique values excluding NULLs
CREATE UNIQUE NONCLUSTERED INDEX ix_RegionPhone
  ON Regions (region_phone)
  WHERE region_phone IS NOT NULL;
 
-- OK, multiple NULLs allowed because filtered out from the UNIQUE index
INSERT INTO Regions VALUES ('NW', 'North West', NULL);  
 
-- Attempt to insert duplicate non NULL value
INSERT INTO Regions VALUES ('NW', 'North West', '902-202-1234');  
 
/*
 
-- Error
Msg 2601, Level 14, State 1, Line 11
Cannot insert duplicate key row in object 'dbo.Regions' with unique index 'ix_RegionPhone'.
The statement has been terminated.
 
*/

Composable DML

April 15, 2008/2 Comments/in SQL Server/by Plamen Ratchev

Another powerful T-SQL enhancement in SQL Server 2008 is the ability to write composable DML. It allows to consume the OUTPUT clause result set and to feed it as source for a query.

Here is one example to demonstrate that. The scenario is to merge data from daily updated branch office table to central office static table. Additional requirement is to store auditing data for any changes in the company name column. In prior versions of SQL Server this would require a couple SQL statements to accomplish. In SQL Server 2008 the new MERGE statement allows to perform all merge actions in a single statement, and then feed via the OUTPUT clause data for the auditing table. That way a single SQL statement completes the task.

-- Create central accounts static table
CREATE TABLE CentralOfficeAccounts (
 account_nbr INT PRIMARY KEY,
 company_name VARCHAR(35),
 primary_contact VARCHAR(35),
 contact_phone VARCHAR(12));
 
-- Daily updated branch data
CREATE TABLE BranchOfficeAccounts (
 account_nbr INT PRIMARY KEY,
 company_name VARCHAR(35),
 primary_contact VARCHAR(35),
 contact_phone VARCHAR(12));
 
-- Insert sample data 
INSERT INTO CentralOfficeAccounts
VALUES (1, 'Bayside Motors', 'Jim Smith', '902-203-1234'),
       (2, 'Dallas Industries', 'Joe Doe', '301-663-9134'),
       (3, 'Sky Computer Systems', 'Jane Brown', '201-943-6053');
 
INSERT INTO BranchOfficeAccounts
VALUES (2, 'Dallas Industries, Inc.', 'Rick Gross', '301-663-9134'),    -- changed
       (3, 'Sky Computer Systems', 'Jane Brown', '201-943-6053'),       -- same
       (4, 'Auto Insurance Co.', 'Chris Jefferson', '313-601-6201');    -- new
 
-- Table for auditing
CREATE TABLE AccountsAudit (
 account_nbr INT,
 change_action NVARCHAR(10),
 change_date DATETIME DEFAULT CURRENT_TIMESTAMP,
 old_company_name VARCHAR(35),
 new_company_name VARCHAR(35),
 PRIMARY KEY(account_nbr, change_action, change_date));
 
-- Single SQL statement performing the merge and auditing actions
INSERT INTO AccountsAudit 
 (account_nbr, change_action, old_company_name, new_company_name)
SELECT account_nbr, merge_action, old_company_name, new_company_name
FROM (MERGE INTO CentralOfficeAccounts AS C    
      USING BranchOfficeAccounts AS B        
         ON C.account_nbr = B.account_nbr
      WHEN MATCHED                        
       AND C.company_name <> B.company_name THEN                        
           UPDATE SET C.company_name = B.company_name
      WHEN NOT MATCHED THEN            
           INSERT (account_nbr, company_name, 
                   primary_contact, contact_phone)
           VALUES (B.account_nbr, B.company_name, 
                   B.primary_contact, B.contact_phone)
     WHEN SOURCE NOT MATCHED THEN        
          DELETE
     OUTPUT $action, 
            COALESCE(inserted.account_nbr, deleted.account_nbr),
            deleted.company_name, 
            inserted.company_name
     ) AS T(merge_action, account_nbr, old_company_name, new_company_name);
 
SELECT account_nbr, change_action, change_date,
       old_company_name, new_company_name
FROM AccountsAudit; 
 
/*
 
-- Results
account_nbr change_action change_date old_company_name   new_company_name
----------- ------------- ----------- ------------------ -----------------------
1           DELETE        2008-04-15  Bayside Motors     NULL
2           UPDATE        2008-04-15  Dallas Industries  Dallas Industries, Inc.
4           INSERT        2008-04-15  NULL               Auto Insurance Co.
 
*/

Note:

This code has been tested with SQL Server 2008 CTP 6 (February 2008). As of SQL Server 2008 Release Candidate 0 (June 2008) the clause “WHEN SOURCE NOT MATCHED” has been replaced with “WHEN NOT MATCHED BY SOURCE”. This makes the wording clearer. It is good to note that this clause of MERGE is not standard.

Delighters in SQL Server 2008

April 8, 2008/2 Comments/in SQL Server/by Plamen Ratchev

Two of the new T-SQL enhancements in SQL Server 2008 are the inline variable initialization and compound assignment (also called delighters). We had those for a long time in languages like C, C++, C#, VB, and they are just coming to T-SQL. While not of significant value, it will make writing code easier and more compact.

Here are a couple examples to illustrate their use.

-- Declare and initialize variable
DECLARE @count INT = 1;
 
-- Compound assignments
SET @count += 1;
SET @count /= 2;
SET @count *= 5;
SET @count %= 3;
SET @count -= 1;
 
SELECT @count;
 
/*
 
-- Result
1
 
*/
 
-- Create sample table for employee pay rates
CREATE TABLE PayRates (
 employee_nbr INT PRIMARY KEY,
 pay_rate DECIMAL(15, 2),
 performance_score INT);
 
-- Insert data using the new row constructors 
INSERT INTO PayRates VALUES (1, 40.00, 5), (2, 45.50, 4), (3, 39.50, 6);
 
-- Apply pay rate increase
-- Compound assignments can be used with columns on the right side
UPDATE PayRates
SET pay_rate *= performance_score * 0.25;
 
SELECT employee_nbr, pay_rate, performance_score
FROM PayRates;
 
-- Declare variable and initialize using query
DECLARE @max_pay_rate DECIMAL(15, 2) = (SELECT MAX(pay_rate)
                                        FROM PayRates);
 
SELECT @max_pay_rate AS max_pay_rate;

UPSERT and More with MERGE

March 25, 2008/3 Comments/in SQL Server/by Plamen Ratchev

One very common requirement is to merge source data to a target table. For example, merge the daily changes to accounts data from branch office table to central accounts table. On SQL Server version 2005 and prior this was accomplished using separate INSERT and UPDATE statements. This involved checking if key column exists to perform update and insert if not, or attempt an update first and then if not rows were affected perform insert. Not to mention if we have to check if account is missing from the branch office data and needs to be deleted from the central accounts table. That way we end up with multiple (sometimes complex) statements to implement one transaction, accessing both source and target tables multiple times.

SQL Server 2008 offers a lot more elegant way using the MERGE statement (MERGE is supported by ANSI SQL). It makes data merging very simple and elegant, as well as efficient.

Here is the base syntax for MERGE:

[ WITH <common_table_expression> [,...n] ]
MERGE 
[ TOP ( expression ) [ PERCENT ] ] 
[ INTO ] target_table [ [ AS ] table_alias ]
        [ WITH ( <merge_hint> ) ]
USING <table_source>
ON <search_condition>
[ WHEN MATCHED [ AND <search_condition> ]
        THEN <merge_matched> ]
[ WHEN [TARGET] NOT MATCHED [ AND <search_condition> ]
        THEN <merge_not_matched> ]
[ WHEN SOURCE NOT MATCHED [ AND <search_condition> ]
        THEN <merge_matched> ]
<output_clause>
[ OPTION ( <query_hint> [ ,...n ] ) ]    
;

Below is the initial setup for the source and target tables.

-- Static table as target for merging data
CREATE TABLE CentralOfficeAccounts (
 account_nbr INT PRIMARY KEY,
 company_name VARCHAR(35),
 primary_contact VARCHAR(35),
 contact_phone VARCHAR(12));
 
-- Dynamic table with daily updates to be merged
CREATE TABLE BranchOfficeAccounts (
 account_nbr INT PRIMARY KEY,
 company_name VARCHAR(35),
 primary_contact VARCHAR(35),
 contact_phone VARCHAR(12));
 
-- Sample centrall office static data 
INSERT INTO CentralOfficeAccounts
VALUES (1, 'Bayside Motors', 'Jim Smith', '902-203-1234'),
       (2, 'Dallas Industries', 'Joe Doe', '301-663-9134'),
       (3, 'Sky Computer Systems', 'Jane Brown', '201-943-6053');
 
-- Daily updated branch office data
INSERT INTO BranchOfficeAccounts
VALUES (2, 'Dallas Industries, Inc.', 'Rick Gross', '301-663-9134'), -- changed
       (3, 'Sky Computer Systems', 'Jane Brown', '201-943-6053'),    -- same
       (4, 'Auto Insurance Co.', 'Chris Jefferson', '313-601-6201'); -- new

Here is how MERGE can be used to perform an update on existing accounts and insert of new accounts.

-- Update existing and add missing
MERGE INTO CentralOfficeAccounts AS C    -- Target
USING BranchOfficeAccounts AS B          -- Source
   ON C.account_nbr = B.account_nbr
WHEN MATCHED THEN                        -- On match update
     UPDATE SET C.company_name = B.company_name,
                C.primary_contact = B.primary_contact,
                C.contact_phone = B.contact_phone
WHEN NOT MATCHED THEN                    -- Add missing
     INSERT (account_nbr, company_name, primary_contact, contact_phone)
     VALUES (B.account_nbr, B.company_name, B.primary_contact, B.contact_phone);
 
SELECT account_nbr, company_name, primary_contact, contact_phone 
FROM CentralOfficeAccounts; 
 
/*
 
-- Results:
 
account_nbr company_name             primary_contact  contact_phone
----------- ------------------------ ---------------- -------------
1           Bayside Motors           Jim Smith        902-203-1234
2           Dallas Industries, Inc.  Rick Gross       301-663-9134
3           Sky Computer Systems     Jane Brown       201-943-6053
4           Auto Insurance Co.       Chris Jefferson  313-601-6201
 
*/

Next step is to enhance the statement including predicates to check and update only accounts that have changed. The only difference here is the additional conditions in the MATCHED clause, results will be the same as before.

-- Update existing that changed and add missing
-- Use of predicates 
MERGE INTO CentralOfficeAccounts AS C   -- Target
USING BranchOfficeAccounts AS B         -- Source
   ON C.account_nbr = B.account_nbr
WHEN MATCHED                            -- On match update
 AND (C.company_name <> B.company_name  -- Additional search conditions
   OR C.primary_contact <> B.primary_contact
   OR C.contact_phone <> B.contact_phone) THEN                        
     UPDATE SET C.company_name = B.company_name,
                C.primary_contact = B.primary_contact,
                C.contact_phone = B.contact_phone
WHEN NOT MATCHED THEN                   -- Add missing
     INSERT (account_nbr, company_name, primary_contact, contact_phone)
     VALUES (B.account_nbr, B.company_name, B.primary_contact, B.contact_phone);

Going one step further, let’s add the option to delete accounts that do not exist in the source table from the target table.

--Update existing that changed and add missing, delete missing in source
MERGE INTO CentralOfficeAccounts AS C   -- Target
USING BranchOfficeAccounts AS B         -- Source
   ON C.account_nbr = B.account_nbr
WHEN MATCHED                            -- On match update
 AND (C.company_name <> B.company_name  -- Additional search conditions
   OR C.primary_contact <> B.primary_contact
   OR C.contact_phone <> B.contact_phone) THEN                        
     UPDATE SET C.company_name = B.company_name,
                C.primary_contact = B.primary_contact,
                C.contact_phone = B.contact_phone
WHEN NOT MATCHED THEN                   -- Add missing
     INSERT (account_nbr, company_name, primary_contact, contact_phone)
     VALUES (B.account_nbr, B.company_name, B.primary_contact, B.contact_phone)
WHEN SOURCE NOT MATCHED THEN            -- Delete missing from source
     DELETE;
 
SELECT account_nbr, company_name, primary_contact, contact_phone 
FROM CentralOfficeAccounts; 
 
/*
 
-- Results:
 
account_nbr company_name             primary_contact  contact_phone
----------- ------------------------ ---------------- -------------
2           Dallas Industries, Inc.  Rick Gross       301-663-9134
3           Sky Computer Systems     Jane Brown       201-943-6053
4           Auto Insurance Co.       Chris Jefferson  313-601-6201
 
*/

There is a lot more to MERGE, but this just shows the tremendous power it provides for merging data.

Note:

References:

MERGE (Transact-SQL)
http://technet.microsoft.com/en-us/library/bb510625(SQL.100).aspx

Table Value Constructors in SQL Server 2008

January 21, 2008/2 Comments/in SQL Server/by Plamen Ratchev

One of the new features of SQL Server 2008 is the support for table value constructors (part of ANSI SQL). Here are a couple quick examples of using them.

-- Populate sample table
 
CREATE TABLE Foo (
  keycol INT PRIMARY KEY,
  datacol VARCHAR(30));

In the past, populating table rows was done like this:

INSERT INTO Foo VALUES (1, 'Books');
INSERT INTO Foo VALUES (2, 'CDs');
INSERT INTO Foo VALUES (3, 'DVDs'); 
 
-- or 
 
INSERT INTO Foo (keycol, datacol)
SELECT 1, 'Books'
UNION ALL
SELECT 2, 'CDs'
UNION ALL
SELECT 3, 'DVDs';
 
-- or using on the fly
 
SELECT keycol, datacol
FROM ( SELECT 1, 'Books'
       UNION ALL 
       SELECT 2, 'CDs'
       UNION ALL
       SELECT 3, 'DVDs') AS Foo (keycol, datacol);

Here is how the same can be done with SQL Server 2008 table value constructors:

INSERT INTO Foo (keycol, datacol)
VALUES (1, 'Books'), (2, 'CDs'), (3, 'DVDs');
 
-- or using on the fly
 
SELECT keycol, datacol
FROM ( VALUES (1, 'Books'),
              (2, 'CDs'),
              (3, 'DVDs') ) AS Foo (keycol, datacol);
 
-- and CTE version
 
WITH Foo (keycol, datacol)
AS( SELECT *
    FROM (  VALUES  (1, 'Books'),
                    (2, 'CDs'),
                    (3, 'DVDs') ) AS F (keycol, datacol))
SELECT keycol, datacol
FROM Foo;

Another interesting option is to derive a row value from a subquery, like this:

INSERT INTO Foo (keycol, datacol)
VALUES ((SELECT MAX(keycol) + 1 FROM Foo), 'Tapes');

Still not possible, but maybe in the next version (or Service Pack) we can see vector expressions in predicates and UPDATE, like:

SELECT keycol, datacol
FROM Foo
WHERE (keycol, datacol) IN (SELECT keycol, datacol FROM Foo2);
 
-- or
 
UPDATE Foo
SET (keycol, datacol) = (SELECT keycol, datacol FROM Foo2);