27,531 reputation
663114
bio website
location United Kingdom
age
visits member for 2 years, 11 months
seen 1 hour ago

Email: stackoverflowmartinsmith@gmail.com


May
28
comment SQL Server Deadlock - Two SELECT Exclusive Locks on Exact Same Index and Key
Well something presumably is running earlier and acquiring the X locks in the same transaction so you might want to trace the queries sent by the application. A select query without hints won't take an x lock.
May
28
comment SQL Server Deadlock - Two SELECT Exclusive Locks on Exact Same Index and Key
But what else runs in the same transaction? Why are they holding X locks anyway?
May
28
comment SQL Server Deadlock - Two SELECT Exclusive Locks on Exact Same Index and Key
Different keys in the index. Hash values a0acc3c15312 vs b9d486d972a2.
May
28
comment SQL Server Deadlock - Two SELECT Exclusive Locks on Exact Same Index and Key
Can you upload the xdl somewhere?
May
28
comment NOLOCK hint on a table that is 100% reads?
If the table isn't being written to why would anything be holding a blocking lock? The analysis doesn't seem to make sense. How have you come to this conclusion?
May
27
awarded  Nice Answer
May
23
comment Why do we not store names in a separate table?
See Is normalizing a person's name going too far?
May
20
awarded  Notable Question
May
12
comment Is there a better option than Union All for multiple selects from the same row?
Just add a where clause if you don't want the nulls.
May
8
awarded  Enlightened
May
8
awarded  Nice Answer
May
8
comment Why does IN (subquery) perform bad when = (subquery) is blazing fast?
See Why would an IN condition be slower than “=” in sql?
Apr
30
comment best practice for 'staging' relation in stored procedure: use temp table, or view?
I presume you meant to say SQL Server "indexed views" specifically have those limitations but still the list is not correct. You can group by or use count_big.
Apr
30
comment What's the difference between a temp table and table variable in SQL Server?
And SQL Server 2014 may change the disc write behaviour of the final section of this answer too. blogs.msdn.com/b/psssql/archive/2014/04/09/…. Not in a position to test that at the moment.
Apr
30
comment Using wildcards in a like statement on an unindexed VARCHAR(MAX) column with more than 1 million records
Knowing that there is over 1 million rows doesn't really help. Each varchar(max) value can be anything up to 2 gb so the impact could be anything from trivial to scanning 2 petabytes of data.
Apr
29
comment '+' operator with one operand!
See connect.microsoft.com/SQLServer/feedback/details/718176/…
Apr
16
awarded  Enlightened
Apr
16
awarded  Nice Answer
Apr
3
comment Phantom insert query
That looks like an Entity Framework generated parameterised query possibly. (IIRC that uses the @@rowcount pattern shown) You could create an insert trigger on the table and log details such as application name, host name etc to help narrow the search down.
Mar
30
comment For SQL Server, What is the proper way to update a table?
Also look into recovery models, backups, and point in time recovery.