Ms Sql Server Technology Discussions

[deals2buy]

[b] Using a TSQL semaphore in SSIS[/b]


In computer science, a semaphore is a variable or abstract data type that provides a simple but useful abstraction for controlling access by multiple processes to a common resource in a parallel programming environment. As long as we are working with a RDBMS ( Relational DataBase Management System), the simplest way to implement a semaphore is to create a specific table and to use the lock mechanism, provided by the RDBMS, on the whole semaphore table or on a row of the semaphore table.

This article, [url="http://www.sqlservercentral.com/articles/Miscellaneous/2649/"]Implementing a T-SQL semaphore[/url], shows how a semaphore is raised inside a T-SQL script. Now we can show how to use this principle in SSIS. We are going to show how to acquire a semaphore before running a Data Flow task and how to release it after the Data Flow is completed .

First, let us create the needed tables :

[CODE]
-- Create semaphore table drop_fi_semaforo
use dwhsk_stage_prod
go
drop table drop_fi_semaforo
create table drop_fi_semaforo ( uno char(1) ,
constraint pk_drop_fi_semaforo primary key clustered ( uno) on dwhsk_stage_data
) on dwhsk_stage_data
/* The table will contain 3 semaphores,
but for our example one will be enough */
insert into drop_fi_semaforo values ( '1')
insert into drop_fi_semaforo values ( '2')
insert into drop_fi_semaforo values ( '3')
-- Create table to perform a long running bulk insert on
USE [DWHSK_TARGET_PROD]
GO
-- drop table [drop_fi_POP_PROVA]
CREATE TABLE [dbo].[drop_fi_POP_PROVA](
[Event_ID] [bigint] NOT NULL,
[Create_Time] [datetime] NOT NULL,
[Current_Flag] [bit] NULL,
[File_ID] [int] NULL,
[Network_Start_Time] [datetime] NOT NULL,
[Start_Time] [datetime] NOT NULL,
[Contract_ID] [int] NOT NULL,
[Customer_ID] [int] NOT NULL,
[Target_Customer_ID] [int] NOT NULL,
[Network_ID] [smallint] NOT NULL)
ON [PS_PORAEV]([Network_Start_Time])
WITH
(
DATA_COMPRESSION = PAGE
)
[/CODE]


Next we create a new SSIS package. The SSIS package and its components must have the property "Isolation Level" set to "Serializable".

We add to the package a Sequence Container, named for example SC, with the TransactionOption property set to "Required". We then add into the Sequence Container a new ExecuteSQLTask named GetSemaphore, with TransactionOption=Required, that tries to acquire the semaphore using this TSQL statement:

[CODE]
/* Acquiring the semaphore means simply putting a lock
on a specific row of table drop_fi_semaforo : no data is modified.
*/
begin tran
select *
from drop_fi_semaforo with ( rowlock, UPDLOCK, holdlock)
where Uno = '1'
[/CODE]

We then add a DataFlow Task that must have the property TransactionOption set to "NotSupported". This task performs a long running bulk insert, into table drop_fi_POP_PROVA, in a separate session and a separate transaction from the session that holds the lock.

When the DataFlow completes, an ExecuteSQLTask named Release Semaphore, with property TransactionOption set to Required, resumes the transaction opened by GetSemaphore and releases the semaphore simply issuing a [b]commit[/b].

The SSIS package will look like this way :
[img]http://www.sqlservercentral.com/Images/17223.jpg[/img]

To display the locks during the running of the package, we can use the following T-SQL statement:

[CODE]
/*
The semaphore table is named drop_fi_semaforo.
The table, drop_fi_POP_PROVA,
is the table on which runs the long running bulk insert
performed in DataFlow
*/
select o.name table_name , l.request_mode lock_type , l.request_type
, l.request_session_id
, coalesce(tt.transaction_id , tt2.transaction_id ) transaction_id ,
coalesce(tt.database_transaction_log_bytes_reserved , tt2.database_transaction_log_bytes_reserved ) transaction_log_use_bytes
from sys.dm_tran_locks l
join ( select * from ( select * from dwhsk_stage_prod.sys.objects
union ALL
select * from dwhsk_target_prod.sys.objects ) oo
where name in ( 'drop_fi_semaforo' , 'drop_fi_POP_PROVA' )
) o
on l.resource_associated_entity_id = o.object_id
left join sys.dm_tran_session_transactions t on l.request_session_id = t.session_id
and l.request_session_id > 0
left join sys.dm_tran_database_transactions tt
on t.transaction_id = tt.transaction_id
and tt.database_id = l.resource_database_id and l.request_session_id > 0
left join sys.dm_tran_database_transactions tt2
on tt2.transaction_id = request_owner_id
[/CODE]


The locks when Get Semaphore has completed and Data Flow has not begun will be : table_name lock_type request_type request_session_id transaction_id transaction_log_use_bytes drop_fi_semaforo IX LOCK 80 202 0

The locks when Get Semaphore has completed and Data Flow is running will be : table_name lock_type request_type request_session_id transaction_id transaction_log_use_bytes drop_fi_POP_PROVA IX LOCK 83 201 643050 drop_fi_semaforo IX LOCK 80 202 0

We see that the session of the DataFlow runs in a different transaction from the transaction that is holding the semaphore, and that holding the semaphore does not use any log space.

When the package finishes to run, all locks will be released.

[deals2buy]

[b] Developing a SQL Server Backup Strategy[/b]

[b] Types of Backups[/b]

There are a number of different database backup types. The table below lists the different backup types and a short description of how they are used:

Backup Type
Description
COPY_ONLY
Is either a FULL or TRANSACTION LOG backup that doesn’t affect the normal sequence of backups
FULL
Backs up all the data extents in a database
DIFFERENTIAL
Backs up all the data extents that have been changed since the last FULL, PARTIAL, or FILE backup
TRANSACTION LOG
Backs up the transaction log information (only relevant for databases in FULL recovery mode)
FILE
Backs up one or more database files or file groups
PARTIAL
Backs up all read-write file groups and optionally one or more read only files

Each one of these is a little different and is used for different purposes. The three most common backup types used are FULL, DIFFERENTIAL and TRANSACTION LOG.


For more detailed info: http://www.databasejournal.com/features/mssql/developing-a-sql-server-backup-strategy.html

[deals2buy]

[quote name='Kaarthikeya' timestamp='1358011302' post='1303097224']
How you find records that exist in one table and do not exist in another table?
[/quote]
[quote name='mtkr' timestamp='1358018657' post='1303097764']
joins....
[/quote]
[quote name='ghazni' timestamp='1358046667' post='1303099226']
undi mama edo nenu use chesa adi kani gurtu ravadam ledu.. its like not exists....
[/quote]
[quote name='Kaarthikeya' timestamp='1358126973' post='1303103677']
subquery na?
[/quote]
[quote name='ghazni' timestamp='1358127232' post='1303103685']
adi epatiko gani strike avadu..... i thought of using but i did not find the right reason used left join and where statement lo Null find peta.......
[/quote]
[quote name='Kaarthikeya' timestamp='1358127319' post='1303103689']
ok...but Oracle lo minus laga SQL Server lo emaina unda ani eppati nundo
[/quote]
[quote name='ghazni' timestamp='1358127323' post='1303103690']
got it except and intersect

[url="http://www.simple-talk.com/sql/performance/the-except-and-intersect-operators-in-sql-server/"]http://www.simple-ta...-in-sql-server/[/url]
[/quote]

[b] Subqueries with NOT EXISTS[/b]

http://msdn.microsoft.com/en-us/library/ms184297%28v=sql.105%29.aspx

[b] IN (Transact-SQL)[/b]

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

[deals2buy]

[b] Introduction of Showplan Warning[/b]


SQL Server is a cost based optimizer and can guide us at every step. Now assume you wanted to learn about Performance tuning and you had a teacher to teach this. It would be much easier for us to learn and most importantly we will learn the concepts, behavior much more quickly. On the same lines, in this post we are using “SQL Server” as our teacher and guide.
[b] Previous Warnings[/b]

In versions prior to SQL Server 2012 we already have had a few warnings in the plan that can help us diagnose the slow query issue. Primarily there are two types of warnings. They are query level warnings and operator level warning. In this blog post we will discuss some of the enhancements available with SQL Server 2012.

A typical example of [b][i]Query level warning[/i][/b] includes missing index we are used to seeing inside Management Studio after executing a query.

