SQL 2025 master class LAB 17 partioning

 https://www.sqlservertutorial.net/wp-content/uploads/SQL-Server-Sample-Database.zip

create database BikeStores 

use bikestores 

BikeStores Sample Database – create objects.sql

 BikeStores Sample Database – load data.sql

--Step 1

 WITH order_data (order_date, product_name, amount)
AS (
SELECT
  order_date,
  product_name,
  SUM(i.quantity * i.list_price * (1 - discount))
FROM sales.orders o
INNER JOIN sales.order_items i
  ON i.order_id = o.order_id
INNER JOIN production.products p
  ON p.product_id = i.product_id
GROUP BY order_date,
         product_name
)

--Step 2
SELECT * FROM order_data;

 WITH order_data (order_date, product_name, amount)
AS (SELECT
  order_date,
  product_name,
  SUM(i.quantity * i.list_price * (1 - discount))
FROM sales.orders o
INNER JOIN sales.order_items i
  ON i.order_id = o.order_id
INNER JOIN production.products p
  ON p.product_id = i.product_id
GROUP BY order_date,
         product_name)

SELECT
  YEAR(order_date) year,
  COUNT(*) row_count
FROM order_data
GROUP BY YEAR(order_date);

  --Step 3

ALTER DATABASE bikestores
ADD FILEGROUP orders_2016;

ALTER DATABASE bikestores
ADD FILEGROUP orders_2017;

ALTER DATABASE bikestores
ADD FILEGROUP orders_2018; 

--Step 4

SELECT
  name
FROM sys.filegroups
WHERE type = 'FG';

ALTER DATABASE bikestores    
ADD FILE     (
    NAME = orders_2016,
    FILENAME = 'C:\SQL_DBs\SQL2025\orders_2016.ndf',
        SIZE = 10 MB, 
        MAXSIZE = UNLIMITED, 
        FILEGROWTH = 1024 KB
    ) TO FILEGROUP orders_2016;

ALTER DATABASE bikestores    
ADD FILE     (
    NAME = orders_2017,
    FILENAME = 'C:\SQL_DBs\SQL2025\orders_2017.ndf',
        SIZE = 10 MB, 
        MAXSIZE = UNLIMITED, 
        FILEGROWTH = 1024 KB
    ) TO FILEGROUP orders_2017;

ALTER DATABASE bikestores    
ADD FILE     (
    NAME = orders_2018,
    FILENAME = 'C:\SQL_DBs\SQL2025\orders_2018.ndf',
        SIZE = 10 MB, 
        MAXSIZE = UNLIMITED, 
        FILEGROWTH = 1024 KB
    ) TO FILEGROUP orders_2018;

 --Step 5

SELECT 
    name as filename,
    physical_name as file_path
FROM sys.database_files
where type_desc = 'ROWS';

--Step 6

CREATE PARTITION FUNCTION order_by_year_function (date)
AS RANGE LEFT 
FOR VALUES ('2016-12-31', '2017-12-31'); 

--Step 7

 CREATE PARTITION SCHEME order_by_year_scheme
AS PARTITION order_by_year_function
TO ([orders_2016], [orders_2017], [orders_2018]);

--Step 8

 CREATE TABLE sales.order_reports (
  order_date date,
  product_name varchar(255),
  amount decimal(10, 2) NOT NULL DEFAULT 0,
  PRIMARY KEY (order_date, product_name)
) 
ON order_by_year_scheme (order_date);

--Step 9

 INSERT INTO sales.order_reports (order_date, product_name, amount)
  SELECT
    order_date,
    product_name,
    SUM(i.quantity * i.list_price * (1 - discount))
  FROM sales.orders o
  INNER JOIN sales.order_items i
    ON i.order_id = o.order_id
  INNER JOIN production.products p
    ON p.product_id = i.product_id
  GROUP BY order_date,
           product_name;

--Step 10

 SELECT 
    p.partition_number AS partition_number,
    f.name AS file_group, 
    p.rows AS row_count
