Menu
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?
463
The ANSI_PADDING setting controls how trailing spaces are handled in columns with CHAR and VARCHAR data types, and trailing zeroes in columns with BINARY and VARBINARY data types. In other words, it specifies how the column stores the values shorter than the column defined size for that data types.
When ANSI_NULLS is ON, a SELECT statement that uses WHERE column_name = NULL returns zero rows even if there are null values in column_name. A SELECT statement that uses WHERE column_name <> NULL returns zero rows even if there are nonnull values in column_name.
The @@OPTIONS function returns a bitmap of the options, converted to a base 10 (decimal) integer. The bit settings are stored in the locations described in a table in the article Configure the user options Server Configuration Option.
To recall, SQL Server SET options are a group of session-level options that control how the SQL Server behaves on the database session level, and the option value can be changed using the SET T-SQL command for the current session that you execute the SET command on.
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, andvarbinarydata.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.
61
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
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
