One of the enhancements being introduced in SQL Server 2016 involves handling large record definitions and large object types, VARCHAR(MAX), NVCARCHAR(MAX) and VARBINARY(MAX). While BINARY(MAX) was not specified in the MSDN article, it may be worth reviewing.
One of the first things I noticed was the extension of built-in string functions to handle LOB types. Len, LTrim, RTrim and Substring can now be used with data longer than 8k, something that didn’t work previously. This allows you to manipulate large strings without having to use old text manipulation techniques.
Functions have been extended also. Now, you can now return LOB types in a scalar User Defined Function.
Another extension that relates indirectly to the LOB types is the ability for a database record to exceed a single 8k page. Previously, only LOB types could exceed an 8k page boundary. If the length of the non-LOB columns defined in your table exceeded 8k, you received a warning that you could have records that would not save. So, you could define more space than 8k, but if your data exceeded 8k, the data would not be written. With this new extension, you can exceed the 8k limit, even when none of the columns use an LOB data type.
These are some pretty significant changes to the SQL engine. It may not seem like a lot, until you need to define a table using data types that make sense, not the data types enforced by the engine. I think I’m more impressed with the string functions.
If you’d like to check out more enhancements to the SQL Engine in 2016, here’s a link to get you started.
Cheers,
Ben