gistfile1.txt

USE StackOverflow2010;
GO

/*Use to check in on locks*/
CREATE OR ALTER FUNCTION dbo.WhatsUpLocks(@SPID INT)  
RETURNS TABLE  
AS  
RETURN  
SELECT    dtl.request_mode,  
          CASE dtl.resource_type   
               WHEN 'OBJECT'   
               THEN OBJECT_NAME(dtl.resource_associated_entity_id)   
               ELSE OBJECT_NAME(p.object_id)   
          END AS locked_object,  
          dtl.resource_type,  
          COUNT_BIG(*) AS total_locks  
FROM      sys.dm_tran_locks AS dtl  
LEFT JOIN sys.partitions AS p  
    ON p.hobt_id = dtl.resource_associated_entity_id  
WHERE     dtl.request_session_id = @SPID  
AND       dtl.resource_type <> 'DATABASE'  
GROUP BY  CASE dtl.resource_type   
               WHEN 'OBJECT'   
               THEN OBJECT_NAME(dtl.resource_associated_entity_id)   
          ELSE OBJECT_NAME(p.object_id)   
          END,   
    dtl.resource_type,   
    dtl.request_mode;
GO 

/*First view, no range locks on update*/
CREATE OR ALTER VIEW dbo.PostScoresVotes
WITH SCHEMABINDING
AS 

SELECT   p.Id, 
         SUM(p.Score * 1.0) AS ScoreSum, 
		 COUNT_BIG(v.Id) AS VoteCount,
		 COUNT_BIG(*) AS OkayThen 
FROM     dbo.Posts AS p
JOIN     dbo.Votes AS v
    ON p.Id = v.PostId
WHERE p.PostTypeId = 2
AND   p.CommunityOwnedDate IS NULL
GROUP BY p.Id;
GO

CREATE UNIQUE CLUSTERED INDEX cx_PostScoresVotes 
    ON dbo.PostScoresVotes(Id);
GO 

/*Update*/
BEGIN TRAN
    UPDATE p
    SET p.Score += 100
    FROM dbo.Posts AS p
    WHERE p.Id BETWEEN 487278 AND 487279;

/*
Check in on locks in a new window
SELECT *
FROM dbo.WhatsUpLocks(54) AS w
*/

/*Don't you forget about me*/
ROLLBACK



/*Clean up*/
DROP INDEX cx_PostScoresVotes 
    ON dbo.PostScoresVotes
GO 


/*Second view, range locks on update*/
CREATE OR ALTER VIEW dbo.UserPostScore
WITH SCHEMABINDING
AS
SELECT u.Id,
       u.DisplayName,
       SUM(u.Reputation * 1.) AS TotalRep,
       SUM(p.Score) AS TotalScore,
	   COUNT_BIG(*) AS ForSomeReason
FROM dbo.Users AS u
JOIN dbo.Posts AS p
    ON u.Id = p.OwnerUserId
WHERE u.Reputation > 1000
GROUP BY u.Id,
         u.DisplayName;
GO 

CREATE UNIQUE CLUSTERED INDEX cx_ups 
    ON dbo.UserPostScore (Id);
GO

BEGIN TRAN
    UPDATE u
    SET u.Reputation += 100
    FROM dbo.Users AS u
    WHERE u.Id BETWEEN 22656 AND 25657;
/*
Check in on locks in a new window
SELECT *
FROM dbo.WhatsUpLocks(54) AS w
*/

/*Don't you forget about me*/
ROLLBACK

/*Clean up*/
DROP INDEX cx_ups ON dbo.UserPostScore