articles

Articles of SQLschool.gr Team

Doing Magic with Partitioning and ColumnStore Index in a Data Warehouse DB on SQL Server 2012

Antonios Chatzipavlis

Friday 21 June 2013

Data WarehousePartitioningColumnstore IndexesAnalysis Services (SSAS)BI DeveloperBI TopicsDB DeveloperDBAIntegration Services (SSIS)SQL EnthusiastSQL Server 2012Tips & Tricks

Αρκετοί με ρωτάνε στα μαθήματα που κάνω αλλά και με emails στο SQL School σχετικά με το θέμα του table partitioning. Δεν ξέρω γιατί αλλά πολλούς έχει προβληματίσει και μπορώ να πω ότι έχει αγχώσει, αν και δεν θα έπρεπε καθώς είναι μια καθαρή λύση.

Υπάρχουν και συνάδελφοι που ασχολούνται με BI και έχουν Data Warehouses (DW) στα οποία θέλουν να επωφεληθούν από το table partitioning αλλά και από την χρήση των columnstore indexes.

Αποφάσισα να σας φτιάξω ένα μικρό demo με το οποίο θα δείτε ότι το να κάνει κάποιος table partitioning δεν είναι κάτι δύσκολο. Μάλιστα το έφτιαξα σε συνδυασμό με τους columnstore indexes για να δείτε το πόσο εύκολο είναι ιδιαίτερα αν είσαι σε DW να έχεις όλα τα δεδομένα σου με columnstore indexes χωρίς να χρειάζεται να κάνεις drop και recreate αυτούς κάθε φορά που θέλεις να ενημερώσεις τον πίνακα σου (τουλάχιστον όπως είναι τώρα τα πράγματα καθώς είναι γνωστό ότι αυτό στον SQL Server 2014 δεν χρειάζεται να το κάνεις).

Το σενάριο

Για το demo αυτό θα πάρουμε ένα σενάριο το οποίο δεν είναι καθόλου υποθετικό είναι πραγματικό και έχει χρησιμοποιηθεί πολλάκις στο παρελθόν.

Είμαστε μια εταιρία που έχει ένα DW στο οποίο έχει ένα data mart με το οποίο μετράει κάποια επιχειρηματική της δραστηριότητα. Σε αυτό έχει ένα fact table τον οποίο θέλει να ενημερώνει κάθε ημέρα από τις κινήσεις που έχει από το OLTP της ή γενικότερα από τα συστήματα και πηγές δεδομένων που διαθέτει. Αυτή η εταιρία ξεκινάει την δουλειά αυτή την 1/1/2013.

Σημείωση: Στο παράδειγμα δεν δείχνω κάτι για τους dimension tables αλλά μπορεί να ισχύσει ότι λέω στο demo αυτό. Επίσης δεν το κάνω με SSIS καθώς θέλω να μπορούν να το καταλάβουν και όσοι δεν έχουν εμπειρία με αυτό. Εξάλλου θα είναι εύκολο για αυτούς που ασχολούνται με SSIS να πάρουν τον κώδικα αυτό και να τον εντάξουν στην ροή ενός SSIS packages. Τέλος αν και αυτονόητο θα πρέπει να αλλάξετε τα paths αν δεν θέλετε να φτιάξετε τα ίδια που χρησιμοποιώ στο demo αυτό. Επίσης μπορείτε να αλλάξετε κατά βούληση το χρονικό παράθυρο ενημέρωσης σε αυτό που θέλετε είτε εβδομάδα είτε μήνα ή κάτι άλλο.

Παρακάτω είναι όλο το script με το οποίο μπορείτε να κάνετε υλοποίηση του demo. Δεν το σπάω σε κομμάτια για να μην μπερδευτείτε αλλά έχω βάλει ενδείξεις και κάποια σχόλια τα οποία θα σας εξηγήσω

Script Step 1

Δημιουργία της βάσης που είναι το DW μας. Το ότι αλλάζω την βάση σε Simple Recovery model δεν έχει να κάνει με την διαδικασία απλά είναι κάτι που συνήθως γίνεται στα DW γιατί δεν θέλουμε να μεγαλώνει το transaction log.

Script Step 2

