Saturday, December 27, 2008
SQL Server 2008 Merge Statement 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.
Saturday, December 6, 2008
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
This article explains the compression ratios with real world databases
Tuesday, December 2, 2008
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
Monday, December 1, 2008
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
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')