FROM sys.partitions p
JOIN sys.destination_data_spaces dds ON p.partition_number = dds.destination_id
JOIN sys.filegroups f ON dds.data_space_id = f.data_space_id
WHERE OBJECT_NAME(OBJECT_ID) = 'order_reports'
order by partition_number;

--with thanks and acknowledgement to https://www.sqlservertutorial.net/sql-server-administration/sql-server-table-partitioning/

--Step 11 

/* --------------------------------------------------
-- Virtual Numbers Table
-------------------------------------------------- */
WITH
  L0   AS (SELECT 1 AS n UNION ALL SELECT 1),              -- 2 rows
  L1   AS (SELECT 1 AS n FROM L0 AS a CROSS JOIN L0 AS b), -- 4 rows (2 x 2)
  L2   AS (SELECT 1 AS n FROM L1 AS a CROSS JOIN L1 AS b), -- 16 rows (4 x 4)
  L3   AS (SELECT 1 AS n FROM L2 AS a CROSS JOIN L2 AS b), -- 256 rows (16 x 16)
  L4   AS (SELECT 1 AS n FROM L3 AS a CROSS JOIN L3 AS b), -- 65 536 rows (256 x 256)
  L5   AS (SELECT 1 AS n FROM L4 AS a CROSS JOIN L4 AS b), -- 4 294 967 296 rows (65 536 x 65 536)
  Nums AS (SELECT ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS n FROM L5)
SELECT TOP (1000) n FROM Nums ORDER BY n;

/* --------------------------------------------------
-- Numbers Table
-------------------------------------------------- */

IF OBJECT_ID('dbo.Numbers') IS NOT NULL
  DROP TABLE dbo.Numbers;
GO

CREATE TABLE Numbers (
    n BIGINT NOT NULL,
    CONSTRAINT PK_Numbers PRIMARY KEY CLUSTERED (n) WITH FILLFACTOR = 100
);
GO

WITH
  L0   AS (SELECT 1 AS n UNION ALL SELECT 1),
  L1   AS (SELECT 1 AS n FROM L0 AS a CROSS JOIN L0 AS b),
  L2   AS (SELECT 1 AS n FROM L1 AS a CROSS JOIN L1 AS b),
  L3   AS (SELECT 1 AS n FROM L2 AS a CROSS JOIN L2 AS b),
  L4   AS (SELECT 1 AS n FROM L3 AS a CROSS JOIN L3 AS b),
  L5   AS (SELECT 1 AS n FROM L4 AS a CROSS JOIN L4 AS b),
  Nums AS (SELECT ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS n FROM L5)
INSERT INTO dbo.Numbers (n)
SELECT TOP (100000) n FROM Nums ORDER BY n; /* Insert as many numbers as you need */
GO

SELECT n FROM dbo.Numbers WHERE n <= 1000;
GO

/* --------------------------------------------------
-- Numbers Table Function
-------------------------------------------------- */


IF OBJECT_ID('dbo.GetNums') IS NOT NULL
  DROP FUNCTION dbo.GetNums;
GO
 
CREATE FUNCTION dbo.GetNums(@n AS BIGINT) RETURNS TABLE AS RETURN
  WITH
  L0   AS (SELECT 1 AS n UNION ALL SELECT 1),
  L1   AS (SELECT 1 AS n FROM L0 AS a CROSS JOIN L0 AS b),
  L2   AS (SELECT 1 AS n FROM L1 AS a CROSS JOIN L1 AS b),
  L3   AS (SELECT 1 AS n FROM L2 AS a CROSS JOIN L2 AS b),
  L4   AS (SELECT 1 AS n FROM L3 AS a CROSS JOIN L3 AS b),
  L5   AS (SELECT 1 AS n FROM L4 AS a CROSS JOIN L4 AS b),
  Nums AS (SELECT ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS n FROM L5)
  SELECT TOP (@n) n FROM Nums ORDER BY n;
GO

SELECT n FROM dbo.GetNums(1000);
GO

 

-- Step 12 partition switching 