Όπως είπαμε θέλουμε να κάνουμε ενημέρωση στοιχείων στον fact table ανά ημέρα. Στην όλη θα διαδικασία θα πρέπει να φροντίζουμε ώστε να έχουμε τα δύο partitions που βρίσκονται σε άκρα να είναι πάντα άδεια καθώς το δεξί το θέλουμε να για να βάζουμε την νέα ημερομηνία ενώ το αριστερό για πιθανό merge. Με αυτή την λογική λοιπόν βάζω στην βάση μου δύο filegroups το FG0000 το οποίο θα παίξει το ρόλο τους αριστερού άκρου και στο παράδειγμα δεν θα το χρησιμοποιήσω και το FG20130101 το οποίο θα παίξει το ρόλο του δεξιού άκρου και το οποίο θα υποδεχθεί τις εγγραφές της 1/1/2013.

Scrip Step 3

Για να δημιουργήσω τα partitions θα πρέπει να φτιάξω μια partitions function με την οποία θα προσδιορίζω τα όρια αυτών και κατόπιν αυτές τις αντιστοιχίζω φυσικά με τα filegroups. Έτσι μιας και δεν έχουμε κάτι άλλο ξεκινάμε με δύο partitions αυτό που είναι για όλες τις εγγραφές που είναι πριν την 1/1/2013 και για αυτές που είναι μετά από αυτές.

Script Step 4

Δημιουργούμε τον fact table ο οποίος τον οποίο και θέλουμε να ενημερώνουμε καθημερινά.

Script Step 5

Με το query αυτό μπορούμε να δούμε τι ακριβώς έχουμε φτιάξει σε partitions μέχρι στιγμής.

Script Step 6

Για να γίνω ακόμα περισσότερο δεικτικός θα βάλω άμεσα και τον columnstore index. Με αυτό πλέον δεν μπορώ να κάνω insert στον πίνακα μου καθώς στο SQL Server 2012 όταν σε ένα πίνακα βάζεις columnstore index αυτός γίνεται read only.

Script Step 7

Με το query αυτό μπορούμε να δούμε τι ακριβώς έχουμε φτιάξει σε partitions μέχρι στιγμής.

Script Step 8

Είμαστε λοιπόν σε θέση να βάλουμε τις εγγραφές της 1/1/2013 στον fact table. Επειδή αυτό πρέπει να το κάνουμε καθημερινά στο δεξί partition που δεν είναι άλλο από αυτό που έχουμε μετά από την 1/1/2013 πρέπει να το κάνουμε split ώστε στο ένα μέρος του να βάλουμε τις εγγραφές της ημερομηνίας που θέλουμε και το άλλο να είναι το άδειο για τις εγγραφές που θα μπουν. Αυτό θα πρέπει να το κάνουμε για κάθε ημέρα ώστε να κυλάει η ροή μας ομαλά. Μην φοβάστε 15000 partitions μπορείτε να έχετε.

Έτσι φτιάχνουμε ένα νέο filegroup και φυσικά βάζουμε σε αυτό ένα data file. Κάνουμε αυτό το filegroup να είναι το επόμενο διαθέσιμο στο partitioning. Προσέξτε το όνομα που έχω βάλει σε αυτό το filegroup είναι επίτηδες τέτοιο ώστε να σας γίνει κατανοητή η διαδικασία. Αυτό στην ουσία είναι αυτό που θα μπουν οι εγγραφές τις επόμενης ημερομηνίας. Το εντάσω στο partition scheme και κάνω alter την partition function κάνοντας split το filegroup που έχω για την 1/1/2013 (FG20130101) απλά προσέξτε την ημερομηνία βάζω την επόμενη για να γίνει σωστά η κατανομή.

Script Step 9

Με το query αυτό μπορούμε να δούμε τι ακριβώς έχουμε φτιάξει σε partitions μέχρι στιγμής.

Script Step 10

Εδώ τώρα είναι το μεγάλο μυστικό και η μαγεία.

Φτιάχνω έναν πίνακα που είναι ακριβώς όμοιος σε γραμμογράφηση με το fact μου. Αλλά προσέξτε αυτός θα γίνει στο ίδιο filegroup με τις εγγραφές που θέλω να μπουν στο fact. Στην περίπτωση μας είναι η 1/1/2013.Επίσης καλό είναι αυτό να είναι σε heap ώστε να είναι και γρήγορη η διαδικασία φόρτωσης του.

