ANSI PADDING, Trailing Whitespace, and Variable Length Character Colum

Question

ANSI PADDING, Trailing Whitespace, and Variable Length Character Colum

Viewing 15 posts - 16 through 30 (of 49 total)

You must be logged in to reply to this topic. Login to reply

Jack Corbett SSC Guru Points: 184394 More actions · Answer 1

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:

Select 'A' + 'A', 'A ' + 'A'

I get:

no_space one_space

-------- ---------

AA A A

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.

Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question

Jeff Moden SSC Guru Points: 1004324 More actions · Answer 2

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.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jack Corbett SSC Guru Points: 184394 More actions · Answer 3

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?

Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question

Jeff Moden SSC Guru Points: 1004324 More actions · Answer 4

Jack Corbett (9/13/2008)
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?

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

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Anipaul SSC-Insane Points: 24681 More actions · Answer 5

Anipaul

SSC-Insane

Points: 24681

September 15, 2008 at 1:06 am

#871605

Excellent Article...

Jack Corbett SSC Guru Points: 184394 More actions · Answer 6

Thanks.

Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question

Wayne West SSC-Insane Points: 22586 More actions · Answer 7

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.

-----
[font="Arial"]Knowledge is of two kinds. We know a subject ourselves or we know where we can find information upon it. --Samuel Johnson[/font]

Jack Corbett SSC Guru Points: 184394 More actions · Answer 8

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!

Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question

TechnoPeasant SSC-Addicted Points: 447 More actions · Answer 9

Jack Corbett (9/12/2008)
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:
Select 'A' + 'A', 'A ' + 'A'
I get:
no_space one_space
-------- ---------
AA A A
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.

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.

Select 'A' + 'A', 'A ' + 'A'

no_space one_space IS ALWAYSTHE RESULT

-------- ---------

AA A A

stephen.hendricks SSC Eights! Points: 845 More actions · Answer 10

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.

============================================================
I believe I found the missing link between animal and civilized man. It is us. -Konrad Lorenz, Nobel laureate (1903-1989)

Jack Corbett SSC Guru Points: 184394 More actions · Answer 11

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.

Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question

stephen.hendricks SSC Eights! Points: 845 More actions · Answer 12

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.

============================================================
I believe I found the missing link between animal and civilized man. It is us. -Konrad Lorenz, Nobel laureate (1903-1989)

Jack Corbett SSC Guru Points: 184394 More actions · Answer 13

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.

Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question

stephen.hendricks SSC Eights! Points: 845 More actions · Answer 14

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 ON

ANSI_PADDING ON

ANSI_WARNINGS ON

ARITHABORT ON

CONCAT_NULL_YIELDS_NULL ON

QUOTED_IDENTIFIER ON

NUMERIC_ROUNDABORT OFF

These 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.

============================================================
I believe I found the missing link between animal and civilized man. It is us. -Konrad Lorenz, Nobel laureate (1903-1989)

Jack Corbett SSC Guru Points: 184394 More actions · Answer 15

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.

Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question