tag:blogger.com,1999:blog-2328222207349876984.post6005473501491288394..comments2024-03-02T15:36:45.785-08:00Comments on SQL Tact: Actual Emails: Is "NOLOCK is the epitome of evil?"Unknownnoreply@blogger.comBlogger2125tag:blogger.com,1999:blog-2328222207349876984.post-61509198087633187802012-01-24T07:34:04.367-08:002012-01-24T07:34:04.367-08:00Steven-
GREAT example. Thanks for sharing!
-Willi...Steven-<br />GREAT example. Thanks for sharing!<br />-Williamwhttps://www.blogger.com/profile/04313158818403554742noreply@blogger.comtag:blogger.com,1999:blog-2328222207349876984.post-19193220972309313692012-01-24T07:02:05.011-08:002012-01-24T07:02:05.011-08:00In my application, I need to calculate a time inte...In my application, I need to calculate a time interval between adjacent rows. To do this, I use a CTE:<br />WITH PointSampleRows AS <br /> ( SELECT ps.*, ROW_NUMBER() OVER (ORDER BY ps.PointID,UTCDatetime) AS rn FROM [tbl_PointSample] ps join<br /> [tbl_Point] p on ps.PointID = p.PointID) <br />INSERT INTO [analysis].[IntervalHistogram] <br />SELECT mc.PointId<br /> , DATEDIFF(second, mc.UTCDatetime, mp.UTCDatetime)<br /> , count(*)<br /> , CAST(0 as bit)<br />FROM PointSampleRows mc JOIN <br /> PointSampleRows mp <br />ON mc.PointId = mp.PointId <br />and mc.rn = mp.rn - 1 <br />group by mc.PointId<br /> , DATEDIFF(second, mc.UTCDatetime, mp.UTCDatetime);<br />I was concerned that this query running over the entire table will end up getting escalated to a SHARE TABLE lock, locking out concurrent writers to this table, so I ran it with NOLOCK. This query returns utter garbage everytime. SNAPSHOT ISOLATION works great.Steven Liszewskihttps://www.blogger.com/profile/07361079841883264765noreply@blogger.com