Column Stores Indexes & Partitioning – Partition Tables

Objective

In a recent post, we created the infrastructure objects ( File Groups, Files, Partition Functions, and Partition Schemes).

In this post, we will create tables that will utilize those objects.

 

Outline

 

Table Row or Column Store Partition Index Columns
Fact.SaleLargeRowStore Row Store [Sale Key]
Fact.SaleLargeRowStoreClusteredPartition Row Store InvoiceDateKeyYear [Sale Key]

[Invoice Date Key]

[Invoice Date Key]

[Customer Key]*
[Profit]*

 Fact.SaleLargeColumnStoreClustered  Column Store
 Fact.SaleLargeColumnStoreClusteredPartition  Column Store  InvoiceDateKeyYear
 Fact.SaleLargeColumnStoreNonClustered  Column Store

 

 

Exercise

Tables

[Fact].[SaleLargeRowStoreClusteredPartition]

SQL

Code

USE [WideWorldImportersDW]
GO

/*

    DROP TABLE [Fact].[SaleLargeRowStoreClusteredPartition]

*/

/*

    exec sp_helpindex '[Fact].[SaleLargeRowStoreClusteredPartition]'

*/

if object_id('[Fact].[SaleLargeRowStoreClusteredPartition]') is null
begin

    CREATE TABLE [Fact].[SaleLargeRowStoreClusteredPartition]
    (
        [Sale Key] [bigint] IDENTITY(1,1) NOT NULL,
        [City Key] [int] NOT NULL,
        [Customer Key] [int] NOT NULL,
        [Bill To Customer Key] [int] NOT NULL,
        [Stock Item Key] [int] NOT NULL,
        
        [Invoice Date Key] [date] NOT NULL,

        [InvoiceDateKeyYear] [int] NOT NULL,
        
        [Delivery Date Key] [date] NULL,
        
        [DeliveryDateKeyYear] [int] NULL,
        
        [Salesperson Key] [int] NOT NULL,
        [WWI Invoice ID] [int] NOT NULL,
        [Description] [nvarchar](100) NOT NULL,
        [Package] [nvarchar](50) NOT NULL,
        [Quantity] [int] NOT NULL,
        [Unit Price] [decimal](18, 2) NOT NULL,
        [Tax Rate] [decimal](18, 3) NOT NULL,
        [Total Excluding Tax] [decimal](18, 2) NOT NULL,
        [Tax Amount] [decimal](18, 2) NOT NULL,
        [Profit] [decimal](18, 2) NOT NULL,
        [Total Including Tax] [decimal](18, 2) NOT NULL,
        [Total Dry Items] [int] NOT NULL,
        [Total Chiller Items] [int] NOT NULL,
        [Lineage Key] [int] NOT NULL
    

        ) 

        --ON [USERDATA] 
        ON [pschemeYear]
        (
            [Invoice Date Key]
        )

end
go

declare @object sysname
declare @objectID int
declare @constraint sysname
declare @index  sysname

declare @addPartitionedIndex   bit
declare @addUnpartitionedIndex bit

set @object = '[Fact].[SaleLargeRowStoreClusteredPartition]'
set @constraint = 'PK_Fact_SaleLargeRowStoreClusteredPartition'

set @objectID = object_id(@object)

if object_id(@object) is not null
begin

    /*

        alter table [Fact].[SaleLargeRowStoreClusteredPartition]
            drop CONSTRAINT [PK_Fact_SaleLargeRowStoreClusteredPartition] 

    */
    if not exists
        (

            select *
            from  sys.key_constraints tblSKC
            where tblSKC.parent_object_id = @objectID
            and   tblSKC.[name] = @constraint

        )
    begin

        print 'Create Primary Key ' 
                + @object 
                + '.' 
                + quoteName(@constraint) 
                + ' ...'

        alter table [Fact].[SaleLargeRowStoreClusteredPartition]
        ADD CONSTRAINT [PK_Fact_SaleLargeRowStoreClusteredPartition] 
        PRIMARY KEY CLUSTERED 
            (

                [Sale Key] ASC
                /*
                Msg 1908, Level 16, State 1, Line 19
                Column 'Invoice Date Key' is partitioning column of the index 'PK_Fact_SaleLargeRowStoreClusteredPartition'. Partition columns for a unique index must be a subset of the index key.
                Msg 1750, Level 16, State 0, Line 19
                Could not create constraint or index. See previous errors.
                */
            , [Invoice Date Key]

            )

        --ON [USERDATA]
        ON [pschemeYear]
        (
            [Invoice Date Key]
        )
            
        update statistics [Fact].[SaleLargeRowStoreClusteredPartition]
            [PK_Fact_SaleLargeRowStoreClusteredPartition] 
            with FULLSCAN;


        print 'Created Constraint - Primary Key'
                + @object + '.' 
                + quoteName(@constraint)

    end



    /*
        drop index [Fact].[SaleLargeRowStoreClusteredPartition].[INDX_InvoiceDateKey_WithIncludedColumns_UnPartition]
    */
    set @addPartitionedIndex = 0;
    set @addPartitionedIndex = 1;
    set @index = 'INDX_InvoiceDateKey_WithIncludedColumns_Partition';

    if not exists
        (
            select *
            from  sys.indexes tblSI
            where tblSI.object_id = @objectID
            and   tblSI.[name] = @index
        )
        and (@addPartitionedIndex = 1)
    begin

        print 'Create Index ' + @object + '.' + quoteName(@index) + ' ...'


        CREATE NONCLUSTERED INDEX [INDX_InvoiceDateKey_WithIncludedColumns_Partition]
        ON [Fact].[SaleLargeRowStoreClusteredPartition] 
        (
            [Invoice Date Key]
        )
        INCLUDE 
        (
              [Customer Key]
            , [Profit]
        )
        with
        (
            FILLFACTOR=100
        )
        /*
        ON [USERDATA]
        */
        ON [pschemeYear]
        (
            [Invoice Date Key]
        )


        update statistics [Fact].[SaleLargeRowStoreClusteredPartition]
            [INDX_InvoiceDateKey_WithIncludedColumns_Partition]
            with FULLSCAN;


        print 'Created Index ' + @object + '.' + quoteName(@index)

    end

