TSQL Support in SQL Data Services

In its first release SQL Data Services will support a subset of TSQL language.  Each TSQL construct can be categorized as supported, partially supported or unsupported in an SDS session.  “Supported” in this context means that there is no difference in the way SDS treats a given statement or function compared to the on-premise SQL; “partially supported” means that SDS supports a subset of the functionality; “unsupported” means that a given feature is not supported by SDS.  At this point we do not plan to add any new TSQL constructs to the language or change the way existing functionality works.

In many respects SDS is very similar to an on-premise instance of SQL Server.  However, there are some differences:

·         SDS is a multi-tenant system

·         The hardware resources are owned, hosted and maintained by Microsoft

·         SDS is a service

These differences impose certain requirements on the system, which, in turn, translate into TSQL restrictions.  These restrictions fit into three buckets:

·         Resource manipulation.  We will block statements and options that try to directly manipulate physical resources.  This category includes RESOURCE GOVERNER, file group references and some physical server DDL statements.

·         Things that are either not applicable in the services world or are taken care of by us.  Replication is a good example – we take care of it, so you don’t have to.

·         Finally, there will be a few things that we just won’t be able to enable in time for our initial release.  Each feature that we add to SDS requires additional work to ensure that the service remains performant, scalable and secure.  This extra work takes time, which means some of the features will have to wait until v2.  Support for distributed query and CLR-related functionality are a few examples of the features in this category.

What’s in/what’s out

So let’s take a look at what we plan to support in SDS v1.  For clarity, we will break everything into three categories and we’ll look into each category separately: DDL, DML and Manageability.

DDL

SDS will support or partially support creation, manipulation and deletion of the following objects:

·         FUNCTION

·         INDEX

·         PROCEDURE

·         ROLE

·         SCHEMA

·         STATISTICS

·         SYNONIM

·         TABLE

·         TRIGGER

·         VIEW

Most of the CREATE and ALTER statements for the above objects will be partially supported.  This partial support will manifest itself in the restriction on some of the parameters you can specify when creating or altering the objects.  For example, the filegroup option of the CREATE TABLE statement will not be supported.

Users will not be able to create objects not listed above.

DML

The majority of DML will be either supported or partially supported.  This includes

·         SELECT/INSERT/UPDATE/DELETE functionality

·         DML triggers

·         JOINs

·         Transactions

·         A large number of built-in functions (aggregates, math, date and time, ranking, etc.)

Manageability

Manageability is the area which will be restricted the most.  The following list highlights some of the details:

·         Things that will work

·         Query tuning via SET SHOWPLAN and SET STATISTICS

·         Index tuning via create and drop index

·         Statistic management via UPDATE STATISTICS

·         Information schema views and system catalog views (e.g., sys.databases)

·         Local HA is automatically provided as part of the service.

·         Upcoming version of SSMS and VS tools will work against SDS

·         Things that won’t work

·         Backup command is not available due to multi-tenant nature of the service. We recommend using BCP or SSIS instead.

·         Server options (sp_configure)

·         SQL Profiler

·         SQL traceflag