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
| Feature | Description |
| Table partitioning | Previous limitations on table partitioning are eliminated. |
| Larger transactions | With 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 rebuild | Before 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 support | With the V12 preview you can issue the T-SQL CHECKPOINT statement for your database. |
| More options on ALTER DATABASE | The 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 commands | The 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
| Feature | Description |
| Window functions in T-SQL queries | The 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 integration | The 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 data | Change 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 indexes | The 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 heap | The 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 roles | For security and permissions control, the V12 preview enables you to issue GRANT – DENY – REMOVE statements against application roles. |
Category: Better customer insights
| Feature | Description |
| 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 tracking | The 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.
| Feature | Description |
| Parallel processing for queries | The V12 preview provides a higher degree of parallelism for queries that can benefit from it. |
| Briefer I/O latency | The V12 preview has significantly briefer latency for input/output operations. |
| Increased IOPS | The V12 preview can process a larger quantity of input/output per second (IOPS). |
| Log rate | The 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.