Jack Corbett (5/13/2008)
GSquared (5/13/2008)
(Your test data doesn't work as written, since you tried to insert the value 1 into your ID field twice, after defining it as the primary key.)Yes, your tests are correct, and are the expected behavior. Why would they not be?
If you set ANSI_PADDING "off", it will do it the way you seem to expect. ANSI_PADDING "on", does it the way your tests show. "On" is the recommended setting. (See "SET ANSI_PADDING" in Books Online for details.)
In addition to the error G, notes, your test is flawed as why would you use like without a wildcard? If that is what I want I will use "=" and that works fine on a variable length character column.
I have to admit I originally thought trailing spaces were trimmed from variable length columns and they are not which does appear to affect Like, but I rarely, if ever, use Like with a leading wildcard and trailing spaces as the leading wildcard in itself will most likely cause the query to not use an any indexes. It is disconcerting that "=" ignores the trailing spaces and "Like" does not, but I have not encountered this because when I use wildcards it is at the end of the statement not the beginning.
That's an interesting read. I'm curious - I have NOT messed with the ANSI_PADDING setting (as in - ever; I steer clear of screwing with the connection and/or db options), and in every database I check - the setting is OFF. And yet - the new default is ON? hmm....
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?