Script Step 11

Γεμίζω τον πίνακα αυτό με τις εγγραφές που θέλω.

Script Step 12

Εδώ είναι η αποκάλυψη του μεγάλου μυστικού που ξεκίνησε στο STEP 10. Τι θέλω να κάνω; Θέλω τα δεδομένα που έχω στο load table χωρίς να τρέξω κάποιο import operation που να μεταφέρει αυτά στο fact να μεταφερθούν απλά εντάσοντας αυτά στο partition.

Για να γίνει αυτό πρέπει στο πίνακα να βάλω ένα check constraint που να υλοποιεί τον ορισμό του συγκεκριμένου partition που έχω στην partition function. Επίσης θα πρέπει να έχει ακριβώς τους ίδιους indexes που έχω και στο κανονικό partition. Στην περίπτωση μας έχουμε μόνο το columstore index

Script Step 13

Εδώ λοιπόν κάνω όλη την δουλεία. Βρίσκω το id του partition που θέλω να βάλω τις εγγραφές και με την alter table μετατρέπω τα δεδομένα του πίνακα load σε δεδομένα του fact στο συγκεκριμένο partition.

You can do magic!!!.

Προσοχή για να παίξει αυτό πρέπει ο load να είναι στο ίδιο filegroup με το συγκεκριμένο partition. Δείτε το Step 10.

Script Step 14

Απλά πλέον σβήνω τον πίνακα και δείτε τα δεδομένα σας στο fact. Ναι βλέπετε κάλα είναι μέσα σε αυτόν το δεδομένα του load table.

Script Step 15

Με το query αυτό μπορούμε να δούμε τι ακριβώς έχουμε φτιάξει σε partitions μέχρι στιγμής.

Στα επόμενα step δείχνω τη διαδικασία για τι πρέπει να γίνει για τις επόμενε μέρες για να γίνει κατανοητό το παράδειγμα. Στην ουσία επαναλαμβάνω τα βήματα 8,9,10,11,12,13,14.

-- STEP 1 - Create a database
USE master;
IF EXISTS (SELECT * FROM sys.sysdatabases WHERE name = 'DemoDW')
    DROP DATABASE DemoDW;
GO
CREATE DATABASE DemoDW;
GO
ALTER DATABASE DemoDW SET RECOVERY SIMPLE 
GO

-- STEP 2 - Create Filegroups
USE DemoDW;
GO
ALTER DATABASE DemoDW ADD FILEGROUP FG0000
GO
ALTER DATABASE DemoDW ADD FILE (NAME = F0000, FILENAME = 'D:\DBSAMPLES\F0000.ndf', SIZE = 10MB, FILEGROWTH = 50%) TO FILEGROUP FG0000;
GO
ALTER DATABASE DemoDW ADD FILEGROUP FG20130101
GO
ALTER DATABASE DemoDW ADD FILE (NAME = F20130101, FILENAME = 'D:\DBSAMPLES\F20130101.ndf', SIZE = 10MB, FILEGROWTH = 50%) TO FILEGROUP FG20130101;
GO

-- STEP 3 - Create Partitions
CREATE PARTITION FUNCTION PF (int) AS RANGE RIGHT FOR VALUES (20130101);
GO
CREATE PARTITION SCHEME PS AS PARTITION PF TO (FG0000, FG20130101);
GO

-- STEP 4 - Create Table
CREATE TABLE fact_table
 (datekey int, measure int)
ON PS(datekey);
GO

-- STEP 5 -  View partition metadata
SELECT  i.index_id
        , i.name AS IndexName
        , ps.name AS PartitionScheme
        , pf.name AS PartitionFunction
        , p.partition_number AS PartitionNumber
        , fg.name AS Filegroup
        , prv_left.value AS StartKey
        , prv_right.value AS EndKey
        , p.row_count Rows
