SQL Server ANSI_Padding

Question

If I right click on a table in SQL Server Management Studio and select 'Script table as > Create to > New Query Editor Window' the code that appears contains:

SET ANSI_PADDING ON

.... create the table ...

SET ANSI_PADDING OFF

So, I guess, whether ANSI_Padding is on or off affects the whole database and it is turned on to create a table and then turned off again?

What happens if you create a table with ANSI_Padding off? How can you turn it on for that table?

Answer 1

SET ANSI_PADDING Controls the way the column stores values shorter than the defined size of the column, and the way the column stores values that have trailing blanks in char, varchar, binary, and varbinary data.

In a future version of MicrosoftSQL Server ANSI_PADDING will always be ON and any applications that explicitly set the option to OFF will produce an error. Avoid using this feature in new development work, and plan to modify applications that currently use this feature.

This setting affects only the definition of new columns. After the column is created, SQL Server stores the values based on the setting when the column was created. Existing columns are not affected by a later change to this setting.

Answer 2

So, I guess, whether ANSI_Padding is on or off affects the whole database and it is turned on to create a table and then turned off again?

no, the SET option only affects the context of your connection, if 2 connections come in and one has ANSI_PADDING OFF and the other one ON they don't affect the other connection. However....the table created in one connection will have the behavior that the setting did to it.

read more here http://msdn.microsoft.com/en-us/library/ms190356.aspx

So if connection 1 comes in with set ansi_nulls off then select * from bla where col = null will work

this however won't work for connection 2 if it comes with with the default ansi_nulls setting (ON)

you can see what you settings are by either executing dbcc useroptions or if you are on 2005 and up

SELECT SPID,VALUE,ANSI_SETTING
FROM (
SELECT @@SPID AS SPID,
CASE quoted_identifier
WHEN 1 THEN 'SET' ELSE 'OFF' END QUOTED_IDENTIFIER,
CASE arithabort
WHEN 1 THEN 'SET' ELSE 'OFF' END ARITHABORT,
CASE ansi_null_dflt_on
WHEN 1 THEN 'SET' ELSE 'OFF' END ANSI_NULL_DFLT_ON,
CASE ansi_defaults
WHEN 1 THEN 'SET' ELSE 'OFF' END ANSI_DEFAULTS ,
CASE ansi_warnings
WHEN 1 THEN 'SET' ELSE 'OFF' END ANSI_WARNINGS,
CASE ansi_padding
WHEN 1 THEN 'SET' ELSE 'OFF' END ANSI_PADDING,
CASE ansi_nulls
WHEN 1 THEN 'SET' ELSE 'OFF' END ANSI_NULLS,
CASE concat_null_yields_null
WHEN 1 THEN 'SET' ELSE 'OFF' END CONCAT_NULL_YIELDS_NULL
FROM sys.dm_exec_sessions
WHERE session_id = @@SPID ) P
UNPIVOT (VALUE FOR ANSI_SETTING IN(
QUOTED_IDENTIFIER,ARITHABORT,ANSI_NULL_DFLT_ON,
ANSI_DEFAULTS,ANSI_WARNINGS,
ANSI_PADDING,ANSI_NULLS,CONCAT_NULL_YIELDS_NULL
)
) AS unpvt