Columnstore Index


Columnstore index was introduced in the SQL Server 2012 to significantly speed-up the processing time of common data warehousing queries.

Generally data warehousing workloads involve summarizing large volume of data. As you might know, we use different techniques in data-warehousing (DW) and decision support systems (DSS) to improve performance like pre-computed summary tables, indexed views, OLAP cubes etc. Although, these techniques greatly speed up query the data processing but they have some side effects. These techniques can be inflexible, hard to maintain, and must be designed specifically for specific query problem.

Data warehousing processing overhead can be significantly reduced by using SQL Server Columnstore index. Not only that, columnstore indexes reduces the overhead of the other solutions. Columnstore indexes also enable queries to compute the results quickly so that pre-computation is not required.


Characterisitcs of SQL Server Columnstore Indexes:

·        Columnar data format – unlike the traditional row based organization of data (called rowstore format), columnstore indexes group and store data in one column at a time. SQL Server query processing can take advantage of this new data layout and significantly improve query execution time.

·        Faster query results – Columnstore indexes can produce faster results for the following reasons:

o    Only the columns needed must be read. Therefore, less data is read from disk to memory and then from memory to processor cache.

o    Columns are heavily compressed. This reduces the number of bytes that must be read and moved and hence less I/O operations.

o    Most queries do not touch all columns of the table. Therefore, many columns will never be brought into memory. This, combined with excellent compression, improves buffer pool usage, which reduces total I/O.

o    Advanced query execution technology processes chunks of columns called batches in a streamlined manner, reducing CPU usage.

·        Key columns – There is no concept of key columns in a columnstore index.

·        Clustered index key – all the columns of clustered index must be present in the nonclustered columnstore index. If a column in the clustering key is not listed in the create index statement, it will be added to the columnstore index automatically.

·        Partitioning – Columnstore indexes works with table partitioning. No change to the table partitioning syntax is required. Albeit, nonclustered columnstore index can only be created on a partitioned table if the partitioning column is part of the columnstore index.

·        Record Size – There is no index key record size limitation on columnstore indexes.

·        Query processing – Along with the columnstore index, SQL Server introduces batch processing to take advantage of the columnar orientation of the data. The columnstore structure and batch processing both contribute to performance gains.

·        Table cannot be updated – For SQL Server 2012, a table with a columnstore index cannot be updated.

How to create Columnstore Index:

Columnstore index can be create using SQL Server Management Studio as well as using T-SQL.

Creating Columnstore index by using SQL Server Management Studio (SSMS): 

1.       Open Management Studio and use Object Explorer to connect to the SQL Server Database Engine.

2.       In Object Explorer, expand the instance of SQL Server, expand Databases, expand a database, expand a table, right-click on required table, point to New Index, and then click Non-Clustered Columnstore Index.

3.       In the Index name dialog box, under the General tab, type a name for the new index, and then click Add.

4.       In the Select Columns dialog box, select the columns to participate in the columnstore index, and then click OK two times to create the index.

Creating Columnstore index by using T-SQL:

Create columnstore index on Employee table, name it CSI_Employee, and include all the columns!

-- Check whether index CSI_Employee exists on Employee

-- and delete if exists.

IF EXISTS(  SELECT  name FROM sys.indexes

            WHERE   object_id = OBJECT_ID('dbo.Employee')

            AND     name = 'CSI_Employee')

DROP INDEX CSI_Employee ON dbo.Employee

GO

 

-- Create nonclustered columnstore index CSI_Employee on Employee.

CREATE NONCLUSTERED COLUMNSTORE INDEX CSI_Employee

ON dbo.Employee

(

     EmployeeID

    ,FirstName

    ,MiddleName

    ,LastName

    ,DOB

    ,DepartmentID

)

GO 

Data Types

Columnstore index support some data-types and doesn't support others. You need to be aware of this while creating columnstore index to avoid any run time errors.

The following data types can be included in a columnstore index:

·        char and varchar

·        nchar and nvarchar (except varchar(max) and nvarchar(max))

·        decimal (and numeric) (Except with precision greater than 18 digits.)

·        int, bigint, smallint, and tinyint

·        float (and real)

·        bit

·        money and smallmoney

·        All date and time data types (except datetimeoffset with scale greater than 2)

The following data types cannot be included in a columnstore index:

·        binary and varbinary

·        ntext, text, and image

·        varchar(max) and nvarchar(max)

·     uniqueidentifier

·         timestamp

·         sql_variant

·         decimal and numeric with precision greater than 18 digits

·         datetimeoffset with scale greater than 2

·         CLR types (hierarchyid and spatial types)

·         xml

 

Columnstore Index Restrictions and Limitations

There are certain restrictions and limitations on columnstore indexes which are listed below:

·         Columnstor index cannot have more than 1024 columns.

·         Columnstor index annot be clustered or unique. Only nonclustered columnstore indexes are available.

·         Columnstor index cannot be created on a view or indexed view.

·         Cannot include columns of restricted data-types as mentioned above.

·         Cannot act as a primary key or a foreign key.

