Category Archives: SQL

Cross Apply > Unpivot

Although the Pivot & Unpivot functions are really useful at times, for normalising and denormalising data, I find the syntax a pain.

I spied a simpler way of using CROSS APPLY to achieve the same results which I find easier to understand, remember, & use:

The questions and the expected response are stored on MyTable in a horizontal fashion id,q1,a1,q2,a2 etc

SELECT id,question,response
u (Question,Response)

This query uses Cross Apply to create a subquery with the syntax similar to that used for inserting explicit values, but in reverse.

Then there is a alias and a subquery table definition like you would use in a CTE, again in reverse.


Always-On Failover proof Jobs

When you are running jobs on the read-write server, but need to fail-over and keep running your jobs (that you remembered to keep in sync…)

Add a first step that exits with success on failure

IF (SELECT ars.role_desc
FROM sys.dm_hadr_availability_replica_states ars
INNER JOIN sys.availability_groups ag
ON ars.group_id = ag.group_id
AND ars.is_local = 1 and = ‘My-Listener’) ‘PRIMARY’
–We”re on the secondary node, throw an error
THROW 50001, ‘Unable to execute job on secondary node’,1

there must be a way to add these and have the sequences readjust – I suspect it involves powershell, scripting them out, deleting and recreating…

msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N’OnPrimaryAGOnly’,
@os_run_priority=0, @subsystem=N’TSQL’,
@command=N’IF (SELECT ars.role_desc
FROM sys.dm_hadr_availability_replica_states ars
INNER JOIN sys.availability_groups ag
ON ars.group_id = ag.group_id
AND ars.is_local = 1 and = ”My-Listener”) ”PRIMARY”
–We”re on the secondary node, throw an error
THROW 50001, ”Unable to execute job on secondary node”,1

Snapshot Isolation

Gathering some info on using Snapshot Isolation levels in SQL statements…

When Snapshot isolation is enabled on a database, versions of data are kept in tempdb whilst transactions are running in order to much reduce readers & writers from blocking each other.

However, if Read Committed Snapshot (RCSI) option is Off then statements wont use this info by default.

This means most transactions run in the default isolation level (Read Committed) unless otherwise specified:

case transaction_isolation_level
when 0 then ‘Unspecified’
when 1 then ‘ReadUncomitted’
when 2 then ‘ReadCommitted’
when 3 then ‘Repeatable’
when 4 then ‘Serializable’
when 5 then ‘Snapshot’
end as transaction_isolation_level
FROM sys.dm_exec_sessions


Traditionally, developers reach for the ‘with (no lock)’ table hint when they want to exec a long-running select statement on a busy table without causing, or waiting for, locks.

This uses the ‘Read Uncomitted’ isolation level and allows dirty reads of uncommitted updates.

Overall, its fine for quick & dirty results, but far from ideal for results that you want to act upon with a degree of confidence as you can get incorrect results from updates that get rolled back, or duplicate data from page splits

Using Snapshot isolation lets you query without the problems due to ‘read Uncommitted’ and with the benefits of increased concurrency. It allows data to be read consistently as it was at the start of the transaction, as previous versions of rows at that time are snapshotted in tempdb whilst updates take place.

In order to get consistency over a batch of reads from separate tables then you should wrap the statements in a transaction, even if you are only reading the data!

If you should need to prevent anyone from updating any table whilst you are running the transaction, then you can hint ‘with (UPDLOCK, ROWLOCK)’

If using Snapshot isolation when updating data, you need to be careful. The default ‘Read committed’ is like checking out a file  in source control so that no one else can change it, Snapshot is like branching & merging. You can get update conflicts, or you can get results that are just different to those you would get normally. Needs consideration & testing.

example of conflict error, under Read Committed one transaction would block the other.

Msg 3960, Level 16, State 2, Line 15 Snapshot isolation transaction aborted due to update conflict. You cannot use snapshot isolation to access table ‘dbo.tbl’ directly or indirectly in database ‘yourdbname’ to update, delete, or insert the row that has been modified or deleted by another transaction. Retry the transaction or change the isolation level for the update/delete statement.

Pooled Connections

One thing to note is that Isolation levels can persist across statements in a pooled connection! So, if you specify a change to the default level then make sure to set back to default at the end of the statement to avoid inadvertently using snapshot level on a query where no level has been specified.

Table Hints

If you add table hints within a query eg with (nolock) then this will override the level set at statement level – so, be sure and remove these hints if you want to use snapshot isolation.

Conflict with Temp Tables

If you see an error message like this:

“Snapshot isolation transaction failed in database ‘tempdb’ because the object accessed by the statement has been modified by a DDL statement in another concurrent transaction since the start of this transaction.  It is disallowed because the metadata is not versioned.  A concurrent update to metadata can lead to inconsistency if mixed with snapshot isolation.”

it can have a couple of sources: firstly if you are rebuilding indexes or actually changing the source tables with a DDL statement, or less obviously, if the statement includes a temporary table with a primary key. Havent found a way around this apart from skipping the Primary key/index.


Concurrent Inserts

Beware of adding/inserting with parallel processes – to avoid duplicate inserts you need to keep a range lock open when you check for the existence of the ID

CREATE PROCEDURE dbo.Insert_Or_Update_Foo

@ID int,

@Bar int






UPDATE dbo.Foo

SET bar = @bar





INSERT INTO dbo.Foo (ID, Bar)

VALUES (@ID, @Bar)




also  consider sp_getapplock

to develop…  use of merge on SQL2008