FROM sys.dm_db_partition_stats p
    INNER JOIN sys.indexes i ON i.OBJECT_ID = p.OBJECT_ID AND i.index_id = p.index_id
    INNER JOIN sys.data_spaces ds ON ds.data_space_id = i.data_space_id
    LEFT OUTER JOIN sys.partition_schemes ps ON ps.data_space_id = i.data_space_id
    LEFT OUTER JOIN sys.partition_functions pf ON ps.function_id = pf.function_id
    LEFT OUTER JOIN sys.destination_data_spaces dds ON dds.partition_scheme_id = ps.data_space_id AND dds.destination_id = p.partition_number
    LEFT OUTER JOIN sys.filegroups fg ON fg.data_space_id = dds.data_space_id
    LEFT OUTER JOIN sys.partition_range_values prv_right ON prv_right.function_id = ps.function_id AND prv_right.boundary_id = p.partition_number
    LEFT OUTER JOIN sys.partition_range_values prv_left ON prv_left.function_id = ps.function_id AND prv_left.boundary_id = p.partition_number - 1
WHERE OBJECT_NAME(p.object_id) = 'fact_table'
GO

-- STEP 6 - Create Columnstore index
CREATE NONCLUSTERED COLUMNSTORE INDEX csidx_fact_table
ON fact_table(datekey, measure);
GO

-- STEP 7 -  View partition metadata
SELECT  i.index_id
        , i.name AS IndexName
        , ps.name AS PartitionScheme
        , pf.name AS PartitionFunction
        , p.partition_number AS PartitionNumber
        , fg.name AS Filegroup
        , prv_left.value AS StartKey
        , prv_right.value AS EndKey
        , p.row_count Rows
FROM sys.dm_db_partition_stats p
    INNER JOIN sys.indexes i ON i.OBJECT_ID = p.OBJECT_ID AND i.index_id = p.index_id
    INNER JOIN sys.data_spaces ds ON ds.data_space_id = i.data_space_id
    LEFT OUTER JOIN sys.partition_schemes ps ON ps.data_space_id = i.data_space_id
    LEFT OUTER JOIN sys.partition_functions pf ON ps.function_id = pf.function_id
    LEFT OUTER JOIN sys.destination_data_spaces dds ON dds.partition_scheme_id = ps.data_space_id AND dds.destination_id = p.partition_number
    LEFT OUTER JOIN sys.filegroups fg ON fg.data_space_id = dds.data_space_id
    LEFT OUTER JOIN sys.partition_range_values prv_right ON prv_right.function_id = ps.function_id AND prv_right.boundary_id = p.partition_number
    LEFT OUTER JOIN sys.partition_range_values prv_left ON prv_left.function_id = ps.function_id AND prv_left.boundary_id = p.partition_number - 1
WHERE OBJECT_NAME(p.object_id) = 'fact_table'
GO


-- STEP 8 - Add a new filegroup and make it the next used
ALTER DATABASE DemoDW ADD FILEGROUP FG20130102
GO
ALTER DATABASE DemoDW ADD FILE (NAME = FG20130102, FILENAME = 'D:\DBSAMPLES\FG20130102.ndf', SIZE = 10MB, FILEGROWTH = 50%) TO FILEGROUP FG20130102;
GO
ALTER PARTITION SCHEME PS
NEXT USED FG20130102;
GO
ALTER PARTITION FUNCTION PF() SPLIT RANGE(20130102);
GO

-- STEP 9 - View partition metadata
SELECT  i.index_id
        , i.name AS IndexName
        , ps.name AS PartitionScheme
        , pf.name AS PartitionFunction
        , p.partition_number AS PartitionNumber
        , fg.name AS Filegroup
        , prv_left.value AS StartKey
        , prv_right.value AS EndKey
        , p.row_count Rows
FROM sys.dm_db_partition_stats p
    INNER JOIN sys.indexes i ON i.OBJECT_ID = p.OBJECT_ID AND i.index_id = p.index_id
    INNER JOIN sys.data_spaces ds ON ds.data_space_id = i.data_space_id
    LEFT OUTER JOIN sys.partition_schemes ps ON ps.data_space_id = i.data_space_id
    LEFT OUTER JOIN sys.partition_functions pf ON ps.function_id = pf.function_id
    LEFT OUTER JOIN sys.destination_data_spaces dds ON dds.partition_scheme_id = ps.data_space_id AND dds.destination_id = p.partition_number
    LEFT OUTER JOIN sys.filegroups fg ON fg.data_space_id = dds.data_space_id
    LEFT OUTER JOIN sys.partition_range_values prv_right ON prv_right.function_id = ps.function_id AND prv_right.boundary_id = p.partition_number
    LEFT OUTER JOIN sys.partition_range_values prv_left ON prv_left.function_id = ps.function_id AND prv_left.boundary_id = p.partition_number - 1
