DatawarehouseETLSQL QueriesSQL Tips

How to create Columnstore Index using SQL

By 24/11/2022 October 8th, 2023 No Comments

Problem Statement:-

Columnstore indexes are highly recommended if you want to improve performance of your stored procedure and/or database objects as it allows to the standard for storing and querying large data warehousing tables. This index uses column-based data storage and query processing to achieve gains up to 10 times the query performance in your data warehouse over traditional row-oriented storage. You can also achieve gains up to 10 times the data compression over the uncompressed data size. This functionality is available with SQL Server 2016 (13.x) SP1 and later versions (including Microsoft Azure database), columnstore indexes enable operational analytics: the ability to run performant real-time analytics on a transactional workload.

Sample Code Snippet:

USE [AdventureWorksDW]
GO

ALTER TABLE [dbo].[FactProductInventory]
DROP CONSTRAINT [PK_FactProductInventory];
GO

ALTER TABLE [dbo].[FactProductInventory]
DROP CONSTRAINT [FK_FactProductInventory_DimDate];
GO

ALTER TABLE [dbo].[FactProductInventory]
DROP CONSTRAINT [FK_FactProductInventory_DimProduct];
GO

CREATE CLUSTERED COLUMNSTORE INDEX [IX_CS_FactProductInventory]
ON dbo.FactProductInventory;
GO
 

Leave a Reply