April 23, 2004 at 9:20 am
We are using Cognos application which received an error; which indicated that the Server Properties Connection Options ANSI warning/padding be enabled.
What is the effect of ANSI warning/padding in simple terms?
Thanks for the help!
April 23, 2004 at 11:56 am
Ansi padding when set to on stops the trailing blanks in character values being trimmed when they are inserted into varchar columns, i think it does the same with vabinary.
Ansi warnings when set to on issues errors if you try try to divide by zero or encounter a null value in an aggregate function e.g sum()
Dave
April 23, 2004 at 6:45 pm
Just a bit more detail
From BOL
ANSI_PADDING
When 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.
Example.
First
SET ANSI_PADDING ON
GO
create table #x (val varchar(10))
insert #x (val) values ('v ')
SELECT val + 'B' from #x
drop table #x
Result
v B
Notice that there are spaces between v and B becuase the the value 'v ' was not trimmed of the trailing spaces on insert.
Next try
SET ANSI_PADDING OFF
GO
create table #x (val varchar(10))
insert #x (val) values ('v ')
SELECT val + 'B' from #x
DROP TABLE #x
The results is
vB
The spaces after the v where removed from the field on insert and not retained.
Now one thing to note thou is this does not apply to variables. They are not trimmed so using ANSI_PADDING OFF like so
SET ANSI_PADDING OFF
GO
DECLARE @val VARCHAR(10)
SET @val = ('v ')
SELECT @val + 'B'
Produces
v B
still.
Also as described, because varchar and varbinary are variable length and not fixed they only retain the actual length with trailing spaces or zeros and do not add to the padding such as char and binary do.
Hope that demonstrates a bit for you.
April 26, 2004 at 8:22 am
Wow, thanks for the detail information...that helps a lot!!!
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply