A Step-by-Step Guide to Implementing Columnar Tables in SQL Server

Columnar storage was introduced in SQL Server 2016 as part of the SQL Server 2016 In-Memory OLTP feature. It is specifically designed for data warehousing and analytical workloads, where large amounts of data need to be scanned, aggregated, or analyzed efficiently. 

Columnar storage stores data in a column-wise format rather than the traditional row-wise storage, offering significant performance benefits for read-heavy operations such as reporting and analytics.

Key Benefits of Columnar Storage

Faster read performance: Optimized for analytics where only a few columns are needed in a query. Compression: Since column data is homogeneous, it achieves high compression rates, saving storage space.

Improved query performance: Aggregating or scanning specific columns is much faster in a columnar format, especially with large datasets.

Setting Up Columnar Tables in SQL Server

SQL Server implements columnar storage through the Columnstore Index. The Columnstore Index is a special kind of index used in large data tables where the data is stored in columns rather than rows. The clustered columnstore index (CCI) is the preferred method when creating columnar tables.

Step 1: Create a Sample Table

Let's start by creating a table with a large number of rows, which we will populate with random data to demonstrate the difference between row-store and column-store formats.

-- Creating a traditional Rowstore Table

CREATE TABLE SalesData_RowStore (
    SalesOrderID INT,
    ProductID INT,
    Quantity INT,
    SalesAmount DECIMAL(18, 2),
    OrderDate DATE
);

Step 2: Insert Data Into Rowstore Table

For the sake of performance demonstration, we will generate a large set of random data.

-- Generate a large set of random data for Rowstore Table
DECLARE @Counter INT = 0;
WHILE @Counter < 1000000
BEGIN
    INSERT INTO SalesData_RowStore (SalesOrderID, ProductID, Quantity, SalesAmount, OrderDate)
    VALUES (FLOOR(RAND() * 1000) + 1, 
            FLOOR(RAND() * 100) + 1, 
            FLOOR(RAND() * 100) + 1, 
            FLOOR(RAND() * 500) + 1, 
            DATEADD(DAY, FLOOR(RAND() * 365) + 1, GETDATE()));
    SET @Counter = @Counter + 1;
END

Implementing Columnstore Index (Columnar Table)

Step 1: Create a Columnstore Table

Now, let's create a table with a clustered columnstore index (CCI). This index allows SQL Server to store the data in a columnar format.

-- Creating a Columnstore Table with Clustered Columnstore Index
CREATE TABLE SalesData_ColumnStore (
    SalesOrderID INT,
    ProductID INT,
    Quantity INT,
    SalesAmount DECIMAL(18, 2),
    OrderDate DATE
);

CREATE CLUSTERED COLUMNSTORE INDEX CCI_SalesData ON SalesData_ColumnStore;

Step 2: Insert the Same Data Into the Columnstore Table

You can insert the same large dataset into the columnar table in the same way.

-- Insert data into Columnstore Table

DECLARE @Counter INT = 0;
WHILE @Counter < 1000000
BEGIN
    INSERT INTO SalesData_ColumnStore (SalesOrderID, ProductID, Quantity, SalesAmount, OrderDate)
    VALUES (FLOOR(RAND() * 1000) + 1, 
           FLOOR(RAND() * 100) + 1, 
            FLOOR(RAND() * 100) + 1, 
            FLOOR(RAND() * 500) + 1, 
            DATEADD(DAY, FLOOR(RAND() * 365) + 1, GETDATE()));
    SET @Counter = @Counter + 1;
END

Query Performance Without Columnar Index

Let's execute a typical query that aggregates data by ProductID and OrderDate. This will involve scanning through a large amount of data in the rowstore table.

-- Query on Rowstore Table
SELECT ProductID, SUM(SalesAmount) AS TotalSales
FROM SalesData_RowStore
WHERE OrderDate > '2023-01-01'
GROUP BY ProductID;

Expected Outcome

The query will scan all the rows in the table. Rowstore tables are not optimized for this type of query, and the performance might degrade with large datasets due to the need to read each row.

Query Performance With Columnar Index

Let's run the same query on the columnar table using a Clustered Columnstore Index.

-- Query on Columnstore Table
SELECT ProductID, SUM(SalesAmount) AS TotalSales
FROM SalesData_ColumnStore
WHERE OrderDate > '2023-01-01'
GROUP BY ProductID;

Expected Outcome

The columnar index stores the data by columns, and SQL Server can read only the relevant columns for the query (i.e., ProductID and SalesAmount). Columnstore indexes are highly optimized for these types of queries, resulting in much faster query execution time.

Comparing the Performance of Both Scenarios

To compare the performance of the two scenarios, we will execute both queries and check the execution plan and query duration.

Step 1: Query Execution Plan Without Columnstore

You can use the following query to view the execution plan for the rowstore table.

-- Displaying Execution Plan for Rowstore Table
SET STATISTICS IO ON;
SET STATISTICS TIME ON;

SELECT ProductID, SUM(SalesAmount) AS TotalSales
FROM SalesData_RowStore
WHERE OrderDate > '2023-01-01'
GROUP BY ProductID;

SET STATISTICS IO OFF;
SET STATISTICS TIME OFF;

This will provide information on:

• Logical reads: The number of data pages read from disk
• CPU time: How much CPU time was consumed
• Elapsed time: The total time taken to execute the query

Step 2: Query Execution Plan With Columnstore

Now, execute the same for the columnstore table.

-- Displaying Execution Plan for Columnstore Table
SET STATISTICS IO ON;
SET STATISTICS TIME ON;


SELECT ProductID, SUM(SalesAmount) AS TotalSales
FROM SalesData_ColumnStore
WHERE OrderDate > '2023-01-01'
GROUP BY ProductID;


SET STATISTICS IO OFF;
SET STATISTICS TIME OFF;

In the execution plan for the columnstore table, SQL Server will typically show fewer logical reads and significantly lower CPU time, as it only scans the necessary columns.

Performance Improvements in Columnar Tables

Scenario 1: Data Compression

Columnar storage achieves higher compression rates because data is stored in homogeneous chunks, which makes it more efficient in terms of storage. Compression reduces disk I/O during query execution.

Scenario 2: Selective Column Scanning

When querying only a few columns, columnar storage avoids scanning the entire row. In contrast, rowstore requires scanning all columns in every row, even if only a subset is required for the query.

Conclusion

In this example, we demonstrated how implementing columnstore indexes in SQL Server can significantly improve query performance, especially for analytics and aggregation queries on large datasets. The comparison showed that columnar storage excels in reducing query times by optimizing disk I/O, leveraging data compression, and selectively reading only the necessary columns. As a result, columnstore indexing is a great choice for data warehousing or any scenario where read performance for large datasets is critical.