SQLServerCentral / Article Discussions / Article Discussions by Author / Discuss content posted by Jack Corbett / ANSI PADDING, Trailing Whitespace, and Variable Length Character Colum / Latest Posts

RE: ANSI PADDING, Trailing Whitespace, and Variable Length Character Colum

Jack Corbett — Mon, 03 Aug 2009 07:00:08 GMT

[quote][b]Ol'SureHand (8/3/2009)[/b][hr][quote][b]Jeff Moden (9/13/2008)[/b][hr][quote][b]Jack Corbett (9/13/2008)[/b][hr][Jack said:] [ANSI_PADDING] is turned off by default at the Database level, which is odd considering the ability to turn it off is going to be deprecated. Oh well, who said MS had to be consistent?[/quote]Heh... I wish MS would stop deprecating useful things.[/quote]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!!!!![/quote]The best option is [url=http://connect.microsoft.com]CONNECT[/url]. MS takes [url=http://connect.microsoft.com]CONNECT[/url] seriously especially if you can get others to vote for it.

RE: ANSI PADDING, Trailing Whitespace, and Variable Length Character Colum

Jeff Moden — Mon, 03 Aug 2009 02:59:52 GMT

It's not quite that bad... with ANSI PADDING ON, VARCHAR can have trailing spaces if they've been assigned. It won't automatically pad spaces to the total width of the column. I can live with that... I just worry about others that can't. It would be like them setting ANSI NULLS to OFF permanently... that would absolutely kill a lot of my code where I depend on NULL being treated for what it is... unknown.I suspect there's not much we can do.

RE: ANSI PADDING, Trailing Whitespace, and Variable Length Character Colum

Ol'SureHand — Mon, 03 Aug 2009 00:38:24 GMT

[quote][b]Jeff Moden (9/13/2008)[/b][hr][quote][b]Jack Corbett (9/13/2008)[/b][hr][Jack said:] [ANSI_PADDING] is turned off by default at the Database level, which is odd considering the ability to turn it off is going to be deprecated. Oh well, who said MS had to be consistent?[/quote]Heh... I wish MS would stop deprecating useful things.[/quote]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!!!!!

RE: ANSI PADDING, Trailing Whitespace, and Variable Length Character Colum

Ol'SureHand — Mon, 03 Aug 2009 00:29:24 GMT

[quote][b]Jack Corbett (7/31/2009)[/b][hr]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.[/quote]Fully agree! I have to issue these warnings to all entusiastic developers who rush into using functions and "clever" UDFs and end up peppering the WHERE clause with such stuff that almost kills the server...And Thank You Jack once again for taking the trouble and being so thorough.

RE: ANSI PADDING, Trailing Whitespace, and Variable Length Character Colum

Jeff Moden — Fri, 31 Jul 2009 10:46:10 GMT

[quote][b]Matt Campbell (7/31/2009)[/b][hr]I take no chances when it comes to SQL Server and trailing spaces. I always use [b]LTRIM(RTRIM([/b][i]col_name[/i][b]))[/b]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! :-)[/quote] Heh... and as Jack points out, that pretty much eliminates any chance at real peformance if the proper indexes are available. I'd suggest a different approach in the future.

RE: ANSI PADDING, Trailing Whitespace, and Variable Length Character Colum

Jack Corbett — Fri, 31 Jul 2009 09:53:04 GMT

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.

RE: ANSI PADDING, Trailing Whitespace, and Variable Length Character Colum

Matt Campbell-473914 — Fri, 31 Jul 2009 09:35:26 GMT

I take no chances when it comes to SQL Server and trailing spaces. I always use [b]LTRIM(RTRIM([/b][i]col_name[/i][b]))[/b]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! :-)

RE: ANSI PADDING, Trailing Whitespace, and Variable Length Character Colum

Jack Corbett — Thu, 30 Jul 2009 17:08:18 GMT

No problem. You made me curious so I had to do something.

RE: ANSI PADDING, Trailing Whitespace, and Variable Length Character Colum

stephen.hendricks — Thu, 30 Jul 2009 16:10:35 GMT

Thanks for your efforts. I appreciate how generous you have been with your time; and for free, no less.

RE: ANSI PADDING, Trailing Whitespace, and Variable Length Character Colum

Jack Corbett — Thu, 30 Jul 2009 15:44:51 GMT

Hey, I ran a Profile trace and then scripted out a table definition as that gets you the settings. The setting for ANSI_PADDING is in sys.all_columns is_ansi_padded.

RE: ANSI PADDING, Trailing Whitespace, and Variable Length Character Colum

stephen.hendricks — Thu, 30 Jul 2009 15:20:14 GMT

Allow me to clarify what I am looking for.Setting or resetting the ANSI_PADDING value of the connection is simple enough, as you have pointed out.For replication, it is necessary for the tables being replicated to conform to the same collection of SET options as for Indexed Views. As I'm sure you already know, these are:ANSI_NULLS ONANSI_PADDING ONANSI_WARNINGS ONARITHABORT ONCONCAT_NULL_YIELDS_NULL ONQUOTED_IDENTIFIER ONNUMERIC_ROUNDABORT OFFThese settings are associated with the table based on their values at the time the table was created.Since our existing tables don't always conform to these settings, we have to take the steps, as part of the Snapshot, to manually modify the .sch files to correct and variances before applying the Snapshot to the subscriber.Conceptually, what I'd like to know is, "Which existing tables don't have these settings correctly configured?"sys.tables.uses_ansi_nulls does show me the settings for ANSI_NULLS. (So far, so good)ObjectProperty() does show me the settings for ANSI_NULLS and QUOTED IDENTIFIER. (Hey, even better! But not all the way there.)What I want is visibility into the other five settings (ANSI PADDING being my immediate concern) so that I, the user, can determine if I'm going to run into trouble when I replicate the table. SQL Server clearly has this information somewhere since it complains if the setting is wrong. Getting to this information is my quest.The long term goal is to determine if it is worth while to re-configure any particular table to have the proper settings. Knowing the current configuration would be the first step in this process.

RE: ANSI PADDING, Trailing Whitespace, and Variable Length Character Colum

Jack Corbett — Thu, 30 Jul 2009 12:18:36 GMT

What do you mean by user? The ANSI_PADDING setting is one of the connection string settings and with SSMS and ADO.NET it defaults to ON. In SSMS you can make a global change through Tools->Options->Query Execution->SQL Server->ANSI or by individual query session under Query->Query Options->Execution-ANSI. OR you can just do SET ANSI_PADDING ON/OFF statement.Using ADO.NET you need to execute a SET ANSI_PADDING OFF as part of the application.

RE: ANSI PADDING, Trailing Whitespace, and Variable Length Character Colum

stephen.hendricks — Thu, 30 Jul 2009 10:27:53 GMT

Thanks for the reply.I assumed that the sys.tables.uses_ansi_nulls represented the SET ANSI_NULLS setting. BOL seems to indicate that it is. I just can't find any place where the ANSI_PADDING information is presented to the user.The odd thing is that replication is complaining about this setting not being correct on my table but the table does not contain ANY character data.; only INT, SMALLINT, BIT, MONEY and SMALLDATETIME.

RE: ANSI PADDING, Trailing Whitespace, and Variable Length Character Colum

Jack Corbett — Wed, 29 Jul 2009 19:03:18 GMT

At least in SQL Server 2008 (I assume 2005 as well) you can query sys.tables and check the uses_ansi_nulls column 1 is ON, 0 is Off. I don't have any 2000 servers available to me at this time so I don't know how you'd find it there, but I assume it is possible.

RE: ANSI PADDING, Trailing Whitespace, and Variable Length Character Colum

stephen.hendricks — Wed, 29 Jul 2009 17:49:58 GMT

Useful information; thank-you.What I am trying to determine, after running into various ANSI_PADDING errors in replication, is which tables currently have the ANSI_PADDING set on. I'd like to be able to predict how each table will behave and take proactive steps to insure that they will.

RE: ANSI PADDING, Trailing Whitespace, and Variable Length Character Colum

BILL SHERMAN — Mon, 15 Sep 2008 15:07:06 GMT

[quote][b]Jack Corbett (9/12/2008)[/b][hr]Bill,I'm not sure what you are exactly asking. Are you mentioning that if you just run the Selects you have listed in SSMS you get the same results? When I run:[code]Select 'A' + 'A', 'A ' + 'A'[/code] I get:[code]no_space one_space-------- ---------AA A A[/code]I would guess that is because the QP is treating the literals as CHAR not VARCHAR.Also SSMS connects to the server with ANSI_PADDING ON in the settings by default, you have to explicitly set it to off.[/quote]I was just trying to recreate your results but no matter what I do in SQL80 QA when run against a SQL90 DB instance I alway get the same padded result. Manipulating ANSI_PADDING at the session level appears to be ignored.[code]Select 'A' + 'A', 'A ' + 'A'[/code] no_space one_space IS ALWAYSTHE RESULT -------- ---------AA A A

RE: ANSI PADDING, Trailing Whitespace, and Variable Length Character Colum

Jack Corbett — Mon, 15 Sep 2008 08:48:22 GMT

Yes a true TRIM function would be nice. I actually had an issue recently when querying FoxPro data in SSIS around trimming. FoxPro has a 4 functions for trimming: LTRIM, RTRIM, TRIM, ALLTRIM. I assumed the TRIM function did RTRIM(LTRIM()), but it doesn't it only does RTRIM. So I was not matching on some data in a lookup because of LEADING spaces the I thought TRIM had removed!

RE: ANSI PADDING, Trailing Whitespace, and Variable Length Character Colum

Wayne West — Mon, 15 Sep 2008 08:41:10 GMT

This has always been one of my biggest frustrations in SQL Server. I don't like having to trim my fields when I'm concatenating other fields, but I accept it. I just wish SQL Server had a Trim() function like Access that is essentially Ltrim(Rtrim([x])). I've considered writing a UDF for it and throwing it in Master & Model, just never bothered.

RE: ANSI PADDING, Trailing Whitespace, and Variable Length Character Colum

Jack Corbett — Mon, 15 Sep 2008 05:14:11 GMT

Thanks.

RE: ANSI PADDING, Trailing Whitespace, and Variable Length Character Colum

Anirban Paul — Mon, 15 Sep 2008 01:06:41 GMT

Excellent Article...

RE: ANSI PADDING, Trailing Whitespace, and Variable Length Character Colum

Jeff Moden — Sat, 13 Sep 2008 23:53:49 GMT

[quote][b]Jack Corbett (9/13/2008)[/b][hr]Thanks, Jeff. I actually put a lot more time into the code than the article. Yeah, I know that it is turned off by default at the Database level, which is odd considering the ability to turn it off is goign to be deprecated. Oh well, who said MS had to be consistent?[/quote]Heh... I wish MS would stop deprecating useful things.

RE: ANSI PADDING, Trailing Whitespace, and Variable Length Character Colum

Jack Corbett — Sat, 13 Sep 2008 22:21:23 GMT

Thanks, Jeff. I actually put a lot more time into the code than the article. Yeah, I know that it is turned off by default at the Database level, which is odd considering the ability to turn it off is goign to be deprecated. Oh well, who said MS had to be consistent?

RE: ANSI PADDING, Trailing Whitespace, and Variable Length Character Colum

Jeff Moden — Sat, 13 Sep 2008 17:26:19 GMT

Nice article, Jack... good code example, too!On the subject of "Why the hell would they do that?", I've found that Microsoft set's the default for databases to ANSI_PADDING OFF using the SELECT DATABASEPROPERTYEX('dbname','IsAnsiPaddingEnabled') code snippet. It's QA and SMS that have it turned on in the connection properties of each.

