partition working 1.sql

USE tempdb
GO

CREATE PARTITION FUNCTION [ThirteenMonthPartFunction](DATETIME) AS 
 RANGE RIGHT FOR VALUES 
    (   N'2015.08.01',
        N'2015.09.01',
        N'2015.10.01',
        N'2015.11.01',
        N'2015.12.01',
        N'2016.01.01',
        N'2016.02.01',
        N'2016.03.01',
        N'2016.04.01',
        N'2016.05.01',
        N'2016.06.01',
        N'2016.07.01',
        N'2016.08.01'
 )
GO

CREATE PARTITION SCHEME [ThirteenMonthPartScheme] 
    AS PARTITION [ThirteenMonthPartFunction] ALL TO ([PRIMARY])
GO

SELECT CAST( N'2015.08.01' AS DATETIME ) Modified
INTO [TableName_Partition2]
--FROM CompassAudit.dbo.[TableName]
--WHERE Modified >= N'2015.08.01'
--  AND Modified < N'2015.09.01'
GO

SELECT CAST( N'2015.08.01' AS DATETIME ) Modified
INTO [TableName]
GO

ALTER TABLE [dbo].[TableName_Partition2] ALTER COLUMN Modified DATETIME NOT NULL
ALTER TABLE [dbo].[TableName] ALTER COLUMN Modified DATETIME NOT NULL
GO

CREATE CLUSTERED INDEX [TableName_Modified]  
    ON [dbo].[TableName_Partition2] ([Modified] ASC)
GO

CREATE CLUSTERED INDEX [TableName]  
    ON [dbo].[TableName] ([Modified] ASC) ON [ThirteenMonthPartScheme]([Modified])
GO

ALTER TABLE [dbo].[TableName_Partition2] 
    ADD CONSTRAINT [CK_TableName_Partition2] 
    CHECK (
		[Modified] >= N'2015.08.01'
   AND  [Modified] < N'2015.09.01' 
	)


-- Validate the constraint's integrity
SELECT OBJECTPROPERTY( OBJECT_ID('CK_TableName_Partition2'), 'CnstIsNotTrusted') CnstIsNotTrusted;
GO

ALTER TABLE [dbo].[TableName_Partition2] 
WITH CHECK CHECK CONSTRAINT [CK_TableName_Partition2] 
GO


truncate table [TableName]
go

--ALTER TABLE SWITCH statement failed. Check constraints or partition function of source table 'tempdb.dbo.TableName_Partition2' 
-- allows values that are not allowed by check constraints or partition function on target table 'tempdb.dbo.TableName'.


ALTER TABLE [TableName_Partition2] 
    SWITCH TO [TableName] PARTITION 2
GO

SELECT * FROM [TableName_Partition2]
SELECT * FROM [TableName]
GO

-- Cleanup
DROP TABLE TableName_Partition2
DROP TABLE TableName
DROP PARTITION scheme [ThirteenMonthPartScheme]
DROP PARTITION FUNCTION [ThirteenMonthPartFunction]
GO