[img]http://www.pinaldave.com/bimg/showplan1.png[/img]

[b][i]Operator level warnings[/i][/b] are raised at operator level. When SSMS sees this type of warning, it puts a yellow exclamation mark on the operator icon. The figure below shows this type of warnings.

[img]http://www.pinaldave.com/bimg/showplan2.jpg[/img]

Prior to 2012, there are two warnings at operator level. “No Join Predicates” and “Missing Column Statistics”. If you enable SET STATISTICS XML ON we can get “Missing Column Statistics” warning as:
[CODE]
<Warnings>
<ColumnsWithNoStatistics>
<ColumnReference Database="[missingstats]" Schema="[dbo]"
Table="[missing_stats]" Column="name" />
</ColumnsWithNoStatistics>
</Warnings>
[/CODE]
[b] New SQL Server 2012 Warnings[/b]

When we troubleshoot / tune a single query because it is slow, we normally just get the query plan. However, there are several trace events that have been very helpful in determining why the queries are slow. “Hash Warning” and “Sort Warnings” trace events are very helpful in determining why a query is slow.

In SQL 2012, the ShowPlan will produce warnings if [i][u]hash join or sort spill to tempdb[/u][/i] because of low memory conditions. In this case, we don’t have to capture trace events just to see if there are any sort warnings or hash warning on the query. To understand this warning in a typical setup, here is the script. First let us get the objects required for this demo.


[CODE]
USE tempdb
GO
-- Preparing data
CREATE TABLE Plan_Warning
(id INT PRIMARY KEY CLUSTERED,
name VARCHAR(25), website CHAR(50))
GO
SET NOCOUNT ON
GO
-- Pumping in dummy Data
BEGIN TRAN
DECLARE @counter INT
SET @counter = 1
WHILE @counter <= 100000
BEGIN
INSERT INTO Plan_Warning(id, name, website)
VALUES (@counter, 'Vinod Kumar' + CONVERT(VARCHAR(10), @counter),
'blogs.ExtremeExperts.com')
SET @counter = @counter + 1
END
COMMIT TRAN
PRINT 'Load completed ...'
GO

[/CODE]

Now execute the next query to look at the execution Plan.

[CODE]
SET STATISTICS XML ON
GO
SELECT * FROM Plan_Warning
WHERE id >= 7000
ORDER BY name DESC OPTION (MAXDOP 1)
GO
SET STATISTICS XML OFF
GO
[/CODE]



This executes the query and shows us the execution plan as below. Click on the Showplan resultset. Alternatively we can also use the Ctrl+M to get the actual execution plan if required.

[img]http://www.pinaldave.com/bimg/showplan3.png[/img]

The execution plan reads like this. And we can see a small [b][i]warning symbol[/i][/b] with the [i]Sort Operator[/i]. Also in the warning section we can see it says the spill did happen to tempdb. This is fundamentally because we have queried more than 93k rows and it was not able to fit in our laptop memory.

[url="http://www.pinaldave.com/bimg/showplan4.png"][img]http://www.pinaldave.com/bimg/showplan4.png[/img][/url]

If we open the XML file we can find the below node. This is the same shown visually.
[CODE]
<Warnings>
<SpillToTempDb SpillLevel="1" />
</Warnings>
[/CODE]
This can happen for [b][i]Hash Spills[/i][/b] too. In this case we will see the warning on the Hash Join node with the same error of spill of data to tempdb.

The SpillLevel for Hash Joins will be 2 in our XML.

The new warnings don’t get populated to legacy execution plans. In other words, if we set statistics profile on, we won’t get the plan with these details.
[b] Final words[/b]

Though SQL Server shows the warnings, we have not explained what to do if you get warnings.[list]
[*]Spills are happening because we have selected more data in a single query than expected and this can be the case for reporting queries.
[*]Try to add additional where clause to reduce the data from the big table.
[*]When it is not possible to add additional where clause, we highly recommend to tune and monitor TempDB growth and contentions.
[/list]

[Darling999]

