Column Stores Indexes & Partitioning – Foundational Work

Background

As data grows bigger and ages it becomes prudent to look at partitioning as an avenue to better reduce the overall footprint of data read from disk into memory.

Column Store by its very definition delivers on the promises of Vertical Partitioning.

Let us see review the steps we have to take to achieve Range Partitioning.

 

Design Goals

Our goal is to divvy up our data into years.

In each table that we will be targeting we will ensure that a date column exists.

And, hope to have one of those date columns track the occurrence date.

 

Overview

Here are the plumbing areas:

  1. Create new Filegroups and files within the new file groups
  2. Create Partitioning Function
  3. Create Partitioning Scheme

 

Steps

Platform

File Groups & Files

Objective

Create File groups for each business year.

And, create a single file in each file group.

SQL


USE [WideWorldImportersDW]   
go

-- Year NULL

if not exists  
(      
	select *      
	from   sys.filegroups tblSFG      
	where  tblSFG.name = 'fg_Partition_Year_Null'  
)  
begin      

	ALTER DATABASE [WideWorldImportersDW] 
		ADD FILEGROUP  [fg_Partition_Year_Null]   
		
end 
go

if not exists  
	(      
		select *      
		from   sys.master_files tblMF      
		where  tblMF.[database_id] = db_id('WideWorldImportersDW')      
		and    tblMF.name = 'fg_Partition_Year_Null__01'  
	)  
begin 

   ALTER DATABASE [WideWorldImportersDW]  
   ADD FILE   
   (      
      NAME = [fg_Partition_Year_Null__01]   
      , FILENAME = 'Z:\Microsoft\SQLServer\DataFiles\WideWorldImportersDW_fg_Partition_Year_Null_file_001.ndf'  
   )  
   TO FILEGROUP [fg_Partition_Year_Null]  
	
end 


-- Year 2010
if not exists  
(      
    select *      
    from   sys.filegroups tblSFG      
    where  tblSFG.name = 'fg_Partition_Year_2010'  
)  
begin

   ALTER DATABASE [WideWorldImportersDW]         
      ADD FILEGROUP  [fg_Partition_Year_2010]   

end 
    
if not exists  
(      
   select *      
   from   sys.master_files tblMF      
   where  tblMF.[database_id] = db_id('WideWorldImportersDW')      
   and    tblMF.name = 'fg_Partition_Year_2010__01'  
)  
begin
   
   ALTER DATABASE [WideWorldImportersDW]  
   ADD FILE   
   (      
         NAME = [fg_Partition_Year_2010__01]   
       , FILENAME = 'Z:\Microsoft\SQLServer\DataFiles\WideWorldImportersDW_fg_Partition_Year_2010_file_001.ndf'  
  )  
  TO FILEGROUP [fg_Partition_Year_2010]  

end 


-- Year 2011
if not exists  
(      
    select *      
    from   sys.filegroups tblSFG      
    where  tblSFG.name = 'fg_Partition_Year_2011'  
)  
begin

   ALTER DATABASE [WideWorldImportersDW]         
      ADD FILEGROUP  [fg_Partition_Year_2011]   

end 
    
if not exists  
(      
   select *      
   from   sys.master_files tblMF      
   where  tblMF.[database_id] = db_id('WideWorldImportersDW')      
   and    tblMF.name = 'fg_Partition_Year_2011__01'  
)  
begin
   
   ALTER DATABASE [WideWorldImportersDW]  
   ADD FILE   
   (      
         NAME = [fg_Partition_Year_2011__01]   
       , FILENAME = 'Z:\Microsoft\SQLServer\DataFiles\WideWorldImportersDW_fg_Partition_Year_2011_file_001.ndf'  
  )  
  TO FILEGROUP [fg_Partition_Year_2011]  

end 


-- Year 2012
if not exists  
(      
    select *      
    from   sys.filegroups tblSFG      
    where  tblSFG.name = 'fg_Partition_Year_2012'  
)  
begin

   ALTER DATABASE [WideWorldImportersDW]         
      ADD FILEGROUP  [fg_Partition_Year_2012]   

end 
    
if not exists  
(      
   select *      
   from   sys.master_files tblMF      
   where  tblMF.[database_id] = db_id('WideWorldImportersDW')      
   and    tblMF.name = 'fg_Partition_Year_2012__01'  
)  
begin
   
   ALTER DATABASE [WideWorldImportersDW]  
   ADD FILE   
   (      
         NAME = [fg_Partition_Year_2012__01]   
       , FILENAME = 'Z:\Microsoft\SQLServer\DataFiles\WideWorldImportersDW_fg_Partition_Year_2012_file_001.ndf'  
  )  
  TO FILEGROUP [fg_Partition_Year_2012]  

end 


-- Year 2013
if not exists  
(      
    select *      
    from   sys.filegroups tblSFG      
    where  tblSFG.name = 'fg_Partition_Year_2013'  
)  
begin

   ALTER DATABASE [WideWorldImportersDW]         
      ADD FILEGROUP  [fg_Partition_Year_2013]   

end 
    
if not exists  
(      
   select *      
   from   sys.master_files tblMF      
   where  tblMF.[database_id] = db_id('WideWorldImportersDW')      
   and    tblMF.name = 'fg_Partition_Year_2013__01'  
)  
begin
   
   ALTER DATABASE [WideWorldImportersDW]  
   ADD FILE   
   (      
         NAME = [fg_Partition_Year_2013__01]   
       , FILENAME = 'Z:\Microsoft\SQLServer\DataFiles\WideWorldImportersDW_fg_Partition_Year_2013_file_001.ndf'  
  )  
  TO FILEGROUP [fg_Partition_Year_2013]  

end 


-- Year 2014
if not exists  
(      
    select *      
    from   sys.filegroups tblSFG      
    where  tblSFG.name = 'fg_Partition_Year_2014'  
)  
begin

   ALTER DATABASE [WideWorldImportersDW]         
      ADD FILEGROUP  [fg_Partition_Year_2014]   

end 
    
if not exists  
(      
   select *      
   from   sys.master_files tblMF      
   where  tblMF.[database_id] = db_id('WideWorldImportersDW')      
   and    tblMF.name = 'fg_Partition_Year_2014__01'  
)  
begin
   
   ALTER DATABASE [WideWorldImportersDW]  
   ADD FILE   
   (      
         NAME = [fg_Partition_Year_2014__01]   
       , FILENAME = 'Z:\Microsoft\SQLServer\DataFiles\WideWorldImportersDW_fg_Partition_Year_2014_file_001.ndf'  
  )  
  TO FILEGROUP [fg_Partition_Year_2014]  

end 


-- Year 2015
if not exists  
(      
    select *      
    from   sys.filegroups tblSFG      
    where  tblSFG.name = 'fg_Partition_Year_2015'  
)  
begin

   ALTER DATABASE [WideWorldImportersDW]         
      ADD FILEGROUP  [fg_Partition_Year_2015]   

end 
    
if not exists  
(      
   select *      
   from   sys.master_files tblMF      
   where  tblMF.[database_id] = db_id('WideWorldImportersDW')      
   and    tblMF.name = 'fg_Partition_Year_2015__01'  
)  
begin
   
   ALTER DATABASE [WideWorldImportersDW]  
   ADD FILE   
   (      
         NAME = [fg_Partition_Year_2015__01]   
       , FILENAME = 'Z:\Microsoft\SQLServer\DataFiles\WideWorldImportersDW_fg_Partition_Year_2015_file_001.ndf'  
  )  
  TO FILEGROUP [fg_Partition_Year_2015]  

end 


-- Year 2016
if not exists  
(      
    select *      
    from   sys.filegroups tblSFG      
    where  tblSFG.name = 'fg_Partition_Year_2016'  
)  
begin

   ALTER DATABASE [WideWorldImportersDW]         
      ADD FILEGROUP  [fg_Partition_Year_2016]   

end 
    
if not exists  
(      
   select *      
   from   sys.master_files tblMF      
   where  tblMF.[database_id] = db_id('WideWorldImportersDW')      
   and    tblMF.name = 'fg_Partition_Year_2016__01'  
)  
begin
   
   ALTER DATABASE [WideWorldImportersDW]  
   ADD FILE   
   (      
         NAME = [fg_Partition_Year_2016__01]   
       , FILENAME = 'Z:\Microsoft\SQLServer\DataFiles\WideWorldImportersDW_fg_Partition_Year_2016_file_001.ndf'  
  )  
  TO FILEGROUP [fg_Partition_Year_2016]  

end 

-- Year 2017
if not exists  
(      
    select *      
    from   sys.filegroups tblSFG      
    where  tblSFG.name = 'fg_Partition_Year_2017'  
)  
begin

   ALTER DATABASE [WideWorldImportersDW]         
      ADD FILEGROUP  [fg_Partition_Year_2017]   

end 
    
if not exists  
(      
   select *      
   from   sys.master_files tblMF      
   where  tblMF.[database_id] = db_id('WideWorldImportersDW')      
   and    tblMF.name = 'fg_Partition_Year_2017__01'  
)  
begin
   
   ALTER DATABASE [WideWorldImportersDW]  
   ADD FILE   
   (      
         NAME = [fg_Partition_Year_2017__01]   
       , FILENAME = 'Z:\Microsoft\SQLServer\DataFiles\WideWorldImportersDW_fg_Partition_Year_2017_file_001.ndf'  
  )  
  TO FILEGROUP [fg_Partition_Year_2017]  

end 

Partition Function

Objective

  1. Create a new Partition Function
  2. Indicate Column’s data type
    • date
  3. Range Type
    • Left or Right
      • Right Values
  4. Values
    • null
      • When date is null
      • When null entries are accounted for, please be sure to use convert function
      • Else
        • Msg 7705, Level 16, State 1
        • Could not implicitly convert range values type specified at ordinal 1 to partition function parameter type

SQL

Code

 CREATE PARTITION FUNCTION [pfnYear]
 ( 
    date 
 )     
 AS RANGE RIGHT FOR VALUES    
 (       
	  convert ( date , null ) 
	, '2010-01-01'
	, '2011-01-01'
	, '2012-01-01'
	, '2013-01-01'
	, '2014-01-01'
	, '2015-01-01'
	, '2016-01-01'
	, '2017-01-01'
	, '2018-01-01'
	, '2019-01-01'
	, '2020-01-01'  
) 

Partition Scheme

SQL



CREATE PARTITION SCHEME [pschemeYear]   
AS PARTITION [pfnYear]    
TO  
(       
	  [fg_Partition_Year_Null]
	, [fg_Partition_Year_2010]
	, [fg_Partition_Year_2011]
	, [fg_Partition_Year_2012]
	, [fg_Partition_Year_2013]
	, [fg_Partition_Year_2014]
	, [fg_Partition_Year_2015]
	, [fg_Partition_Year_2016]
	, [fg_Partition_Year_2017]
	, [fg_Partition_Year_2018]
	, [fg_Partition_Year_2019]
	, [fg_Partition_Year_2020]
	, [fg_Partition_Year_Next]  
	
) 

Summary

To make it easier to come back and edit our documentation, we will address using the underlying Partitioning components in a secondary post

 

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