ANSI PADDING OFF not working

Hi,

I have set ANSI PADDING off by default for the database but even then when I create any table ANSI PADDING is on.

While creation of table I am able to set it off before I create it and then create the table with correct settings. But when I am altering the table it is always off even when I am setting specifically before altering the table.

Any ideas how to make this work?

Thanks.

The SQL Server ODBC driver and OLEDB provider sets several SET options ON by default. And ANSI_PADDING is one of those. So it deosn't matter if you switch it OFF at the database level. Any connection made by one of the data access API will automatically have ANSI_PADDING ON. I am curious as to why you want to set it to OFF. It is recommended to use the ANSI settings by default since lot of features depend on it (like computed column indexes, indexed view matching etc) and it is also compatible with other databases like DB2/Oracle. See the topics below for more details:

SET ANSI_PADDING topic

http://msdn2.microsoft.com/en-us/library/ms187403.aspx

SET ANSI_DEFAULTS topic

http://msdn2.microsoft.com/en-us/library/ms188340.aspx

Btw, the only way is to modify your application code to set ANSI_PADDING off after making the connection. This also means that if you need to work with the schema in OSQL or ISQLW or SSMS for example you need to do the same because they all use OLEDB/ODBC/ADO.NET to make connection to SQL Server. So it is actually easier to get into trouble not using the defaults. Note that in the future we may remove the SET options and consider making the ANSI settings default.

|||If ANSI Padding is on is stored with spaces and we do not want that.|||

I am not sure if there is a question in your response. If you want to use ANSI_PADDING OFF on the server-side then you need to enable in explicitly after connection. This is the only way due to the reasons I described before. Best is to trim values before it reaches the database server on the client-side.

Additionally, you need to be aware of recompilation of SPs/statements that access tables with ANSI_PADDING off and the session setting is different. So please be aware of all these limitations in addition to other missing enterprise features (computed column matching, indexed views matching etc). See the link below for the how batch compilation, recompilation etc works and how the SET options are used:

http://www.microsoft.com/technet/prodtechnol/sql/2005/recomp.mspx

|||

I can give you reasons NEVER to have padding on. Any tables you create will have a hidden padding on setting so that you can no longer control padding with set, ODBC, ADO or even database level switches. But more importantly, features such as the like clause with the underscore pattern match will return incorrect results on any of these “damaged” tables.

Eg. Select fred where fred like ‘123__’

Will return results of:

123

123A

123AB

It should only return 123AB. <null> should not be a character. Time consuming bugs like these are the reason I would recommend never setting padding on and I hope you seriously consider defaulting to padding off in future. It's by far the more useful setting.