516
Part V: Enterprise Data Management
In Management Studio, the ANSI settings can be enabled (ON) at the following levels:■ Server level: Checks the ANSI setting check box in the Server properties
Connections tab (refer to Figure 19-9).
■ Database level: Checks the ANSI setting check box in the Database Properties
Options tab (refer to Figure 19-2).
■ Connection level: Click the Query menu ➪ Query Options ➪ Execution ➪ ANSI, and
check the ANSI setting check box.The sp_dboption procedure is no longer supported in this version of SQL Server. To set these options use the
ALTER DATABASE command.
You can change the default database setting for ANSI in the model system database and then the defaults change for
all future databases.
The database setting for ANSI overwrites the server setting, and the connection setting overwrites the server and
database setting.ANSI Defaults
SQL Server provides the SET ANSI_DEFAULTS command to manage a group of SQL
Server settings. When SET ANSI_DEFAULTS is enabled, it enables the following settings
(explained later in this section):■ (^) SET ANSI_NULLS
■ (^) SET ANSI_NULL_DFLT_ON
■ (^) SET ANSI_PADDING
■ (^) SET ANSI_WARNINGS
■ (^) SET CURSOR_CLOSE_ON_COMMIT
■ (^) SET IMPLICIT_TRANSACTIONS
■ (^) SET QUOTED_IDENTIFIER
To set ANSI_DEFAULTS in code, do the following:
SET ANSI_DEFAULTS ON; ANSI Null Default
The ANSI_NULL_DEFAULT setting controls the default nullability. This setting is used
when a NULL or NOT_NULL is not explicitly specifi ed when creating a table. The default
database setting for ANSI_NULL_DEFAULTis OFF.
To set the ANSI_NULL_DEFAULT option on for the AdventureWorks2012 sample database in
code, do the following:
ALTER DATABASE AdventureWorks2012 SET ANSI_NULL_DEFAULT ON;
c19.indd 516c19.indd 516 7/30/2012 5:43:09 PM7/30/2012 5:43:09 PM
http://www.it-ebooks.info