711 reputation
17
bio website
location
age
visits member for 1 year, 7 months
seen Apr 10 at 12:24

Mar
27
comment Is there anyway to make this stored procedure thread safe?
I think you have the best solution - one that works for you : )
Mar
27
comment Is there anyway to make this stored procedure thread safe?
Hi Craig. It's great the READPAST worked for you but you should be aware there is a note in Books Online: "Update or delete operations that use READPAST may block when reading foreign keys or indexed views, or when modifying secondary indexes...". I extended my rig to include foreign keys for areaId and measureId and most definitely get deadlocks using a READPAST-only proc. Again in my rig I don't get any deadlocks whatsoever with the REPEATABLE READ/BEGIN TRAN version but that could just be my test data. As you are doing two operations (two DELETEs) they should really be in a transaction.
Mar
27
comment How to aggregate pairs of id values into groups
That's good news Matt. I did wonder if there might be a simpler way, eg with LAG/LEAD but sometimes it's good to get something working and build on it.
Mar
27
answered How to aggregate pairs of id values into groups
Mar
26
revised Is there anyway to make this stored procedure thread safe?
full test rig added after OP request
Mar
25
answered Is there anyway to make this stored procedure thread safe?
Nov
21
revised Optimizing query on a partitioned table
added 855 characters in body
Nov
21
answered Optimizing query on a partitioned table
Nov
17
answered Keeping an eye on contention
Oct
29
comment Index on Persisted Computed column needs key lookup to get columns in the computed expression
lol @GarethD, maybe next year : )
Oct
29
comment Index on Persisted Computed column needs key lookup to get columns in the computed expression
Thanks @PaulWhite! : )
Oct
28
answered Index on Persisted Computed column needs key lookup to get columns in the computed expression
Oct
26
answered Sql server full text search performance with additional conditions
Oct
25
comment Force spool to avoid creating temp tables?
...but I don't think that's a massive problem.
Oct
25
comment Force spool to avoid creating temp tables?
I can't think of a situation where you absolutely have to put things into a view. It's a design choice that you make. Options would be for example: stored proc, view, batch process to persist resultset to table, indexed view. There are pros and cons for each, eg batch process introduces latency / has to be updated on regular basis but would be quick, indexed views destroy insert/update/delete performance to underlying tables, not compatible with PIVOT etc I can't think of any cons for the stored proc option, other than the one you've mentioned, that they can't be easily in-lined.
Oct
24
answered Force spool to avoid creating temp tables?
Oct
20
answered Query that only uses index takes long time to execute
Oct
19
answered Duration Field in Profiler
Oct
16
awarded  Revival
Oct
15
comment What is a Scalable Storage Mechanism for large TTL data collections
No you can query them as one. It's one of the strengths of partitioning.