This was an interesting topic to look into. First, I wondered how to get to know my current ANSI_PADDING setting value. Thanks to Copilot I found this out quickly: DBCC USEROPTIONS; If "ansi_padding SET" is in the list, it is ON, else it is OFF. Next I wondered how I can see with which setting an existing table was created. Again Copilot assisted with a useful answer which lead me to write these queries: SELECT * FROM sys.columns WHERE object_id = OBJECT_ID ('TestAnsiPaddingOn'); SELECT * FROM sys.columns WHERE object_id = OBJECT_ID ('TestAnsiPaddingOff'); Look at the value in column "is_ansi_padded". This value does not surface in the INFORMATION_SCHEMA.COLUMNS-view (at least not on SQL Server 2016). This lead to another interesting discovery, namely that the ANSI_PADDING setting seems to be irrelevant for nchar- and nvarchar-columns.
This was an interesting topic to look into.
First, I wondered how to get to know my current ANSI_PADDING setting value. Thanks to Copilot I found this out quickly:
DBCC USEROPTIONS;
If "ansi_padding SET" is in the list, it is ON, else it is OFF.
Next I wondered how I can see with which setting an existing table was created. Again Copilot assisted with a useful answer which lead me to write these queries:
SELECT * FROM sys.columns WHERE object_id = OBJECT_ID ('TestAnsiPaddingOn');
SELECT * FROM sys.columns WHERE object_id = OBJECT_ID ('TestAnsiPaddingOff');
Look at the value in column "is_ansi_padded".
This value does not surface in the INFORMATION_SCHEMA.COLUMNS-view (at least not on SQL Server 2016).
This lead to another interesting discovery, namely that the ANSI_PADDING setting seems to be irrelevant for nchar- and nvarchar-columns.
Interesting!
ridiculously short, Xtremely useful!
Thank you!
Should we incorporate this setting at the database level for all newly created tables?
If that is your business requirement. I would usually stay with the default settings, unless required to change it.