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

Email: stackoverflowmartinsmith@gmail.com


4h
comment how to order a field based on two parts of the row value
What DBMS. Why isn't this two separate columns?
6h
comment Why does a UNIQUE constraint allow only one NULL?
Allowing a single null doesn't achieve this goal either. Because the missing value might turn out to be the same as the value in one of the other rows.
12h
comment Strange query results for DELETE with ROW_NUMBER
There's nothing unsafe or un set based about deleting with a window function. Often it is the most efficient way. The semantics of the delete operation are fine here. The only issue is the unexpected re-application of the row numbering when computing the output result.
12h
comment Strange query results for DELETE with ROW_NUMBER
But you might as well suggest keeping the original code and removing the output clause as a "workaround" then. The only issue that needs working around is the unexpected result of that.
22h
comment SQL-Server 2012: Same query takes different Query Plans - but why?
Can you upload the plans' XML somewhere? Possibly parameter sniffing.
23h
comment Strange query results for DELETE with ROW_NUMBER
@MaxVernon - Why? Do you have a good general workaround? For a workaround in this specific case it is always guaranteed to delete all except one row in the table so changing the output clause to OUTPUT Deleted.name, 1 + Deleted.r AS r would work. For more complicated WHERE clauses with less predictable behaviour I can't think of a "nice" work around.
23h
comment How many LEFT JOINS make a VIEW unusable?
Related question on SO. Joining 100 tables
1d
comment Why does a UNIQUE constraint allow only one NULL?
@supercat (regarding your guarantee expectation) Other types of constraint in SQL such as FK and check constraints also count nulls as passing. The check constraint x > 4 is only violated if false, not unknown.
2d
comment What's the term that describes filtering query that makes use of indexes
@a_horse_with_no_name - It doesn't rewrite the query but it matches the computed column during query compilation.
Oct
15
comment Trying to return empty rows even if data does not exsist in SQL
user_id in (users) is not valid SQL. (unless that is the name of a column in which case the semantics are probably wrong) What is users representing there?
Oct
15
comment Extracting a field from RESTORE HEADERONLY in sql server
@TimAbell - Yes I don't think I would actually use this in practice except maybe to get the table definition in the first place.
Oct
14
comment Why can't I obtain OBJECT_NAME() for a temporary table?
db_id('tempdb') is always 2 AFAIK.
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
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.
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
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
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?