Cheap And Reliable Hosting :: Latest SQL Database Update V12

This topic describes latest Azure SQL Database V12 (preview). The new preview provides nearly complete compatibility with the Microsoft SQL Server engine. And the preview brings more Premium performance to customers. These enhancements help to streamline SQL Server application migrations to Azure, and help customers who have heavier database workloads.

This preview marks the first step in delivering the next generation of the Azure SQL Database service. It gives customers more compatibility, flexibility, and performance. Internal tests of the preview at the Premium service tier showed that some queries now complete in a fraction of the time they take on today’s Premium Azure SQL Database. Even bigger improvements were seen in some scenarios that benefit from the in-memory columnstore technology.

NOTE:

Test databases, database copies, or new databases, are good candidates for upgrading to the preview. Production databases that your business depends on should wait until after the preview period.

Key highlights

Key highlights of this V12 preview include the following:

  • Easier management of large databases to support heavier workloads with parallel queries (Premium only), table partitioning, online indexing, worry-free large index rebuilds with 2GB size limit removed, and more options on the alter database
  • Support for key programmability functions to drive more robust application design with CLR integration, T-SQL window functions, XML indexes, and change tracking for data.
  • Breakthrough performance with support for in-memory columnstore queries (Premium tier only) for data mart and smaller analytic workloads.
  • Monitoring and troubleshooting are improved with visibility into over 100 new table views in an expanded set of Database Management Views (DMVs)
  • New S3 performance level in the Standard tier: offers more pricing flexibility between Standard and Premium. S3 will deliver more DTUs (database throughput units) and all the features available in the Standard tier.

Details of the new V12 preview enhancements explained

This section names and explains the new features in each category.

Category: Expanded database management

 
FeatureDescription
Table partitioningPrevious limitations on table partitioning are eliminated.
Larger transactionsWith the V12 preview you are no longer limited to a maximum of 2 GB of data modifications in a single transaction.One benefit is that rebuilding a large index is no longer limited by 2 GB transaction size limit.For general information, see Azure SQL Database Resource Limits.
Online index build and rebuildBefore the V12 preview, Azure SQL Database generally supported the (ONLINE=ON) clause of the ALTER INDEX statement, but this was not supported for indexes on a BLOB type column. Now the V12 preview does support (ONLINE=ON) even for indexes on BLOB columns.The ONLINE feature enables queries to benefit from an index even while the index is being rebuilt.
CHECKPOINT supportWith the V12 preview you can issue the T-SQL CHECKPOINT statement for your database.
More options on ALTER DATABASEThe V12 preview supports more of the options that are available on the ALTER DATABASE statement.For more information, see ALTER DATABASE (Transact-SQL) or Azure SQL Database Transact-SQL Reference.
More DBCC commandsThe V12 preview supports the following additional DBCC commands:CHECKALLOC
CHECKCONSTRAINTS
CHECKDB
CHECKFILEGROUP
CHECKIDENT
CHECKTABLECLEANTABLE
DBREINDEX
INDEXDEFRAG
INPUTBUFFER
OPENTRAN
PROCCACHE
SHOWCONTIG
SQLPERF
TRACESTATUS
UPDATEUSAGE
USEROPTIONS

Category: Programming and application support

 
FeatureDescription
Window functions in T-SQL queriesThe ANSI window functions are access with the OVER clause.Itzik Ben-Gan has an excellent blog post that further explains window functions and the OVER clause.
.NET CLR integrationThe CLR (common language runtime) of the .NET Framework is integrated into the V12 preview.Only SAFE assemblies that are fully compiled to binary code are supported. For details see CLR Integration Programming Model Restrictions.You can find information about this feature in the following topics:
* Introduction to SQL Server CLR Integration
* CREATE ASSEMBLY (Transact-SQL).
Change tracking for dataChange tracking for data can now be configured at the database or table level.For information about change tracking, see About Change Tracking (SQL Server).
XML indexesThe V12 preview enables you use the T-SQL statements CREATE XML INDEX and CREATE SELECTIVE XML INDEX.Information about XML indexes is available at:
* CREATE XML INDEX (Transact-SQL)
* Create, Alter, and Drop Selective XML Indexes
Table as a heapThe V12 preview enables you to create a table that has no clustered index.This feature is especially helpful for its support of the T-SQL SELECT…INTO statement which creates a table from a query result.
Application rolesFor security and permissions control, the V12 preview enables you to issue GRANT – DENY – REMOVE statements against application roles.

Category: Better customer insights

 
FeatureDescription
DMVs (dynamic management views)The following DMVs are added:dm_clr_properties
dm_clr_tasksdm_db_file_space_usage
dm_db_log_space_usage
dm_db_session_space_usage
dm_db_task_space_usagedm_exec_background_job_queue
dm_exec_background_job_queue_stats
dm_exec_query_optimizer_info
dm_exec_query_profiles
dm_exec_query_resource_semaphores
dm_exec_query_transformation_statsdm_tran_active_snapshot_database_transactions
dm_tran_commit_table
dm_tran_current_snapshot
dm_tran_current_transaction
dm_tran_top_version_generators
dm_tran_transactions_snapshot
dm_tran_version_store
Change trackingThe V12 preview fully supports change tracking.For details of this feature see Enable and Disable Change Tracking (SQL Server).

Performance improvements at the Premium service tier

These performance enhancements apply to the P2 and P3 levels within the Premium service tier.

 
FeatureDescription
Parallel processing for queriesThe V12 preview provides a higher degree of parallelism for queries that can benefit from it.
Briefer I/O latencyThe V12 preview has significantly briefer latency for input/output operations.
Increased IOPSThe V12 preview can process a larger quantity of input/output per second (IOPS).
Log rateThe V12 preview can log more data changes per second.

Summary of enhancements

The V12 preview elevates our SQL Database close to full feature compatibility with our SQL Server product. The V12 preview focuses on the most popular features, and on programmability support. This makes your development more efficient and more enjoyable. It is now even easier to move your SQL database applications to the cloud.

And at the Premium tier the V12 preview brings major performance improvements. Some applications will see extreme gains in query speed. Applications with heavy workloads will see reliably robust throughput.

Cautions for the V12 preview

  • Any database that is upgraded to the preview cannot be reverted back to the earlier version.
  • Test databases, database copies, or new databases, are good candidates for upgrading to the preview. Production databases that your business depends on should wait until after the preview period.
  • The Web and Business service pricing tier is not supported on this preview version.