• Latest
  • Trending
SQL Server Database Partitioning

SQL Server Database Partitioning

June 29, 2022
ATC Ghana supports Girls-In-ICT Program

ATC Ghana supports Girls-In-ICT Program

April 25, 2023
Vice President Dr. Bawumia inaugurates  ICT Hub

Vice President Dr. Bawumia inaugurates ICT Hub

April 2, 2023
Co-Creation Hub’s edtech accelerator puts $15M towards African startups

Co-Creation Hub’s edtech accelerator puts $15M towards African startups

February 20, 2023
Data Leak Hits Thousands of NHS Workers

Data Leak Hits Thousands of NHS Workers

February 20, 2023
EU Cybersecurity Agency Warns Against Chinese APTs

EU Cybersecurity Agency Warns Against Chinese APTs

February 20, 2023
How Your Storage System Will Still Be Viable in 5 Years’ Time?

How Your Storage System Will Still Be Viable in 5 Years’ Time?

February 20, 2023
The Broken Promises From Cybersecurity Vendors

Cloud Infrastructure Used By WIP26 For Espionage Attacks on Telcos

February 20, 2023
Instagram and Facebook to get paid-for verification

Instagram and Facebook to get paid-for verification

February 20, 2023
YouTube CEO Susan Wojcicki steps down after nine years

YouTube CEO Susan Wojcicki steps down after nine years

February 20, 2023
Inaugural AfCFTA Conference on Women and Youth in Trade

Inaugural AfCFTA Conference on Women and Youth in Trade

September 6, 2022
Instagram fined €405m over children’s data privacy

Instagram fined €405m over children’s data privacy

September 6, 2022
8 Most Common Causes of a Data Breach

5.7bn data entries found exposed on Chinese VPN

August 18, 2022
  • Consumer Watch
  • Kids Page
  • Directory
  • Events
  • Reviews
Sunday, 4 June, 2023
  • Login
itechnewsonline.com
  • Home
  • Tech
  • Africa Tech
  • InfoSEC
  • Data Science
  • Data Storage
  • Business
  • Opinion
Subscription
Advertise
No Result
View All Result
itechnewsonline.com
No Result
View All Result

SQL Server Database Partitioning

by ITECHNEWS
June 29, 2022
in Data Science, Leading Stories
0 0
0
SQL Server Database Partitioning

What is database table partitioning?

By Default Data of a table reside in one filegroup called Primary. Partitioning enables divide large table into units that may be spread across more than one filegroup in a database and offer fast ways to load and remove large amounts of data from a table. By splitting into smaller units, queries that access only a fraction of the data can run faster because there is less data to scan.

Benefits of Partitioning

  • Aid in Maintenance of Large Table

Perform maintenance operations quickly because operations target only subsets of data instead of the whole table.

YOU MAY ALSO LIKE

ATC Ghana supports Girls-In-ICT Program

Vice President Dr. Bawumia inaugurates ICT Hub

We can transfer or access subsets of data quickly and efficiently while maintaining the integrity of a data collection.

Ex.
Loading data to the table (ETL)

  • Reduce overall response time to read and load data

Queries may be improved as the query might only scan a specific partition or partitions instead of the entire Table.

Types of Partition

  1. Vertical Partition

The table will be divided into multiple Tables based on columns.

  1. Horizontal Partition

The Table will be divided into multiple with the same number of Columns with a fewer number of rows.

Create Horizontal partitioning in SQL server.

  1. Create Test DB and Test Table
   CREATE DATABASE PartitionTest

   GO

   USE PartitionTest

   CREATE TABLE Orders
       (
         OrderID INT IDENTITY NOT NULL,
         OrderDate DATETIME NOT NULL ,
         OrderMonth INT NOT NULL
       );

  1. Create the partition function

Create Partition function. Defines the number of Partitions. We need to have a column(Partition Column) of Table or index that will be used by partition functions to create and populate partitions. Each value in the partitioning column is an input to the partitioning function, which returns a partition value.

