Matt Campbell (7/31/2009)

---

I take no chances when it comes to SQL Server and trailing spaces. I always use LTRIM(RTRIM(col_name))when selecting data or updating data if the field is any kind of string-holder, and I do so on left- and right-hand side comparison clauses too. Basically, anywhere I refer to a table field that is a string container, it always gets this kind of treatment. It adds overhead of course to the query but unless there is a critical timing issue (and there oughtn't be if you wrote the app right), using this "Kill 'em all let God sort 'em out" approach has never failed me.

I also always Trim() string values from ADO recordset fields to be doubly-sure. Just because I am paranoid doesn't mean I'm not right!

Jack Corbett (7/31/2009)

---

Matt,

The only problem with LTRIM(RTRIM(column)) in comparison (WHERE or JOIN) clauses it that you no longer give the optimizer the option to use an index seek, the best it can do it scan as it HAS to evaluate every row using the function. And, as the chart shows, for equality/inequality that is unnecessary.

Certainly using it when inserting/updating a value is okay, although, in my opinion, the UI/business layer should clean this up.

Jeff Moden (9/13/2008)

---

Heh... I wish MS would stop deprecating useful things.

Ol'SureHand (8/3/2009)

---

Guys, can we do anything about it? Like write to Microsoft or something?

If ANSI_PADDING OFF is deprecated, and the ON becomes the only setting, I reckon that eliminates the difference between CHAR and VARCHAR.
Why bother having 2 data types that behave the same way and use the same amount of space. . . oh, that's not so, in the case of VARCHAR, it will use 2 extra bytes for the length!!!!!