Free Webinar about Columnstore Indexes

I’m doing a small series on indexing basics for SQL Server, and on May 14th I’ll be presenting the third part which is about columnstore indexes. You can watch the webinar for free, but registration is required. The webinar is only 30 minutes long and will focus on the basics.

In the demo scripts I use a couple of “big” fact tables, which are just the Internet Sales table from AdventureWorks, but with the data inserted multiple tables. First I create the tables themselves:

CREATE TABLE [dbo].[FactInternetSales_Big](
	[ProductKey] [INT] NOT NULL,
	[OrderDateKey] [INT] NOT NULL,
	[DueDateKey] [INT] NOT NULL,
	[ShipDateKey] [INT] NOT NULL,
	[CustomerKey] [INT] NOT NULL,
	[PromotionKey] [INT] NOT NULL,
	[CurrencyKey] [INT] NOT NULL,
	[SalesTerritoryKey] [INT] NOT NULL,
	[SalesOrderNumber] [NVARCHAR](20) NOT NULL,
	[SalesOrderLineNumber] [TINYINT] NOT NULL,
	[RevisionNumber] [TINYINT] NOT NULL,
	[OrderQuantity] [SMALLINT] NOT NULL,
	[UnitPrice] [MONEY] NOT NULL,
	[ExtendedAmount] [MONEY] NOT NULL,
	[UnitPriceDiscountPct] [FLOAT] NOT NULL,
	[DiscountAmount] [FLOAT] NOT NULL,
	[ProductStandardCost] [MONEY] NOT NULL,
	[TotalProductCost] [MONEY] NOT NULL,
	[SalesAmount] [MONEY] NOT NULL,
	[TaxAmt] [MONEY] NOT NULL,
	[Freight] [MONEY] NOT NULL,
	[CarrierTrackingNumber] [NVARCHAR](25) NULL,
	[CustomerPONumber] [NVARCHAR](25) NULL,
	[OrderDate] [DATETIME] NULL,
	[DueDate] [DATETIME] NULL,
	[ShipDate] [DATETIME] NULL
);
GO

CREATE CLUSTERED COLUMNSTORE INDEX [CCI_FactInternetSales_Big]
ON [dbo].[FactInternetSales_Big];
GO

CREATE TABLE [dbo].[FactInternetSales_Big_Sorted](
	[ProductKey] [INT] NOT NULL,
	[OrderDateKey] [INT] NOT NULL,
	[DueDateKey] [INT] NOT NULL,
	[ShipDateKey] [INT] NOT NULL,
	[CustomerKey] [INT] NOT NULL,
	[PromotionKey] [INT] NOT NULL,
	[CurrencyKey] [INT] NOT NULL,
	[SalesTerritoryKey] [INT] NOT NULL,
	[SalesOrderNumber] [NVARCHAR](20) NOT NULL,
	[SalesOrderLineNumber] [TINYINT] NOT NULL,
	[RevisionNumber] [TINYINT] NOT NULL,
	[OrderQuantity] [SMALLINT] NOT NULL,
	[UnitPrice] [MONEY] NOT NULL,
	[ExtendedAmount] [MONEY] NOT NULL,
	[UnitPriceDiscountPct] [FLOAT] NOT NULL,
	[DiscountAmount] [FLOAT] NOT NULL,
	[ProductStandardCost] [MONEY] NOT NULL,
	[TotalProductCost] [MONEY] NOT NULL,
	[SalesAmount] [MONEY] NOT NULL,
	[TaxAmt] [MONEY] NOT NULL,
	[Freight] [MONEY] NOT NULL,
	[CarrierTrackingNumber] [NVARCHAR](25) NULL,
	[CustomerPONumber] [NVARCHAR](25) NULL,
	[OrderDate] [DATETIME] NULL,
	[DueDate] [DATETIME] NULL,
	[ShipDate] [DATETIME] NULL
);
GO

CREATE CLUSTERED COLUMNSTORE INDEX [CCI_FactInternetSales_Big_Sorted]
ON [dbo].[FactInternetSales_Big_Sorted]
ORDER ([CustomerKey]);
GO

