You cannot enable the Stretch DB feature for any SQL Server table. A table should have Stretch DB-friendly properties. The following list shows the table properties that prevent the use of the Stretch DB feature:

The list is not so short, but let's see how huge these limitations are in practice. Despite their power and a completely new technology stack behind them, memory-optimized tables are still not in use intensively. From my experience, I can say that most companies still don't use memory-optimized tables in production environments due to the lack of use cases for them, hardware resources, or even the knowledge required for their implementation and configuration. In addition to this, memory-optimized tables usually store hot data, data that is frequently needed and whose content is not intended to be sent to the cloud. Therefore, you cannot say that the first limitation is a huge one. You will spend more time on memory-optimized tables later in this book (in Chapter 11, Introducing SQL Server In-Memory OLTP and Chapter 12, In-Memory OLTP Improvements in SQL Server 2016).

File Table and FILESTREAM tables appear frequently in the list of limitations for new SQL Server features. The same is true for tables using Change Data Capture or Change Tracking features. They are simply not compatible with many other features since they address specific use cases. Therefore, I am not surprised to see them in this list. Full-text indexes and indexed views also prevent Stretch DB usage. From my experience, in companies that I have worked with or where I was involved as a consultant, less than 10% of tables belong to these categories. According to all of these limitations, I would say that the potential of Stretch DB is slightly reduced, but not significantly. However, the most important limitation is that a table cannot be referenced with a foreign key. This is an implementation of database integrity and many tables are and should be referenced with foreign keys. Therefore, this is a serious limitation for Stretch DB usage and significantly reduces the number of potential tables that can be stretched.

However, this is just the beginning; more limitations will come in the next subsection.

Even if your table does not violate constraints from the preceding table of limitations, it is still far away from fulfilling the conditions for stretching. The following properties and characteristics of table columns don't support the Stretch DB feature:

Let's repeat a similar analysis of the reduced potential of Stretch DB for the items from this list. I think that Microsoft has a balanced approach with deprecated data types; they are not removed in order to prevent the breaking of changes in legacy code, but all new features don't support them. This is completely correct and it should not be considered a limitation.

The other unsupported data types are used rarely and do not represent a huge limitation.

However, the most important limitation in this list is that a table column in a Stretch Database cannot have a default or check constraint. This is a huge limitation and significantly reduces the usage and importance of the Stretch DB feature!

Also, as mentioned before, you should not remove database objects created to implement database integrity just to enable Stretch DB. Default constraints and foreign keys are the reasons why there is not a single table in the AdventureWorks2014 database that is ready for stretching.