28,719 reputation
771121
bio website
location United Kingdom
age
visits member for 3 years, 1 month
seen 45 mins ago

Email: stackoverflowmartinsmith@gmail.com


Oct
12
comment SQL Server stuck in single_user with auto_update_statistics_async on
FWIW I tried reproducing this situation and couldn't get the database locked out with both options set and running queries which would trigger auto update stats on Microsoft SQL Server 2012 - 11.0.5058.0.
Oct
12
comment Does tempDB disk perf matter if you have plenty of unused memory?
There are some changes in SQL Server 2014 of relevance blogs.msdn.com/b/psssql/archive/2014/04/09/…
Oct
5
awarded  Enlightened
Oct
5
awarded  Nice Answer
Oct
3
comment Non clustered index on multiple columns
Even without that the key length is still potentially over 900 bytes. (3 * 255 = 765) but nvarchar is 2 bytes per character so double that.
Oct
3
comment In SQL Server what is the database cost for setting a default value on table that already has over 1mil records?
This doesn't apply to defaults. They aren't checking anything.
Oct
3
answered In SQL Server what is the database cost for setting a default value on table that already has over 1mil records?
Oct
2
awarded  Yearling
Sep
30
awarded  Explainer
Sep
30
awarded  Good Answer
Sep
29
comment Converting a Unicode value to a non-Unicode value (SQL Server)
Just reading this page and remembered this. Not sure if SQL Server uses exact same "best fit" algorithms.
Sep
28
revised Slow running aggregate of aggregate query
added 19 characters in body
Sep
28
answered Slow running aggregate of aggregate query
Sep
28
comment Slow running aggregate of aggregate query
Actually one other thing you could also try would be to create a calculated column on post_year_month and use that as the leading column on the index instead of post_date. Then the query to extract a relevant month would be a straight equality seek. And the matching rows would be ordered by department_id,location_id,account_id so could do a merge join without a sort when comparing to the temp table. Also can you just calculate this at the end of each month and store it. Or might rows be updated later or historic rows inserted at a later date?
Sep
28
revised Slow running aggregate of aggregate query
Added info from comments to question
Sep
26
comment Slow running aggregate of aggregate query
Assuming the actual query you want optimising is the 30 minute one I'd probably consider working through it in an iterative way a month at a time from the start and making sure you have an index with post_date,department_id,location_id,account_id INCLUDE (amount) You'd need to maintain a temp table with clustered PK on department_id,location_id,account_id and a balance_to_date column. Then extract a month at a time and merge into that table. And calculate your monthly total for department_id, location_id
Sep
26
comment Slow running aggregate of aggregate query
How many distinct dept,location combinations and dept,location,account combinations?
Sep
26
comment Slow running aggregate of aggregate query
How many rows in the table? How long does the query take?
Sep
26
comment How I convert it to SQL Server please
@hasnbatal use ItemId then.
Sep
26
comment How does SQL Server determine precision / scale?
This doesn't always help though. Both (1+1) and 2 are of type int but this question has an example where they end up producing a differently typed result.