Microsoft’s documentation indicates that this change will greatly simplify execution plans and reduce compilation time for the execution plan.What does this mean for any existing keys and the queries that write to related columns?Some of these changes are very straightforward and do not require any real testing, but discussion is still worth the time and effort 🙂 Any database may have its compatibility mode set to a previous (supported) version of SQL Server.
The resulting execution plans for the above queries are as follows: I didn’t bother to fit the entire plans into single images.
It’s clear from the number of tables being queries above that we are not getting a clear foreign key operator, and are instead getting the same plans we’ve always gotten previously. The following image is from an MSDN blog post: This shows clearly that the long string of referential integrity check scans/seek are replaced with a single simplified operator that presumably outperforms the previous methodology.
Let’s try a few other queries and see what changes we can uncover: INSERT INTO Production.
Product ( Name , Product Number , Make Flag , Finished Goods Flag , Color , Safety Stock Level , Reorder Point , Standard Cost , List Price , Size , Size Unit Measure Code , Weight Unit Measure Code , Weight , Days To Manufacture , Product Line , Class , Style , Product Subcategory ID , Product Model ID , Sell Start Date , Sell End Date , Discontinued Date ,rowguid , Modified Date ) SELECT 'Wing Nut', 'WN-1234', 1, 0, 'Silver', 1000, 500, 1.00, 1.50, NULL, 'G', 'CM', NULL, 0, NULL, NULL, NULL, NULL, NULL, CAST(CURRENT_TIMESTAMP AS DATE), NULL, NULL, NEWID(), CURRENT_TIMESTAMP; DELETE FROM Production.
This means that if you upgrade a server from SQL Server 2014 to 2016 and leave a database in compatibility level 120, then query plans will remain the same as they were on the old version.
New optimizer features will only become available when the database is altered to utilize compatibility level 130.
We can validate the current compatibility mode for any (or all) databases on a server via the Running this on my local machine returns a single database that is running under SQL Server 2008R2 compatibility mode: Compatibility mode may be updated using an ALTER DATABASE command, such as this: This TSQL will adjust Adventure Works2014 to use SQL Server 2016 compatibility.
Prior to SQL Server 2016, running a database in a previous version’s compatibility mode had no guarantees with regards to the behavior of the query optimizer and the executions plans it generated.
Whenever a foreign key column is written, a check must occur of the referenced table to ensure the write operation doesn’t violate the foreign key definition.