Limitations for Stretch Database in SQL

We know that Stretch Database is a Microsoft solution for securely migrating cold data to Azure with remote query processing abilities. The biggest fact is that it does not require any change in your business application code and SQL data components. We have options to choose the pricing tier and configure settings in the Azure Portal to maintain control over price and costs as per our business needs and have the facility to scale up or down whenever it required.

This is the great feature of SQL Server 2016 but it has some limitations which cannot work in Stretch databases such as-

A. Limitations that currently prevent us from enabling Stretch for a table – There are some table properties, data types, column types, Constraints and Indexes that stop us from enabling Stretch for a table which are given below:

1. Table Properties: If a table contains more than 1,023 columns or more than 998 indexes then we can enable that table for Stretch. Memory-optimized tables do not fall for Stretching also. FILESTREAM data is not workable for Stretch.
2. Data types - Stretch is not workable for text, ntext and image, timestamp, sql_variant, XML and CLR data types including geometry, geography, hierarchyid, and CLR user-defined types.
3. Column types - Stretch is not appropriate for COLUMN_SET and Computed columns also.
4. Constraints - If a table contains default constraints and check constraints and having foreign key constraints that reference the table then we cannot enable it for stretching. In a parent-child relationship case, we can enable Stretch for the child table but not for the parent table.
5. Indexes - If a table contains full text indexes, XML indexes, Spatial indexes and Indexed views for reference the table then we cannot enable it for stretching.

B. Limitations for Stretch-enabled tables – If our table is enabled for stretching then it should have the following limitations-

1. Constraints - Uniqueness is not enforced for UNIQUE constraints and PRIMARY KEY constraints in the Azure table that contains the migrated data.
2. DML operations - We cannot UPDATE or DELETE rows from the stretch-enabled table that have been migrated, or rows that are eligible for migration. The same condition is applicable for a view that includes Stretch-enabled tables. If you are working on linked server then you can't INSERT rows into a Stretch-enabled table.
3. Indexes - We cannot create an index for a view that includes Stretch-enabled tables. Another thing is that filters on SQL Server indexes are not propagated to the remote table.

Limitations for Stretch Database in SQL Server
Enable Database for Stretch to Azure

References: https://msdn.microsoft.com/en-us/library/dn935011.aspx