PARTITION TABLE

PARTITION TABLE

                                                  

                                     You can create a partitioned table or index in SQL Server.The data in partitioned tables or indexes divided into two different data set for given table.Using following T-SQL commands will help you to  partition tables.

USE [master]

GO

ALTER DATABASE [DATABASEUSERGROUP] ADD FILEGROUP [SALES];

GO

ALTER DATABASE [DATABASEUSERGROUP] ADD FILEGROUP [ARCHIVE_OLD];

GO

ALTER DATABASE [DATABASEUSERGROUP] ADD FILEGROUP [ARCHIVE_2002];

GO

ALTER DATABASE [DATABASEUSERGROUP] ADD FILEGROUP [ARCHIVE_2003];

GO

ALTER DATABASE [DATABASEUSERGROUP] ADD FILE ( NAME = N'SALES_01', 

  FILENAME = N'C:\MSSQL\2016\Sales_01.ndf' , 

  SIZE = 1024KB , 

  FILEGROWTH = 1024KB ) TO FILEGROUP [SALES];

GO

ALTER DATABASE [DATABASEUSERGROUP] ADD FILE ( NAME = N'ARCHIVE_OLD', 

  FILENAME = N'C:\MSSQL\2016\Archive_Old_01.ndf' , 

  SIZE = 1024KB , 

  FILEGROWTH = 1024KB ) TO FILEGROUP [ARCHIVE_OLD];

GO

ALTER DATABASE [DATABASEUSERGROUP] ADD FILE ( NAME = N'ARCHIVE_2002', 

  FILENAME = N'C:\MSSQL\2016\Archive_2002_01.ndf' , 

  SIZE = 1024KB , 

  FILEGROWTH = 1024KB ) TO FILEGROUP [ARCHIVE_2002];

GO

ALTER DATABASE [DATABASEUSERGROUP] ADD FILE ( NAME = N'ARCHIVE_2003', 

  FILENAME = N'C:\MSSQL\2016\Archive_2003_01.ndf' , 

  SIZE = 1024KB , 

  FILEGROWTH = 1024KB ) TO FILEGROUP [ARCHIVE_2003];

GO

USE [DATABASEUSERGROUP]

GO

IF NOT EXISTS (SELECT * FROM sys.partition_functions WHERE name = N'pf_year_archive') BEGIN

CREATE PARTITION FUNCTION [pf_year_archive](tinyint) AS RANGE LEFT FOR VALUES (0, 1, 2, 3)

END

GO

IF NOT EXISTS (SELECT * FROM sys.partition_schemes WHERE name = N'ps_year_archive') BEGIN

CREATE PARTITION SCHEME [ps_year_archive] AS PARTITION [pf_year_archive] 

TO ([SALES], [ARCHIVE_OLD], [ARCHIVE_2002], [ARCHIVE_2003], [SALES])

END

GO

ALTER TABLE [DATABASEUSERGROUP].[dbo].[SalesOrderDetail] ADD archive tinyint DEFAULT(0) NOT NULL;

--ALTER TABLE [DATABASEUSERGROUP].[dbo].[SalesOrderDetail] DROP COLUMN archive ;

GO

IF NOT EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(N'[dbo].[SalesOrderDetail]') AND name = N'CIX_SalesOrderDetail_ProductID') BEGIN

CREATE CLUSTERED INDEX [CIX_SalesOrderDetail_ProductID] ON [dbo].[SalesOrderDetail]([ProductID] ASC) ON [ps_year_archive]([archive]);

END

GO

SELECT * FROM [DATABASEUSERGROUP].[dbo].[SalesOrderDetail];

GO

SELECT * FROM sys.dm_db_partition_stats WHERE object_id = OBJECT_ID('dbo.SalesOrderDetail');  

GO  

UPDATE [DATABASEUSERGROUP].[dbo].[SalesOrderDetail] SET archive=1 WHERE YEAR(ModifiedDate)<2002;

GO

SELECT * FROM sys.dm_db_partition_stats WHERE object_id = OBJECT_ID('dbo.SalesOrderDetail');  

GO 

UPDATE [DATABASEUSERGROUP].[dbo].[SalesOrderDetail] SET archive=2 WHERE YEAR(ModifiedDate)=2002;

GO

SELECT * FROM sys.dm_db_partition_stats WHERE object_id = OBJECT_ID('dbo.SalesOrderDetail');  

GO 

UPDATE [DATABASEUSERGROUP].[dbo].[SalesOrderDetail] SET archive=3 WHERE YEAR(ModifiedDate)=2003;

GO

SELECT * FROM sys.dm_db_partition_stats WHERE object_id = OBJECT_ID('dbo.SalesOrderDetail');  

GO 

UPDATE [DATABASEUSERGROUP].[dbo].[SalesOrderDetail] SET archive=4 WHERE SalesOrderID=61184;

GO

Cheers...
and Most welcome your comments and ideas...