A Generic SQL [url="http://www.i-programmer.info/programming/database/3624-a-generic-sql-performance-test-harness.html#"]Performance Test[/url]Harness [color=#999999][size=1]Written by Ian Stirk [/size][/color]
Article Index [url="http://www.i-programmer.info/programming/database/3624-a-generic-sql-performance-test-harness.html"]A Generic SQL Performance Test Harness[/url] [url="http://www.i-programmer.info/programming/database/3624-a-generic-sql-performance-test-harness.html?start=1"]How it works[/url]
Page 1 of 2
Do you know how to measure the impact on performance of your SQL changes? Do you know which parts of your stored procedures (or batch of SQL statements) you need to optimise? Both of these questions are answered in this article.
[b] Background[/b]

Since this article uses Dynamic Management Views (DMVs), you need SQL Server version 2005 or above to use the described generic test harness.
Various changes can be made to improve the performance of your [url="http://www.i-programmer.info/programming/database/3624-a-generic-sql-performance-test-harness.html#"]SQL queries[/url]. Such changes include adding an index, putting the underlying data on different physical devices, and using a different algorithm.
This article will attempt to quantify the effect of any changes on your SQL queries
In my previous article, [url="http://www.i-programmer.info/programming/database/3208-improve-sql-performance-find-your-missing-indexes.html"]Improve SQL performance – find your missing indexes[/url], I showed how SQL Server’s DMVs can be used to identify your missing indexes.
These missing indexes can have a profound impact on the performance of your SQL queries. I ended the article by saying that when you change your SQL (or add indexes), it is prudent to test any changes to ensure they have a positive effect on performance.
Sometimes it can be difficult to quantify the effect of a change on performance. Often the change is too small to measure, or requires the source code to be instrumented (i.e. have debug statements inserted) before measurements can be taken. Additionally, it is not always clear which statements have been affected by the change.
The test harness described in this article can be used to quantify the effect of a proposed change on the performance of your SQL queries. Additionally, it highlights, for a batch of SQL or a stored procedure, detailed performance related metrics for each individual SQL statement executed. This latter point can be useful in identifying those sections of your stored procedures (or batch of SQL statements) where performance tuning effort should be focused.
[b] Which sections of your SQL to improve?[/b]

When your SQL queries run slowly, the query plan is often used to determine where the underlying bottleneck resides, and where subsequent attention and refactoring should be focused. Often the query plan is very useful in identifying the problematic SQL statements, however, since the plan knows nothing about concurrency and which other queries are running, it can give misleading results.

On one occasion, the query plan informed me a delete statement was responsible for 0% of a stored procedure’s execution cost, however, the generic test harness described below, showed me the same delete statement was actually taking 85% of the stored procedure’s execution time. With this is mind, I believe the generic test harness described here will prove most useful.
[b] Method[/b]

To identify how long each SQL statement in a stored procedure or batch takes to execute, we use DMV snapshots.
Here we take an initial snapshot of the values of the relevant DMV counters, then run the SQL we want to investigate, and then take another DMV snapshot of the same counters.
Finally, we calculate the delta between the two snapshots, we do this because the DMV counters are accumulative.
This will provide us, for each SQL statement run between the DMV snapshots, with [url="http://www.i-programmer.info/programming/database/3624-a-generic-sql-performance-test-harness.html#"]details[/url] of the total duration, time on the CPU, time being blocked or waiting, number of reads, number of writes, and the number of times each statement was executed.
Summing the total durations will give us a measure of the total duration of the SQL query under investigation. Perhaps more importantly, the delta will identify which SQL statements are taking a long time to execute, and these can be the target for any proposed improvements.
Details of the relevant DMVs involved in the snapshots are given below:
[b]DMV[/b] [b]Description[/b] sys.dm_exec_query_stats Contains aggregated performance statistics for cached query plans sys.dm_exec_sql_text DMF that returns the SQL text identified by a given plan_handle or sql_handle

[b] The Generic Test Harness[/b]

Running the SQL query given below will produce metrics for each individual SQL statement run. You will need to replace the line “EXEC PutYourQueryHere” with the name of the stored procedure or batch of SQL statements, you want to test.
SET TRANSACTION ISOLATION LEVEL READ
UNCOMMITTED
-- Pre-work snapshot.
SELECT sql_handle, plan_handle
, total_elapsed_time
, total_worker_time
, total_logical_reads
, total_logical_writes
, total_clr_time, execution_count
, statement_start_offset
, statement_end_offset
INTO #PreWorkQuerySnapShot
FROM sys.dm_exec_query_stats

-- ToDo: put your own SQL query here.
EXEC PutYourQueryHere

-- Post-work snapshot.
SELECT sql_handle, plan_handle
, total_elapsed_time
, total_worker_time
, total_logical_reads
, total_logical_writes
, total_clr_time
, execution_count
, statement_start_offset
, statement_end_offset
INTO #PostWorkQuerySnapShot
FROM sys.dm_exec_query_stats

-- Calculate snapshot delta.
SELECT p2.total_elapsed_time -
ISNULL(p1.total_elapsed_time, 0)
AS [Duration]
, p2.total_worker_time -
ISNULL(p1.total_worker_time, 0)
AS [Time on CPU]
, (p2.total_elapsed_time -
ISNULL(p1.total_elapsed_time, 0)) -
(p2.total_worker_time -
ISNULL(p1.total_worker_time, 0))
AS [Time blocked]
, p2.total_logical_reads -
ISNULL(p1.total_logical_reads, 0)
AS [Reads]
, p2.total_logical_writes -
ISNULL(p1.total_logical_writes, 0)
AS [Writes]
, p2.total_clr_time -
ISNULL(p1.total_clr_time, 0)
AS [CLR time]
, p2.execution_count -
ISNULL(p1.execution_count, 0)
AS [Executions]
, SUBSTRING (
qt.text,p2.statement_start_offset/2 + 1,
((CASE WHEN p2.statement_end_offset = -1
THEN LEN(CONVERT(NVARCHAR(MAX), qt.text)) * 2
ELSE p2.statement_end_offset
END - p2.statement_start_offset)/2) + 1)
AS [Individual Query]
, qt.text AS [Parent Query]
, DB_NAME(qt.dbid) AS DatabaseName
FROM #PreWorkQuerySnapShot p1
RIGHT OUTER JOIN #PostWorkQuerySnapShot p2
ON p2.sql_handle =
ISNULL(p1.sql_handle, p2.sql_handle)
AND p2.plan_handle =
ISNULL(p1.plan_handle, p2.plan_handle)
AND p2.statement_start_offset =
ISNULL(p1.statement_start_offset,
p2.statement_start_offset)
AND p2.statement_end_offset =
ISNULL(p1.statement_end_offset,
p2.statement_end_offset)
CROSS APPLY sys.dm_exec_sql_text(p2.sql_handle)
AS qt
WHERE p2.execution_count !=
ISNULL(p1.execution_count, 0)
ORDER BY qt.text,
p2.statement_start_offset


-- Tidy up.
DROP TABLE #PreWorkQuerySnapShot
DROP TABLE #PostWorkQuerySnapShot

[Darling999]

[color=#333333][font=Helvetica, Arial, sans-serif][size=3]
[i]Click to see the listing with syntax coloring in a new tab:[/i][/size][/font][/color][color=#333333][font=Helvetica, Arial, sans-serif][size=3]
[url="http://www.i-programmer.info/images/stories/Core/Database/testharnes/listing1big.jpg"][img]http://www.i-programmer.info/images/stories/Core/Database/testharnes/listing1small.JPG[/img][/url][/size][/font][/color][color=#333333][font=Helvetica, Arial, sans-serif][size=3]

The script starts with the statement [/size][/font][/color][color=#333333][font=Helvetica, Arial, sans-serif][size=3]
SET TRANSACTION ISOLATION LEVEL[/size][/font][/color][color=#333333][font=Helvetica, Arial, sans-serif][size=3]
READ UNCOMMITTED.[/size][/font][/color][color=#333333][font=Helvetica, Arial, sans-serif][size=3]
The purpose of this is to ensure the SQL that follows does not take out any locks and does not honour any locks, allowing it to run faster and without hindrance.[/size][/font][/color][color=#333333][font=Helvetica, Arial, sans-serif][size=3]
We take a snapshot of the initial state of the relevant DMV counters before we run the query under investigation. The values of these counters are stored in the temporary table named #PreWorkQuerySnapshot.[/size][/font][/color][color=#333333][font=Helvetica, Arial, sans-serif][size=3]
[url="http://www.i-programmer.info/programming/database/3624-a-generic-sql-performance-test-harness.html#"]Next[/url] we run the SQL query we are investigating, in my example it is a stored procedure named “PutYourQueryHere”, you should replace this with your own stored procedure or batch of SQL statements.[/size][/font][/color][color=#333333][font=Helvetica, Arial, sans-serif][size=3]
After this SQL query has run, we take another snapshot of the DMVs counters and store them in a temporary table named #PostWorkQuerySnapshot. The difference in the values of the DMV counters, recorded in the snapshots either side of running our SQL query, will identify for each individual SQL statement run, important performance related metrics.

To obtain the metrics, we RIGHT OUTER [url="http://www.i-programmer.info/programming/database/3624-a-generic-sql-performance-test-harness.html#"]JOIN[/url] the #PreWorkQuerySnapshot and the #PostWorkQuerySnapshot temporary tables, this is necessary because the SQL statements in the #PostWorkQuerySnapshot may not already exist in the #PreWorkQuerySnapshot temporary table, but we want to include them in our results.[/size][/font][/color][color=#333333][font=Helvetica, Arial, sans-serif][size=3]
Additionally, if the statements are not present in the #PreWorkQuerySnapshot temporary table we set any null values to zero, using the ISNULL function, allowing us to record the metrics for these queries. We join to the DMF sys.dm_exec_sql_text, passing it the sql_handle, to obtain the underlying SQL text the sql_handle related to. Appling the relevant offsets to this SQL text will allow us to obtain the individual SQL statement the DMV metrics relate to.[/size][/font][/color][color=#333333][font=Helvetica, Arial, sans-serif][size=3]
To ensure we only output metrics about SQL statements that have run, we only include statements where the execution_count has changed, using a WHERE statement.[/size][/font][/color][color=#333333][font=Helvetica, Arial, sans-serif][size=3]
The results of running this generic test harness are sorted by the SQL text (i.e. the stored procedure under investigation) and then the offset of the individual SQL statements within that stored procedure. Running this test harness on my server gives the results shown below:[/size][/font][/color][color=#333333][font=Helvetica, Arial, sans-serif][size=3]

[img]http://www.i-programmer.info/images/stories/Core/Database/testharnes/results.jpg[/img][/size][/font][/color][color=#333333][font=Helvetica, Arial, sans-serif][size=3]
One use of the generic test harness is to determine if your changes have improved the performance of your[url="http://www.i-programmer.info/programming/database/3624-a-generic-sql-performance-test-harness.html#"]SQL queries[/url]. The output from the initial run of the test harness should be recorded, any improvements applied to the SQL query we are investigating (e.g. add an index), and the test harness run again. The output from both runs can be compared and summed to determine of the SQL query is now faster.[/size][/font][/color][color=#333333][font=Helvetica, Arial, sans-serif][size=3]
Another use of the generic test harness is to identify which SQL statements are taking the longest time to execute, and here is where our potential improvements should be focused, for example, adding an index that relates to the statement that has the most reads.[/size][/font][/color][color=#333333][font=Helvetica, Arial, sans-serif][size=3]
A side-effect of the sort order supplied (i.e. qt.text,p2.statement_start_offset) is it allows us to identify which statements have been executed and their order, this can be useful in identifying program flow and which statements have or have not been executed.This side-effect, in essence, provides a cumulative trace-like functionality, at an insignificant performance cost.[/size][/font][/color][color=#333333][font=Helvetica, Arial, sans-serif][size=3]
It should be noted that the DMV snapshots will record metrics for all SQL queries that run between them, not just the query under investigation. Sometimes this is not a concern, since you will want to know how the different queries interact. If it is a concern, it is possible to get around this problem by running the SQL at a time when there is nothing else running on the server.[/size][/font][/color][color=#333333][font=Helvetica, Arial, sans-serif][size=3]
Often, adding a relevant index will improve a SQL query’s performance. But how does the SQL Server optimizer know it should use an index? And how should it use the index (i.e. seek, scan or lookup?).[/size][/font][/color][color=#333333][font=Helvetica, Arial, sans-serif][size=3]
The answers to these questions lie in the data statistics that relate to the distribution and density of the index’s column values. The use of statistics plays a central role in the performance of a SQL query, but that’s another article...[/size][/font][/color]
[b] Conclusion[/b]
[color=#333333][font=Helvetica, Arial, sans-serif][size=3]
DMVs provide a quick and easy way to identify areas in your SQL queries where performance might be improved, and measure any subsequent performance improvements. There really are a great number of SQL Server secrets that DMVs can reveal.[/size][/font][/color][color=#333333][font=Helvetica, Arial, sans-serif][size=3]
For further information, see my recent book [i]SQL Server DMVs in Action[/i] which contains more than 100 useful scripts.[/size][/font][/color]

[deals2buy]

[b] Creating SSIS Package Templates for Reusability[/b]

[b] Problem[/b]

We often need to create similar SSIS packages which contain some common components such as connection managers, data flow components, log providers, event handlers, etc... So do we really need to create a package from scratch each time and add all these commonly used components in each package again and again. Is it possible to create a SSIS package with a basic structure/workflow and common components which can be used as template to create subsequent packages?
[b] Solution[/b]

SSIS allows creating a package with a basic structure/workflow that has common components which can be further leveraged as a template for reusability or for creating new packages based on this template. This means you are not required to add each item over and over again for each package that you create. This reduces the time needed for development and makes packages look/behave consistently.
[b] Creating a Package to be used as Template[/b]

To create a SSIS package to be used as template you have to follow the same approach as creating a new package. You need to use Business Intelligence Development Studio (BIDS) to create a new project of type "Integration Services Project". Create a new package, specify an appropriate name for this package and add the work flow and components you want to be part of the template.

For example, I have a sample package below in which the first task logs the start of the ETL batch. Next I have a container which will eventually contain components for loading data into staging. After that I have another container which will contain components for data loading into dimensions and facts and for cube processing. At the end, it will log success or failure for the package.

[img]http://www.mssqltips.com/tipimages2/2841_img3.jpg[/img]

Once you are done with creating the basic structure of the package and have added the common components, you need to save a copy of this package at the following locations based on the version of SQL Server you are using:

[b]For SQL Server 2005[/b]

[i]<<Installation drive>>:\Program Files (x86)\Microsoft Visual Studio 8\Common7\IDE\PrivateAssemblies\ProjectItems\DataTransformationProject\DataTransformationItems [/i]
[b]OR[/b]
[i]<<Installation drive>>:\Program Files\Microsoft Visual Studio 8\Common7\IDE\PrivateAssemblies\ProjectItems\DataTransformationProject\DataTransformationItems[/i]

[b]For SQL Server 2008[/b]

[i]<<Installation drive>>:\Program Files (x86)\Microsoft Visual Studio 9.0\Common7\IDE\PrivateAssemblies\ProjectItems\DataTransformationProject\DataTransformationItems[/i]
[b]OR [/b]
[i]<<Installation drive>>:\Program Files\Microsoft Visual Studio 9.0\Common7\IDE\PrivateAssemblies\ProjectItems\DataTransformationProject\DataTransformationItems[/i]


[img]http://www.mssqltips.com/tipimages2/2841_img2.jpg[/img]

[b]For SQL Server 2012[/b]

[i]<<Installation drive>>:\Program Files (x86)\Microsoft Visual Studio 10.0\Common7\IDE\PrivateAssemblies\ProjectItems\DataTransformationProject\DataTransformationItems[/i]
[b]OR [/b]
[i]<<Installation drive>>:\Program Files\Microsoft Visual Studio 10.0\Common7\IDE\PrivateAssemblies\ProjectItems\DataTransformationProject\DataTransformationItems[/i]

You need to specify the drive location where Business Intelligence Development Studio (BIDS) or SQL Server Data Tools (SSDT) has been deployed. Please note, as BIDS or SSDT runs locally on client machine, you need to copy the template package to the above location on all the development machines you want it to use. For this example we are naming the template package "SamplePackageTemplate.dtsx".

You are not restricted to deploying only one template. You can deploy as many templates as you want to the folders listed above and reuse them as needed.
[b] Using the SSIS Template in Other Projects[/b]

In a new or existing project where you want to add this SSIS package template, you just need to right click on the project name in the Solution Explorer, click on Add > New Item as shown below:

[img]http://www.mssqltips.com/tipimages2/2841_img4.jpg[/img]

In the Add New Item dialog box, you will notice the deployed package template as shown below. You can select it and specify a name for the package for which the template will be used and click on the Add button to add the new package to your project based on the selected template. That's all you have to do. You now have a package that is pre-configured and you can now customize it for your specific need. Please note, the modifications that are done in the new package do not impact the deployed template as we are working with a copy of the template which is now part of the current project and not the template itself.

[img]http://www.mssqltips.com/tipimages2/2841_img5.jpg[/img]

If you are using SQL Server 2012, when you add a new item you will see the template appearing in the Add New Item dialog box as shown below. Select the template and specify the name for the new package which will be based on this template.

[img]http://www.mssqltips.com/tipimages2/2841_img6.jpg[/img]

[b] Generate Unique ID[/b]

If you are using SQL Server 2005 or 2008 then you should generate a unique ID. This is recommended because it helps in analyzing log data with better differentiation for each package. To generate a unique ID value for the package, click the ID in the Properties pane, and then click Generate New ID.

In SQL Server 2012 when you add a package based on a template, SSDT generates a unique ID for each package and hence you don't need to do it separately.

[deals2buy]

[b][size=6]SQL Server Analysis Services KPIs[/size][/b]

Key Performance Indicators (KPIs) are measurements used to gauge business performance against quantifiable goals. SQL Server Analysis Services provides functionality to define KPI calculations and associate KPIs to measure groups in a multidimensional cube.

SQL Server Analysis Services KPIs consist of the following properties:[list]
[*][b]Value Expression:[/b] An MDX expression that returns the KPIs actual value.
[*][b]Goal Expression:[/b] An MDX expression that returns the KPIs target value.
[*][b]Status Expression:[/b] An MDX expression that returns the KPIs state at a point in time. The MDX expression must return a value between 1 and –1. Values of –1 or lower are interpreted negatively. A value of 0 is interpreted as neutral. Values of +1 or higher are interpreted positively.
[*][b]Trend Expression:[/b] An MDX expression that evaluates the KPIs performance over time.
[/list]
Several other properties are associated with KPIs, but the four listed above are the most crucial.

To create a KPI, click the ‘KPIs’ tab in BIDS or SSDT.

[url="http://salvoz.com/blog/wp-content/uploads/2012/12/image10.png"][img]http://salvoz.com/blog/wp-content/uploads/2012/12/image_thumb11.png[/img][/url]
Then click the ‘New KPI’ button.

[url="http://salvoz.com/blog/wp-content/uploads/2012/12/image11.png"][img]http://salvoz.com/blog/wp-content/uploads/2012/12/image_thumb12.png[/img][/url]
You should now see the KPI form displayed below:

[url="http://salvoz.com/blog/wp-content/uploads/2012/12/image12.png"][img]http://salvoz.com/blog/wp-content/uploads/2012/12/image_thumb13.png[/img][/url]

First provide a name for the KPI and select the appropriate measure group. In this example, my KPI is named ‘Sales Quota’ and it is associated to a measure group with the same name.

[url="http://salvoz.com/blog/wp-content/uploads/2012/12/image13.png"][img]http://salvoz.com/blog/wp-content/uploads/2012/12/image_thumb14.png[/img][/url]

You must now specify a ‘Value Expression’. In this example, my value expression is simply the ‘Sales Amount’ measure.

[url="http://salvoz.com/blog/wp-content/uploads/2012/12/image14.png"][img]http://salvoz.com/blog/wp-content/uploads/2012/12/image_thumb15.png[/img][/url]

Now specify a ‘Goal Expression’. The goals are already stored in a measure called ‘Sales Quota’ in this example.

[url="http://salvoz.com/blog/wp-content/uploads/2012/12/image15.png"][img]http://salvoz.com/blog/wp-content/uploads/2012/12/image_thumb16.png[/img][/url]

Once you’ve specified Value and Goal expressions, you may define the Status Expression.

The following business rules apply to the status calculation in this example:[list]
[*]If no KPI goal exists, then the status is neutral.
[*]If the KPI value divided by the KPI goal is greater than or equal to 100%, the status is positive.
[*]If the KPI value divided by the KPI goal is greater than or equal to 90% but less than 100%, then the status is neutral.
[*]If the KPI value divided by the KPI goal is less than 90%, the status is negative.
[/list]
[url="http://salvoz.com/blog/wp-content/uploads/2012/12/image16.png"][img]http://salvoz.com/blog/wp-content/uploads/2012/12/image_thumb17.png[/img][/url]

In addition to specifying a Status Expression, you must also select a ‘Status indicator’ that specifies the types of icons that will be used to indicate the status.

After specifying the Status expression and indicator, you may now specify a Trend expression and indicator.

The following business rules apply to the trend calculation in this example:[list]
[*]If any of the following criteria are true, then we cannot evaluate a trend and return neutral.
[list]
[*]No KPI goal exists.
[*]The prior date period falls within the prior fiscal year.
[*]No date period is specified (the user has not sliced by Date in the cube).
[/list][*]If the KPI value divided by the KPI goal in the current date period is less than the KPI value divided by the KPI goal for all prior date periods within the same fiscal year (not including the current date period), then the trend is negative.
[*]If the KPI value divided by the KPI goal in the current date period is equal to the KPI value divided by the KPI goal for all prior date periods (not including the current date period), then the trend is neutral.
[*]If the value divided by the goal in the current period is greater than the value divided by the goal for all prior periods (not including the current period), then the trend is positive
[/list]
[url="http://salvoz.com/blog/wp-content/uploads/2012/12/image17.png"][img]http://salvoz.com/blog/wp-content/uploads/2012/12/image_thumb18.png[/img][/url]

The complete MDX expression is displayed below:

[url="http://salvoz.com/blog/wp-content/uploads/2012/12/image18.png"][img]http://salvoz.com/blog/wp-content/uploads/2012/12/image_thumb19.png[/img][/url]

The ‘Additional Properties’ section contains the following properties that can be configured if necessary or desired:[list]
[*][b]Display Folder:[/b] Categorizes the KPI for client application display purposes.
[*][b]Parent KPI:[/b] Defines an existing KPI under which the current KPI is categorized. If this property is set, then ‘Display Folder’ is ignored.
[*][b]Current Time Member:[/b] An MDX expression that returns a time member that specifies the temporal context of the KPI.
[*][b]Weight:[/b] A weighting factor that is applied to the KPI.
[*][b]Description:[/b] Description of the KPI.
[/list]

[Darling999]

[quote name='Kaarthikeya' timestamp='1358290360' post='1303113524']
[b][size=6]SQL Server Analysis Services KPIs[/size][/b]

Key Performance Indicators (KPIs) are measurements used to gauge business performance against quantifiable goals. SQL Server Analysis Services provides functionality to define KPI calculations and associate KPIs to measure groups in a multidimensional cube.

SQL Server Analysis Services KPIs consist of the following properties:[list]
[*][b]Value Expression:[/b] An MDX expression that returns the KPIs actual value.
[*][b]Goal Expression:[/b] An MDX expression that returns the KPIs target value.
[*][b]Status Expression:[/b] An MDX expression that returns the KPIs state at a point in time. The MDX expression must return a value between 1 and –1. Values of –1 or lower are interpreted negatively. A value of 0 is interpreted as neutral. Values of +1 or higher are interpreted positively.
[*][b]Trend Expression:[/b] An MDX expression that evaluates the KPIs performance over time.
[/list]
Several other properties are associated with KPIs, but the four listed above are the most crucial.

To create a KPI, click the ‘KPIs’ tab in BIDS or SSDT.

[url="http://salvoz.com/blog/wp-content/uploads/2012/12/image10.png"][img]http://salvoz.com/blog/wp-content/uploads/2012/12/image_thumb11.png[/img][/url]
Then click the ‘New KPI’ button.

[url="http://salvoz.com/blog/wp-content/uploads/2012/12/image11.png"][img]http://salvoz.com/blog/wp-content/uploads/2012/12/image_thumb12.png[/img][/url]
You should now see the KPI form displayed below:

[url="http://salvoz.com/blog/wp-content/uploads/2012/12/image12.png"][img]http://salvoz.com/blog/wp-content/uploads/2012/12/image_thumb13.png[/img][/url]

First provide a name for the KPI and select the appropriate measure group. In this example, my KPI is named ‘Sales Quota’ and it is associated to a measure group with the same name.

[url="http://salvoz.com/blog/wp-content/uploads/2012/12/image13.png"][img]http://salvoz.com/blog/wp-content/uploads/2012/12/image_thumb14.png[/img][/url]

You must now specify a ‘Value Expression’. In this example, my value expression is simply the ‘Sales Amount’ measure.

[url="http://salvoz.com/blog/wp-content/uploads/2012/12/image14.png"][img]http://salvoz.com/blog/wp-content/uploads/2012/12/image_thumb15.png[/img][/url]

Now specify a ‘Goal Expression’. The goals are already stored in a measure called ‘Sales Quota’ in this example.

[url="http://salvoz.com/blog/wp-content/uploads/2012/12/image15.png"][img]http://salvoz.com/blog/wp-content/uploads/2012/12/image_thumb16.png[/img][/url]

Once you’ve specified Value and Goal expressions, you may define the Status Expression.

The following business rules apply to the status calculation in this example:[list]
[*]If no KPI goal exists, then the status is neutral.
[*]If the KPI value divided by the KPI goal is greater than or equal to 100%, the status is positive.
[*]If the KPI value divided by the KPI goal is greater than or equal to 90% but less than 100%, then the status is neutral.
[*]If the KPI value divided by the KPI goal is less than 90%, the status is negative.
[/list]
[url="http://salvoz.com/blog/wp-content/uploads/2012/12/image16.png"][img]http://salvoz.com/blog/wp-content/uploads/2012/12/image_thumb17.png[/img][/url]

In addition to specifying a Status Expression, you must also select a ‘Status indicator’ that specifies the types of icons that will be used to indicate the status.

After specifying the Status expression and indicator, you may now specify a Trend expression and indicator.

The following business rules apply to the trend calculation in this example:[list]
[*]If any of the following criteria are true, then we cannot evaluate a trend and return neutral.
[list]
[*]No KPI goal exists.
[*]The prior date period falls within the prior fiscal year.
[*]No date period is specified (the user has not sliced by Date in the cube).
[/list][*]If the KPI value divided by the KPI goal in the current date period is less than the KPI value divided by the KPI goal for all prior date periods within the same fiscal year (not including the current date period), then the trend is negative.
[*]If the KPI value divided by the KPI goal in the current date period is equal to the KPI value divided by the KPI goal for all prior date periods (not including the current date period), then the trend is neutral.
[*]If the value divided by the goal in the current period is greater than the value divided by the goal for all prior periods (not including the current period), then the trend is positive
[/list]
[url="http://salvoz.com/blog/wp-content/uploads/2012/12/image17.png"][img]http://salvoz.com/blog/wp-content/uploads/2012/12/image_thumb18.png[/img][/url]

The complete MDX expression is displayed below:

[url="http://salvoz.com/blog/wp-content/uploads/2012/12/image18.png"][img]http://salvoz.com/blog/wp-content/uploads/2012/12/image_thumb19.png[/img][/url]

The ‘Additional Properties’ section contains the following properties that can be configured if necessary or desired:[list]
[*][b]Display Folder:[/b] Categorizes the KPI for client application display purposes.
[*][b]Parent KPI:[/b] Defines an existing KPI under which the current KPI is categorized. If this property is set, then ‘Display Folder’ is ignored.
[*][b]Current Time Member:[/b] An MDX expression that returns a time member that specifies the temporal context of the KPI.
[*][b]Weight:[/b] A weighting factor that is applied to the KPI.
[*][b]Description:[/b] Description of the KPI.
[/list]
[/quote]
this is interesting GP Dealstobuy

[deals2buy]

[b] Difference Between Database Mail and SQLMail[/b]

Database mail is newly introduced concept in SQL Server 2005 and it is replacement of SQLMail of SQL Server earlier version. Database Mail has many enhancement over SQLMail. Database Mail is based on SMTP (Simple Mail Transfer Protocol) and also very fast and reliable where as SQLMail is based on MAPI (Messaging Application Programming Interface). Database mail depends on Service Broker so this service must be enabled for Database Mail. Database Mail can be encrypted for additional security. SQLMail is lesser secure as it can encrypt the message as well anybody can use SMTP to send email. Additionally, for MAPI to be enabled for SQLMail it will require Outlook to be installed. All this leads to potential security threat to database server.

In summary, it is suggested that if you are using SQLMail, it is right time to upgrade to Database Mail.

If you still want to use SQLMail you will have to enable it with specific commands.

[CODE]
EXEC sp_configure 'SQL Mail XPs', 1
GO
RECONFIGURE
GO
[/CODE]

[deals2buy]

[b] Configure Database Mail – Send Email From SQL Database[/b]

Database mail is the replacement of the SQLMail with many enhancements. So one should stop using the SQL Mail and upgrade to the Database Mail.

In order to send mail using Database Mail in SQL Server, there are 3 basic steps that need to be carried out. 1) Create Profile and Account 2) Configure Email 3) Send Email.


[b]Step 1) Create Profile and Account:[/b]

You need to create a profile and account using the Configure Database Mail Wizard which can be accessed from the Configure Database Mail context menu of the Database Mail node in Management Node. This wizard is used to manage accounts, profiles, and Database Mail global settings which are shown below:

[img]http://www.pinaldave.com/bimg/dbmail/step-2%20openWizard.gif[/img]

[img]http://www.pinaldave.com/bimg/dbmail/step-4%20CreateProfile.gif[/img]

[img]http://www.pinaldave.com/bimg/dbmail/step-5%20CreateProfile2.gif[/img]

[img]http://www.pinaldave.com/bimg/dbmail/step-6%20AddAccount.gif[/img]

[img]http://www.pinaldave.com/bimg/dbmail/step-7%20SMTPAccounts.gif[/img]

[img]http://www.pinaldave.com/bimg/dbmail/step-8%20Manage.gif[/img]

[img]http://www.pinaldave.com/bimg/dbmail/step-9%20Parameters.gif[/img]

[img]http://www.pinaldave.com/bimg/dbmail/step-10%20Complete.gif[/img]

[img]http://www.pinaldave.com/bimg/dbmail/step-11%20Success.gif[/img]


[b]Step 2) Configure Email:[/b]

After the Account and the Profile are created successfully, we need to configure the Database Mail. To configure it, we need to enable the Database Mail XPs parameter through the sp_configure stored procedure, as shown here:

[CODE]
sp_CONFIGURE 'show advanced', 1
GO
RECONFIGURE
GO
sp_CONFIGURE 'Database Mail XPs', 1
GO
RECONFIGURE
GO
[/CODE]


[img]http://www.pinaldave.com/bimg/dbmail/ConfigQuery.gif[/img]


[b]Step 3) Send Email:[/b]

After all configurations are done, we are now ready to send an email. To send mail, we need to execute a stored procedure sp_send_dbmail and provide the required parameters as shown below:

[CODE]
USE msdb
GO
EXEC sp_send_dbmail @profile_name='PinalProfile',
@recipients='[email protected]',
@subject='Test message',
@body='This is the body of the test message.
Congrates Database Mail Received By you Successfully.'
[/CODE]

[img]http://www.pinaldave.com/bimg/dbmail/SendQuery.gif[/img]

After all validations of the parameters entered are done, certain stored procedures are executed and the mail is queued by Service Broker, read more at [url="http://blog.sqlauthority.com/2008/07/18/sql-server-introduction-to-service-broker/"]SQL SERVER – Introduction to Service Broker[/url].

Database Mail keeps copies of outgoing e-mail messages and displays them in the [b]sysmail_allitems,[/b][b] sysmail_sentitems[/b], [b]sysmail_unsentitems[/b], [b]sysmail_faileditems[/b] [b].[/b] The status of the mail sent can be seen in [b]sysmail_mailitems[/b] table, when the mail is sent successfully the sent_status field of the sysmail_mailitems table is set to 1 which can again be seen in sysmail_sentitems table. The mails that are failed will have the sent_status field value to 2 and those are unsent will have value 3.

The log can be checked in [b]sysmail_log[/b] table as shown below:

[CODE]
SELECT *
FROM sysmail_mailitems
GO
SELECT *
FROM sysmail_log
GO
[/CODE]

[img]http://www.pinaldave.com/bimg/dbmail/MailLog.gif[/img]

Status can be verified using sysmail_sentitems table.

[img]http://www.pinaldave.com/bimg/dbmail/sent_status.gif[/img]


After sending mail you can check the mail received in your inbox, just as I received as shown below.

[Darling999]

Nee opika tagaleyya [img]http://lh3.ggpht.com/-5qMKWyHLx3E/TmVJ7qqznsI/AAAAAAAAEAo/sIb0IGlN7TI/Pk-16.gif[/img]

[deals2buy]

[b] Using the MERGE Statement[/b]

This Stairway will provide you with a basic understanding of how to work with data from SQL Server tables, using SQL Server’s Transact-SQL (T-SQL) dialect. DML is the Data Manipulation Language, and is the aspect of the language dealing with the data. It includes the statements SELECT, INSERT, UPDATE and DELETE. This Stairway will as also provide some history of the SQL language and some general concepts about set theory. Each level will build upon the prior level, so by the time you are finished you will have a good understanding of how to select and modify data from SQL Server.

How many times have you built logic in your code that first checks for the existence of a row, then based on the outcome of that existence check you either perform an INSERT statement (if the row doesn’t exist), or an UPDATE statement (if the row already exists)? With the release of SQL Server 2008 Microsoft introduced the MERGE statement to make that task a lot easier. In this article I’ll be discussing how to perform insert, update and delete logic using the MERGE statement. This is also the last article in this stairway series.
[b] What are the Capabilities of the MERGE statement?[/b]

The MERGE statement allows you to write code to determine how to compare two tables and then determine if an INSERT, UPDATE, and/or DELETE operations needs to be performed against one of the tables. The MERGE statement can also use the TOP clause to only perform a specific number of operations. Like the normal INSERT, UPDATE and DELETE statements the MERGE statement can also output information so you can identify specifically what rows and values where inserted, updated, or deleted. The best way to describe the capabilities of the MERGE statement is with a series of examples.
[b] Simple INSERT or UPDATE Logic[/b]

In this example I will be using the MERGE statement to INSERT or UPDATE records in one table based on the records contained in another table. This type of processing is commonly called an “UPSERT” (UPDATE/INSERT) operation. These tables are identified to the MERGE statement as a “Source” and a “Target” table. The “Target” is the table that will have records updated or inserted, whereas the “Source” table contains the records that will be used by the MERGE statement to identify the records in the “Target” table that will be updated or inserted.
For this example I need to first create two different sample tables. If you want to follow along and run my example you can use the code in Listing 1 to create my two sample tables.
[CODE]
USE tempdb;
GO

SET NOCOUNT ON;

-- Create Target Table
CREATE TABLE dbo.Sales (Id int,
SalesAmount money);
INSERT INTO dbo.Sales VALUES(1,10.99);

-- Create Source Table
CREATE TABLE dbo.NewSalesNAdjustments(Id int,
SalesAmount money);
INSERT INTO dbo.NewSalesNAdjustments VALUES (1, 12.99);
INSERT INTO dbo.NewSalesNAdjustments VALUES (2, 5.99);
[/CODE]
[b]Listing 1: Create sample target and source tables[/b]
In Listing 1 I created the table [i]dbo.Sales[/i]. This table will be the “Target” table for my MERGE operation. The second table created is my “Source” table that is called [i]dbo.NewSalesNAdjustments[/i]. This table will be used to update/insert rows in the [i]dbo.Sales[/i] table using the MERGE statement.

If you review the records in the [i]dbo.NewSalesNAdjustments[/i] and compare them to the records in [i]dbo.Sales[/i]table, based on the [i]Id [/i]column, you can see the row with an [i]Id[/i] value of 1 is in both tables. In my MERGE example, I will use the[i]Id[/i] column to determine whether or not a row from the [i]dbo.NewSalesNAdjustments[/i]should update or insert a row in the [i]dbo.Sales[/i] table. I want my merge logic to update the “Target” table anytime there are matching rows between the “Source” and “Target” tables based on the [i]Id [/i]column values. But if any rows in the “Source” table do not have matching rows in the “Target” table based on [i]Id[/i] column, then an INSERT operation will be will be performed. My MERGE statement in Listing 2 will accomplish this “UPSERT” operation.
[CODE]
USE tempdb;
GO
-- Update and Insert into Customers
MERGE dbo.Sales AS T -- Target
USING dbo.NewSalesNAdjustments AS S -- Source
ON T.Id = S.Id
WHEN MATCHED THEN -- Update
UPDATE SET T.SalesAmount = S.SalesAmount
WHEN NOT MATCHED THEN -- Insert
INSERT (Id,SalesAmount) VALUES (S.Id,S.SalesAmount);
-- Verify UPSERT operation
SELECT * FROM dbo.Sales;
[/CODE]
[b]Listing 2: MERGE statement performing an Upsert operation[/b]
In my MERGE statement in Listing 2 the “Target” table is the table that is referenced right after the MERGE keyword. The “Source” table of my MERGE statement is reference right after the USING keyword.

In order to identify the records to be inserted or updated the ON clause is used to join the records between the “Target” and “Source” tables, just like you would do in a normal JOIN operation. In my example I joined these two table based on the[i] Id[/i] column, to determine if a record in the “Target” table should be updated or inserted the WHEN clauses are used. The two WHEN clauses in this example have two different criteria. The first one says “WHEN MATCHED”, and the second one says “WHEN NOT MATCHED”. The “WHEN MATCHED” option the action to perform when a record in the “Source” table matches a record in the “Target” table based on the ON condition. If the “WHEN MATCHED” criteria is met then the code will use column information in the matched “Source” table record to update the matching record in the “Target” table. If a row in the “Source” table doesn’t match to a record in the “Target” table then the “WHEN NOT MATCHED” logic will be used to insert a record into the “Target” table, based on the column information in the “Source” table for the unmatched row.

The SELECT statement at the bottom of my code in Listing 2 displays the rows in my “Target” table after the MERGE statement has executed. The output displayed by this SELECT statement can be found in Report 1.

Id SalesAmount
----------- ---------------------
1 12.99
2 5.99

[b]Report 1: Resulting record set after MERGE statement in Listing 1 is executed[/b]
By reviewing the results in Report 1 you can see that the [i]SalesAmount[/i] for the row with an [i]Id[/i] of 1 was updated from 10.99 to 12.99. This was accomplished because an [i]Id[/i] column value of1 met the WHEN MATCHEDcondition, which caused the UPDATE statement to be executed that used the [i]SalesAmount[/i] column value from the “Source” table to update the “Target” table. The row with an [i]I[/i][i]d[/i] value of 2 was inserted into the “Target” table because the row with an [i]I[/i][i]d[/i] value of 2 in the “Source” table met the NOT MATCHED condition.
[b] Deleting Records Using the MERGE Statement[/b]

The MERGE statement can perform more than just an INSERT and UPDATE against the “Target” table, it can also perform DELETE operations. Suppose you need to build a process that needs to maintain a table that contains all the records in a source system. Meaning when a records are inserted, update, or deleted from the source system you want to insert, update, or delete records from the table you are maintaining.

To show you how this works I will be maintaining a [i]CurrentListing[/i] table for a real estate brokerage that contains current properties that are for sale. Monthly, I will get a new list of current properties from another source that needs to be used to keep my [i]CurrentListing[/i]table up to date. I will use the MERGE statement to keep my [i]CurrentListing[/i] table updated. Prior to showing you my MERGE statement I need to create and populate my target[i]CurrentListing[/i] table, as well as create a table that will contain the monthly current listings I get from my another source. I will be populating both of these tables by using the code in Listing 3.
[CODE]
USE tempdb;
GO
SET NOCOUNT ON;

CREATE TABLE dbo.CurrentListing -- My Target Table
(ListingID int,
ListingAddress varchar(250),
BedroomCount tinyint,
BathCount decimal(2,1),
Squarefootage smallint,
GarageSize tinyint,
AskingPrice decimal(10,2));

INSERT dbo.CurrentListing VALUES (1,'1234 MyStreet, Somewhere, WA',4, 2, 2100,2,275000);
INSERT dbo.CurrentListing VALUES (2,'9876 X Street, Nowhere, WA',3, 1.5, 1500,0,175000);
INSERT dbo.CurrentListing VALUES (3,'9990 Highend Way, Richville, WA',8, 6, 8000,3,2500000);

CREATE TABLE dbo.MonthlyListingUpdate -- My Source Table
(ListingID int,
ListingAddress varchar(250),
BedroomCount tinyint,
BathCount decimal(2,1),
Squarefootage smallint,
GarageSize tinyint,
AskingPrice decimal(10,2));

INSERT dbo.MonthlyListingUpdate VALUES (1,'1234 MyStreet, Somewhere, WA',4, 2, 2100,2,275000);
INSERT dbo.MonthlyListingUpdate VALUES (3,'9990 Highend Way, Richville, WA',8, 6, 8000,3,2000000);
INSERT dbo.MonthlyListingUpdate VALUES (4,'1950 Grand Lane, Somewhere, WA',4,2,2350,2,325000);
[/CODE]

[b]Listing 3: Script to create Target and Source tables for Insert, Update, and Delete MERGE example[/b]
In Listing 3 I created two tables: [i]CurrentListing[/i], and [i]MonthlyListingUpdate[/i]. The [i]CurrentListing[/i] table will be the “Target” table of my MERGE statement. This is the table that needs to be update/maintained with current listings. The [i]MonthlyListingUpdate[/i] table contains the records I get monthly, which represents a view of all the current listings. The records in the [i]MonthlyListingUpdate[/i] table will be the “Source” records for my MERGE statement.
To maintain my [i]CurrentListing[/i] table all I need to do is create the right MATCHED and NOT MATCHED conditions to determine when to do an INSERT or UPDATE, and then include a new NOT MATCHED BY SOURCE condition to perform the DELETE operation. I will use MERGE code in List 4 to keep my [i]CurrentListing[/i] table up to date.
[CODE]
USE tempdb;
GO
MERGE dbo.CurrentListing AS T -- Target
USING dbo.MonthlyListingUpdate AS S -- Source
ON T.ListingId = S.ListingId
WHEN MATCHED THEN -- Update
UPDATE SET T.ListingAddress = S.ListingAddress,
T.BedroomCount = S.BedroomCount,
T.BathCount = S.BathCount,
T.Squarefootage = S.Squarefootage,
T.GarageSize = S.GarageSize,
T.AskingPrice =S.AskingPrice
WHEN NOT MATCHED THEN -- Insert
INSERT (ListingId,
ListingAddress,
BedroomCount,
BathCount,
Squarefootage,
GarageSize,
AskingPrice)
VALUES (S.ListingId,
S.ListingAddress,
S.BedroomCount,
S.BathCount,
S.Squarefootage,
S.GarageSize,
S.AskingPrice)
WHEN NOT MATCHED BY SOURCE THEN -- DELETE
DELETE;
[/CODE]
[b]Listing 4: Performing INSERT, UPDATE and DELETE statements using different MERGE criteria[/b]
The code in listing 4 is similar to the MERGE statement in Listing 2, except this time I also included a DELETE clause. To identify which records in the “Target” table are to be deleted I specified a “WHEN NOT MATCHED BY SOURCE” clause. This clause tells SQL Server whenever it finds a record in the “Target” table that is not contained in the "Source" table that it needs to perform a DELETE operation.
[b] Using the OUTPUT Clause with the MERGE Statement[/b]

If you need to monitor what the MERGE statement is doing you can use the OUTPUT clause to display information about what values that were updated, inserted or deleted. To demonstrate how to use the OUTPUT clause review the code in listing 5.
[CODE]
USE tempdb;
GO
-- Stage MonthlyListingUpdate table
-- For MERGE statement with OUTPUT clause
TRUNCATE TABLE dbo.MonthlyListingUpdate
INSERT dbo.MonthlyListingUpdate VALUES (3,'9990 Highend Way, Richville, WA',8, 6, 8000,3,2500000);
INSERT dbo.MonthlyListingUpdate VALUES (5,'9301 Bayview Ln, Nowhere, WA',4,2,2350,2,325000);

-- MERGE Statement with OUTPUT
MERGE dbo.CurrentListing AS T -- Target
USING dbo.MonthlyListingUpdate AS S -- Source
ON T.ListingId = S.ListingId
WHEN MATCHED THEN -- Update
UPDATE SET T.ListingAddress = S.ListingAddress,
T.BedroomCount = S.BedroomCount,
T.BathCount = S.BathCount,
T.Squarefootage = S.Squarefootage,
T.GarageSize = S.GarageSize,
T.AskingPrice =S.AskingPrice
WHEN NOT MATCHED THEN -- Insert
INSERT (ListingId,
ListingAddress,
BedroomCount,
BathCount,
Squarefootage,
GarageSize,
AskingPrice)
VALUES (S.ListingId,
S.ListingAddress,
S.BedroomCount,
S.BathCount,
S.Squarefootage,
S.GarageSize,
S.AskingPrice)
WHEN NOT MATCHED BY SOURCE THEN -- DELETE
DELETE
OUTPUT $action, inserted.ListingId As insertedListingId,
deleted.ListingId as deletedListingId;
[/CODE]
[b]Listing 5: Using the OUTPUT clause[/b]
In the code in Listing 5 I ran the same MERGE statement as in Listing 4 with the addition of an OUTPUT clause at the end of the MERGE statement. Additionally, prior to the MERGE statement I cleaned up and repopulated my [i]MonthlyListingUpdate[/i] table. This MERGE statement updated one row, inserted another, and deleted rows where the “Source”did not have a matching [i]ListingID[/i] value in the“Target” table[i]. [/i]

The OUTPUT clause that I added to Listing 5 uses the “inserted” and “deleted” pseudo tables to display the [i]ListingId’s[/i]for those rows that where inserted or deleted, as well as displays whether an INSERT, UPDATE, DELETE statement was done by reference the [i]$action[/i] notation. Note that I only displayed a single column value from the [i]inserted [/i]and [i]deleted [/i]pseudo tables, but I could have easily displayed all of the columns in those pseudo tables with the OUTPUT clause. When I run the code in Listing 5 I get the output in Report 2
$action InsertedListingId DeletedListingId
---------- ----------------- ----------------
INSERT 5 NULL
DELETE NULL 1
UPDATE 3 3
DELETE NULL 4
[b]Report 2: Results displayed by using OUTPUT clause on MERGE statement[/b]
By reviewing the output in Report 2 you can see that four different statements were executed. For each statement executed the [i]ListingId [/i]values for the [i]inserted[/i] and [i]deleted[/i] pseudo tables were displayed. Note that the only the UPDATE statement contains a [i]ListingID[/i] value in both the inserted and deleted pseudo tables. These two values represent the before and after images of the [i]ListingId[/i] column when the UPDATE operation was performed.
[b] [b]TOP Clause[/b][/b]

The MERGE statement also supports a TOP clause. Using the TOP clause on a MERGE statement is just like using the TOP clause on a SELECT statement. When you used the TOP clause, SQL Server will not more rows than the number supplied with the TOP clause. To demonstrate this I will run the code in Listing 6.
[CODE]
USE tempdb;
GO
SET NOCOUNT ON;
-- Stage Source table for TOP clause example
TRUNCATE TABLE dbo.MonthlyListingUpdate
INSERT dbo.MonthlyListingUpdate VALUES (6,'1460 Vashon Street, Microtown, WA',8, 6, 8000,3,2500000);
INSERT dbo.MonthlyListingUpdate VALUES (7,'7651 Defiance Avenue, Softwareville, WA',4,2,2350,2,325000);
INSERT dbo.MonthlyListingUpdate VALUES (8,'990 Shaking Way, SQLLand, WA',8, 6, 8000,3,2500000);
INSERT dbo.MonthlyListingUpdate VALUES (1,'1234 MyStreet, Somewhere, WA',4, 2, 2100,2,200000);

-- MERGE Statement with TOP clause
MERGE TOP(2) dbo.CurrentListing AS T -- Target
USING dbo.MonthlyListingUpdate AS S -- Source1
ON T.ListingId = S.ListingId
WHEN MATCHED THEN -- Update
UPDATE SET T.ListingAddress = S.ListingAddress,
T.BedroomCount = S.BedroomCount,
T.BathCount = S.BathCount,
T.Squarefootage = S.Squarefootage,
T.GarageSize = S.GarageSize,
T.AskingPrice =S.AskingPrice
WHEN NOT MATCHED THEN -- Insert
INSERT (ListingId,
ListingAddress,
BedroomCount,
BathCount,
Squarefootage,
GarageSize,
AskingPrice)
VALUES (S.ListingId,
S.ListingAddress,
S.BedroomCount,
S.BathCount,
S.Squarefootage,
S.GarageSize,
S.AskingPrice)
WHEN NOT MATCHED BY SOURCE THEN -- DELETE
DELETE
OUTPUT $action, inserted.ListingId As insertedListingId,
deleted.ListingId as deletedListingId;
[/CODE]
[b]Listing 6: Script to create Sample Data[/b]
By reviewing the code in Listing 6 you can see that the “Source” table for my MERGE statement contains 3 new listings and one updated listing. Additionally, all of my existing rows in the[i] CurrentListing[/i] table are missing from the “Source” table. When I run the code in Listing 6 I get the output in Report 3.
$action insertedListingId deletedListingId
---------- ----------------- ----------------
INSERT 6 NULL
INSERT 7 NULL

[b]Report 3: Results displayed by using OUTPUT clause on MERGE statement[/b]
As you can see, my MERGE statement with the TOP clause only executed two INSERT statements for records with [i]ListingID[/i] values of 6 and 7. The TOP clause caused my MERGE statement to not perform the additional INSERT, UPDATE, or DELETE operations that would have normally been executed if the TOP clause was not present.
[b] A MERGE Primer[/b]

The examples here are only meant to be a primer to help you understand the MERGE statement. Keep in mind there are many different ways you can put together the MATCH. NOT MATCHED, and NOT MATCHED BY SOURCE conditions to help you build the insert, update and delete logic needed to help you maintain a SQL Server table.

As already stated this is the final article in the “[url="http://www.sqlservercentral.com/stairway/75773/"]Stairway to T-SQL DML[/url]” series. This stairway exposed you to the history of SQL Server and provided you with the basic DML statement to allow you to query and maintain your SQL Server database tables.

[deals2buy]

[b] SQL Server T-SQL Tuning – NOT IN and NOT Exists[/b]


[b]Not in and Not Exists commands :[/b]
In some circumstances we will need to select/update/delete orphan data – that is data does not exists in the major table but still exists in other interrelated tables.

The response to such a circumstance will often be to utilize the “Not in” and “Not Exists” commands. This can however have a major impact on performance as the Not IN command requires a check to be made on each record individually resulting in a strain on resources and can also lead to locks particularly when updating or deleting a large volume of data .

[b]Alternatives to NOT IN and NOT Exists[/b]
SQL Server 2008 introduced the Merge command for bulk insert / update / delete operations which can be used with the Except clause to minimize its performance hit.

[b]Examples:[/b] First we will create two tables :

[CODE]
use [Workshops]
Create table Mytable1 (companyname varchar (100) )
Create table Mytable2 (companyname varchar (100) )
[/CODE]

[i]Select/update/delete orphaned data using Not in command :[/i]

[i][CODE]
SELECT companyname FROM Mytable1 where companyname not in (select companyname from Mytable2)
UPDATE Mytable1 SET companyname =N'Company_Name' where companyname not in (select companyname from Mytable2)
DELETE Mytable1 FROM Mytable1 where companyname not in (select companyname from Mytable2)
[/CODE][/i]

[i]Improved performance using Merge and Except :[/i]

[i][CODE]
merge Mytable1 T using (select companyname from mytable1 except select companyname from mytable2 )S on t.companyname=s.companyname
when matched then update SET companyname=N'Company_Name' ;
merge Mytable1 T using (select companyname from mytable1 except select companyname from mytable2 )S on t.companyname=s.companyname
when matched then delete ;
SELECT * FROM MYTable1 S where not exists (select 1 from MYTable1 inner join MYTable2 on MYTable1.companyname=MYTable2.companyname and MYTable1.companyname=s.companyname)
[/CODE][/i]

Note that this also applies Not Exists :

[CODE]
SELECT * FROM MYTable1 S where not exists (select 1 from MYTable1 inner join MYTable2 on MYTable1.companyname=MYTable2.companyname and MYTable1.companyname=s.companyname)
[/CODE]

[b]Healthy T-SQL practices to Select orphaned data using Except Command[/b]

[b][CODE]
select companyname from mytable1 except select companyname from mytable2
[/CODE][/b]

[deals2buy]

The TRY…CATCH construct cannot be used in a user-defined function.

Is it true?