1. what is

• Partitioning is the process of breaking or separating something into smaller pieces
• Partitioning a HDD allows for smaller, separate instances
• Breaking data in a table down into smaller groupings allow for faster processing
• Each partition created, if there is an index, will have a separate B-Tree

2.Steps in Creating a Partition

• Create a Partition Function
• Create File Groups & Assign a File to Each
• Create a Partition Scheme
• Create/Modify a Table/Index with Partition Scheme

3.Partition Point to Consider

• Partitioning can create extra index B-Tree’s to monitor. Be sure the table should have an index at all
• Partitioning can be done on tables easily, undoing them requires dropping the table
• Partitioning takes time and memory, don’t partition unless it’s necessary
• Usually only performed in Data Warehouses
• can have 15000 partitions
• always use data to create partition

4. 优势

• when index is created on partitioned table,A b-tree is created for each partition
  index depth is less
• rebuild / reorganize index, only on particular partition
  faster and easy
• if some wrong happened on a particular partition, it will not interval other partition
• we can place partitions on different file groups
  easy back up
  data reterival fast
• truncate partition faster than delete
  SWITCH (move data to other place?) fast
• minize lock; max lock is on partition

5. disadvantage

• when we have more partition,we need merge them; maintaince and overhead

6.note

• you can create file groups and add partitions to different file groups but it is not a mandatory step.
• ndf :secondary files
• mdf: primary file
• every table has partition(?)

Common Questions on Partitioning

1. How do you create partitioning on an existing table without dropping it
   https://www.mssqltips.com/sqlservertip/5296/implementation-of-sliding-window-partitioning-in-sql-server-to-purge-data/
2. How do you create partitioning on an existing table with CI without dropping table
3. How do you create different types of indexes on different partitions
   filtered index(?)
4. What is sliding window or rolling window mechanism in partitions
   https://www.databasejournal.com/features/mssql/partitioning-in-sql-server-managing-sliding-window-scenario.html
5. How can you back up data faster with partitions
6. What is the new feature of 2016 related to partitioning? --TRUNCATE TABLE <TABLE NAME> WITH PARTITION (1)
7. How many partitions are possible for each table? --2012 AND LATER 15000
8. What are the advantages of partitions?

7.switch

https://www.mssqltips.com/sqlservertip/5296/implementation-of-sliding-window-partitioning-in-sql-server-to-purge-data/

视频代码

create partition function part_func (int)
as
range left for values (50,100,150,200)

alter database test_db
    add filegroup filegroup1
alter database test_db
    add filegroup filegroup2
alter database test_db
    add filegroup filegroup3
alter database test_db
    add filegroup filegroup4
alter database test_db
    add filegroup filegroup5

alter database test_db
add file
(name=fg1_dat,
filename="c:\partition\fg1.ndf",
size = 30mb,
maxsize =50mb,
filegrowth = 5mb)
to filegroup filegroup1

alter database test_db
add file
(name=fg2_dat,
filename="c:\partition\fg2.ndf",
size = 30mb,
maxsize =50mb,
filegrowth = 5mb)
to filegroup filegroup2

alter database test_db
add file
(name=fg3_dat,
filename="c:\partition\fg3.ndf",
size = 30mb,
maxsize =50mb,
filegrowth = 5mb)
to filegroup filegroup3

alter database test_db
add file
(name=fg4_dat,
filename="c:\partition\fg4.ndf",
size = 30mb,
maxsize =50mb,
filegrowth = 5mb)
to filegroup filegroup4

alter database test_db
add file
(name=fg5_dat,
filename="c:\partition\fg5.ndf",
size = 30mb,
maxsize =50mb,
filegrowth = 5mb)
to filegroup filegroup5

create partition scheme part_scheme
as
partition part_func to
(filegroup1,filegroup2,filegroup3,filegroup4,filegroup5)

create table partition_table
(id int identity(1,1),name varchar(50)) on part_scheme (id)

drop table partition_table
drop partition scheme part_scheme
drop partition function part_func

alter database test_db
    remove file fg1_dat
