SQL Server Overrides My ANSI_PADDING Setting

[Bob]

I used Enterprise Manager to change the data type of one table column
from one thing to another. When I attempted to save the change, I was
warned that "one or more existing columns have ANSI_PADDING 'off' and
will be re-created with ANSI_PADDING 'on'". I didn't expect to see
this message. I verified that ANSI_PADDING is 'off' at the database
level by running the following statement:

SELECT DATABASEPROPERTYEX('database', 'IsAnsiPaddingEnabled')

Further, the ANSI padding option on the Connections tab of the SQL
Server Properties window is not selected. If ANSI_PADDING is 'off' at
the database level, why does SQL Server assume I want to re-create a
column with ANSI_PADDING 'on'?

[Erland Sommarskog]

Bob (bo******@enron.com) writes:

I used Enterprise Manager to change the data type of one table column
from one thing to another. When I attempted to save the change, I was
warned that "one or more existing columns have ANSI_PADDING 'off' and
will be re-created with ANSI_PADDING 'on'". I didn't expect to see
this message. I verified that ANSI_PADDING is 'off' at the database
level by running the following statement:

SELECT DATABASEPROPERTYEX('database', 'IsAnsiPaddingEnabled')

Further, the ANSI padding option on the Connections tab of the SQL
Server Properties window is not selected. If ANSI_PADDING is 'off' at
the database level, why does SQL Server assume I want to re-create a
column with ANSI_PADDING 'on'?

Because ANSI_PADDING is the default, and you should stick to it,
unless you have actually have reasons not to. To wit, there are
features in SQL Server you cannot use with ANSI_PADDING off. (Indexed
views and indexes on computed columns.)

The setting on database level is by no means any final say. There are
many ways to control a setting, and SET command always wins. Mainly
they are useful if you have legacy apps that is using DB-Library or
some other client library that does not set ANSI_PADDINGS, and other
ANSI settings by default. By turning on ANSI_PADDING for the database,
you can still get this behaviour if you desire.

But if you want ANSI_PADDING to be off, then you have to take steps
in your application and elsewhere to enforce this.

--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp