28,280 reputation
771121
bio website
location United Kingdom
age
visits member for 3 years
seen 11 hours ago

Email: stackoverflowmartinsmith@gmail.com


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.
Sep
24
awarded  Autobiographer
Sep
23
comment Inserting variables into another variable
Is that your actual code? I don't see any initialisation of @counter
Sep
23
revised Inserting variables into another variable
added 147 characters in body
Sep
20
awarded  Enlightened
Sep
20
revised How to resolve stored procedure output parameter error?
added 96 characters in body
Sep
19
comment Tsql Query speed slow due to Or within where clause, causing index scan instead of seek
What version of SQL Server?
Sep
19
comment Why does a simple select query produce a deadlock?
@Shanky - I prefer the code here for the reasons here
Sep
17
comment What is the logic behind ISNUMERIC for certain special characters?
@AaronBertrand There seems to be a reasonably significant number of cases where it does not even meet that intention too.
Sep
17
comment What is the logic behind ISNUMERIC for certain special characters?
Though from NCHAR(0) - NCHAR(65535) I see 112 discrepancies. Including characters such as ₁,₂,₃,4,5,6,7,8,9 which look numeric but don't cast successfully to anything for me. Fiddle