ColumnStore Index Best Practice at SQLdays

Last week I did two talks at SQLdays, Erding, Germany. One of them was about “ColumnStore Index Best Practice”. I basically set the frame by using a three-folded agenda:

  • ColumnStore
  • Non-Clustered ColumnStore Index
  • Clustered ColumnStore Index

It is important to understand the basic concepts of a ColumnStore, as opposed to the way data in a relational database was stored over the last decades. The latter is now called RowStore and didn’t have a special name until ColumnStore was introduced, because it was the one and only way how to store data.

After getting behind the “magic” of a ColumnStore it is much more easier to evaluate the use cases, where it is appropriate, and what to consider when maintaining this special kind of “index”. (Even the definition of the word “index” gets a new point of view, as rows in a ColumnStore are not sorted – which is conversed to a b-tree index.)

Non-Clustered ColumnStore Index was introduced in SQL Server 2012 and came with a whole list of limitations. One of the most discussed limitation was, that the table for which the Non-Clustered ColumnStore Index was created, becomes read-only.

Clustered ColumnStore Index was introduced in SQL Server 2014 and came with a better performance and a shorter list of limitations. One of the limitations we got rid of was, that the table for which a Clustered ColumnStore Index was created, was still updateable. (But in SQL 2014 tables with a Non-Clustered ColumnStore Index is still read-only).

You can find my slide deck here.

Sincerly,
Markus Ehrenmüller-Jensen
@MEhrenmueller & @SQLederhose
markus.ehrenmueller@gmail.com

Advertisements
ColumnStore Index Best Practice at SQLdays

Introduction to SQL Azure Databases at SQLdays

This week I did two talks at SQLdays, Erding, Germany. One of them was an “Introduction to SQL Azure Databases”. As it was an introductionary level 200 talk, I had lot of slides to explain the concepts and the use cases pro & contra cloud computing, but also prepared some quick demos to show how to

  • subscribe to Azure,
  • create a server,
  • create a database, and last-but-not-least,
  • manage a database

The aim was, that the attendees could get an impression, how putting a database in Microsoft’s cloud offering would “feel” alike.

You can finde my slidedeck here.

Unfortunately through my demos I had to discover that I could not find the link to “Azure Online SQL Database Management”, which I was sure was there the other day, when I rehearsed my demos.

Doing a proper research I found the solution:
When I upgraded my server from V11 to V12 (to get the new (preview) features like Auditing, Dynamic data masking, Transparent data encryption, …) I unintentionally disabled the SQL Database Management, which is a deprecated feature and not available for databases in V12.

Personally I feel really bad about the fact, that SQL Database Mangement is abandoned in the new version. On the one hand I can understand, that resources even in a big company like Microsoft are not unlimited, but on the other hand being able to manage your database objects through the browser without any need to install tools locally, was really helpful. From V12 on we have to stick to tools installed on-premises on your client to maintain tables, views, indexes, and the like.

Sincerly,
Markus Ehrenmüller-Jensen
@MEhrenmueller & @SQLederhose

Introduction to SQL Azure Databases at SQLdays