27,122 reputation
660112
bio website
location United Kingdom
age
visits member for 2 years, 10 months
seen 10 hours ago

Email: stackoverflowmartinsmith@gmail.com


Jun
17
awarded  Generalist
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
Jun
16
comment Rejoining range seek on nullable composite index?
Thanks. That's a new one for me!
Jun
16
accepted Rejoining range seek on nullable composite index?
Jun
15
comment Rejoining range seek on nullable composite index?
@ypercube - Did you see the extension to my question? I think you may be describing the same thing? In which case simpler syntax, simpler looking plan superficially with a single seek operator but actually that seek operator still ends up performing two separate seeks to retrieve the A =@A AND B>@B then the A > @A so that makes me think it won't ever actually give me the plan I originally wanted with a single seek and a contiguous scan.
Jun
15
revised Table Data Space Used Confusion
edited tags
Jun
15
comment Table Data Space Used Confusion
Heaps don't get logical fragmentation. You were probably hitting the issue here. I suppose that might be regarded as internal fragmentation. I have no idea what random inserts in a heap are supposed to be. They always just go where there is space.
Jun
15
revised Rejoining range seek on nullable composite index?
added 589 characters in body
Jun
15
reviewed Close Reducing log files?
Jun
15
awarded  Custodian
Jun
15
reviewed Reopen “Replicate” Table structures with sp_addscriptexec
Jun
15
comment Rejoining range seek on nullable composite index?
@ypercube - The plans are simpler as it chops off the seek and corresponding merge union that will never return anything for that case. Still 1020 reads though. Maybe that's as good as it will get and there is no SQL that will return the optimum plan.
Jun
15
comment Rejoining range seek on nullable composite index?
@ypercube - Looks worse as 1523 reads and it no longer leverages the index order and has an explicit sort. Frustrating because all I want it to do is seek into (NULL, 1000 ) follow the index along and chuck out the next 1,000 rows in index order but doesn't seem easy!
Jun
15
comment Rejoining range seek on nullable composite index?
@ypercube - SQL Server just gives an ordered scan for that unfortunately so re-reads all the rows already processed by the application (logical reads 2015). It doesn't seek into to the first key of (NULL, 1000 )
Jun
15
comment Rejoining range seek on nullable composite index?
SQL Fiddle
Jun
15
comment Rejoining range seek on nullable composite index?
Yes Oracle is different I believe.
Jun
14
asked Rejoining range seek on nullable composite index?
Jun
12
awarded  Nice Answer
Jun
12
comment SQL Server large transaction log for simple recovery model? help!
Have you tried shrinking that file specifically with DBCC SHRINKFILE? Also why do you care about the size of the log file anyway if you have plenty of disc space? If it has to regrow to that size shrinking will just waste resources as the growth involves zeroing out previous disc contents.