Log Shipping – NORECOVERY vs. STANDBY mode

 

Transact-SQL

Feature	Description
Selecting from columns or tables named NEXT	Sequences use the ANSI standard NEXT VALUE FOR function. If a table or a column is named NEXT and the table or column is aliased as VALUE, and if the ANSI standard AS is omitted, the resultant statement can cause an error. To work around, include the ANSI standard AS keyword. For example, SELECT NEXT VALUE FROM Table should be rewritten as SELECT NEXT AS VALUE FROM Table and SELECT Col1 FROM NEXT VALUE should be rewritten as SELECT Col1 FROM NEXT AS VALUE.
PIVOT operator	The PIVOT operator is not allowed in a recursive common table expression (CTE) query when the database compatibility level is set to 110. Rewrite the query, or change the compatibility level to 100 or lower. Using PIVOT in a recursive CTE query produces incorrect results when there is more than a single row per grouping.
sp_setapprole and sp_unsetapprole	The cookie OUTPUT parameter for sp_setapprole is currently documented as varbinary(8000) which is the correct maximum length. However the current implementation returns varbinary(50). Applications should continue to reserve varbinary(8000) so that the application continues to operate correctly if the cookie return size increases in a future release. For more information, see sp_setapprole (Transact-SQL).
EXECUTE AS	The cookie OUTPUT parameter for EXECUTE AS is currently documented as varbinary(8000) which is the correct maximum length. However the current implementation returns varbinary(100). Applications should continue to reserve varbinary(8000) so that the application continues to operate correctly if the cookie return size increases in a future release. For more information, see EXECUTE AS (Transact-SQL).
sys.fn_get_audit_file function	Two additional columns (user_defined_event_id and user_defined_information) have been added to support user-defined audit events. Applications that do not select columns by name might return more columns than expected. Either select columns by name, or adjust the application to accept these additional columns.
WITHIN reserved keyword	WITHIN is now a reserved keyword. References to objects or columns named 'within' will fail. Rename the object or column name or delimit the name by using brackets or quotes. For example, SELECT * FROM [within].
CAST and CONVERT operations on computed columns of type time or datetime2	In earlier versions of SQL Server, the default style for CAST and CONVERT operations on time and datetime2 data types is 121 except when either type is used in a computed column expression. For computed columns, the default style is 0. This behavior impacts computed columns when they are created, used in queries involving auto-parameterization, or used in constraint definitions. Under compatibility level 110, the default style for CAST and CONVERT operations on time and datetime2 data types is always 121. If your query relies on the old behavior, use a compatibility level less than 110, or explicitly specify the 0 style in the affected query. Upgrading the database to compatibility level 110 will not change user data that has been stored to disk. You must manually correct this data as appropriate. For example, if you used SELECT INTO to create a table from a source that contained a computed column expression described above, the data (using style 0) would be stored rather than the computed column definition itself. You would need to manually update this data to match style 121.
ALTER TABLE	The ALTER TABLE statement allows only two-part (schema.object) table names. Specifying a table name using the following formats now fails at compile time with error 117. server.database.schema.table .database.schema.table ..schema.table In earlier versions specifying the format server.database.schema.table returned error 4902. Specifying the format .database.schema.table or the format ..schema.table succeeded. To resolve the problem, remove the use of a 4-part prefix.
Browsing metadata	Querying a view using FOR BROWSE or SET NO_BROWSETABLE ON now returns the metadata of the view, not the metadata of the underlying object. This behavior now matches other methods of browsing metadata.
SOUNDEX	Under database compatibility level 110, the SOUNDEX function implements new rules that may cause the values computed by the function to be different than the values computed under earlier compatibility levels. After upgrading to compatibility level 110, you may need to rebuild the indexes, heaps, or CHECK constraints that use the SOUNDEX function. For more information, see SOUNDEX (Transact-SQL).
Row count message for failed DML statements	In SQL Server 2012, the Database Engine will consistently send the TDS DONE token with RowCount: 0 to clients when a DML statement fails. In earlier versions of SQL Server, an incorrect value of -1 is sent to the client when the DML statement that fails is contained in a TRY-CATCH block and is either autoparameterized by the Database Engine or the TRY-CATCH block is not on the same level as the failed statement. For example, if a TRY-CATCH block calls a stored procedure and a DML statement in the procedure fails, the client will incorrectly receive a -1 value. Applications that rely on this incorrect behavior will fail.
SERVERPROPERTY ('Edition')	Installed product edition of the instance of SQL Server 2012. Use the value of this property to determine the features and the limits, such as maximum number of CPUs that are supported by the installed product. Based on the installed Enterprise edition, this can return 'Enterprise Edition' or 'Enterprise Edition: Core-based Licensing'. The Enterprise editions are differentiated based on the maximum compute capacity by a single instance of SQL Server. For more information on Compute capacity limits in SQL Server 2012, see Compute Capacity Limits by Edition of SQL Server.
CREATE LOGIN	The CREATE LOGIN WITH PASSWORD = 'password' HASHED option cannot be used with hashes created by SQL Server 7 or earlier.

Dynamic Management Views

View	Description
sys.dm_exec_requests	The command column changes from nvarchar(16) to nvarchar(32).
sys.dm_os_memory_cache_counters	The following columns have been renamed. Previous Column Name New Column Name single_pages_kb pages_kb multi_pages_kb pages_in_use_kb
sys.dm_os_memory_cache_entries	The column pages_allocated_count column has been renamed pages_kb.
sys.dm_os_memory_clerks	The column multi_pages_kb has been removed. The column single_pages_kb column has been renamed pages_kb.
sys.dm_os_memory_nodes	The following columns have been renamed. Previous Column Name New Column Name single_pages_kb pages_kb multi_pages_kb foreign_committed_kb
sys.dm_os_memory_objects	The following columns have been renamed. Previous Column Name New Column Name pages_allocated_count pages_in_bytes max_pages_allocated_count max_pages_in_bytes
sys.dm_os_sys_info	The following columns have been renamed. Previous Column Name New Column Name physical_memory_in_bytes physical_memory_kb bpool_commit_target committed_target_kb bpool_visible visible_target_kb virtual_memory_in_bytes virtual_memory_kb bpool_commited committed_kb
sys.dm_os_workers	The locale column has been removed.

Catalog Views

View

Description

sys.data_spaces sys.partition_schemes sys.filegroups sys.partition_functions

A new column, is_system, has been added to sys.data_spaces and sys.partition_functions. (sys.partition_schemes and sys.filegroups inherit the columns of sys.data_spaces.) A value of 1 in this column indicates that the object is used for full-text index fragments. In sys.partition_functions, sys.partition_schemes, and sys.filegroups, the new column is not the last column. Revise existing queries that rely on the order of columns returned from these catalog views.

SQL CLR Data Types (geometry, geography, and hierarchyid)

The assembly Microsoft.SqlServer.Types.dll, which contains the spatial data types and the hierarchyid type, has been upgraded from version 10.0 to version 11.0. Custom applications that reference this assembly may fail when the following conditions are true.

• When you move a custom application from a computer on which SQL Server 2008 R2 was installed to a computer on which only SQL Server 2012 is installed, the application will fail because the referenced version 10.0 of the SqlTypes assembly is not present. You may see this error message: "Could not load file or assembly 'Microsoft.SqlServer.Types, Version=10.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91' or one of its dependencies. The system cannot find the file specified."
  
• When you reference the SqlTypes assembly version 11.0, and version 10.0 is also installed, you may see this error message: "System.InvalidCastException: Unable to cast object of type 'Microsoft.SqlServer.Types.SqlGeometry' to type 'Microsoft.SqlServer.Types.SqlGeometry'."
  
• When you reference the SqlTypes assembly version 11.0 from a custom application that targets .NET 3.5, 4, or 4.5, the application will fail because SqlClient by design loads version 10.0 of the assembly. This failure occurs when the application calls one of the following methods:
  
  • GetValue method of the SqlDataReader class
    
  • GetValues method of the SqlDataReader class
    
  • bracket index operator [] of the SqlDataReader class
    
  • ExecuteScalar method of the SqlCommand class
    

You can work around this issue by using one of the following methods:

• You can work around this issue in your code by calling the GetSqlBytes method, instead of the Get methods listed above, to retrieve CLR SQL Server system types, as shown in the following example:
  

  C#
  

  string query = "SELECT [SpatialColumn] FROM [SpatialTable]";
  

        using (SqlConnection conn = new SqlConnection("..."))
  

        {
  

              SqlCommand cmd = new SqlCommand(query, conn);
  

  
   

              conn.Open();
  

              SqlDataReader reader = cmd.ExecuteReader();
  

  
   

              while (reader.Read())
  

              {
  

                    // In version 11.0 only
  

                    SqlGeometry g =
  

  SqlGeometry.Deserialize(reader.GetSqlBytes(0));
  

  
   

                    // In version 10.0 or 11.0
  

                    SqlGeometry g2 = new SqlGeometry();
  

                    g.Read(new BinaryReader(reader.GetSqlBytes(0).Stream));
  

              }
  

        }
  

