Not Dead Yet - Text and NText in SQL 2014

I was recently approached by a client who was running SQL Server 2008 with some databases in SQL 2000 compatibility level. He asked what would happen if his databases were backed up and restored to SQL Server 2014. What's the compatibility level? Is the text data type still supported? Would it even restore?

Here's the answers, based off SQL Server 2014 currently in CTP2, and for these matters, they hold true with this statement on MSDN: "No breaking changes in SQL Server 2014."

SQL Server 2012 does not support SQL 2000 compatibility mode, and neither does the SQL Server 2014 CTP2.

When restoring a full backup of a SQL Server 2008 R2 database in SQL 2000 compatibility level to SQL Server 2014 CTP2, the restore process will upgrade the database and change the compatibility level to 100 (SQL 2008).

Compatibility Levels Supported by SQL 2012 SP1

Compatibility Levels Supported by SQL 2014 CTP2

Frustratingly, one of the deprecated old data types from SQL's nascent years is still hanging around.  The text (and ntext) data types still work in SQL Server 2014 CTP2.

When given the opportunity to upgrade that text data type to a modern data type, take care to do a MAX(DATALENGTH(textcolumn)) and find out how many bytes you're actually storing. If it is less than 8000 bytes, (8000 characters in text or 4000 characters in ntext), don't just go straight to varchar(max).

Other breaking changes that occurred after SQL 2000, like ambiguous columns in an ORDER BY or old "star join" syntax ("*=" and "=*"), are things you need to watch out for when promoting the compatibility mode of a SQL 2000 database, as always.

When you restore a SQL 2000 compatibility level database to SQL Server 2012 or SQL Server 2014, syntax that broke between SQL 2000 and SQL 2005 are now pushed into action and will fail. This isn't news, just a reminder.