SQL Server 2008 Merge Statement Error

If multiple rows match in the target for the criteria then MERGE statement will throw the following error

Msg 8672, Level 16, State 1, Line 1
The MERGE statement attempted to UPDATE or DELETE the same row more than once. This happens when a target row matches more than one source row. A MERGE statement cannot UPDATE/DELETE the same row of the target table multiple times.

MERGE statement is deterministic whereas update with join will update any row without error. You can use one of the following options to fix the error

1.Make sure that the target row matches at most one source row
2.Use a GROUP BY to group the source rows.

In-line variable Initialization and Compound Assignment

SQL Server 2008 supports in-line variable initialization and compound assignment, a feature which developers have been enjoying for years. This feature saves lot of code and it makes it more readable.

In line Variable Assignment

--SQL Server 2005

DECLARE @i INT

SET @i = 1

SELECT @i AS VALUE

GO

--SQL Server 2008

DECLARE @i INT = 2

SELECT @i AS VALUE

GO

Compound Assignment:

--Simple variable

DECLARE @i INT = 3

SELECT @i += 1

SELECT @i

GO

--DML Statements

--Create table

CREATE TABLE Color

(ID int, ColorName varchar(10))

GO

--Populate Staging table

INSERT INTO Color VALUES (1, 'Red') , (2, 'Blue') , (3, 'Green')

GO

SELECT ColorName FROM Color WHERE ID = 1 --returns Red

UPDATE Color SET ColorName += '-B'

WHERE ID = 1

SELECT ColorName FROM Color WHERE ID = 1 --returns Red-B

SQL Server 2008 Data Compression

SQL Server 2008 offers two different forms of data compression – Row and Page level compression.

Row Compression

Row level compression does not apply any algorithm to compress the data. It changes the physical storage format which is efficient in storing the numeric and fixed length character data types. It minimizes the metadata (column information, length, offset etc) associated with a row. No application changes are required when tables are enabled for compression.

Here is an example

--Create a table

CREATE TABLE TestData

(ID int,DataValue varchar(50))

GO

--Insert data into the table

DECLARE @i INT

SET @i = 1

WHILE (@i <= 100000)

BEGIN

INSERT INTO TestData VALUES (@i, 'value:' + cast(@i AS VARCHAR(10)))

SET @i = @i + 1

END

GO

--Check for the savings if row level compression is enabled

sp_estimate_data_compression_savings 'dbo','TestData',NULL,NULL,'Row'

GO

--Alter the table to apply row level compression

ALTER TABLE TestData REBUILD

WITH (DATA_COMPRESSION = ROW)

Page Compression

This shares the common data between rows in a given page. It uses the both Row Compression and Prefix Compression.

Prefix Compression

For every column the duplicate prefixes are saved in the compressed information headers (CI) which reside after the page header. A reference number is assigned to the prefix and it is replaced on where the prefixes are used.

Dictionary Compression

All the duplicate values in the entire page are stored on the CI. Prefix compression is applicable to columns where as dictionary compression is applicable to the entire page.

--Check the savings is Page level compression is enabled

sp_estimate_data_compression_savings 'dbo','TestData',NULL,NULL,'Page'

GO

--Alter the table to apply page level compression

ALTER TABLE TestData REBUILD

WITH (DATA_COMPRESSION = Page)

Compressing a table is a CPU intensive operation. There is an optional parameter called maxdop which can be used to specify the max number of processors we want the rebuild to use.

--Alter the table to apply page level compression

ALTER TABLE TestData REBUILD

WITH (DATA_COMPRESSION = Page, MAXDOP = 4)

For more information here

http://blogs.msdn.com/sqlserverstorageengine/archive/2007/11/12/types-of-data-compression-in-sql-server-2008.aspx

This article explains the compression ratios with real world databases

http://sqlblog.com/blogs/linchi_shea/archive/2008/05/11/sql-server-2008-page-compression-compression-ratios-from-real-world-databases.aspx

SQL Server 2008 – Backup Compression feature

To compress a backup specify COMPRESSION in the WITH clause of the backup command.

Here is an example:

BACKUP DATABASE YourDatabaseName

TO DISK = 'C:\YourDatabaseName.bak'

WITH INIT, COMPRESSION

Compressing a backup typically needs less I/O, because it is smaller so it increases the speed of the backup. But compression process consumes CPU usage.

By default backup compression is off. To enable it we can use the ‘backup compression default’ configuration.

Use master

Go

Exec sp_configure 'backup compression default', '1'

GO

RECONFIGURE

You don’t have to specify anything to restore the compressed backup

SQL Server 2008 - Row Constructors

There are times when we need to insert multiple rows into a table. SQL Server 2008 has row constructors which simplifies the data insertion process.

Here are the various ways we can insert multiple rows with a single INSERT statement

--Create a table

CREATE TABLE RealTable

(ID int, name varchar(10))

GO

-- Insert with VALUE clause

INSERT INTO RealTable Values (1,'Mary')

INSERT INTO RealTable Values (2,'Jim')

--Insert with Select clause

INSERT INTO RealTable SELECT 3, 'Hillary'

INSERT INTO RealTable SELECT 4, 'Lisa'

--Insert with UNION ALL

INSERT INTO RealTable

SELECT 5, 'Rick'

UNION ALL

SELECT 6, 'John'

UNION ALL

SELECT 7, 'Tom'

--Insert with Row Constructors (new in SQL Server 2008)

INSERT INTO RealTable (ID, Color)

VALUES

(8, 'Toni'),

(9, 'Joe'),

(10, 'dave')

SQL Server 2008 MERGE

