DB For Partitioning SQL

USE Master
GO

CREATE DATABASE DemoDBForPartitioning
ON PRIMARY
(NAME='DemoDBForPartitioning_1',
FILENAME=
'C:\PartitionDB\FG1\DemoDBForPartitioning_1.mdf',
SIZE=2,
MAXSIZE=100,
FILEGROWTH=1 ),
FILEGROUP FG2
(NAME = 'DemoDBForPartitioning_2',
FILENAME =
'C:\PartitionDB\FG2\DemoDBForPartitioning_2.ndf',
SIZE = 2,
MAXSIZE=100,
FILEGROWTH=1 ),
FILEGROUP FG3
(NAME = 'DemoDBForPartitioning_3',
FILENAME =
'C:\PartitionDB\FG3\DemoDBForPartitioning_3.ndf',
SIZE = 2,
MAXSIZE=100,
FILEGROWTH=1 )
GO

------------------------------------------------
Use DemoDBForPartitioning
GO
-- Confirm Filegroups
SELECT name as [File Group Name]
FROM sys.filegroups
WHERE type = 'FG'
GO -- Confirm Datafiles
SELECT name as [DB File Name],physical_name as [DB File Path]
FROM sys.database_files
where type_desc = 'ROWS'
GO

--------------------------------------------------------------------------
--Step 1.. Create partition function
Use DemoDBForPartitioning
GO
CREATE PARTITION FUNCTION salesYearPartitions (datetime)
AS RANGE RIGHT FOR VALUES ( '2014-01-01', '2015-01-01')
GO

---------------------------------------------------------------------------

--Step 2.. Create partiton scheme

Use DemoDBForPartitioning
GO
CREATE PARTITION SCHEME Test_PartitionScheme
AS PARTITION salesYearPartitions
TO ([PRIMARY], FG2, FG3 )
GO

-------------------------------------------------------------------------
--Step 3.. Create partiton table

Use DemoDBForPartitioning
GO
CREATE TABLE CountryMaster
(SaleTime datetime PRIMARY KEY,
ItemName varchar(50))
ON Test_PartitionScheme (SaleTime);
GO

-----------------------------------------------

Use DemoDBForPartitioning
GO
INSERT INTO CountryMaster (SaleTime, ItemName)
values
('2009-12-30','val1'),
('2010-01-01','val2'),
('2010-05-24','val3')
GO


select * from CountryMaster

Use DemoDBForPartitioning
GO
select partition_id, index_id, partition_number, Rows
FROM sys.partitions
WHERE OBJECT_NAME(OBJECT_ID)='CountryMaster'
GO


---------------------------------------------------------------------
---------------------------------------------------------------------

--Applying partition in an existing table

CREATE TABLE [dbo].[TABLE1]
([pkcol] [int] NOT NULL,
 [datacol1] [int] NULL,
 [datacol2] [int] NULL,
 [datacol3] [varchar](50) NULL,
 [partitioncol] datetime)
GO
ALTER TABLE dbo.TABLE1 ADD CONSTRAINT PK_TABLE1 PRIMARY KEY CLUSTERED (pkcol)
GO

DECLARE @val INT
SELECT @val=1
WHILE @val < 1000
BEGIN
   INSERT INTO dbo.Table1(pkcol, datacol1, datacol2, datacol3, partitioncol)
      VALUES (@val,@val,@val,'TEST',getdate()-@val)
   SELECT @val=@val+1
END
GO

----------------------------------------------------

CREATE PARTITION FUNCTION myDateRangePF (datetime)
AS RANGE RIGHT FOR VALUES ('2013/01/01', '2014/01/01','2015/01/01')
GO

CREATE PARTITION SCHEME myPartitionScheme
AS PARTITION myDateRangePF ALL TO ([PRIMARY])
GO

SELECT ps.name,pf.name,boundary_id,value
FROM sys.partition_schemes ps
INNER JOIN sys.partition_functions pf ON pf.function_id=ps.function_id
INNER JOIN sys.partition_range_values prf ON pf.function_id=prf.function_id

-----------------------------------------------

ALTER TABLE dbo.TABLE1 DROP CONSTRAINT PK_TABLE1
GO
ALTER TABLE dbo.TABLE1 ADD CONSTRAINT PK_TABLE1 PRIMARY KEY NONCLUSTERED  (pkcol)

CREATE CLUSTERED INDEX IX_TABLE1_partitioncol ON dbo.TABLE1 (partitioncol)
ON myPartitionScheme(partitioncol)
GO

------------------------------------------------------------

DROP TABLE TABLE1
DROP PARTITION SCHEME myPartitionScheme
DROP PARTITION FUNCTION myDateRangePF


---------------------------------------------------------------------