Category Archives: Dev

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
from
dbo.mytable
CROSS APPLY
(
VALUES
(q1,a1),
(q2,a2),
(q3,a3)
)
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.

Advertisements

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

AS

SET NOCOUNT, XACT_ABORT ON

BEGIN TRAN

IF EXISTS(SELECT * FROM dbo.Foo WITH (UPDLOCK, HOLDLOCK) WHERE ID = @ID)

BEGIN

UPDATE dbo.Foo

SET bar = @bar

WHERE ID = @ID

END

ELSE

BEGIN

INSERT INTO dbo.Foo (ID, Bar)

VALUES (@ID, @Bar)

END

COMMIT

RETURN @@ERROR

http://weblogs.sqlteam.com/dang/archive/2007/10/28/Conditional-INSERTUPDATE-Race-Condition.aspx

also  consider sp_getapplock

http://sqlblogcasts.com/blogs/tonyrogerson/archive/2006/06/30/855.aspx

to develop…¬† use of merge on SQL2008