Partition in Sql Server Working

CHECK if any Partition present in our database

SELECT
  o.name objectName,
  i.name as indexname,
  partition_id,
  partition_number, [rows]
from sys.partitions p
INNER JOIN sys.objects o on o.object_id = p.object_id
INNER JOIN sys.indexes i on i.object_id=p.object_id and p.index_id=i.index_id
WHERE  o.name like '%TransactionHistoryArchieve%'

Check File groups already present in our database

• Only single file group is present 
• 

STEP 1: determining the range. What kind of data do we want to specify the range by? Check year wise range

• Year wise records present 

SELECT 
    DISTINCT YEAR(TransactionDate) AS [YEAR], COUNT(*) AS TotalRows
FROM AdventureWorks2012.Production.TransactionHistoryArchive
GROUP BY YEAR(TransactionDate)
ORDER BY 1

STEP 2: CREATE FILE GROUP CONTAINERS

• Now, in a production situation, to get even greater speed when you split a table, If you have them, you should make the file groups on different physical discs.
• For testing purpose lets create for folder with drive names in a  Drive
  • `

STEP 3: Create file group

• Create file group for each drive. right now its not mapping with folders
• CREATE these file group in Master Database

use master
go

alter database AdventureWorks2012
ADD FILEGROUP [Drive W]

alter database AdventureWorks2012
ADD FILEGROUP [Drive X]

alter database AdventureWorks2012
ADD FILEGROUP [Drive Y]

alter database AdventureWorks2012
ADD FILEGROUP [Drive Z]

• Create four file groups for each year, 2011, 2012, 2013, and all rest of the data that is not in the first three partitions.
• Now check properties of database [AdventureWorks2012] > filegroups. We can see 4 additional, file groups or containers that have been created.
  • 

STEP 4: Create data file (.ndf) in each file group and maps each with respective Drives/ Drive folder (in our case)

Create 4 data files, .ndf  files in the file group for each partition'

 

ALTER DATABASE AdventureWorks2012
ADD FILE (
    NAME = N'TRANS2011',
    FILENAME=N'D:\Sql Partition\Drive W\TRANS2011.ndf',
    SIZE = 4096KB,
    FILEGROWTH = 1024KB
)
TO FILEGROUP [Drive W]
GO

 

ALTER DATABASE AdventureWorks2012
ADD FILE (
    NAME = N'TRANS2012',
    FILENAME=N'D:\Sql Partition\Drive X\TRANS2012.ndf',
    SIZE = 4096KB,
    FILEGROWTH = 1024KB
)
TO FILEGROUP [Drive X]
GO

 

ALTER DATABASE AdventureWorks2012
ADD FILE (
    NAME = N'TRANS2013',
    FILENAME=N'D:\Sql Partition\Drive Y\TRANS2013.ndf',
    SIZE = 4096KB,
    FILEGROWTH = 1024KB
)
TO FILEGROUP [Drive Y]
GO

 

ALTER DATABASE AdventureWorks2012
ADD FILE (
    NAME = N'TRANS2014',
    FILENAME=N'D:\Sql Partition\Drive Z\TRANS2014.ndf',
    SIZE = 4096KB,
    FILEGROWTH = 1024KB
)
TO FILEGROUP [Drive Z]
GO 

STEP 5: Cross check files 

• see files in each drive / folder - 
  • 

STEP 6: Segregate Data in respective partitions using Years and create partition

BEGIN TRANSACTION

-- All data related to 2011 will go in one range
-- CRATE DATA PARTION ON BASIS OF RANGE GIVEN
CREATE PARTITION FUNCTION [FUNCTION_TRANSHISTORY](datetime)
AS RANGE LEFT FOR VALUES ('2011-12-31T23:59:59.997', '2012-12-31T23:59:59.997', '2013-12-31T23:59:59.997');

 

-- Take all that specific data now AND DUMP it to these RESPECTIVE FILE GROUPS AND associate it with these file groups.
CREATE PARTITION SCHEME [SCHEMA_TRANSHISTORY]
AS PARTITION [FUNCTION_TRANSHISTORY]
TO ([Drive W],[Drive X],[Drive Y],[Drive Z])

ALTER TABLE
[Production].[TransactionHistoryArchive]
DROP CONSTRAINT [PK_TransactionHistoryArchive_TransactionID]

ALTER TABLE
[Production].[TransactionHistoryArchive]
ADD CONSTRAINT [PK_TransactionHistoryArchive_TransactionID] PRIMARY KEY NONCLUSTERED
(
  TransactionID ASC
) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF,
IGNORE_DUP_KEY = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)

CREATE CLUSTERED INDEX [ClusterIndex_on_SCHEMA_TRANSHISTORY_675922165997540276] ON [Production].[TransactionHistoryArchive]
(
  TransactionDate
) WITH (SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE=OFF) ON [SCHEMA_TRANSHISTORY](TransactionDate)

DROP INDEX [ClusterIndex_on_SCHEMA_TRANSHISTORY_675922165997540276] ON [Production].[TransactionHistoryArchive]

COMMIT TRANSACTION

STEP 7: Verify Partition again

SELECT
   o.name objectName,
   i.name as indexname,
   partition_id,
   partition_number, [rows]
from sys.partitions p
INNER JOIN sys.objects o on o.object_id = p.object_id
INNER JOIN sys.indexes i on i.object_id=p.object_id and p.index_id=i.index_id
WHERE  o.name like '%TransactionHistoryArchive%'

 

SELECT 
    DISTINCT YEAR(TransactionDate) AS [YEAR], COUNT(*) AS TotalRows
FROM AdventureWorks2012.Production.TransactionHistoryArchive
GROUP BY YEAR(TransactionDate)
ORDER BY 1

Results of both are matching

Check Partition tables, Partition function and Partition Schema

Check for Partition Schemes:

SELECT
   name AS partition_scheme_name
FROM sys.partition_schemes;

Check for Partitioned Tables (using a specific partition scheme):

SELECT
   t.name AS table_name,
   i.name AS index_name,
   ps.name AS partition_scheme_name
FROM sys.tables AS t
INNER JOIN sys.indexes AS i ON t.object_id = i.object_id
INNER JOIN sys.partition_schemes AS ps ON i.data_space_id = ps.data_space_id
WHERE ps.name = 'SCHEMA_TRANSHISTORY';

Check for Partition Functions:

SELECT
   name AS partition_function_name
FROM sys.partition_functions;
 

Drop Partition tables, Partition function and Partition Schema
DROP PARTITION SCHEME [SCHEMA_TRANSHISTORY];
DROP PARTITION FUNCTION [FUNCTION_TRANSHISTORY];

Lets check by inserting a new record beyond 2013- entry must reflect in 4th Partition

INSERT INTO [Production].[TransactionHistoryArchive]
          ([TransactionID]
          ,[ProductID]
          ,[ReferenceOrderID]
          ,[ReferenceOrderLineID]
          ,[TransactionDate]
          ,[TransactionType]
          ,[Quantity]
          ,[ActualCost]
          ,[ModifiedDate])
    VALUES
          (89254
          ,1
          ,1
          ,1
          ,'2025-11-30'
          ,'p'
          ,22
          ,2200
          ,'2025-11-30')
GO

Check  Partitioned Data again

SELECT
  o.name objectName,
  i.name as indexname,
  partition_id,
  partition_number, [rows]
from sys.partitions p
INNER JOIN sys.objects o on o.object_id = p.object_id
INNER JOIN sys.indexes i on i.object_id=p.object_id and p.index_id=i.index_id
WHERE  o.name like '%TransactionHistoryArchive%'

Any record after 2013 will got to 4th Partition

See Record of Particular Partition

SELECT *
FROM [Production].[TransactionHistoryArchive]
WHERE $PARTITION.[FUNCTION_TRANSHISTORY]([TRANSACTIONDATE]) = 4;