MERGE statement performs insert,update or delete operations on target table based on the results of a join with source table.

Here is a simple example

--Staging table

CREATE TABLE Staging

(ID int, Color varchar(10))

GO

--Real table

CREATE TABLE RealTable

(ID int, Color varchar(10))

GO

--Populate Staging table

INSERT INTO Staging VALUES (1, 'Red') , (2, 'Blue') , (3, 'Green')

GO

--Merge statement

MERGE RealTable RT

USING

(SELECT ID, Color FROM Staging) S

ON (RT.ID = S.ID)

WHEN MATCHED THEN --update rows when matched

UPDATE SET RT.Color = S.Color

WHEN NOT MATCHED BY TARGET --insert if rows donot exist in the target table

THEN INSERT VALUES (ID, Color)

WHEN NOT MATCHED BY SOURCE --delete if rows donot exist in the source table

THEN DELETE;

GO

SELECT * FROM RealTable

GO

--Test the update

UPDATE Staging SET Color = 'Pink' WHERE ID = 3

GO

MERGE RealTable RT

USING

(SELECT ID, Color FROM Staging) S

ON (RT.ID = S.ID)

WHEN MATCHED THEN --update rows when matched

UPDATE SET RT.Color = S.Color

WHEN NOT MATCHED BY TARGET --insert if rows donot exist in the target table

THEN INSERT VALUES (ID, Color)

WHEN NOT MATCHED BY SOURCE --delete if rows donot exist in the source table

THEN DELETE;

GO

SELECT * FROM RealTable

GO

--Test the delete

DELETE FROM Staging WHERE ID = 2

GO

MERGE RealTable RT

USING

(SELECT ID, Color FROM Staging) S

ON (RT.ID = S.ID)

WHEN MATCHED THEN --update rows when matched

UPDATE SET RT.Color = S.Color

WHEN NOT MATCHED BY TARGET --insert if rows donot exist in the target table

THEN INSERT VALUES (ID, Color)

WHEN NOT MATCHED BY SOURCE --delete if rows donot exist in the source table

THEN DELETE;

GO

SELECT * FROM RealTable

GO

DROP TABLE RealTable

GO

DROP TABLE Staging

GO

UPSERT functionality

It UPDATEs rows if they exist in the selected row set or INSERTs rows if they don’t exist in one Sql command.

SQL Server 2005 does not have this feature. You will need 2 queries to get the functionality.

Here is a simple example

--Staging table

CREATE TABLE Staging

(ID int, Color varchar(10))

GO

--Real table

CREATE TABLE RealTable

(ID int, Color varchar(10))

GO

--Populate Staging table

INSERT INTO Staging VALUES (1, 'Red') , (2, 'Blue') , (3, 'Green')

GO

--UPSERT Functionality

--Perform UPDATE first

UPDATE RealTable

SET Color = S.Color

FROM RealTable AS RT (NOLOCK)

INNER JOIN Staging S (NOLOCK) ON S.ID = RT.ID

GO

--Perform INSERT

INSERT INTO RealTable(ID,Color)

SELECT ID, Color FROM Staging AS S

WHERE NOT EXISTS (SELECT * FROM RealTable AS RT WHERE RT.ID = S.ID)

GO

SELECT * FROM RealTable

GO

--Test the update

UPDATE Staging SET Color = 'Pink' WHERE ID = 3

GO

UPDATE RealTable

SET Color = S.Color

FROM RealTable AS RT (NOLOCK)

INNER JOIN Staging S (NOLOCK) ON S.ID = RT.ID

GO

--Perform INSERT

INSERT INTO RealTable(ID,Color)

SELECT ID, Color FROM Staging AS S

WHERE NOT EXISTS (SELECT * FROM RealTable AS RT WHERE RT.ID = S.ID)

GO

SELECT * FROM RealTable

GO

DROP TABLE RealTable

GO

DROP TABLE Staging

GO

SQL Server 2008 - Policy Based Management

After lots of confusion between DMF (Dynamic Management Functions) and DMF (Declarative Management Framework), a new name is given to be clear – Microsoft SQL Server 2008 Policy Based Management.

Policy Based Management allows to define and enforce the polices for configuring and managing SQL Servers across the enterprise. Here is just a brief description of the terminology.

Target – Object to be managed

Facet – Property of the object(target)

Condition – list of criteria for target’s facets

Policy - Set of conditions on the facets of a target

Evaluation Modes

On Demand – policy is evaluated only when manually run by admin

On change: Prevent – DDL triggers prevent policy violations

On Change: log only – Event notifications are used to check the policy when changes are made

On Schedule – SQL Agent job checks the policies for periodical violations

SSIS Data types and expressions

Here is the link to integration services data types

http://msdn.microsoft.com/en-us/library/ms141036.aspx

Expressions in SSIS:

To convert today’s date (getdate()) to string (Format: 2008-05-07)

SUBSTRING((DT_STR, 30, 1252)GETDATE(), 1, 10 )

To convert today’s date (getdate()) to string (format: 20080507)

( (DT_STR, 4, 1252) Year(GETDATE() )+RIGHT("0" + (DT_STR, 2, 1252) MONTH( GETDATE() ), 2) +RIGHT("0" + (DT_STR, 2, 1252) Day(GETDATE() ), 2) )

To convert today’s date (getdate()) to int (Format: 20080507)

(DT_I4) ( (DT_STR, 4, 1252) Year(GETDATE() )+RIGHT("0" + (DT_STR, 2, 1252) MONTH( GETDATE() ), 2) +RIGHT("0" + (DT_STR, 2, 1252) Day(GETDATE() ), 2) )