Monday, August 13, 2018

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... 

No comments:

Post a Comment