·         Cannot be changed using the ALTER INDEX statement. Drop and re-create the columnstore index instead.

·         Cannot be created with the INCLUDE keyword.

·         Cannot include the ASC or DESC keywords for sorting the index. Columnstore indexes are ordered according to the compression algorithms.

·         Does not use or keep statistics in the manner of a traditional index.

Note: You can compare the performance optimization be executing heavy volume queries before and after creating columnstore index. You might get 50% to 90% performance gain!

 

Introducing IIF() To  SQL Server Family

IIF() is a brand new logical function introduced with SQL Server 2012 that allows you to perform IF...THEN...ELSE conditional statements within a single function. Behavior of this function is similar to any other programming language IIF() function!

In earlier versions of SQL Server, we have used IF...ELSE and CASE WHEN...THEN...ELSE...END to perform logical conditional operations.

IIF() can be handy for writing conditional CASE statements in a single function. It evaluates the expression passed in the first parameter and returns either TRUE or FALSE.

--Example1: Repalcement of simple IF...ELSE statement

DECLARE @GradeCHAR(1) = 'A'

SELECT IIF(@Grade = 'F', 'Failed', 'Passed') AS Result

GO

Here is the output of above T-SQL code:
 

--Example2: Nested IIF()

DECLARE @PercentDECIMAL(5, 2) = '50'

SELECT IIF(@Percent > 80, 'A',

IIF(@Percent > 60, 'B', 'C'))

GO;

Here is the output of above T-SQL code:

----

C

(1 row(s) affected)

-- Example3: In this example, we will evaluate the marks obtained
-- by Smith and Hari and identify who got higer marks between them.

DECLARE @Smith_Marks INT= 72

,@Hari_Marks INT = 86

SELECT IIF(@Smith_Marks > @Hari_Marks

,CONCAT('Smith got ',@Smith_Marks-@Hari_Marks,' marks higher than Hari')

,CONCAT('Hari got ' ,@Hari_Marks-@Smith_Marks,' marks higher than Smith')

)

GO

Here is the output:

---------------------------------------------

Hari got 14 marks higher than Smith

(1 row(s) affected)

 

SQL Server 2012 Analytical Functions: Lead and Lag

SQL Server 2012 supports many T-SQL analytic functions which are listed below:

• LEAD: Accesses data from a subsequent row in the same result set without the use of a self-join.
• LAG: Accesses data from a previous row in the same result set without the use of a self-join.
• CUME_DIST: Calculates the cumulative distribution of a value in a group of values. In other words, CUME_DIST computes the relative position of a specified value in a group of values
• FIRST_VALUE: Returns the first value in an ordered set of values.
• LAST_VALUE: Returns the last value in an ordered set of values.
• PERCENTILE_CONT: Calculates a percentile based on a continuous distribution of the column.
• PERCENTILE_DISC: Computes a specific percentile for sorted values in an entire rowset or within distinct partitions of a rowset.
• PERCENT_RANK: Calculates the relative rank of a row within a group of rows.

Analytic functions compute an aggregate value based on a group of rows. However, unlike aggregate functions, they can return multiple rows for each group. You can use analytic functions to compute moving averages, running totals, percentages or top-N results within a group.

In this article, we are going to focus only on Lead and Lag functions.

Lead function accesses data from a subsequent row and Lag function accesses from previous row in the same result set without the use of a self-join.

Syntax:

LEAD | Lag ( scalar_expression [ ,offset ] , [ default ] )
    OVER ( [ partition_by_clause ] order_by_clause )

scalar_expression is the value to be returned based on the specified offset. Offset specify the number of rows forward/backward from the current row from which to obtain a value. Default is the value to return when scalar_expression at offset is NULL.

Now lets explorer Lead function with an example:

Problem: Compare values between current year, previors year, and next year.

---------------------

-- Prepare Test Data

---------------------

DECLARE @TestData TABLE (Period DATE, Amount INT

INSERT @TestData

SELECT '2006-12-31' Period, 1000 Amount UNION

SELECT '2007-12-31' Period, 3000 Amount UNION

SELECT '2008-12-31' Period, 5000 Amount UNION

SELECT '2009-12-31' Period, 4000 Amount UNION

SELECT '2010-12-31' Period, 7000 Amount UNION

SELECT '2011-12-31' Period, 6000 Amount UNION

SELECT '2012-12-31' Period, 8000 Amount

 

----------------------------------------------------------

-- Fetch current year, previous year, and next year data

----------------------------------------------------------

SELECT Period
,Amount AS CurrentAmount

,LAG(Amount, 1, 0) OVER (ORDER BY Period) AS PreviousAmount

,LEAD(Amount, 1, 0) OVER (ORDER BY Period) AS NextAmount

FROM @TestData

 

 

 

 

 

 

Note that in the above we have set the default vaule 0 for Lead and Lag functions so we are getting zero (0) instead of NULL. Also we have set the offset to 1 for lead and lag function so we are getting only subsequent  and previous values. Offset can be set as per requirement but in most of the cases we use 1.

For more information on Analytical Functions, please click here.