Export (0) Print
Expand All
3 out of 4 rated this helpful - Rate this topic

Behavior Changes to Database Engine Features in SQL Server 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.

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).

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.

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.

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.

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

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.

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.

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).

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).

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
Did you find this helpful?
(1500 characters remaining)
Thank you for your feedback

Community Additions

ADD
Show:
© 2014 Microsoft. All rights reserved.