What I like – SQL Server 2012 SP1 patch release

As of now (this post written date) the Service Pack1 for SQL Server 2012 still in CTP4 stage for your own testing on enhancements, which will be ready in next few weeks time as a full RTM’d downloadable package.

During the preliminary testing the list of features I like and useful for users are:

  • XML INDEX – Selective. A valuable enhancement to fine tune XML related data.
    • Selective XML Index allows users to promote certain paths from their XML documents that will be indexed. The improvement introduces a new type of XML index to SQL Server in addition to Primary XML Index (PXI). The new indexing will improve querying performance over data stored as XML in SQL Server, thus allow for much faster indexing of large XML data workloads and improve on scalability by reducing storage costs of the index itself.
  • Permissions to run DBCC SHOW_STATISTICS. As it sounds DBCC is always restricted to SA or elevated permissions users, but since PCU1 for SQL Server 2012 this is modified to change restriction and allow users with SELECT permission to use this statement. The relevant permissions for the users must be:
    • Users must have permissions on all columns in the statistics object 
    • Users must have permission on all columns in a filter condition (if one exists) 
  • A better way to know when was last time the statistics object was updated for a table or indexed view. A DMF (Function)  sys.dm_db_stats_properties allows the users to return the properties of statistics for a specified database object. Only users with SA, DB_OWNER and DDL_ADMIN privileges can use this DMF. This was ONLY available since SQL Server 2008 R2 SP2 release, and to obtain list of statistics BOL refers:
    • SELECT sp.stats_id, name, filter_definition, last_updated, rows, rows_sampled, steps, unfiltered_rows, modification_counter FROM sys.stats AS stat CROSS APPLY sys.dm_db_stats_properties(stat.object_id, stat.stats_id) AS sp WHERE stat.object_id = object_id('<ObjectName>');
  • SQL Server 2012 Express with full SSMS features (WHOA this is biggie!) You may wonder many times users complain to DBA that ‘many’ features are not available within their SQL Server tools, because they might have downloaded the light-weighted tool SQL Server Management Studio Express. Now the SQL Server 2012 SP1 Express editions will have complete SSMS tool features! Also the SQL Server 2012 delivers the specified list of features within SSMS.
  • SlipStream full installation. Since SQL Server 2012 RTM release it was referred that SlipStream (that was available since SQL Server 2008 R2 & SQL Server 2008 SP2) is deprecated, as it is replaced by Product Update feature. Now SQL Server 2012 SP1 builds provides capability to install/extract complete installation up to Service pack using SlipStream image that contains SQL Server 2012 RTM image + SP1 files.