WHERE OBJECT_NAME(p.object_id) = 'fact_table'
GO

-- STEP 10 - Create a load table
CREATE TABLE load_table
 (datekey int, measure int)
ON FG20130101;
GO

-- STEP 11 - Bulk load new data
INSERT load_table VALUES (20130101, 1);
INSERT load_table VALUES (20130101, 2);
GO

-- STEP 12 - Add constraints and indexes
ALTER TABLE load_table
WITH CHECK ADD CONSTRAINT BOUNDS
CHECK (datekey >= 20130101 and datekey < 20130102 and datekey IS NOT NULL);
GO
CREATE NONCLUSTERED COLUMNSTORE INDEX csidx_load_table
ON load_table(datekey, measure);
GO

-- STEP 13 - Switch the partition
DECLARE @p int = $PARTITION.PF(20130101);
ALTER TABLE load_table
SWITCH TO fact_table PARTITION @p
GO
-- STEP 14 - Clean up and view partition metadata
DROP TABLE load_table;
GO

-- STEP 15 - View partition metadata
SELECT  i.index_id
        , i.name AS IndexName
        , ps.name AS PartitionScheme
        , pf.name AS PartitionFunction
        , p.partition_number AS PartitionNumber
        , fg.name AS Filegroup
        , prv_left.value AS StartKey
        , prv_right.value AS EndKey
        , p.row_count Rows
FROM sys.dm_db_partition_stats p
    INNER JOIN sys.indexes i ON i.OBJECT_ID = p.OBJECT_ID AND i.index_id = p.index_id
    INNER JOIN sys.data_spaces ds ON ds.data_space_id = i.data_space_id
    LEFT OUTER JOIN sys.partition_schemes ps ON ps.data_space_id = i.data_space_id
    LEFT OUTER JOIN sys.partition_functions pf ON ps.function_id = pf.function_id
    LEFT OUTER JOIN sys.destination_data_spaces dds ON dds.partition_scheme_id = ps.data_space_id AND dds.destination_id = p.partition_number
    LEFT OUTER JOIN sys.filegroups fg ON fg.data_space_id = dds.data_space_id
    LEFT OUTER JOIN sys.partition_range_values prv_right ON prv_right.function_id = ps.function_id AND prv_right.boundary_id = p.partition_number
    LEFT OUTER JOIN sys.partition_range_values prv_left ON prv_left.function_id = ps.function_id AND prv_left.boundary_id = p.partition_number - 1
WHERE OBJECT_NAME(p.object_id) = 'fact_table'
GO


-- STEP 16 - Add Next day 200130102
-- Add a new filegroup and make it the next used
ALTER DATABASE DemoDW ADD FILEGROUP FG20130103
GO
ALTER DATABASE DemoDW ADD FILE (NAME = FG20130103, FILENAME = 'D:\DBSAMPLES\FG20130103.ndf', SIZE = 10MB, FILEGROWTH = 50%) TO FILEGROUP FG20130103;
GO
ALTER PARTITION SCHEME PS
NEXT USED FG20130103;
GO
ALTER PARTITION FUNCTION PF() SPLIT RANGE(20130103);
GO
-- Create a load table
CREATE TABLE load_table
 (datekey int, measure int)
ON FG20130102;
GO
-- Bulk load new data
INSERT load_table VALUES (20130102, 1);
INSERT load_table VALUES (20130102, 2);
GO
-- Add constraints and indexes
ALTER TABLE load_table
WITH CHECK ADD CONSTRAINT BOUNDS
CHECK (datekey >= 20130102 and datekey < 20130103 and datekey IS NOT NULL);
GO
CREATE NONCLUSTERED COLUMNSTORE INDEX csidx_load_table
ON load_table(datekey, measure);
GO
-- Switch the partition
DECLARE @p int = $PARTITION.PF(20130102);
ALTER TABLE load_table
SWITCH TO fact_table PARTITION @p
GO
-- Clean up and view partition metadata
DROP TABLE load_table;
GO
-- View partition metadata
SELECT  i.index_id
        , i.name AS IndexName
        , ps.name AS PartitionScheme
        , pf.name AS PartitionFunction
        , p.partition_number AS PartitionNumber
        , fg.name AS Filegroup
        , prv_left.value AS StartKey
        , prv_right.value AS EndKey
        , p.row_count Rows
