Geek City: Build a Big Table with a Columnstore Index

Kalen Delaney — Thu, 26 Apr 2012 18:18:00 GMT

I was looking all over to find a big table to use for my columnstore examples in my SQL Server 2012 book, and the only one I found was from someone at Microsoft and was not publicly available. When I demonstrate code in my writings, I like to have the code available for anyone to use, so that was no use.

Finally I realized I was just going to have to do it myself. I actually based the script on some work by the awesome Benjamin Nevarez (blog| twitter), but I needed to make a couple of changes to the table structure, and then the loop for adding the data took some tweeking to make sure that uniqueness was possible, in case you need to test out how the metadata changes when a columnstore index is built on a table with a unique clustered index compared to a nonunique clustered index compared to a heap.

I have just finished the chapter on indexes, and decided to make this script available. The initial table data is based on the data in Microsoft AdventureWorksDW2012 sample database that you can download here. (Note that the DW version is the first one under “Other Available Downloads”’; it’s not the one under “Recommended Download”. )

Here is the section of the script that populates most of the FactInternetSalesBIG table. (The original 60K rows were just copied from the original FactInternetSales table. Note that all the statements have to be run in a single batch because the local variable’s scope is the batch, and because the GO 9 applies to the single batch that precedes it. Of course, you can change the number of iterations to end up with a different size table. Mine ends up at just over 30 million rows. In addition to modifying the SalesOrderNumber value on each iteration, I also changed the value of the Revision column to indicate which pass through the insert loop was being executed.

-- Copy the new big table into itself 9 times
DECLARE @RevisionNumber nchar(2);
SELECT @RevisionNumber = RevisionNumber + 1 FROM RevisionNumberValue;
SELECT @RevisionNumber as RevisionNumber;
INSERT INTO dbo.FactInternetSalesBig WITH (TABLOCK)
SELECT ProductKey
,OrderDateKey
,DueDateKey
,ShipDateKey
,CustomerKey
,PromotionKey
,CurrencyKey
,SalesTerritoryKey
,SalesOrderNumber + @RevisionNumber
,SalesOrderLineNumber
,@RevisionNumber
,OrderQuantity
,UnitPrice
,ExtendedAmount
,UnitPriceDiscountPct
,DiscountAmount
,ProductStandardCost
,TotalProductCost
,SalesAmount
,TaxAmt
,Freight
,CarrierTrackingNumber
,CustomerPONumber
,OrderDate
,DueDate
,ShipDate
FROM dbo.FactInternetSalesBig;
UPDATE RevisionNumberValue SET RevisionNumber = RevisionNumber + 1;
GO 9

Make sure you have enough log space. My log grew from 1GB to just over 8GB when running the script to build the 30 million row table and columnstore index, with no clustered index.

The downloadable script has commented options to build either a clustered or nonclustered index before you build the columnstore index. I suggest building the clustered index before the columnstore index, because building the clustered index will rebuild any existing nonclustered indexes, including my columnstore index. Building the columnstore index can take a while, on my system it was about 10 minutes. (Building the original 30+ million row table took even longer.)

After I finish the chapter on partitioning, I'll have a script to partition this table, but feel free to do that yourself!

Have fun!

~Kalen

Search results matching tags 'columnstore', 'SQL Server 2012', and 'indexes'

Geek City: Build a Big Table with a Columnstore Index