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

Email: stackoverflowmartinsmith@gmail.com


Oct
20
comment Why does a UNIQUE constraint allow only one NULL?
@GreenstoneWalker - It doesn't take the "safe" route. It assumes that the missing value won't conflict. CREATE TABLE #T(A INT NULL UNIQUE);INSERT INTO #T VALUES (1),(NULL);UPDATE #T SET A = 1 WHERE A IS NULL; will raise an error. According to your theory of the design motivations it ought to have prevented the insertion of NULL in the first case - because the incomplete knowledge means there is no guarantee that the value is different.
Oct
20
comment how to order a field based on two parts of the row value
What DBMS. Why isn't this two separate columns?
Oct
20
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.
Oct
19
revised Strange query results for DELETE with ROW_NUMBER
added 343 characters in body
Oct
19
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.
Oct
19
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.
Oct
19
awarded  Enlightened
Oct
19
awarded  Nice Answer
Oct
19
revised Strange query results for DELETE with ROW_NUMBER
added 779 characters in body
Oct
19
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.
Oct
19
comment How many LEFT JOINS make a VIEW unusable?
Related question on SO. Joining 100 tables
Oct
18
revised Strange query results for DELETE with ROW_NUMBER
added 202 characters in body
Oct
18
answered Strange query results for DELETE with ROW_NUMBER
Oct
18
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.
Oct
17
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
17
revised Performance first low, then high after adding index, then keep high after drop index
edited tags
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
revised Trying to return empty rows even if data does not exsist in SQL
added 24 characters in body
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.