27,037 reputation
660112
bio website
location United Kingdom
age
visits member for 2 years, 9 months
seen 17 hours ago

Email: stackoverflowmartinsmith@gmail.com


1d
comment Finding last time table was updated?
@SeanPerkins - See edited version
Jul
16
comment Is it possible for SQL Server to miss rows on a select * where PK = “value” query
@BrentOzar Craig Freedman has a similar demo for read committed here but don't think you could get a PK to return null with this type of issue.
Jul
13
comment Adding warning with sp_add_alert doesn't work with “Greater Than” and SQL Server 2014
I don't have 2014 installed at the moment. Does msdb.sys.sp_helptext 'dbo.sp_verify_performance_condition' shed any light on it? The relevant section in 2012 looks like this i.stack.imgur.com/W0rX6.png
Jul
13
comment Get the first and last names of all employee who entered the project at the same time as at least one other employee
Please provide the definition of the tables involved. It is not possible to answer this question at the moment unless we guess the existence of an EmployeeID column (and the name of it) in both tables.
Jul
11
comment Why a select from the tables, joined by a CASE statement, could be 7x slower?
What are your table definitions and execution plans? You mention indexes but your sample DDL doesn't contain any.
Jul
8
comment How to update ID=null values in table to incremental counter values?
@ErikHart - Er. What? It doesn't look like (or perform anything like) a cursor...
Jun
30
comment Join reorders results
And in SQL Server 2012 you'll find that even the order you observed without the join will likely no longer appear.
Jun
30
comment Is select * still a big no-no on SQL Server 2012?
An objection to * even if you need all the columns now is that later someone may add extra columns. Then your application will be bringing back a load of extra columns that aren't needed. And yes that issue still exists obviously.
Jun
29
comment Why should a CTE start with a semi-colon?
And sometimes the semi colon prefix breaks stuff
Jun
29
comment SQL Server: Are there any performance benefits from indexing a table <8kb in size?
@BIMonkey - Yes if both the non covering non clustered index and the base table itself (CI or heap) both fit on a single page you'd need to factor in the cost of another page read that would probably outweigh the benefit of binary seek. For tables this small it is exceedingly likely to matter anyway. There are always bigger fish to fry than this!
Jun
25
comment When to use TINYINT over INT?
@GeorgeShouse Demonstration of that here
Jun
23
comment Changing Databases Dynamically using Variables
Related SO question. stackoverflow.com/q/727788/73226
Jun
23
comment Changing Databases Dynamically using Variables
@Max No. The scope gets reset when the exec finishes. So you end up back where you started. To use this approach all your remaining code would also need to be appended after the use.
Jun
22
comment Reporting Services buttons show up one under the other instead of side-by-side
Looks like you need to install up to at least SP1 CU8 or hack around with some files
Jun
20
comment Improve query performance when selecting almost all rows with many “group by” columns
Also, you haven't told us anything about what is consuming these rows. Are they being sent over the network? If running in SSMS how long does it take if you enable the "discard results" option?
Jun
19
comment What's the difference between a temp table and table variable in SQL Server?
Found it from @PaulWhite here sqlblog.com/blogs/paul_white/archive/2012/08/15/… There is another condition not listed above. If the table is empty at compilation time, RT is set to 1.
Jun
19
comment What's the difference between a temp table and table variable in SQL Server?
@AaronBertrand I'm sure I read about this before somewhere but I don't see it in either of the plan caching white papers.
Jun
19
comment What's the difference between a temp table and table variable in SQL Server?
@AaronBertrand Yes, agree that I missed the n=6 one. I do definitely see that if n=0 even the addition of a single row causes a statement level recompile and an adjustment to the TableCardinality shown in the plan though.
Jun
17
comment Sequence is reusing
@AaronBertrand And the Connect Item doesn't have many votes so presumably can't be a common issue.
Jun
16
comment Sequence is reusing
What is @@VERSION? Also has anything changed about the environment? There is a connect item reporting something similar. The OP there reckons it was associated with 11.0.3000.0