27,446 reputation
662114
bio website
location United Kingdom
age
visits member for 2 years, 10 months
seen 6 hours ago

Email: stackoverflowmartinsmith@gmail.com


Jun
12
revised What's the difference between a temp table and table variable in SQL Server?
added 517 characters in body
Jun
12
comment What's the difference between a temp table and table variable in SQL Server?
@Aaron Ah I didn't test that. So sounds like it stores compile time cardinality and if different from execution time cardinality then you get a recompile then?
Jun
12
comment What's the difference between a temp table and table variable in SQL Server?
I think the effective logic is basically if TF 2453 enabled and non trivial plan and table variable has more than zero rows the statement gets a recompile.
Jun
11
comment What's the difference between a temp table and table variable in SQL Server?
@AaronBertrand I see a SQL:StmtRecompile event being fired in Profiler with event subclass "Statistics Changed" and accurate table cardinality. Screenshot. Doesn't seem to work with QUERYTRACEON though.
Jun
11
comment What's the difference between a temp table and table variable in SQL Server?
Or does it auto recompile if X number of rows inserted perhaps?
Jun
11
comment What's the difference between a temp table and table variable in SQL Server?
@AaronBertrand Thanks. I haven't had a chance to look at that yet. Would I be right in thinking it is just effectively working as though the query contains OPTION(RECOMPILE)? The row count was tracked before but didn't help in most cases as the statement was compiled before the rows were added.
Jun
4
comment Does the Inserted table contain Identity values?
Please post the table definition and complete trigger definition.
Jun
4
comment Does the Inserted table contain Identity values?
You might get that error if the insert statement inserted zero rows. Your trigger is broken for inserts of multiple rows too. Try INSERT INTO AuditTerms (TermID, UpdatedAt) SELECT TermId, GetDate() FROM Inserted. If that doesn't help post more code.
Jun
1
comment Why is the ORDER BY clause in a view ignored as soon as it's called with a WHERE claue?
+1 Also relevant TOP 100 Percent ORDER BY Considered Harmful.
Jun
1
comment Identifying Unused Stored Procedures
This will always return all procedures as procedures don't get entries made in the index usage stats DMV...
May
29
revised How can I count different types in a column in sql?
edited tags
May
28
comment SQL Server Deadlock - Two SELECT Exclusive Locks on Exact Same Index and Key
@Charlie Most likely an insert, update, delete, merge. Could also be a select with an xlock hint (the statements you have shown don't have this hint)
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