FROM sys.dm_db_partition_stats p
    INNER JOIN sys.indexes i ON i.OBJECT_ID = p.OBJECT_ID AND i.index_id = p.index_id
    INNER JOIN sys.data_spaces ds ON ds.data_space_id = i.data_space_id
    LEFT OUTER JOIN sys.partition_schemes ps ON ps.data_space_id = i.data_space_id
    LEFT OUTER JOIN sys.partition_functions pf ON ps.function_id = pf.function_id
    LEFT OUTER JOIN sys.destination_data_spaces dds ON dds.partition_scheme_id = ps.data_space_id AND dds.destination_id = p.partition_number
    LEFT OUTER JOIN sys.filegroups fg ON fg.data_space_id = dds.data_space_id
    LEFT OUTER JOIN sys.partition_range_values prv_right ON prv_right.function_id = ps.function_id AND prv_right.boundary_id = p.partition_number
    LEFT OUTER JOIN sys.partition_range_values prv_left ON prv_left.function_id = ps.function_id AND prv_left.boundary_id = p.partition_number - 1
WHERE OBJECT_NAME(p.object_id) = 'fact_table'
GO

-- STEP 17 - 200130103
-- Add a new filegroup and make it the next used
ALTER DATABASE DemoDW ADD FILEGROUP FG20130104
GO
ALTER DATABASE DemoDW ADD FILE (NAME = FG20130104, FILENAME = 'D:\DBSAMPLES\FG20130104.ndf', SIZE = 10MB, FILEGROWTH = 50%) TO FILEGROUP FG20130104;
GO
ALTER PARTITION SCHEME PS
NEXT USED FG20130104;
GO
ALTER PARTITION FUNCTION PF() SPLIT RANGE(20130104);
GO
-- Create a load table
CREATE TABLE load_table
 (datekey int, measure int)
ON FG20130103;
GO
-- Bulk load new data
INSERT load_table VALUES (20130103, 1);
INSERT load_table VALUES (20130103, 2);
GO
-- Add constraints and indexes
ALTER TABLE load_table
WITH CHECK ADD CONSTRAINT BOUNDS
CHECK (datekey >= 20130103 and datekey < 20130104 and datekey IS NOT NULL);
GO
CREATE NONCLUSTERED COLUMNSTORE INDEX csidx_load_table
ON load_table(datekey, measure);
GO
-- Switch the partition
DECLARE @p int = $PARTITION.PF(20130103);
ALTER TABLE load_table
SWITCH TO fact_table PARTITION @p
GO
-- Clean up and view partition metadata
DROP TABLE load_table;
GO
-- View partition metadata
SELECT  i.index_id
        , i.name AS IndexName
        , ps.name AS PartitionScheme
        , pf.name AS PartitionFunction
        , p.partition_number AS PartitionNumber
        , fg.name AS Filegroup
        , prv_left.value AS StartKey
        , prv_right.value AS EndKey
        , p.row_count Rows
FROM sys.dm_db_partition_stats p
    INNER JOIN sys.indexes i ON i.OBJECT_ID = p.OBJECT_ID AND i.index_id = p.index_id
    INNER JOIN sys.data_spaces ds ON ds.data_space_id = i.data_space_id
    LEFT OUTER JOIN sys.partition_schemes ps ON ps.data_space_id = i.data_space_id
    LEFT OUTER JOIN sys.partition_functions pf ON ps.function_id = pf.function_id
    LEFT OUTER JOIN sys.destination_data_spaces dds ON dds.partition_scheme_id = ps.data_space_id AND dds.destination_id = p.partition_number
    LEFT OUTER JOIN sys.filegroups fg ON fg.data_space_id = dds.data_space_id
    LEFT OUTER JOIN sys.partition_range_values prv_right ON prv_right.function_id = ps.function_id AND prv_right.boundary_id = p.partition_number
    LEFT OUTER JOIN sys.partition_range_values prv_left ON prv_left.function_id = ps.function_id AND prv_left.boundary_id = p.partition_number - 1
