What does USE ANSI nulls, padding and warnings do?

This option affects TDS agent & Lite Driver connections to MS SQLServer databases. Sybase connectivity is not affected.

When enabled, the agent issues following Transact-SQL commands at connect time:

SET ANSI_ NULL_ DFLT_ ON ON

When disabled, the agent issues the commands:

SET ANSI_NULLS OFF
SET ANSI_PADDING OFF
SET ANSI_WARNINGS OFF
SET CONCAT_ NULL_ YIELDS_ NULL OFF

Refer to the MS SQLServer Transact-SQL Reference @ http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_tsqlcon_6lyk.asp for additional information.

  • SET ANSI_ NULL_ DFLT_ ON - Affects the nullability of new columns when the nullability of the column is not specified in the CREATE TABLE and ALTER TABLE statements.
  • SET ANSI_NULLS - Specifies SQL-92 compliant behavior of the Equals (=) and Not Equal to (<>) comparison operators when used with null values.
  • 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.
  • SET ANSI_WARNINGS - Specifies SQL-92 standard behavior for several error conditions.
  • SET CONCAT_ NULL_ YIELDS_ NULL - Controls whether or not concatenation results are treated as null or empty string values.

Referenced by...