RE: ANSI PADDING, Trailing Whitespace, and Variable Length Character Colum

Jack Corbett — Fri, 12 Sep 2008 16:30:02 GMT

Bill,I'm not sure what you are exactly asking. Are you mentioning that if you just run the Selects you have listed in SSMS you get the same results? When I run:[code]Select 'A' + 'A', 'A ' + 'A'[/code] I get:[code]no_space one_space-------- ---------AA A A[/code]I would guess that is because the QP is treating the literals as CHAR not VARCHAR.Also SSMS connects to the server with ANSI_PADDING ON in the settings by default, you have to explicitly set it to off.

RE: ANSI PADDING, Trailing Whitespace, and Variable Length Character Colum

BILL SHERMAN — Fri, 12 Sep 2008 15:37:58 GMT

Then why is it that regardless of session or db settings SQL90 returns "A A" for select 'A' + 'A' and select 'A ' + 'A'Is that because theres still a "table" involved albeit a temporary one and the "settings at creation" rule is being applied?

RE: ANSI PADDING, Trailing Whitespace, and Variable Length Character Colum

Carla Wilson-484785 — Fri, 12 Sep 2008 11:02:27 GMT

Tobar and Jack,Thanks for your comments. I just wanted to make sure that there wasn't something I should have been doing instead of using the RTrim function. I am always looking to improve my code and my knowledge of SQL. (These articles and discussions are great!)

RE: ANSI PADDING, Trailing Whitespace, and Variable Length Character Colum

Jack Corbett — Fri, 12 Sep 2008 10:33:53 GMT

Thanks for the positive comments Carla, glad it was helpful. If you really want to turn it off you could actually make the change in the advanced properties of the connection. The biggest issue is that MS is going to deprecate the SET ANSI_PADDING statement so using that will eventually break your code.

RE: ANSI PADDING, Trailing Whitespace, and Variable Length Character Colum

Tobar — Fri, 12 Sep 2008 10:31:42 GMT

From what I have seen ""Set ANSI_PADDING OFF" somewhere in the Control Flow" will have no effect. The setting is a "table creation" parameter, if you will, in that it depends what is in effect when the table is created, not when the table is populated.

RE: ANSI PADDING, Trailing Whitespace, and Variable Length Character Colum

Carla Wilson-484785 — Fri, 12 Sep 2008 09:52:04 GMT

I just ran into this problem in SSIS this week! In my Data Flow, I had to convert varchar columns to be used in nvarchar columns in the final destination table, and apparently this introduced blanks. I solved it by add RTrim() to the conversion, but now I wonder if it would be better to use "Set ANSI_PADDING OFF" somewhere in the Control Flow?p.s. - Jack, thanks for the summary and the excellent script to demonstrate the variations!

RE: ANSI PADDING, Trailing Whitespace, and Variable Length Character Colum

Jack Corbett — Fri, 12 Sep 2008 08:26:54 GMT

[quote][b]Reginald J Ray Jr (9/12/2008)[/b][hr]I'm confused. I get the same results from 'select * from duh' for both settings of ANSI_PADDING. set ANSI_PADDING offgocreate table duh ( col1 varchar(50))insert into duh select 'a ' + 'a'select * from duhdrop table duhjack rayjray@validata.org[/quote]Okay, maybe I was not clear. The ANSI_PADDING setting is based on the connection setting. So if a table is created with ANSI_PADDING OFF then inserts and updates to varchar columns in that table will not have include the trailing spaces while nvarchar columns will take on the characteristics of the ANSI_PADDING setting at insert. If you download and run the script I provided with the article you can see the inconsistent behavior which, IMHO, is a big part of the issue. I don't want to have to run profiler to see how all my developers and 3rd party applications are setting the ANSI_PADDING setting.The key point I was trying to make in the article is that I had always understood varchar/nvarchar columns automatically trimmed trailing spaces and that is not true so you need to deal with that situation. The best thing to do is to RTRIM varchar/nvarchar columns when inserting or updating so that you get consistent behavior. If you have ever used an SSIS lookup this is important as in SSIS the spaced count.Thanks to all for the comments thus far.