alter database test_db
    remove file fg2_dat
alter database test_db
    remove file fg3_dat
alter database test_db
    remove file fg4_dat
alter database test_db
    remove file fg5_dat

alter database test_db
remove filegroup filegroup1
alter database test_db
remove filegroup filegroup2
alter database test_db
remove filegroup filegroup3
alter database test_db
remove filegroup filegroup4
alter database test_db
remove filegroup filegroup5

note代码

USE CSI
GO

--Step 1: Create Partition Function
CREATE PARTITION FUNCTION fnPartFunction(DATE)
AS
RANGE LEFT FOR VALUES ('12/31/2016', '12/31/2017', '12/31/2018')
/*
Above Function will create 4 partitions
With LEFT orientation/align
P1 - Data with Dates on or Before 12/31/2016    
P2 - 01/01/2017 - 12/31/2017
P3 - 01/01/2018 - 12/31/2018
P4 - Data with Dates on or After 01/01/2019

With RIGHT orientation/align
P1 - Data with Dates Before 12/31/2016  
P2 - 12/31/2016 - 12/30/2017
P3 - 12/31/2017 - 12/30/2018
P4 - Data with Dates on or After 12/31/2018
*/

--You can create file groups and add partitions to different file groups but it is not a mandatory step. 
You can assign all partitions to 1 file group or PRIMARY file group
ALTER DATABASE CSI 
ADD FILEGROUP FG1
GO
ALTER DATABASE CSI 
ADD FILEGROUP FG2
GO
ALTER DATABASE CSI 
ADD FILEGROUP FG3
GO
ALTER DATABASE CSI 
ADD FILEGROUP FG4
GO

--Creating secondary files for the Database
ALTER DATABASE CSI
ADD FILE
(NAME = FG1_Dat,
FileName = "C:\Partitions\FG1.ndf",
SIZE = 10 MB,
MaxSize = 50 MB,
FileGrowth = 5 MB)
TO FileGroup FG1
GO

ALTER DATABASE CSI
ADD FILE
(NAME = FG2_Dat,
FileName = "C:\Partitions\FG2.ndf",
SIZE = 10 MB,
MaxSize = 50 MB,
FileGrowth = 5 MB)
TO FileGroup FG2
GO

ALTER DATABASE CSI
ADD FILE
(NAME = FG3_Dat,
FileName = "C:\Partitions\FG3.ndf",
SIZE = 10 MB,
MaxSize = 50 MB,
FileGrowth = 5 MB)
TO FileGroup FG3
GO

ALTER DATABASE CSI
ADD FILE
(NAME = FG4_Dat,
FileName = "C:\Partitions\FG4.ndf",
SIZE = 10 MB,
MaxSize = 50 MB,
FileGrowth = 5 MB)
TO FileGroup FG4
GO

--Creating partition scheme based on the function created earlier
CREATE PARTITION SCHEME schPartScheme
AS
PARTITION fnPartFunction TO
(FG1, FG2, FG3, FG4) --Assigning each partition to a different file
GO

--In the following case we are assigning 3 partitions to 1 file and 4th to another file
CREATE PARTITION SCHEME schPartScheme
AS
PARTITION fnPartFunction TO
(FG1, FG1, FG1, FG4)
GO

--In the following case we are assigning all partitions to primary file (.mdf)
CREATE PARTITION SCHEME schPartScheme
AS
PARTITION fnPartFunction ALL TO
([PRIMARY])
GO

DROP TABLE IF EXISTS [tblPartition]
GO

--Creating table on partitions scheme
CREATE TABLE [dbo].[tblPartition](
    [SalesPersonID] [int] NULL,
    [OrderDate] [date] NULL,
    [SalesOrderNumber] [nvarchar](25) NOT NULL,
    [CustomerID] [int] NOT NULL,
    [SubTotal] [money] NOT NULL,
    [TaxAmt] [money] NOT NULL,
    [Freight] [money] NOT NULL,
    [TotalDue] [money] NOT NULL,
    [FirstName] [nvarchar](50) NULL,
    [LastName] [nvarchar](50) NULL
) ON schPartScheme (OrderDate)
GO

