Behavior Changes to Database Engine Features in SQL Server 2012

Article
04/01/2012

This topic describes behavior changes in the Database Engine. Behavior changes affect how features work or interact in SQL Server 2012 as compared to earlier versions of SQL Server.

Metadata Discovery

Improvements in the Database Engine beginning with SQL Server 2012 allow SQLDescribeCol to obtain more accurate descriptions of the expected results than those returned by SQLDescribeCol in previous versions of SQL Server. For more information, see Metadata Discovery.

The SET FMTONLY option for determining the format of a response without actually running the query is replaced with sp_describe_first_result_set (Transact-SQL), sp_describe_undeclared_parameters (Transact-SQL), sys.dm_exec_describe_first_result_set (Transact-SQL), and sys.dm_exec_describe_first_result_set_for_object (Transact-SQL).

Changes to Behavior in Scripting a SQL Server Agent Task

In SQL Server 2012, if you create a new job by copying the script from an existing job, the new job might inadvertently affect the existing job. To create a new job using the script from an existing job, manually delete the parameter @schedule\_uid which is usually the last parameter of the section which creates the job schedule in the existing job. This will create a new independent schedule for the new job without affecting existing jobs.

Constant Folding for CLR User-Defined Functions and Methods

In SQL Server 2012, the following user-defined CLR objects are now foldable:

Deterministic scalar-valued CLR user-defined functions.
Deterministic methods of CLR user-defined types.

This improvement seeks to enhance performance when these functions or methods are called more than once with the same arguments. However, this change may cause unexpected results when non-deterministic functions or methods have been marked as deterministic in error. The determinism of a CLR function or method is indicated by the value of the IsDeterministic property of the SqlFunctionAttribute or SqlMethodAttribute.

Behavior of STEnvelope() Method Has Changed with Empty Spatial Types

The behavior of the STEnvelope method with empty objects is now consistent with the behavior of other SQL Server spatial methods.

In SQL Server 2008, the STEnvelope method returned the following results when called with empty objects:

select geometry::Parse('POINT EMPTY').STEnvelope().ToString()
-- returns POINT EMPTY
select geometry::Parse('LINESTRING EMPTY').STEnvelope().ToString()
-- returns LINESTRING EMPTY
select geometry::Parse('POLYGON EMPTY').STEnvelope().ToString()
-- returns POLYGON EMPTY

In SQL Server 2012, the STEnvelope method now returns the following results when called with empty objects:

select geometry::Parse('POINT EMPTY').STEnvelope().ToString()
-- returns GEOMETRYCOLLECTION EMPTY
select geometry::Parse('LINESTRING EMPTY').STEnvelope().ToString()
-- returns GEOMETRYCOLLECTION EMPTY
select geometry::Parse('POLYGON EMPTY').STEnvelope().ToString()
-- returns GEOMETRYCOLLECTION EMPTY

To determine whether a spatial object is empty, call the STIsEmpty (geometry Data Type) method.

LOG Function Has New Optional Parameter

The LOG function now has an optional base parameter. For more information, see LOG (Transact-SQL).

Statistics Computation during Partitioned Index Operations Has Changed

In SQL Server 2012, statistics are not created by scanning all rows in the table when a partitioned index is created or rebuilt. Instead, the query optimizer uses the default sampling algorithm to generate statistics. After upgrading a database with partitioned indexes, you may notice a difference in the histogram data for these indexes. This change in behavior may not affect query performance. To obtain statistics on partitioned indexes by scanning all the rows in the table, use CREATE STATISTICS or UPDATE STATISTICS with the FULLSCAN clause.

Data Type Conversion by the XML value Method Has Changed

The internal behavior of the value method of the xml data type has changed. This method performs an XQuery against the XML and returns a scalar value of the specified SQL Server data type. The xs type has to be converted to the SQL Server data type. Previously, the value method internally converted the source value to an xs:string, then converted the xs:string to the SQL Server data type. In SQL Server 2012, the conversion to xs:string is skipped in the following cases:

Source XS data type	Destination SQL Server data type
byte short int integer long unsignedByte unsignedShort unsignedInt unsignedLong positiveInteger nonPositiveInteger negativeInteger nonNegativeInteger	tinyint smallint int bigint decimal numeric
decimal	decimal numeric
float	real
double	float

The new behavior improves performance when the intermediate conversion can be skipped. However, when data type conversions fail, you see different error messages than those that were raised when converting from the intermediate xs:string value. For example, if the value method failed to convert the int value 100000 to a smallint, the previous error message was:

The conversion of the nvarchar value '100000' overflowed an INT2 column. Use a larger integer column.

In SQL Server 2012, without the intermediate conversion to xs:string, the error message is:

Arithmetic overflow error converting expression to data type smallint.

sqlcmd.exe Behavior Change in XML Mode

There are behavior changes if you use sqlcmd.exe with XML mode (:XML ON command) when executing a SELECT * from T FOR XML …. For more information, see Manageability Enhancements (Database Engine).

DBCC CHECKIDENT Revised Message

In SQL Server 2012, the message returned by the DBCC CHECKIDENT command has changed only when it is used with RESEED new_reseed_value to change current identity value. The new message is "Checking identity information: current identity value '<current identity value>'. DBCC execution completed. If DBCC printed error messages, contact your system administrator."

In earlier versions, the message is "Checking identity information: current identity value '<current identity value>', current column value '<current column value>'. DBCC execution completed. If DBCC printed error messages, contact your system administrator." The message is unchanged when DBCC CHECKIDENT is specified with NORESEED, without a second parameter, or without a reseed value. For more information, see DBCC CHECKIDENT (Transact-SQL).

Behavior of exist() function on XML datatype has changed

The behavior of the exist() function has changed when comparing an XML data type with a null value to 0 (zero). Consider the following example:

DECLARE @test XML;
SET @test = null;
SELECT COUNT(1) WHERE @test.exist('/dogs') = 0;

In earlier versions, this comparison return 1 (true); now, this comparison returns 0 (zero, false).

The following comparisons have not changed:

DECLARE @test XML;
SET @test = null;
SELECT COUNT(1) WHERE @test.exist('/dogs') = 1; -- 0 expected, 0 returned
SELECT COUNT(1) WHERE @test.exist('/dogs') IS NULL; -- 1 expected, 1 returned