Partitioning Column (OrderMonth)

   CREATE PARTITION FUNCTION PartitionByMonth (INT)
   AS RANGE RIGHT
   FOR VALUES (202201, 202202, 202203);
  1. Create File Group
   --Create File Group
   ALTER DATABASE PartitionTest ADD FILEGROUP FGJan
   GO
   ALTER DATABASE PartitionTest ADD FILEGROUP FGFeb
   GO
   ALTER DATABASE PartitionTest ADD FILEGROUP FGMarch
   GO
  1. Add files to the filegroups
   --Create files and attach to File Group
   ALTER DATABASE PartitionTest
   ADD FILE
   (
     NAME = [File_Month_Jan],
     FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL15.MSSQLSERVER\MSSQL\DATA\File_Month_Jan.ndf',
       SIZE = 5 MB,
       MAXSIZE = UNLIMITED,
       FILEGROWTH = 10 MB
   ) TO FILEGROUP FGJan

   ALTER DATABASE PartitionTest
   ADD FILE
   (
     NAME = [File_Month_Feb],
     FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL15.MSSQLSERVER\MSSQL\DATA\File_Month_Feb.ndf',
       SIZE = 5 MB,
       MAXSIZE = UNLIMITED,
       FILEGROWTH = 10 MB
   ) TO FILEGROUP FGFeb

   ALTER DATABASE PartitionTest
   ADD FILE
   (
     NAME = [File_Month_March],
     FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL15.MSSQLSERVER\MSSQL\DATA\File_Month_March.ndf',
       SIZE = 5 MB,
       MAXSIZE = UNLIMITED,
       FILEGROWTH = 10 MB
   ) TO FILEGROUP FGMarch
  1. Create the partition scheme
   --Create the partition scheme
   CREATE PARTITION SCHEME OrdersPS
    AS PARTITION PartitionByMonth
    TO ([Primary], FGJan, FGFeb, FGMarch);

Here we have to mention all the filegroups including default Primary File Group.

Query to get available File Groups

   SELECT name AS AvailableFilegroups
     FROM sys.filegroups
     WHERE type = 'FG'
  1. Create/Update Table with Partitioning
   --Create Index
   CREATE CLUSTERED INDEX IX_Orders ON [dbo]. [Orders]
   (
      [OrderMonth]
   )WITH (SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF) ON OrdersPS(OrderMonth)
  1. Verify partitions with Row Count
   SELECT p.partition_number AS PartitionNumber,
          f.name             AS PartitionFilegroup,
          p.rows             AS NumberOfRows
   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) = 'Orders'

| PartitionFilegroup | PartitionNumber | NumberOfRows |
| —————— | ————— | ———— |
| PRIMARY | 1 | 0 |
| FGJan | 2 | 0 |
| FGFeb | 3 | 0 |
| FGMarch | 4 | 0 |

Insert Test Data

   INSERT INTO PartitionTest.dbo.Orders (OrderDate, OrderMonth)
   VALUES (N'2022-01-18 17:25:05.000', 202201);

   INSERT INTO PartitionTest.dbo.Orders (OrderDate, OrderMonth)
   VALUES (N'2022-02-18 17:25:39.000', 202202);

   INSERT INTO PartitionTest.dbo.Orders (OrderDate, OrderMonth)
   VALUES (N'2022-03-18 17:25:58.000', 202203);

We can see rows are distributed among each file groups

| PartitionFilegroup | PartitionNumber | NumberOfRows |
| —————— | ————— | ———— |
| PRIMARY | 1 | 0 |
| FGJan | 2 | 1 |
| FGFeb | 3 | 1 |
| FGMarch | 4 | 1 |

Complete Query

CREATE DATABASE PartitionTest

GO

USE PartitionTest

CREATE TABLE Orders
(
    OrderID    INT IDENTITY NOT NULL,
    OrderDate  DATETIME     NOT NULL,
    OrderMonth INT          NOT NULL
);

CREATE PARTITION FUNCTION PartitionByMonth (INT)
    AS RANGE RIGHT
    FOR VALUES (202201, 202202, 202203);

--Create File Group
ALTER DATABASE PartitionTest ADD FILEGROUP FGJan
GO
ALTER DATABASE PartitionTest ADD FILEGROUP FGFeb
GO
ALTER DATABASE PartitionTest ADD FILEGROUP FGMarch
GO
--Create files and attach to File Group
ALTER DATABASE PartitionTest
    ADD FILE
        (
            NAME = [File_Month_Jan],
            FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL15.MSSQLSERVER\MSSQL\DATA\File_Month_Jan.ndf',
            SIZE = 5 MB,
            MAXSIZE = UNLIMITED,
            FILEGROWTH = 10 MB
            ) TO FILEGROUP FGJan