DECLARE @Days INT
SELECT @Days = DATEDIFF(DD, MAX(OrderDate), GETDATE())
FROM AdventureWorks2017.Sales.SalesOrderHeader H

INSERT INTO [tblPartition]
SELECT H.SalesPersonID, CAST(DATEADD(DD, @Days, H.OrderDate) AS DATE) OrderDate, 
H.SalesOrderNumber, H.CustomerID, H.SubTotal, H.TaxAmt, H.Freight, H.TotalDue, P.FirstName, P.LastName  
FROM AdventureWorks2017.Sales.SalesOrderHeader H
LEFT JOIN AdventureWorks2017.Person.Person P
ON P.BusinessEntityID = H.SalesPersonID
GO

--Creating index on partition scheme
CREATE [ UNIQUE ] [ CLUSTERED | NONCLUSTERED ] INDEX index_name
    ON <object> ( column [ ASC | DESC ] [ ,...n ] )
    [ INCLUDE ( column_name [ ,...n ] ) ]
    [ WHERE <filter_predicate> ]
    [ WITH ( <relational_index_option> [ ,...n ] ) ]
    [ ON { partition_scheme_name ( column_name )
         | filegroup_name
         | default
         }
    ]
    [ FILESTREAM_ON { filestream_filegroup_name | partition_scheme_name | "NULL" } ]
  
[ ; ]


SELECT * FROM SYS.partition_functions
SELECT * FROM SYS.partition_schemes
SELECT * FROM SYS.partition_parameters
SELECT * FROM SYS.partition_range_values
SELECT * FROM SYS.partitions
WHERE object_id = OBJECT_ID('tblPartition')

--Creating another table without Partitions to compare performance

DROP TABLE IF EXISTS tblNonPartition

DECLARE @Days INT
SELECT @Days = DATEDIFF(DD, MAX(OrderDate), GETDATE())
FROM AdventureWorks2017.Sales.SalesOrderHeader H

SELECT H.SalesPersonID, CAST(DATEADD(DD, @Days, H.OrderDate) AS DATE) OrderDate, 
H.SalesOrderNumber, H.CustomerID, H.SubTotal, H.TaxAmt, H.Freight, H.TotalDue, 
P.FirstName, P.LastName INTO tblNonPartition  
FROM AdventureWorks2017.Sales.SalesOrderHeader H
LEFT JOIN AdventureWorks2017.Person.Person P
ON P.BusinessEntityID = H.SalesPersonID
GO

--Enable actual exec plan and run following 2 queries together to compare the performance of partitions 
and see in first execution plan that Table scan uses only 2 partitions eventhough there are 4 partitions. 
In my execution plan first query took 16% and second query took 84%
SELECT * FROM tblPartition
WHERE OrderDate BETWEEN '5/1/2016' AND '6/15/2017'

SELECT * FROM tblNonPartition
WHERE OrderDate BETWEEN '5/1/2016' AND '6/15/2017'

DROP TABLE IF EXISTS [tblNonPartition]
GO

--Removing Partitioning
DROP TABLE IF EXISTS [tblPartition]
GO

DROP PARTITION SCHEME schPartScheme
GO
--Remove Files related to Partition Table
ALTER DATABASE CSI
REMOVE FILE FG1_dat
GO

ALTER DATABASE CSI
REMOVE FILE FG2_dat
GO

ALTER DATABASE CSI
REMOVE FILE FG3_dat
GO

ALTER DATABASE CSI
REMOVE FILE FG4_dat
GO

--Removing File Groups
ALTER DATABASE CSI
REMOVE FILEGROUP FG1
GO
ALTER DATABASE CSI
REMOVE FILEGROUP FG2
GO
ALTER DATABASE CSI
REMOVE FILEGROUP FG3
GO
ALTER DATABASE CSI
REMOVE FILEGROUP FG4
GO

--Drop partition function
DROP PARTITION FUNCTION fnPartFunction