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

Email: stackoverflowmartinsmith@gmail.com


Jul
21
comment Why am I getting `Impossible WHERE noticed after reading const tables` in explain query?
You can't have row s that do. There is a unique constraint on (fromid,toid) so there would be a maximum of one surely? And from the message you say you are getting MySQL doesn't think that there is even one that does. Do you mean that you have some rows that match fromid=60 and some rows that match toid=78 but not necessarily the same rows?
Jul
20
answered Why am I getting `Impossible WHERE noticed after reading const tables` in explain query?
Jul
20
comment Why do wildcards in GROUP BY statements not work?
@ypercube - As far as I've seen if you group by A.PK, A.some, A.other, A.columns it doesn't bother actually comparing some, other, columns this is just required by the syntax.
Jul
20
comment Why am I getting `Impossible WHERE noticed after reading const tables` in explain query?
What is the table definition including datatypes? Your second set of queries are not equivalent BTW. And has higher precedence than Or so the equivalent parenthesised version is WHERE (fromid=60 and toid=78 AND is_approved='s') OR is_approved='f' OR is_approved='t'
Jul
19
comment can sql generate a good plan for this procedure?
In this case both branches are likely to get a trivial plan anyway assuming myname is found by a unique index. Statement level recompile only helps if the statement is subject to a deferred compile or a recompile though. Otherwise all statements will be compiled according to the parameter values in the first invocation even if that statement was not actually executed.
Jul
19
comment Should I add SET NOCOUNT ON to all my triggers?
If you ever edit values via the SSMS table editor this is required for triggers otherwise the extra row count message from the trigger confuses it
Jul
19
awarded  Enlightened
Jul
19
awarded  Nice Answer
Jul
18
comment Primary key should be chosen so as attributes never change?
One possible reason. If the PK is being used as a foreign key then updating the PK also means the change has to be propagated in multiple places. 5 desirable criteria are listed here. Not sure what the source of that list is though.
Jul
17
comment Does a cluster index provide more benefits than pre-sorting the load file and creating non-cluster index?
@FrankComputer - I agree. This happened to me before and I ended up getting them refunded a few days later I assume some script must run periodically. So maybe find another of Jonathan's answers to reward if and when that happens?
Jul
17
comment Does a cluster index provide more benefits than pre-sorting the load file and creating non-cluster index?
@JonathanLeffler - Only one of the people that voted to close on SO even has an account here and that is inactive so doubt they care about "drumming up business" for the site.
Jul
12
comment SQL Server : where with cast(value as varbinary) vs cast(value as varbinary(300))
If the column is indexed where value = @p AND cast(value as varbinary(x)) = cast(@p as varbinary(x)); would probably be better as at least that can use the index then discard those that aren't an exact binary match.
Jul
12
comment SQL Server : where with cast(value as varbinary) vs cast(value as varbinary(300))
cast(value as varbinary) will truncate the value to 30 bytes. You should avoid any functions on columns including casts if you care about performance as it almost always makes the expression unsargable. Why do you need to do this?
Jul
12
comment Slow deletion of records when a trigger is enabled
Ah, I asked a question about this on SO sometime ago and never got a definitive answer.
Jul
11
comment CASE statement with IS NULL and NOT NULL
SQL Server 2012 has IIF but the question is tagged 2005.
Jul
10
comment Why am I getting Table scan on a partitioned table query?
No point in having the NCI on IR_ID then AFAIK. It can already tell which partitions contain a particular value from the partitioning metadata and within each partition all rows in the index will have the same value.
Jul
10
comment Why am I getting Table scan on a partitioned table query?
As IR_ID is the partitioning column it can just look at the three partitions of interest. Do any of the partitions contain more than one IR_ID value?
Jul
10
answered can access to a view be controlled by access to a referenced table?
Jul
10
comment SQL Server query slow when paginated
Well deleting the index entirely is one way of preventing it from being used I guess!
Jul
8
revised Does SQL Server read all of a COALESCE function even if the first argument is not NULL?
added 9 characters in body