-- Drop objects if they already exist
IF EXISTS (SELECT * FROM sys.tables WHERE name = N'SalesSource')
  DROP TABLE SalesSource;
IF EXISTS (SELECT * FROM sys.tables WHERE name = N'SalesTarget')
  DROP TABLE SalesTarget;
IF EXISTS (SELECT * FROM sys.partition_schemes WHERE name = N'psSales')
  DROP PARTITION SCHEME psSales;
IF EXISTS (SELECT * FROM sys.partition_functions WHERE name = N'pfSales')
  DROP PARTITION FUNCTION pfSales;
 
-- Create the Partition Function 
CREATE PARTITION FUNCTION pfSales (DATE)
AS RANGE RIGHT FOR VALUES 
('2022-01-01', '2023-01-01', '2024-01-01');
 
-- Create the Partition Scheme
CREATE PARTITION SCHEME psSales
AS PARTITION pfSales 
ALL TO ([Primary]);
 
-- Create the Partitioned Source Table (Heap) on the Partition Scheme
CREATE TABLE SalesSource (
  SalesDate DATE,
  Quantity INT
) ON psSales(SalesDate);
 
-- Insert test data
INSERT INTO SalesSource(SalesDate, Quantity)
SELECT DATEADD(DAY,dates.n-1,'2021-01-01') AS SalesDate, qty.n AS Quantity
FROM GetNums(DATEDIFF(DD,'2021-01-01','2022-01-01')) dates
CROSS JOIN GetNums(1000) AS qty;

-- Create the Partitioned Target Table (Heap) on the Partition Scheme
CREATE TABLE SalesTarget (
  SalesDate DATE,
  Quantity INT
) ON psSales(SalesDate);
 
-- Insert test data
INSERT INTO SalesTarget(SalesDate, Quantity)
SELECT DATEADD(DAY,dates.n-1,'2022-01-01') AS SalesDate, qty.n AS Quantity
FROM GetNums(DATEDIFF(DD,'2016-01-01','2022-01-01')) dates
CROSS JOIN GetNums(1000) AS qty;

-- Verify row count before switch
SELECT 
    pstats.partition_number AS PartitionNumber
    ,pstats.row_count AS PartitionRowCount
FROM sys.dm_db_partition_stats AS pstats
WHERE pstats.object_id = OBJECT_ID('SalesSource')
ORDER BY PartitionNumber; -- 366000 rows in Partition 1, 0 rows in Partitions 2-4
SELECT 
    pstats.partition_number AS PartitionNumber
    ,pstats.row_count AS PartitionRowCount
FROM sys.dm_db_partition_stats AS pstats
WHERE pstats.object_id = OBJECT_ID('SalesTarget')
ORDER BY PartitionNumber; -- 0 rows in Partition 1, 365000 rows in Partitions 2-4

-- Turn on statistics
SET STATISTICS TIME ON;

-- Is it really that fast...?
ALTER TABLE SalesSource SWITCH PARTITION 1 TO SalesTarget PARTITION 1; 
-- YEP! SUPER FAST!

-- Turn off statistics
SET STATISTICS TIME OFF;

-- Verify row count after switch
SELECT 
    pstats.partition_number AS PartitionNumber
    ,pstats.row_count AS PartitionRowCount
FROM sys.dm_db_partition_stats AS pstats
WHERE pstats.object_id = OBJECT_ID('SalesSource')
ORDER BY PartitionNumber; -- 0 rows in Partition 1-4
SELECT 
    pstats.partition_number AS PartitionNumber
    ,pstats.row_count AS PartitionRowCount
FROM sys.dm_db_partition_stats AS pstats
WHERE pstats.object_id = OBJECT_ID('SalesTarget')
ORDER BY PartitionNumber; -- 366000 rows in Partition 1, 365000 rows in Partitions 2-4

 --with thanks and acknowledgement to https://www.cathrinewilhelmsen.net/table-partitioning-in-sql-server-partition-switching/

 --housekeeping

use master

ALTER DATABASE bikestores
SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
GO
DROP DATABASE bikestores;
GO