After a theoretical introduction, it is time to start using columnar storage. You will start by learning how to create and use NCCI. You already know from the previous section that an NCCI can be filtered. Now you will learn how to create, use, and ignore an NCCI. In addition, you will measure the compression rate of the columnar storage.

Because of the different burdens on SQL Server when a transactional application uses it compared to analytical applications usage, traditionally, companies split these applications and created data warehouses. Analytical queries are diverted to the data warehouse database. This means that you have a copy of data in your data warehouse, of course with a different schema. You also need to implement the ETL process for scheduled loading of the data warehouse. This means that the data you analyze is somehow stalled. Frequently, the data is loaded overnight and is thus one day old when you analyze it. For many analytical purposes, this is good enough. However, in some cases, users would like to analyze current data together with archived data. This is called operational analytics. SQL Server 2016 with columnar storage and in-memory tables makes operational analytics realistically possible.

In this section, you will learn how to do the following:

• Create nonclustered columnstore indexes
• Ignore an NCCI in a query
• Use NCCI in a query
• Architect an operational analytics solution