Columnstore Indexes: Finally Awesome in SQL 2016

Columnstore indexes are amazing for optimizing the performance of large rowcount queries that would otherwise look like a SCAN operation in an execution plan. Introduced in SQL Server 2012, they render the table read-only, however, and needed to be dropped (not even disabled) in order to insert/update/delete rows.

If you tried to update a table with a nonclustered columnstore index in SQL 2012 or SQL 2014:

Msg 35330, Level 15, State 1, Line 2
UPDATE statement failed because data cannot be updated in a table that has a nonclustered columnstore index. Consider disabling the columnstore index before issuing the UPDATE statement, and then rebuilding the columnstore index after UPDATE has completed.

In SQL 2014, you can now make a clustered columnstore index, but it has only narrow, specific uses because clustered columnstore indexes don't support traditional (B-tree) nonclustered indexes, a primary key, foreign keys, etc.

All those limitations above are going away in SQL 2016. Right now: SQL 2016 will release on June 1 2016. Release candidates are available for download but aren’t for production use.

SQL 2012 - Nonclustered columnstore indexes can be created, but aren’t updateable. 

SQL 2014 - Nonclustered columnstore indexes can be created, but still aren’t updateable. Clustered columnstore indexes can now be created, are updateable.

SQL 2016 - Nonclustered and clustered columnstore indexes can be created, and are updateable. Plus, you can mix clustered columnstore with traditional B-tree indexes. (Lots of other features too!)

More about these features as Columnstore indexes evolve here: https://msdn.microsoft.com/en-us/library/dn934994%28v=sql.130%29.aspx

Want to benefit from columnstore indexes, but not using SQL 2016 yet?

You have three options.

1) Disable/rebuild columnstore nonclustered indexes when you do updates to the table. Not a big deal for nightly-build data warehouses, but painful for frequently-updating tables. Note that the ONLINE feature to create traditional indexes isn't available here.

DROP INDEX [IDX_CS_Table_Covering] ON [dbo].[Table]
GO
<do updates/inserts>
GO
CREATE NONCLUSTERED COLUMNSTORE INDEX [IDX_CS_Table] ON [dbo].[Table] ( Column1, Column2... )
GO

(When you move to SQL 2016, these drop/create steps won’t be necessary any more.)

2) SQL 2014 only. Drop all existing clustered/nonclustered indexes and foreign keys on the table, change the table to have a single clustered columnstore index. This requires performance testing for smaller-scope queries, but should provide the big performance gains you’re looking for, at the cost of dropping the primary key and related foreign keys. This is suitable for narrow use only in reporting database designs and not in normalized environments.

<drop all existing indexes on [dbo].[Table], including the primary key and linked foreign keys>
CREATE CLUSTERED COLUMNSTORE INDEX [IDX_CS_Table] ON [dbo].[Table]
GO

3) SQL 2014 only. Create a copy of the table that implements 2) above, so you’ll have a table with traditional indexes plus an exact copy of the table but with only a clustered columnstore index. You would then need to point big report queries at dbo.Table_CS and other queries (that use your traditional nonclustered indexes) at dbo.Table. And we would need to modify all insert/update/delete operations in the ETL on dbo.Table in order to replicate any row operations into dbo.Table_CS. This is painful, I understand, but should provide the big performance gains you're looking for. (It would be totally unnecessary to design two tables and redirect some reports in this way if you intend to move to SQL 2016 in the near term, so this step is not recommended.)

That's it!

Need proof that columnstore indexes are awesome and will improve the performance of your large queries? Get used to loving columnstore scans! 

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.