• You can work around this issue by using assembly redirection in the application configuration file, as shown in the following example:
  

  Xml
  

  <runtime>
  

  <assemblyBinding xmlns="urn:schemas-microsoft-com:asm.v1">
  

  ...
  

  <dependentAssembly>
  

  <assemblyIdentity name="Microsoft.SqlServer.Types" publicKeyToken="89845dcd8080cc91" culture="neutral" />
  

  <bindingRedirect oldVersion="10.0.0.0" newVersion="11.0.0.0" />
  

  </dependentAssembly>
  

  ...
  

  </assemblyBinding>
  

  <runtime>
  

• You can work around this issue in your connection string by specifying a value of "SQL Server 2012" for the "Type System Version" attribute to force SqlClient to load version 11.0 of the assembly. This connection string attribute is available only in .NET 4.5 and above.
  
• The assemblyBinding tag should be wrapped under the runtime tag.
  

Support for AWE

32-bit Address Windowing Extensions (AWE) support is discontinued. This might result in slower performance on 32-bit operating systems. For installations using large amounts of memory, migrate to a 64-bit operating system.

XQuery Functions Are Surrogate-Aware

The W3C recommendation for XQuery functions and operators requires them to count a surrogate pair that represents a high-range Unicode character as a single glyph in UTF-16 encoding. However, in versions of SQL Server prior to SQL Server 2012, string functions did not recognize surrogate pairs as a single character. Some string operations – such as string length calculations and substring extractions – returned incorrect results. SQL Server 2012 now fully supports UTF-16 and the correct handling of surrogate pairs.

The XML data type in SQL Server only allows well-formed surrogate pairs. However, some functions can still return undefined or unexpected results in certain circumstances, since it is possible to pass invalid or partial surrogate pairs to XQuery functions as string values. Consider the following methods for generating string values when using XQuery in SQL Server:

• Provide a constant string value as a binary value. When using this method, it remains possible to pass invalid or partial surrogate pairs.
  
• Provide a constant string value by providing character entities. When using this method, it is not possible to pass invalid surrogate pairs. The XQuery functions require a single character entity for the high-level character. These functions raise an error if the character entities for the surrogate pair characters are provided.
  
• Import external values by using sql:column or sql:variable. When using these methods, it remains possible to introduce invalid or partial surrogate pairs.
  

Affected XQuery Functions and Operators

The following XQuery functions and operators now handle UTF-16 surrogate pairs correctly in SQL Server 2012:

• fn:string-length. However, if an invalid or partial surrogate pair is passed as an argument, the behavior of string-length is undefined.
  
• fn:substring.
  
• fn:contains. However, if a partial surrogate pair is passed as a value, contains may return unexpected results, since it may find the partial surrogate pair contained in well-formed surrogate pair.
  
• fn:concat. However, if a partial surrogate pair is passed as a value, concat can produce incorrect surrogate pairs or partial surrogate pairs.
  
• Comparison operators and the order by clause. Comparison operators include +, <, >, <=, >=, eq, lt, gt, le, and ge.
  

Distributed Query Calls to a System Procedure

Distributed query calls through OPENQUERY to some system procedures will fail when called from one server to another. This occurs when the Database Engine cannot discovery metadata for a procedure. For example, SELECT * FROM OPENQUERY(..., 'EXEC xp_loginfo').

Backward Compatibility

New behavior depends on compatibility level

The following functions and operators demonstrate the new behavior described above only when the compatibility level is 110 or higher:

• fn:contains.
  
• fn:concat.
  
• comparison operators and order by clause
  

New behavior depends on default namespace URI for functions

The following functions demonstrate the new behavior described above only when the default namespace URI corresponds to the namespace in the final recommendation, that is, http://www.w3.org/2005/xpath-functions. When the compatibility level is 110 or higher, then by default SQL Server 2012 binds the default function namespace to this namespace. However these functions demonstrate the new behavior when this namespace is used regardless of the compatibility level.

• fn:string-length
  
• fn:substring