Columnstore & Horizontal Partitioning SQL Server Tables

ColumnstoreParitioning.sql

CREATE PARTITION FUNCTION year_partition_function (varchar(4)) AS 
  RANGE FOR VALUES ('2019', '2020', '2021') 
GO

CREATE PARTITION SCHEME year_partition_scheme AS 
  PARTITION year_partition_function ALL TO ([PRIMARY])
GO

CREATE TABLE users
(
  Name varchar(10),
  Year varchar(4),
) ON year_partition_scheme(year)
GO

CREATE CLUSTERED COLUMNSTORE INDEX cidx 
  ON users 
  -- partition-aligned index
  ON year_partition_scheme(year)

INSERT INTO users VALUES
   ('Jerry', '2020')
  ,('Joe', '2021')
  ,('Jim', '2021')
  ,('Jon', '2021')
  ,('Jack', '1999')
GO

SELECT  
   CONCAT(SCHEMA_NAME([schema_id]), '.', t.[name]) AS [table]
  ,CONCAT(i.name, ' (', lower(i.[type_desc]) COLLATE DATABASE_DEFAULT, ')') AS [index (type)]
  ,CONCAT(format(p.[partition_number], '00'), ' (', cast(coalesce(r.[value], 'default') as varchar(50)), ')') as [partition (boundary)]
  ,p.[rows] AS [row_count]
  ,CONCAT(ps.[name], ' -> ', pf.[name]) as [scheme -> function]
FROM sys.tables AS t
INNER JOIN sys.partitions AS p 
  ON p.[object_id] = t.[object_id]
INNER JOIN sys.indexes AS i 
  ON p.[object_id] = i.[object_id] 
  AND p.[index_id] = i.[index_id]
INNER JOIN sys.data_spaces AS ds 
  ON i.[data_space_id] = ds.[data_space_id]
INNER JOIN sys.partition_schemes AS ps 
  ON ds.[data_space_id] = ps.[data_space_id]
INNER JOIN sys.partition_functions AS pf 
  ON ps.[function_id] = pf.[function_id]
LEFT JOIN sys.partition_range_values AS r 
  ON pf.[function_id] = r.[function_id] 
  AND r.[boundary_id] = p.[partition_number]
ORDER BY 
     t.[name]
    ,i.[name]
    ,p.[partition_number];
GO

DROP TABLE users
GO

DROP PARTITION SCHEME year_partition_scheme
GO

DROP PARTITION FUNCTION year_partition_function 
GO