InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comSat, 18 May 2013 23:31:18 GMT20http://www.sqlservercentral.com/Forums/Topic568248-1254-1.aspxI believe, and I'm not an SSIS expert, that SSIS is doing the compare within SSIS, meaning that .NET comparison rules apply. You need to match case AND trim any trailing spaces. I also believe that an Excel source uses Unicode data types for any strings, so SQL Server would pad with trailing spaces.Sun, 30 Oct 2011 19:20:56 GMT Jack Corbetthttp://www.sqlservercentral.com/Forums/Topic568248-1254-1.aspxTo brighten the path for others following this later (like me), I wanted to spell out more about what this means for a Lookup in SSIS (according to the package I just got working). SSIS includes any trailing spaces in a lookup, and when matching to the input source, 'a' != 'a ' (space).My source was Excel, and I didn't find any data conversion that would add the trailing spaces so that 'a' coming in would be 'a ', so I changed my lookup from a table/view selection to a SQL statement that trimmed my db-side fields to that 'a ' was changed to 'a', thus matching the 'a' coming from Excel.Adding the trim is easy enough, but I'm puzzled why SSIS won't allow a data conversion to a type that will behave like char(10) and automatically add trailing spaces (if I just missed it, please explain how to do that in SSIS).Fri, 28 Oct 2011 15:25:56 GMTDan W-960228http://www.sqlservercentral.com/Forums/Topic568248-1254-1.aspx[quote][b]tony.sawyer (7/16/2010)[/b][hr]The ANSI_PADDING affects the CHAR types too - surprised the hell out of me when I finally discovered why my tables weren't linking!doing a simple [code]select '<' + prod_code + '>' from ...[/code] showed it to only contain 7 characters with no trailing white space. [code]select '<' + cast(prod_code as char(10)) + '>' from ...[/code] showed up the trailing 3 spaces (and then allowed EF4 to link the data to another table that had ANSI_PADDING set to ON)We're using SQL Server 2005 here - give it a shot and see if you get the same results[/quote]Tony,You are correct. The difference is that unicode (nchar/nvarchar) always behave with ANSI_PADDING ON settings.Tue, 20 Jul 2010 07:10:42 GMT Jack Corbetthttp://www.sqlservercentral.com/Forums/Topic568248-1254-1.aspxNice article. I too had some of the same misconceptions about padding as you.Tue, 20 Jul 2010 06:33:16 GMTDaniel Bowlinhttp://www.sqlservercentral.com/Forums/Topic568248-1254-1.aspxWell that's just weird. I just posted on one of the 2-year-old threads referenced in this article earlier this morning, before I saw that this article had been republished. Perhaps now would be a good time to buy a Lotto ticket?Sat, 17 Jul 2010 11:28:33 GMTPaul Whitehttp://www.sqlservercentral.com/Forums/Topic568248-1254-1.aspxI'm glad this was republished. Thanks Jack - good explanation.Fri, 16 Jul 2010 12:45:49 GMTSQLRNNRhttp://www.sqlservercentral.com/Forums/Topic568248-1254-1.aspxThe ANSI_PADDING affects the CHAR types too - surprised the hell out of me when I finally discovered why my tables weren't linking!doing a simple [code]select '<' + prod_code + '>' from ...[/code] showed it to only contain 7 characters with no trailing white space. [code]select '<' + cast(prod_code as char(10)) + '>' from ...[/code] showed up the trailing 3 spaces (and then allowed EF4 to link the data to another table that had ANSI_PADDING set to ON)We're using SQL Server 2005 here - give it a shot and see if you get the same resultsFri, 16 Jul 2010 10:32:29 GMTtony.sawyerhttp://www.sqlservercentral.com/Forums/Topic568248-1254-1.aspxTony,Are you sure it isn't just .NET issue? The ANSI PADDING setting should only affect variable length column (VARCHAR/NVARCHAR) not CHAR.Fri, 16 Jul 2010 10:22:12 GMT Jack Corbetthttp://www.sqlservercentral.com/Forums/Topic568248-1254-1.aspxNice article Jack - I think stuff like this that underlines how complex the simple can be is a real benefit to the community... :-)Fri, 16 Jul 2010 08:03:44 GMTMatt Whitfieldhttp://www.sqlservercentral.com/Forums/Topic568248-1254-1.aspxHi All,The ANSI PADDING also has effects on Entity Framework 4!I had written an Entity Diagram linking two tables together through a CHAR(10) field that wasn't fully populated. For some reason the link never worked and it took me a long time to get to the issue and notice that even though both fields were the same type and fixed length one of the tables was set to ANSI_PADDING ON and the other wasn't (yeah - great design there originally!)When EF4 got the data from both tables, even though the fields were fixed length in the database and there were no problems accessing the data within SQL Server, as soon as it was returned outside of the database the table with ANSI_PADDING set to OFF acted more like a VARCHAR(10) field and only returned a 7 character field with no trailing spaces. The other table returned a 10 character field with 3 trailing spaces. EF4 was unable to link those two records together as it believed them to be different.To solve the issue, we created a view of the table and cast the field to a CHAR(10) and linked through the new field and the padded field which fixed the issue (yes a bodge rather than fixing the data in the table). At the time we weren't sure whether we would use EF4 so we weren't prepared to do major undertakings on the database if we weren't getting a real benefit to our production system.TonyFri, 16 Jul 2010 04:02:20 GMTtony.sawyerhttp://www.sqlservercentral.com/Forums/Topic568248-1254-1.aspxIn my oppinion ANSI padding (for storage) should always be on and I cannot see any justification for it not to be.If my program stores the varchar 'A' plus a space and I read it back, I expect the same result back, and not just an 'A'! It is my and my program's responsibility to make sure I trim/normalize any user input if this makes sense given the function of the data being entered by an user.I can only see this option as once added to support lazy and incorrect written code and this "feature" should have been declared obsolete a long time ago.Unfortunately, while ansi_padding works great on the storage part, it fails miserably when it comes to compares. There it performs always an rtrim, no matter what setting you use and this is really a braindead situation :(. It causes bugs and implies inherrent performance losses in many operations.My reasoning is that a trailing space constitues just as much information as does a trailing '0' or a trailing 'Z' and hence there should be no special, implied treatment/overhead when storing or comparing varchar fields.What also really annoys me with these pre-historical quirks is that len automatically performs a rtrim where it is not expected as well! Everyone I know has been at once time or another been surprised by this behavior.Fri, 16 Jul 2010 03:18:06 GMTpeter-757102http://www.sqlservercentral.com/Forums/Topic568248-1254-1.aspx[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.Mon, 03 Aug 2009 07:00:08 GMT Jack Corbetthttp://www.sqlservercentral.com/Forums/Topic568248-1254-1.aspxIt'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.Mon, 03 Aug 2009 02:59:52 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic568248-1254-1.aspx[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!!!!!Mon, 03 Aug 2009 00:38:24 GMTOl'SureHandhttp://www.sqlservercentral.com/Forums/Topic568248-1254-1.aspx[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.Mon, 03 Aug 2009 00:29:24 GMTOl'SureHandhttp://www.sqlservercentral.com/Forums/Topic568248-1254-1.aspx[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.Fri, 31 Jul 2009 10:46:10 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic568248-1254-1.aspxMatt,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.Fri, 31 Jul 2009 09:53:04 GMT Jack Corbetthttp://www.sqlservercentral.com/Forums/Topic568248-1254-1.aspxI 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! :-)Fri, 31 Jul 2009 09:35:26 GMTMatt Campbell-473914http://www.sqlservercentral.com/Forums/Topic568248-1254-1.aspxNo problem. You made me curious so I had to do something.Thu, 30 Jul 2009 17:08:18 GMT Jack Corbetthttp://www.sqlservercentral.com/Forums/Topic568248-1254-1.aspxThanks for your efforts. I appreciate how generous you have been with your time; and for free, no less.Thu, 30 Jul 2009 16:10:35 GMTstephen.hendrickshttp://www.sqlservercentral.com/Forums/Topic568248-1254-1.aspxHey, 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.Thu, 30 Jul 2009 15:44:51 GMT Jack Corbetthttp://www.sqlservercentral.com/Forums/Topic568248-1254-1.aspxAllow 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.Thu, 30 Jul 2009 15:20:14 GMTstephen.hendrickshttp://www.sqlservercentral.com/Forums/Topic568248-1254-1.aspxWhat 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.Thu, 30 Jul 2009 12:18:36 GMT Jack Corbetthttp://www.sqlservercentral.com/Forums/Topic568248-1254-1.aspxThanks 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.Thu, 30 Jul 2009 10:27:53 GMTstephen.hendrickshttp://www.sqlservercentral.com/Forums/Topic568248-1254-1.aspxAt 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.Wed, 29 Jul 2009 19:03:18 GMT Jack Corbetthttp://www.sqlservercentral.com/Forums/Topic568248-1254-1.aspxUseful 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.Wed, 29 Jul 2009 17:49:58 GMTstephen.hendrickshttp://www.sqlservercentral.com/Forums/Topic568248-1254-1.aspx[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 AMon, 15 Sep 2008 15:07:06 GMTTechnoPeasanthttp://www.sqlservercentral.com/Forums/Topic568248-1254-1.aspxYes 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!Mon, 15 Sep 2008 08:48:22 GMT Jack Corbetthttp://www.sqlservercentral.com/Forums/Topic568248-1254-1.aspxThis 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.Mon, 15 Sep 2008 08:41:10 GMTWayne Westhttp://www.sqlservercentral.com/Forums/Topic568248-1254-1.aspxThanks.Mon, 15 Sep 2008 05:14:11 GMT Jack Corbetthttp://www.sqlservercentral.com/Forums/Topic568248-1254-1.aspxExcellent Article...Mon, 15 Sep 2008 01:06:41 GMTAnipaulhttp://www.sqlservercentral.com/Forums/Topic568248-1254-1.aspx[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.Sat, 13 Sep 2008 23:53:49 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic568248-1254-1.aspxThanks, 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?Sat, 13 Sep 2008 22:21:23 GMT Jack Corbetthttp://www.sqlservercentral.com/Forums/Topic568248-1254-1.aspxNice 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.Sat, 13 Sep 2008 17:26:19 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic568248-1254-1.aspxBill,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.Fri, 12 Sep 2008 16:30:02 GMT Jack Corbetthttp://www.sqlservercentral.com/Forums/Topic568248-1254-1.aspxThen 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?Fri, 12 Sep 2008 15:37:58 GMTTechnoPeasanthttp://www.sqlservercentral.com/Forums/Topic568248-1254-1.aspxTobar 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!)Fri, 12 Sep 2008 11:02:27 GMTCarla Wilson-484785http://www.sqlservercentral.com/Forums/Topic568248-1254-1.aspxThanks 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.Fri, 12 Sep 2008 10:33:53 GMT Jack Corbetthttp://www.sqlservercentral.com/Forums/Topic568248-1254-1.aspxFrom 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.Fri, 12 Sep 2008 10:31:42 GMTTobarhttp://www.sqlservercentral.com/Forums/Topic568248-1254-1.aspxI 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!Fri, 12 Sep 2008 09:52:04 GMTCarla Wilson-484785