Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12345»»»

ANSI PADDING, Trailing Whitespace, and Variable Length Character Colum Expand / Collapse
Author
Message
Posted Thursday, September 11, 2008 10:22 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Thursday, July 24, 2014 9:07 AM
Points: 11,157, Visits: 12,899
Comments posted to this topic are about the item ANSI PADDING, Trailing Whitespace, and Variable Length Character Colum



Jack Corbett

Applications Developer

Don't let the good be the enemy of the best. -- Paul Fleming

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
How to Post Performance Problems
Crosstabs and Pivots or How to turn rows into columns Part 1
Crosstabs and Pivots or How to turn rows into columns Part 2
Post #568248
Posted Friday, September 12, 2008 7:02 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Friday, September 12, 2008 1:58 PM
Points: 16, Visits: 81
jack, the table of behaviors is cutting off (part of 3rd column) in my browser.
Post #568451
Posted Friday, September 12, 2008 7:19 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Thursday, July 24, 2014 9:07 AM
Points: 11,157, Visits: 12,899
steitelbaum (9/12/2008)
jack, the table of behaviors is cutting off (part of 3rd column) in my browser.


What browser are you using? I have viewed the article in IE 7, Firefox 3, and Chrome without any problems. How about screen resolution?




Jack Corbett

Applications Developer

Don't let the good be the enemy of the best. -- Paul Fleming

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
How to Post Performance Problems
Crosstabs and Pivots or How to turn rows into columns Part 1
Crosstabs and Pivots or How to turn rows into columns Part 2
Post #568466
Posted Friday, September 12, 2008 7:39 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Friday, September 12, 2008 1:58 PM
Points: 16, Visits: 81
IE 7.0.5730.11
resolution 1024 x 768
Post #568493
Posted Friday, September 12, 2008 7:43 AM


SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Tuesday, July 8, 2014 1:39 PM
Points: 245, Visits: 735
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.


<><
Livin' down on the cube farm. Left, left, then a right.
Post #568501
Posted Friday, September 12, 2008 7:57 AM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Wednesday, August 17, 2011 7:09 AM
Points: 869, Visits: 963
Good article! I recently ran into issues regarding this and your post summarizes the behavior nicely. I'll use it for quick, future reference.
Post #568517
Posted Friday, September 12, 2008 7:58 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Thursday, July 24, 2014 9:07 AM
Points: 11,157, Visits: 12,899
steitelbaum (9/12/2008)
IE 7.0.5730.11
resolution 1024 x 768


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.




Jack Corbett

Applications Developer

Don't let the good be the enemy of the best. -- Paul Fleming

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
How to Post Performance Problems
Crosstabs and Pivots or How to turn rows into columns Part 1
Crosstabs and Pivots or How to turn rows into columns Part 2
Post #568518
Posted Friday, September 12, 2008 8:07 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Thursday, July 24, 2014 9:07 AM
Points: 11,157, Visits: 12,899
Jack Corbett (9/12/2008)
steitelbaum (9/12/2008)
IE 7.0.5730.11
resolution 1024 x 768


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.


This is also only an issue in IE. Firefox and Chrome both display the entire table.




Jack Corbett

Applications Developer

Don't let the good be the enemy of the best. -- Paul Fleming

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
How to Post Performance Problems
Crosstabs and Pivots or How to turn rows into columns Part 1
Crosstabs and Pivots or How to turn rows into columns Part 2
Post #568526
Posted Friday, September 12, 2008 8:09 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Thursday, February 21, 2013 8:12 AM
Points: 17, Visits: 55
I'm confused. I get the same results from 'select * from duh' for both settings of ANSI_PADDING.

set ANSI_PADDING off
go

create table duh (
col1 varchar(50)
)

insert into duh select 'a ' + 'a'

select * from duh

drop table duh

jack ray
jray@validata.org
Post #568529
Posted Friday, September 12, 2008 8:26 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Thursday, July 24, 2014 9:07 AM
Points: 11,157, Visits: 12,899
Reginald J Ray Jr (9/12/2008)
I'm confused. I get the same results from 'select * from duh' for both settings of ANSI_PADDING.

set ANSI_PADDING off
go

create table duh (
col1 varchar(50)
)

insert into duh select 'a ' + 'a'

select * from duh

drop table duh

jack ray
jray@validata.org


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.




Jack Corbett

Applications Developer

Don't let the good be the enemy of the best. -- Paul Fleming

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
How to Post Performance Problems
Crosstabs and Pivots or How to turn rows into columns Part 1
Crosstabs and Pivots or How to turn rows into columns Part 2
Post #568566
« Prev Topic | Next Topic »

Add to briefcase 12345»»»

Permissions Expand / Collapse