Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
anuradhay
Starting Member
41 Posts |
 Posted - 2004-12-28 : 04:31:40
|
| hi,i have a table. while creating the table the ansi_padding setting was off. i have a char type field in that table. The size of the field is 18.it is a nullable field. i have some records in the table. The actual size of the data in this field is 8. since the padding is off it will not be padded with space right? now the datalength of this field is giving 8. but in the front end it is taking the full 18 characters. when i trim this field it is taking only 8 characters. why it is behaving like this?when i test this with a sample table it is working fine. but this is the existing table which is giving problem.Thanks, |
|
|
derrickleggett
Pointy Haired Yak DBA
4184 Posts |
Posted - 2004-12-28 : 17:33:26
|
| ??? Is ansi_padding still set to off?MeanOldDBAderrickleggett@hotmail.comWhen life gives you a lemon, fire the DBA. |
 |
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2004-12-28 : 17:36:14
|
| a char column is always padded.You need a varchar to have a variable length column.==========================================Cursors are useful if you don't know sql.DTS can be used in a similar way.Beer is not cold and it isn't fizzy. |
|
|
|
derrickleggett
Pointy Haired Yak DBA
4184 Posts |
Posted - 2004-12-28 : 17:41:28
|
BOL:ANSI_PADDINGWhen set to ON, trailing blanks in character values inserted into varchar columns and trailing zeros in binary values inserted into varbinary columns are not trimmed. Values are not padded to the length of the column. When set to OFF, the trailing blanks (for varchar) and zeros (for varbinary) are trimmed. This setting affects only the definition of new columns. Char(n) and binary(n) columns that allow nulls are padded to the length of the column when SET ANSI_PADDING is set to ON, but trailing blanks and zeros are trimmed when SET ANSI_PADDING is OFF. Char(n) and binary(n) columns that do not allow nulls are always padded to the length of the column. Important It is recommended that ANSI_PADDING always be set to ON. SET ANSI_PADDING must be ON when creating or manipulating indexes on computed columns or indexed views. The status of this option can be determined by examining the IsAnsiPaddingEnabled property of the DATABASEPROPERTYEX function. MeanOldDBAderrickleggett@hotmail.comWhen life gives you a lemon, fire the DBA. |
|
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2004-12-28 : 17:55:45
|
| Oops - didn't notice the allow nulls bit.You also need SET ANSI_DEFAULTS off otherwise the padding will be ignored.Also how are you testing to see if the column is padded?Could be that the spaces are added in retrieval or processing.==========================================Cursors are useful if you don't know sql.DTS can be used in a similar way.Beer is not cold and it isn't fizzy. |
|
|
|
derrickleggett
Pointy Haired Yak DBA
4184 Posts |
Posted - 2004-12-28 : 17:56:57
|
| Also, if the server setting is ANSI_PADDING ON now, then any stored procedure using CHAR in a temp table or variable referencing the column will put in the padding regardless of what the table was created with. Just an fyi.MeanOldDBAderrickleggett@hotmail.comWhen life gives you a lemon, fire the DBA. |
|
|
|
anuradhay
Starting Member
41 Posts |
Posted - 2004-12-29 : 00:57:33
|
| Hi,The SP is not using any temp table. it is taking the value directly from the table. datalength of that column is giving 6 whereas the actual size is 18. so i felt that blank spaces are trimmed.Thanks |
|
|
|
|
|
|
|
|
ansi_padding issue