end


go

 

[Fact].[SaleLargeColumnStoreClusteredPartition]

SQL

Code

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

USE [WideWorldImportersDW]
GO

/*

	DROP TABLE [Fact].[SaleLargeColumnStoreClusteredPartition]

	exec sp_helpindex '[Fact].[SaleLargeColumnStoreClusteredPartition]'

*/
if object_id('[Fact].[SaleLargeColumnStoreClusteredPartition]') is null
begin


	CREATE TABLE [Fact].[SaleLargeColumnStoreClusteredPartition]
	(
		[Sale Key] [bigint] IDENTITY(1,1) NOT NULL,
		[City Key] [int] NOT NULL,
		[Customer Key] [int] NOT NULL,
		[Bill To Customer Key] [int] NOT NULL,
		[Stock Item Key] [int] NOT NULL,
	
		[Invoice Date Key] [date] NOT NULL,
		[InvoiceDateKeyYear] [int] NOT NULL,
			
		[Delivery Date Key] [date] NULL,
		[DeliveryDateKeyYear] [int] NULL,
			
		[Salesperson Key] [int] NOT NULL,
		[WWI Invoice ID] [int] NOT NULL,
		[Description] [nvarchar](100) NOT NULL,
		[Package] [nvarchar](50) NOT NULL,
		[Quantity] [int] NOT NULL,
		[Unit Price] [decimal](18, 2) NOT NULL,
		[Tax Rate] [decimal](18, 3) NOT NULL,
		[Total Excluding Tax] [decimal](18, 2) NOT NULL,
		[Tax Amount] [decimal](18, 2) NOT NULL,
		[Profit] [decimal](18, 2) NOT NULL,
		[Total Including Tax] [decimal](18, 2) NOT NULL,
		[Total Dry Items] [int] NOT NULL,
		[Total Chiller Items] [int] NOT NULL,
		[Lineage Key] [int] NOT NULL
	
			

	) ON [USERDATA]

end


/*
	Object:  Index [INDX_FACT_SaleLargeColumnStoreClustered] 
*/

declare @table sysname
declare @index sysname

set @table = '[Fact].[SaleLargeColumnStoreClusteredPartition]';
set @index = 'INDX_FACT_SaleLargeColumnStoreClusteredPartition'

if object_id(@table) is not null
begin

	if not exists
	(
		select *
		from   sys.indexes tblSI
		where  tblSI.object_id = object_id(@table) 
		and    tblSI.[name] = @index
	) --and (1=0)
	begin

		print 'Create Index ' 
				+ @table
				+ ' - Index '
				+ @index
		
		CREATE CLUSTERED INDEX [INDX_FACT_SaleLargeColumnStoreClusteredPartition] 
		ON [Fact].[SaleLargeColumnStoreClusteredPartition] 
		(
			[Invoice Date Key]
		)
		WITH 
		(
				DROP_EXISTING = OFF
			--, FILLFACTOR = 100
			--, COMPRESSION_DELAY = 0
		) 
		--ON [USERDATA]
		ON [pschemeYear]
			(
				[Invoice Date Key]
			)
		
	end


	if exists
	(
		select *
		from   sys.indexes tblSI
		where  tblSI.object_id = object_id(@table) 
		and    tblSI.[name] = @index
		and    tblSI.type_desc != 'CLUSTERED COLUMNSTORE'
	)
	begin

		print 'Create Index ' 
				+ @table
				+ ' - Index '
				+ @index

		CREATE CLUSTERED COLUMNSTORE INDEX [INDX_FACT_SaleLargeColumnStoreClusteredPartition] 
		ON [Fact].[SaleLargeColumnStoreClusteredPartition] 
		WITH 
		(
			DROP_EXISTING = ON
		) 
		--ON [USERDATA]
		ON [pschemeYear]
		(
			[Invoice Date Key]
		)


	end

end
GO


Source Code Control

GitHub

The Code for creating the tables is posted on Github here

 

Summary

We will add corresponding data to our newly created partitioned tables and run a few queries and see the effect of the partitioning.

 

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s