RE: ANSI PADDING, Trailing Whitespace, and Variable Length Character Colum

Reginald J Ray Jr — Fri, 12 Sep 2008 08:09:17 GMT

I'm confused. I get the same results from 'select * from duh' for both settings of ANSI_PADDING. set ANSI_PADDING offgocreate table duh ( col1 varchar(50))insert into duh select 'a ' + 'a'select * from duhdrop table duhjack rayjray@validata.org

RE: ANSI PADDING, Trailing Whitespace, and Variable Length Character Colum

Jack Corbett — Fri, 12 Sep 2008 08:07:54 GMT

[quote][b]Jack Corbett (9/12/2008)[/b][hr][quote][b]steitelbaum (9/12/2008)[/b][hr]IE 7.0.5730.11resolution 1024 x 768[/quote]It is related to the resolution. Apparently the table is not resizing based on resolution. Not being an HTML guy I'll have to fumble around a little.Maybe someone here can tell me how to fix it.[/quote]This is also only an issue in IE. Firefox and Chrome both display the entire table.

RE: ANSI PADDING, Trailing Whitespace, and Variable Length Character Colum

Jack Corbett — Fri, 12 Sep 2008 07:58:27 GMT

[quote][b]steitelbaum (9/12/2008)[/b][hr]IE 7.0.5730.11resolution 1024 x 768[/quote]It is related to the resolution. Apparently the table is not resizing based on resolution. Not being an HTML guy I'll have to fumble around a little.Maybe someone here can tell me how to fix it.

RE: ANSI PADDING, Trailing Whitespace, and Variable Length Character Colum

chrs-513176 — Fri, 12 Sep 2008 07:57:29 GMT

Good article! I recently ran into issues regarding this and your post summarizes the behavior nicely. I'll use it for quick, future reference.

RE: ANSI PADDING, Trailing Whitespace, and Variable Length Character Colum

Tobar — Fri, 12 Sep 2008 07:43:30 GMT

Jack,Great article! I did read your sources and you summarized them well.I have found this behavior equally as frustrating as you. I never realized the Trim twins did not handle what you call "special characters", "C" and Oracle, my background, has always treated them as white space. One more anxiety pill when dealling with SQL Server.I always use varchar to minimize trailing snafus. Any time you import data just be super vigilant about getting rid of trailings. Once it is in the DB clean, and you are using varchar, you are home free.

RE: ANSI PADDING, Trailing Whitespace, and Variable Length Character Colum

steitelbaum — Fri, 12 Sep 2008 07:39:28 GMT

IE 7.0.5730.11resolution 1024 x 768

RE: ANSI PADDING, Trailing Whitespace, and Variable Length Character Colum

Jack Corbett — Fri, 12 Sep 2008 07:19:25 GMT

[quote][b]steitelbaum (9/12/2008)[/b][hr]jack, the table of behaviors is cutting off (part of 3rd column) in my browser.[/quote]What browser are you using? I have viewed the article in IE 7, Firefox 3, and Chrome without any problems. How about screen resolution?

RE: ANSI PADDING, Trailing Whitespace, and Variable Length Character Colum

steitelbaum — Fri, 12 Sep 2008 07:02:08 GMT

jack, the table of behaviors is cutting off (part of 3rd column) in my browser.

ANSI PADDING, Trailing Whitespace, and Variable Length Character Colum

Jack Corbett — Thu, 11 Sep 2008 22:22:42 GMT

Comments posted to this topic are about the item [B]ANSI PADDING, Trailing Whitespace, and Variable Length Character Colum[/B]