WHERE OBJECT_NAME(p.object_id) = 'fact_table'
GO


-- STEP 18 - 200130131
-- Add a new filegroup and make it the next used
ALTER DATABASE DemoDW ADD FILEGROUP FG20130201
GO
ALTER DATABASE DemoDW ADD FILE (NAME = FG20130201, FILENAME = 'D:\DBSAMPLES\FG20130201.ndf', SIZE = 10MB, FILEGROWTH = 50%) TO FILEGROUP FG20130201;
GO
ALTER PARTITION SCHEME PS
NEXT USED FG20130201;
GO
ALTER PARTITION FUNCTION PF() SPLIT RANGE(20130201);
GO
-- Create a load table
CREATE TABLE load_table
 (datekey int, measure int)
ON FG20130104;
GO
-- Bulk load new data
INSERT load_table VALUES (20130131, 1);
INSERT load_table VALUES (20130131, 2);
GO
-- Add constraints and indexes
ALTER TABLE load_table
WITH CHECK ADD CONSTRAINT BOUNDS
CHECK (datekey >= 20130104 and datekey < 20130201 and datekey IS NOT NULL);
GO
CREATE NONCLUSTERED COLUMNSTORE INDEX csidx_load_table
ON load_table(datekey, measure);
GO
-- Switch the partition
DECLARE @p int = $PARTITION.PF(20130104);
ALTER TABLE load_table
SWITCH TO fact_table PARTITION @p
GO
-- Clean up and view partition metadata
DROP TABLE load_table;
GO
-- View partition metadata
SELECT  i.index_id
        , i.name AS IndexName
        , ps.name AS PartitionScheme
        , pf.name AS PartitionFunction
        , p.partition_number AS PartitionNumber
        , fg.name AS Filegroup
        , prv_left.value AS StartKey
        , prv_right.value AS EndKey
        , p.row_count Rows
FROM sys.dm_db_partition_stats p
    INNER JOIN sys.indexes i ON i.OBJECT_ID = p.OBJECT_ID AND i.index_id = p.index_id
    INNER JOIN sys.data_spaces ds ON ds.data_space_id = i.data_space_id
    LEFT OUTER JOIN sys.partition_schemes ps ON ps.data_space_id = i.data_space_id
    LEFT OUTER JOIN sys.partition_functions pf ON ps.function_id = pf.function_id
    LEFT OUTER JOIN sys.destination_data_spaces dds ON dds.partition_scheme_id = ps.data_space_id AND dds.destination_id = p.partition_number
    LEFT OUTER JOIN sys.filegroups fg ON fg.data_space_id = dds.data_space_id
    LEFT OUTER JOIN sys.partition_range_values prv_right ON prv_right.function_id = ps.function_id AND prv_right.boundary_id = p.partition_number
    LEFT OUTER JOIN sys.partition_range_values prv_left ON prv_left.function_id = ps.function_id AND prv_left.boundary_id = p.partition_number - 1
WHERE OBJECT_NAME(p.object_id) = 'fact_table'
GO

Antonios Chatzipavlis

Antonios Chatzipavlis is a highly experienced Data Solutions Consultant and Trainer. He has been working in the IT industry since 1988, holding various roles such as senior developer, IT Manager, Data & AI Solutions Architect and Consultant.

Since 1995, Antonios has focused on modern technologies and software development tools, primarily by Microsoft. He has specialized in Data & AI since 2000, with expertise in Microsoft Data Platform (SQL Server, Azure SQL Databases, Azure Synapse Analytics, Microsoft Fabric, Power BI, AI) and Databricks.

Antonios is also a Microsoft Certified Trainer (MCT) for over 25 years, has been recognized as a Microsoft Most Valuable Professional (MVP) in Data Platform since 2010 and he is in the Data Expert 40 Powerlist 2024 by Boussias. He is the co-founder and visionary behind XLYTiCA, a company dedicated to Data & AI solutions.