MERGE statement - a new tsql feature of SQL Server 2008 by serverku

It will a lengthy and complex coding if we need to perform insert, update and delete statement individually. Instead of writing separate statements for the insert, update and delete operation, we have one more option which can be very helpful in this matter.

Yes, that feature is "Merge" statement and supported in SQL server 2008 or later version. Merge is allow multiple DML operation to perform. That must be ended by semicolon. Let's see the example using Merge statement.

-- Creating Database

CREATE DATABASE MergeDatabase

GO

USE MergeDatabase

GO

-- Creating tables used for merged operation

IF ( Object_id('UsingTable') > 0 )
  DROP TABLE UsingTable

GO

CREATE TABLE UsingTable
  (
     RefId INT IDENTITY(1, 1),
     name  VARCHAR(100)
  )

GO

IF ( Object_id('TargetTable') > 0 )
  DROP TABLE TargetTable

GO

CREATE TABLE TargetTable
  (
     ChildId INT,
     val     INT
  )

GO

-- Inserting records in both tables

INSERT INTO UsingTable(name)
VALUES      ('Target-1'),
            ('Target-2'),
            ('Target-3'),
            ('Target-4'),
            ('Target-5')

GO

INSERT INTO TargetTable(ChildId,val)
VALUES      (1,1),
            (2,2),
            (3,3),
            (6,6) 

GO

Let us see how Merge statement works.

1. Merge statement with WHEN MATCHED clause and updating records,>

MERGE TargetTable 
USING UsingTable
ON (RefId = ChildId)

WHEN MATCHED THEN
UPDATE set val = val + 5 ;

2. Merge statement with WHEN MATCHED clause and deleting records,

MERGE TargetTable
USING UsingTable
ON (RefId = ChildId)

WHEN MATCHED AND ChildId = 3 THEN
DELETE ;

3. Merge statement with WHEN NOT MATCHED BY TARGET clause and inserting records,

MERGE TargetTable
USING UsingTable
ON (RefId = ChildId)

WHEN NOT MATCHED BY TARGET THEN
INSERT(childId,val)
VALUES(4,4)
;

4. Merge statement with WHEN NOT MATCHED BY SOURCE clause and deleting records,

MERGE TargetTable
USING UsingTable
ON (RefId = ChildId)

WHEN NOT MATCHED BY SOURCE
THEN DELETE;

5. All together at once,

MERGE TargetTable
USING UsingTable
ON (RefId = ChildId)


WHEN MATCHED AND ChildId = 3 THEN
DELETE 

WHEN MATCHED THEN
UPDATE set val = val + 5

WHEN NOT MATCHED BY TARGET THEN
INSERT(childId,val)
VALUES(4,4)

WHEN NOT MATCHED BY SOURCE
THEN DELETE;

Let's see the result set of TargetTable before and after the Merge statement used.

Before Merge statement ran,

After Merge statement ran,

6. Using OUTPUT with Merge statement,

MERGE TargetTable
USING UsingTable
ON (RefId = ChildId)


WHEN MATCHED AND ChildId = 3 THEN
DELETE 

WHEN MATCHED THEN
UPDATE set val = val + 5

WHEN NOT MATCHED BY TARGET THEN
INSERT(childId,val)
VALUES(4,4)

WHEN NOT MATCHED BY SOURCE
THEN DELETE

OUTPUT

$action,
INSERTED.ChildId,
INSERTED.Val,
DELETED.childId,
DELETED.val
;

Hope you have already started to use Merge statement.

DDL Trigger and DDL Auditing in SQL Server 2005 by serverku

Hope this help you. For very sensitive and secure databases, it is required to track all the activities and changes done with the database objects. And it should be very secure as well, so nobody can play with database objects. Even we should have all the history and tracking for what are database object structure changes occurred, what, when and who did it?

For the tracking all these stuffs we need to have a better process to change them and it should be done by a responsible person. Bur sometime the issues may be occur with database objects changes, at that time if we have all the enough information then we can correct and revert it back as original. Today I would like to introduce to handle all the stuffs, and it is Database DDL Triggers. DDL Triggers can be specified at database and server level, but for the database object tracking we need it on the database. Let us see an example.

-- Creating audit database
CREATE DATABASE AuditDatabase
GO