ALTER DATABASE PartitionTest
    ADD FILE
        (
            NAME = [File_Month_Feb],
            FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL15.MSSQLSERVER\MSSQL\DATA\File_Month_Feb.ndf',
            SIZE = 5 MB,
            MAXSIZE = UNLIMITED,
            FILEGROWTH = 10 MB
            ) TO FILEGROUP FGFeb

ALTER DATABASE PartitionTest
    ADD FILE
        (
            NAME = [File_Month_March],
            FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL15.MSSQLSERVER\MSSQL\DATA\File_Month_March.ndf',
            SIZE = 5 MB,
            MAXSIZE = UNLIMITED,
            FILEGROWTH = 10 MB
            ) TO FILEGROUP FGMarch

GO

--Create the partition scheme
CREATE PARTITION SCHEME OrdersPS
    AS PARTITION PartitionByMonth
    TO ([Primary],FGJan, FGFeb, FGMarch);

--Create Index
CREATE CLUSTERED INDEX IX_Orders ON [dbo].[Orders]
    (
     [OrderMonth]
        ) WITH (SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF) ON OrdersPS(OrderMonth)

-- Insert Test Data
INSERT INTO PartitionTest.dbo.Orders (OrderDate, OrderMonth)
VALUES (N'2022-01-18 17:25:05.000', 202201);

INSERT INTO PartitionTest.dbo.Orders (OrderDate, OrderMonth)
VALUES (N'2022-02-18 17:25:39.000', 202202);

INSERT INTO PartitionTest.dbo.Orders (OrderDate, OrderMonth)
VALUES (N'2022-03-18 17:25:58.000', 202203);

-- View File Groups
SELECT name AS AvailableFilegroups
FROM sys.filegroups
WHERE type = 'FG'

-- View Row counts of Partitions
SELECT p.partition_number AS PartitionNumber,
       f.name             AS PartitionFilegroup,
       p.rows             AS NumberOfRows
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) = 'Orders'
Source: Jeevan Wijerathna
Tags: SQL Server Database Partitioning
ShareTweetShare
Plugin Install : Subscribe Push Notification need OneSignal plugin to be installed.

Search

No Result
View All Result

Recent News

ATC Ghana supports Girls-In-ICT Program

ATC Ghana supports Girls-In-ICT Program

April 25, 2023
Vice President Dr. Bawumia inaugurates  ICT Hub

Vice President Dr. Bawumia inaugurates ICT Hub

April 2, 2023
Co-Creation Hub’s edtech accelerator puts $15M towards African startups

Co-Creation Hub’s edtech accelerator puts $15M towards African startups

February 20, 2023

About What We Do

itechnewsonline.com

We bring you the best Premium Tech News.

Recent News With Image

ATC Ghana supports Girls-In-ICT Program

ATC Ghana supports Girls-In-ICT Program

April 25, 2023
Vice President Dr. Bawumia inaugurates  ICT Hub

Vice President Dr. Bawumia inaugurates ICT Hub

April 2, 2023

Recent News

  • ATC Ghana supports Girls-In-ICT Program April 25, 2023
  • Vice President Dr. Bawumia inaugurates ICT Hub April 2, 2023
  • Co-Creation Hub’s edtech accelerator puts $15M towards African startups February 20, 2023
  • Data Leak Hits Thousands of NHS Workers February 20, 2023
  • Home
  • InfoSec
  • Opinion
  • Africa Tech
  • Data Storage

© 2021-2022 iTechNewsOnline.Com - Powered by BackUPDataSystems

No Result
View All Result
  • Home
  • Tech
  • Africa Tech
  • InfoSEC
  • Data Science
  • Data Storage
  • Business
  • Opinion

© 2021-2022 iTechNewsOnline.Com - Powered by BackUPDataSystems

Welcome Back!

Login to your account below

Forgotten Password?

Retrieve your password

Please enter your username or email address to reset your password.

Log In
Go to mobile version