CREATE TABLE [dbo].[FactInternetSales_Big_Rowstore](
	[ProductKey] [INT] NOT NULL,
	[OrderDateKey] [INT] NOT NULL,
	[DueDateKey] [INT] NOT NULL,
	[ShipDateKey] [INT] NOT NULL,
	[CustomerKey] [INT] NOT NULL,
	[PromotionKey] [INT] NOT NULL,
	[CurrencyKey] [INT] NOT NULL,
	[SalesTerritoryKey] [INT] NOT NULL,
	[SalesOrderNumber] [NVARCHAR](20) NOT NULL,
	[SalesOrderLineNumber] [TINYINT] NOT NULL,
	[RevisionNumber] [TINYINT] NOT NULL,
	[OrderQuantity] [SMALLINT] NOT NULL,
	[UnitPrice] [MONEY] NOT NULL,
	[ExtendedAmount] [MONEY] NOT NULL,
	[UnitPriceDiscountPct] [FLOAT] NOT NULL,
	[DiscountAmount] [FLOAT] NOT NULL,
	[ProductStandardCost] [MONEY] NOT NULL,
	[TotalProductCost] [MONEY] NOT NULL,
	[SalesAmount] [MONEY] NOT NULL,
	[TaxAmt] [MONEY] NOT NULL,
	[Freight] [MONEY] NOT NULL,
	[CarrierTrackingNumber] [NVARCHAR](25) NULL,
	[CustomerPONumber] [NVARCHAR](25) NULL,
	[OrderDate] [DATETIME] NULL,
	[DueDate] [DATETIME] NULL,
	[ShipDate] [DATETIME] NULL
);
GO

Then I insert the data from the original fact tables into one of the tables, and then copy the data again to the other tables:

INSERT INTO dbo.FactInternetSales_Big
(
    ProductKey
    ,OrderDateKey
    ,DueDateKey
    ,ShipDateKey
    ,CustomerKey
    ,PromotionKey
    ,CurrencyKey
    ,SalesTerritoryKey
    ,SalesOrderNumber
    ,SalesOrderLineNumber
    ,RevisionNumber
    ,OrderQuantity
    ,UnitPrice
    ,ExtendedAmount
    ,UnitPriceDiscountPct
    ,DiscountAmount
    ,ProductStandardCost
    ,TotalProductCost
    ,SalesAmount
    ,TaxAmt
    ,Freight
    ,CarrierTrackingNumber
    ,CustomerPONumber
    ,OrderDate
    ,DueDate
    ,ShipDate
)
SELECT f.*
FROM dbo.FactInternetSales f
CROSS JOIN
(SELECT * FROM SYS.columns WHERE object_id < 500) tmp;

INSERT INTO dbo.FactInternetSales_Big_Rowstore
(
    ProductKey
    ,OrderDateKey
    ,DueDateKey
    ,ShipDateKey
    ,CustomerKey
    ,PromotionKey
    ,CurrencyKey
    ,SalesTerritoryKey
    ,SalesOrderNumber
    ,SalesOrderLineNumber
    ,RevisionNumber
    ,OrderQuantity
    ,UnitPrice
    ,ExtendedAmount
    ,UnitPriceDiscountPct
    ,DiscountAmount
    ,ProductStandardCost
    ,TotalProductCost
    ,SalesAmount
    ,TaxAmt
    ,Freight
    ,CarrierTrackingNumber
    ,CustomerPONumber
    ,OrderDate
    ,DueDate
    ,ShipDate
)
SELECT * FROM dbo.FactInternetSales_Big;

INSERT INTO dbo.FactInternetSales_Big_Sorted
(
    ProductKey
    ,OrderDateKey
    ,DueDateKey
    ,ShipDateKey
    ,CustomerKey
    ,PromotionKey
    ,CurrencyKey
    ,SalesTerritoryKey
    ,SalesOrderNumber
    ,SalesOrderLineNumber
    ,RevisionNumber
    ,OrderQuantity
    ,UnitPrice
    ,ExtendedAmount
    ,UnitPriceDiscountPct
    ,DiscountAmount
    ,ProductStandardCost
    ,TotalProductCost
    ,SalesAmount
    ,TaxAmt
    ,Freight
    ,CarrierTrackingNumber
    ,CustomerPONumber
    ,OrderDate
    ,DueDate
    ,ShipDate
)
SELECT * FROM dbo.FactInternetSales_Big;