USE AuditDatabase
GO

-- Creating table, which capture all the objects structure changes
CREATE TABLE ObjectTracking
(
TrackingId bigint identity(1,1),
TrackingDate datetime NOT NULL DEFAULT GETDATE(),
DatabaseName varchar(500),
EventType varchar(500),
ObjectName varchar(500),
ObjectType varchar(500),
LoginName varchar(500),
HostName varchar(500),
SqlCommand nvarchar(max)
)

GO

Now to audit the database objects, DDL operation or structure changes we have to create a DDL trigger at database level.

-- Creating triggers, which will be fire on every objects actions on the database of that instances.
CREATE TRIGGER [AuditObjects]
ON DATABASE
FOR
-- DDL_DATABASE_LEVEL_EVENTS 
-- Defining all the DDL database events on which we need track
CREATE_PROCEDURE, ALTER_PROCEDURE, DROP_PROCEDURE,
CREATE_TABLE, ALTER_TABLE, DROP_TABLE,
CREATE_FUNCTION, ALTER_FUNCTION, DROP_FUNCTION,
CREATE_VIEW,ALTER_VIEW,DROP_VIEW,
CREATE_INDEX,ALTER_INDEX,DROP_INDEX
AS
BEGIN
SET NOCOUNT ON

DECLARE @data XML
DECLARE @HostName varchar(500)
SET @data = EVENTDATA()
set @HostName = HOST_NAME()

-- Inserting tracking information in audit table
INSERT INTO ObjectTracking(DatabaseName,EventType,ObjectName,ObjectType,LoginName,HostName,SqlCommand)
VALUES(
@data.value('(/EVENT_INSTANCE/DatabaseName)[1]', 'varchar(500)'),
@data.value('(/EVENT_INSTANCE/EventType)[1]', 'varchar(500)'),
@data.value('(/EVENT_INSTANCE/ObjectName)[1]', 'varchar(500)'),
@data.value('(/EVENT_INSTANCE/ObjectType)[1]', 'varchar(500)'),
@data.value('(/EVENT_INSTANCE/LoginName)[1]', 'varchar(500)'),
@HostName,
@data.value('(/EVENT_INSTANCE/TSQLCommand)[1]', 'varchar(max)')
)
END
GO

Now we have created tables and DDL triggers to capture the object movement. Now we have turn to test it.
Checking :

-- Creating , Altering and Destroying some of the various objects
-- Creating table event
CREATE TABLE AuditTest (AuditId int, Auditname char(10))
GO

-- Altering table event
ALTER TABLE AuditTest
ADD  AuditDate datetime NULL
GO

-- Creating Indexes events
CREATE CLUSTERED INDEX  ix_AuditId on AuditTest(AuditId)
GO

CREATE NONCLUSTERED INDEX  ix_AuditDate on AuditTest(AuditDate)
GO

-- Dropping index events
DROP INDEX  ix_AuditDate on AuditTest
GO

-- Creating view events
CREATE VIEW AuditView
AS
SELECT * FROM AUDITTEST
GO

-- Creating procedure events
CREATE PROCEDURE AuditProc
AS
BEGIN
SET NOCOUNT ON
SELECT * FROM AUDITVIEW
END
GO

-- Altering procedure events
ALTER PROCEDURE AuditProc
AS
BEGIN
SET NOCOUNT ON
SELECT * FROM AuditTest
END
GO

-- Dropping view and table events
DROP VIEW AuditView
GO

DROP TABLE AuditTest
GO

Now audit test over, we have to review the history in audit table now,

SELECT
TrackingId,
TrackingDate,
DatabaseName,
EventType,
ObjectName,
ObjectType,
LoginName,
--HostName,
'Paresh-PC' as HostName,
SqlCommand
FROM ObjectTracking
ORDER BY TrackingDate
GO

Conclusion : We have some other new features for DDL Auditing in SQL Server 2008. But before SQL Server 2008 we can use this method for auditing.

Are you using this or using something else? Make your comments.

My recent blog posts and just learned tips - SQL Server by serverku

Recently I have posted some of the articles and some just learned from the month of October. This article just summarizes all the posts which recently published. You can find the links below to go into the details.

Blog Posts:

• The database owner SID recorded in the master database differs from the database owner SID recorded in database
• Database Backup files Verification and Details
• Grouping Sets vs Native method of Group By - Performance review
• Multi server Query with Central Management Servers
• How to reset SQL Server all default settings in SQL Server Denali CTP3
• Stored Procedure Internal Activation in Service Broker
• New Logical functions coming in SQL Server Denali CTP3
• The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION
• New String functions arrived by SQL Server Denali CTP3
• Service Broker - Conversation between Databases
• Backup Statistics and History
• NOLOCK Hint & READ UNCOMMITTED Isolation level On table and Query/Session level
• Can not add a shared registered server with the same name as configuration Server
• Database Backup Compression, Amazing feature for DBA
• Review of some replication issues and workaround
• Multi Monitor Support of SSMS

Just Learned tips:

• Script to download file via FTP in SQL Server
• Perform database backup using SQLCMD utility in SQL Server
• Could not connect to server 'RemoteServer' because 'LocalServer' is not defined as a remote server in sql server
• Could not find stored procedure 'sp_MSins_TableName' in replication in sql server
• There is insufficient system memory to run this query
• Change SQL Server instance name
• Adding a transactional pull subscriptions to a publication in replication
• Adding Indexed views in publication for transactional replication
• Adding the transactional publication in Replication
• Enabling the replication database
• Add Functions in publication for transactional replication using tsql script
• Add Stored Procedures in publication for transactional replication using script
• Script to add views in existing publication in transactional replication
• Script to add table in existing publication in transactional replication

Hope these posts help you.

New Logical functions coming in SQL Server Denali CTP3 by serverku

As I have started to learn new functions arrived by SQL Server Denali CTP3 version and I have posted some of them. You can learn Analytical functions , Conversion functions and String functions. You can also get the list of all the features coming in SQL Server 2011 CTP3.

Now diverting on this post for the new Logical functions which are following,

1. IIF : It returns one of two arguments, depending on the evaluation of expression. It has require three arguments, first is condition, second and third are the values. Depending on the evaluation of first condition second or third values will be returned, means if the first condition is true, then it will return second value and if it is false then it will return third value. It returns the data type with the highest precedence from the types in true_value and false_value. Let us evaluate it to know it better.

DECLARE @IsDone bit
SET @IsDone = 1
SELECT iif(@IsDone = 1,'Success', 'Failed')
GO

Above code returns "Success". Now les us assign the NULL value to variable then verify.

DECLARE @IsDone bit
SET @IsDone = NULL
SELECT iif(@IsDone = 1,'Success', 'Failed')
GO

Here it returns "Failed" as output. Now elaborate it with more examples.

DECLARE @IsDone bit
DECLARE @FirstVal varchar(10)
DECLARE @SecondVal varchar(10)
SET @IsDone = 1
SET @FirstVal = NULL
SET @SecondVal = NULL

SELECT iif(@IsDone = 1,@FirstVal, @SecondVal)
GO

Above code returns NULL as resulted output. What happen if we pass directly NULL in both first and second values?

DECLARE @IsDone bit
SET @IsDone = 1

SELECT iif(@IsDone = 1,NULL, NULL)
GO

It comes with following error,

"Msg 8133, Level 16, State 1, Line 4
At lease one of the result expressions in a CASE specification must be an expression other than the NULL constant."

Now second turns come for CHOOSE function.

2. CHOOSE : It returns the value at the specified index from among the lists. It has require the first argument as the Index and hen we can pass multiple parameters for the values. It returns the data type with the highest precedence from the set of types passed to the function. Let elaborate it with sample examples.

DECLARE @Index int
SET @Index = 2

SELECT CHOOSE (@Index,'First','Second','Third')
GO

It returns "Second" value because it belongs to the second index.

DECLARE @Index int
SET @Index = 0

SELECT CHOOSE (@Index,'First','Second','Third')
GO

Above query returns NULL as output.

DECLARE @Index int
SET @Index = NULL

SELECT CHOOSE (@Index,'First','Second','Third')
GO

Same as earlier query it also returns NULL as resulted output. What happen if we pass all the values with NULL?

DECLARE @Index int
SET @Index = 2

SELECT CHOOSE (@Index,NULL,NULL,NULL)
GO

It's also come up with a NULL. Hope you liked these functions. Stay tuned for more posts.