You cannot enable the Stretch DB feature for any SQL Server table. A table should have stretch-friendly properties. The following list shows the table properties that prevent the use of the Stretch DB feature:
- It is a memory-optimized table
- It is a file table
- It contains FILESTREAM data
- It uses Change Data Capture or Change Tracking features
- It has more than 1,023 columns or more than 998 indexes
- Tables referenced with a foreign key
- It is referenced by indexed views
- It contains full-text indexes
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 2017).
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.