TrimTrailingBlanks in SQL Server

Question

In sp_help documentation doesn't says much about the property TrimTrailingBlanks of a table:

TrimTrailingBlanks | varchar(35) | Trim the trailing blanks. Returns Yes or No.

so I'm thinking if is good practice to turn it on or if I should let it's default off. What I want to do is to delete blanks from left and right strings, and (by the name) is what this property enables.

Now, do you know if is good practice to turn this property on?

I know I can do the trailling in my application code, but it will be very nice to have this done automatically in the database before any insert or update. I don't know if I will have any drawback if I change the value No by Yes.

Answer 1

I would trim spaces in code.

You should think twice before altering this setting. TrimTrailingBlanks=No, is the effect of the setting ANSI_PADDING being ON. ANSI_PADDING is on by default for connection from most clients (DB-Library being the exception.) Furthermore, ANSI_PADDING must be on when you are using indexed views and indexed computer columns. There may be further situations where ANSI_PADDING is needed in future versions of SQL Server.

That said, ALTER TABLE is what you can use:

SET ANSI_PADDING OFF
go
CREATE TABLE trimtrailer(a varchar(12) NOT NULL)
go
INSERT trimtrailer(a) VALUES ('Trimmer ')
go
SET ANSI_PADDING ON
go
ALTER TABLE trimtrailer ALTER COLUMN a varchar(12) NOT NULL
go
INSERT trimtrailer(a) VALUES ('Trimmer ')
go
SELECT '<' + a + '>' FROM trimtrailer
go
DROP TABLE trimtrailer

From Erland Sommarskog

Answer 2

The SET ANSI_PADDING setting will be deprecated (and always be "ON" in later versions of SQL Server). I read something the other day that this happens in the next version (after R2?)

We set it in code because of this (a simple RTRIM, we do it in SQL because we have multiple clients).

Answer 3

This has to do with how trailing blanks are handled. It doesn't have anything to do with leading blanks. It has to do with whether fixed-length strings are padded with spaces out to their defined length or not.

For details, see SET ANSI_PADDING, which has a nice table of the effects. The sp_help procedure is reporting on the settings for those columns at their time of definition.

To be safe, you should always clean up your data before you store it, whether it has to do with padding or trimming, setting to a specific case, or whatever. You never know what a database upgrade will change.