Table Partitioning in SQL Server

ALTER DATABASE YourDatabaseName ADD FILEGROUP FG2019;
ALTER DATABASE YourDatabaseName ADD FILEGROUP FG2020;
ALTER DATABASE YourDatabaseName ADD FILEGROUP FG2021;
ALTER DATABASE YourDatabaseName ADD FILEGROUP FGMAX;

ALTER DATABASE YourDatabaseName 
ADD FILE (
    NAME = Sales2019,
    FILENAME = 'D:\SQLData\Sales2019.ndf',
    SIZE = 5MB,
    MAXSIZE = 100MB,
    FILEGROWTH = 5MB
) TO FILEGROUP FG2019;

ALTER DATABASE YourDatabaseName 
ADD FILE (
    NAME = Sales2020,
    FILENAME = 'D:\SQLData\Sales2020.ndf',
    SIZE = 5MB,
    MAXSIZE = 100MB,
    FILEGROWTH = 5MB
) TO FILEGROUP FG2020;

ALTER DATABASE YourDatabaseName 
ADD FILE (
    NAME = Sales2021,
    FILENAME = 'D:\SQLData\Sales2021.ndf',
    SIZE = 5MB,
    MAXSIZE = 100MB,
    FILEGROWTH = 5MB
) TO FILEGROUP FG2021;

ALTER DATABASE YourDatabaseName 
ADD FILE (
    NAME = SalesMAX,
    FILENAME = 'D:\SQLData\SalesMAX.ndf',
    SIZE = 5MB,
    MAXSIZE = 100MB,
    FILEGROWTH = 5MB
) TO FILEGROUP FGMAX;

CREATE PARTITION FUNCTION pf_SaleYear (INT)
AS RANGE LEFT FOR VALUES (2019, 2020, 2021);

CREATE PARTITION SCHEME ps_SaleYear
AS PARTITION pf_SaleYear
TO (FG2019, FG2020, FG2021, FGMAX);

CREATE TABLE Sales (
    SaleID INT,
    SaleAmount DECIMAL(10,2),
    SaleDate DATE,
    SaleYear AS YEAR(SaleDate) PERSISTED
)
ON ps_SaleYear(SaleYear);

INSERT INTO Sales (SaleID, SaleAmount, SaleDate) VALUES
(1, 5000.00, '2019-05-10'),
(2, 12000.00, '2020-03-22'),
(3, 8000.00, '2021-07-11'),
(4, 9500.00, '2022-11-19');

SELECT *, 
       $PARTITION.pf_SaleYear(SaleYear) AS PartitionNumber
FROM Sales;

 
-- Declare variables for path check
    DECLARE @FilePath NVARCHAR(500) = 'D:\SQLData\';
    DECLARE @PathExists INT;
 
    -- Create a temp table to capture the output
    DECLARE @FileCheck TABLE (
        FileExists INT,
        IsDirectory INT,
        ParentDirExists INT
    );
 
    -- Insert the output from xp_fileexist
    INSERT INTO @FileCheck
    EXEC master.dbo.xp_fileexist @FilePath;
 
    -- Get the IsDirectory value
    SELECT @PathExists = IsDirectory FROM @FileCheck;
 
    -- Validate
    IF @PathExists = 0
    BEGIN
        PRINT 'ERROR: Folder path "' + @FilePath + '" does NOT exist. Please create the directory before running this script.';
        RETURN;
    END
    ELSE
    BEGIN
        PRINT 'Folder path "' + @FilePath + '" exists. Continuing...';
 
 
 
    -------------------------------------------------------
    -- STEP 1: Drop the existing Sales table if it exists
    -------------------------------------------------------
    IF OBJECT_ID('Sales', 'U') IS NOT NULL
    BEGIN
        DROP TABLE Sales;
        PRINT 'Existing "Sales" table dropped.';
    END
 
    -------------------------------------------------------
    -- STEP 2: Create a new Sales table
    -------------------------------------------------------
    CREATE TABLE Sales (
        SaleID INT PRIMARY KEY,
        CustomerName VARCHAR(100),
        ProductName VARCHAR(100),
        Quantity INT,
        Price DECIMAL(10,2),
        SaleDate DATE
    );
    PRINT 'New "Sales" table created.';
 
    -------------------------------------------------------
    -- STEP 3: Insert 4000 dummy records into Sales table
    -------------------------------------------------------
    WITH Numbers AS (
        SELECT TOP 4000 ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS Num
        FROM sys.all_objects a
        CROSS JOIN sys.all_objects b
    )
    INSERT INTO Sales (SaleID, CustomerName, ProductName, Quantity, Price, SaleDate)
    SELECT
        Num,
        'Customer_' + CAST(ABS(CHECKSUM(NEWID())) % 1000 AS VARCHAR),
        'Product_' + CAST(ABS(CHECKSUM(NEWID())) % 500 AS VARCHAR),
        ABS(CHECKSUM(NEWID())) % 10 + 1,
        CAST(ROUND(RAND(CHECKSUM(NEWID())) * 1000 + 100, 2) AS DECIMAL(10,2)),
        DATEADD(DAY, -ABS(CHECKSUM(NEWID())) % 365, GETDATE())
    FROM Numbers;
    PRINT 'Inserted 4000 dummy rows into "Sales".';
 
    -------------------------------------------------------
    -- STEP 4: Create filegroups if they do not exist
    -------------------------------------------------------
    IF NOT EXISTS (SELECT * FROM sys.filegroups WHERE name = 'FG2019')
        ALTER DATABASE Dummy ADD FILEGROUP FG2019;
 
    IF NOT EXISTS (SELECT * FROM sys.filegroups WHERE name = 'FG2020')
        ALTER DATABASE Dummy ADD FILEGROUP FG2020;
 
    IF NOT EXISTS (SELECT * FROM sys.filegroups WHERE name = 'FG2021')
        ALTER DATABASE Dummy ADD FILEGROUP FG2021;
 
    IF NOT EXISTS (SELECT * FROM sys.filegroups WHERE name = 'FGMAX')
        ALTER DATABASE Dummy ADD FILEGROUP FGMAX;
 
    PRINT 'Filegroups created or already exist.';
 
    -------------------------------------------------------
    -- STEP 5: Add physical files to filegroups
    -------------------------------------------------------
-- Add Sales2019.ndf to FG2019
    IF NOT EXISTS (SELECT * FROM sys.master_files WHERE physical_name = @FilePath + 'Sales2019.ndf')
    BEGIN
        DECLARE @SQL2019 NVARCHAR(MAX) =
        'ALTER DATABASE Dummy ADD FILE (
            NAME = Sales2019,
            FILENAME = N''' + @FilePath + 'Sales2019.ndf' + ''',
            SIZE = 5MB,
            MAXSIZE = 100MB,
            FILEGROWTH = 5MB
        ) TO FILEGROUP FG2019;';
     
        EXEC sp_executesql @SQL2019;
        PRINT 'File "Sales2019.ndf" added to FG2019.';
    END
    ELSE
        PRINT 'File "Sales2019.ndf" already exists.';
 
    -- Add Sales2020.ndf to FG2020
    IF NOT EXISTS (SELECT * FROM sys.master_files WHERE physical_name = @FilePath + 'Sales2020.ndf')
    BEGIN
        DECLARE @SQL2020 NVARCHAR(MAX) =
        'ALTER DATABASE Dummy ADD FILE (
            NAME = Sales2020,
            FILENAME = N''' + @FilePath + 'Sales2020.ndf' + ''',
            SIZE = 5MB,
            MAXSIZE = 100MB,
            FILEGROWTH = 5MB
        ) TO FILEGROUP FG2020;';
     
        EXEC sp_executesql @SQL2020;
        PRINT 'File "Sales2020.ndf" added to FG2020.';
    END
    ELSE
        PRINT 'File "Sales2020.ndf" already exists.';
 
    -- Add Sales2021.ndf to FG2021
    IF NOT EXISTS (SELECT * FROM sys.master_files WHERE physical_name = @FilePath + 'Sales2021.ndf')
    BEGIN
        DECLARE @SQL2021 NVARCHAR(MAX) =
        'ALTER DATABASE Dummy ADD FILE (
            NAME = Sales2021,
            FILENAME = N''' + @FilePath + 'Sales2021.ndf' + ''',
            SIZE = 5MB,
            MAXSIZE = 100MB,
            FILEGROWTH = 5MB
        ) TO FILEGROUP FG2021;';
     
        EXEC sp_executesql @SQL2021;
        PRINT 'File "Sales2021.ndf" added to FG2021.';
    END
    ELSE
        PRINT 'File "Sales2021.ndf" already exists.';
 
    -- Add SalesMAX.ndf to FGMAX
    IF NOT EXISTS (SELECT * FROM sys.master_files WHERE physical_name = @FilePath + 'SalesMAX.ndf')
    BEGIN
        DECLARE @SQLMAX NVARCHAR(MAX) =
        'ALTER DATABASE Dummy ADD FILE (
            NAME = SalesMAX,
            FILENAME = N''' + @FilePath + 'SalesMAX.ndf' + ''',
            SIZE = 5MB,
            MAXSIZE = 100MB,
            FILEGROWTH = 5MB
        ) TO FILEGROUP FGMAX;';
     
        EXEC sp_executesql @SQLMAX;
        PRINT 'File "SalesMAX.ndf" added to FGMAX.';
    END
    ELSE
        PRINT 'File "SalesMAX.ndf" already exists.';
 
    -------------------------------------------------------
    -- STEP 6: Drop existing partition scheme and function
    -------------------------------------------------------
    IF EXISTS (SELECT * FROM sys.partition_schemes WHERE name = 'ps_SaleDate')
        DROP PARTITION SCHEME ps_SaleDate;
 
    IF EXISTS (SELECT * FROM sys.partition_functions WHERE name = 'pf_SaleDate')
        DROP PARTITION FUNCTION pf_SaleDate;
 
    PRINT 'Dropped existing partition scheme/function (if any).';
 
    -------------------------------------------------------
    -- STEP 7: Create partition function and scheme
    -------------------------------------------------------
    CREATE PARTITION FUNCTION pf_SaleDate (DATE)
    AS RANGE LEFT FOR VALUES ('2024-12-31', '2025-12-31', '2026-12-31');
 
    CREATE PARTITION SCHEME ps_SaleDate
    AS PARTITION pf_SaleDate
    TO (FG2019, FG2020, FG2021, FGMAX);
 
    PRINT 'Partition function and scheme created.';
 
    -------------------------------------------------------
    -- STEP 8: Create partitioned table
    -------------------------------------------------------
    IF OBJECT_ID('Sales_Partitioned', 'U') IS NOT NULL DROP TABLE Sales_Partitioned;
 
    CREATE TABLE Sales_Partitioned (
        SaleID INT IDENTITY(1,1),
        CustomerName VARCHAR(100),
        ProductName VARCHAR(100),
        Quantity INT,
        Price DECIMAL(10,2),
        SaleDate DATE,
        CONSTRAINT PK_Sales_Partitioned PRIMARY KEY (SaleDate, SaleID)
    )
    ON ps_SaleDate(SaleDate);
 
    PRINT 'Partitioned table "Sales_Partitioned" created.';
 
    -------------------------------------------------------
    -- STEP 9: Migrate data using TRY-CATCH
    -------------------------------------------------------
    BEGIN TRY
        SET IDENTITY_INSERT Sales_Partitioned ON;
 
        INSERT INTO Sales_Partitioned (SaleID, CustomerName, ProductName, Quantity, Price, SaleDate)
        SELECT SaleID, CustomerName, ProductName, Quantity, Price, SaleDate
        FROM Sales;
 
        SET IDENTITY_INSERT Sales_Partitioned OFF;
        PRINT 'Data migrated successfully to partitioned table.';
    END TRY
    BEGIN CATCH
        PRINT 'ERROR during data migration: ' + ERROR_MESSAGE();
        IF IDENT_CURRENT('Sales_Partitioned') IS NOT NULL
            SET IDENTITY_INSERT Sales_Partitioned OFF;
    END CATCH;
 
    -------------------------------------------------------
    -- STEP 10: Rename and clean up
    -------------------------------------------------------
    IF OBJECT_ID('Sales_Old', 'U') IS NOT NULL DROP TABLE Sales_Old;
 
    EXEC sp_rename 'Sales', 'Sales_Old';
    EXEC sp_rename 'Sales_Partitioned', 'Sales';
    DROP TABLE Sales_Old;
 
    PRINT 'Partitioned table renamed to "Sales".';
 
    -------------------------------------------------------
    -- STEP 11: Validate Partitioning
    -------------------------------------------------------
    SELECT 
        $PARTITION.pf_SaleDate(SaleDate) AS PartitionNumber,
        COUNT(*) AS RecordCount
    FROM Sales
    GROUP BY $PARTITION.pf_SaleDate(SaleDate)
    ORDER BY PartitionNumber;
 
    PRINT 'Partition validation completed. See result above.';
END;

SELECT 
    name AS FileGroupName,
    type_desc AS Type,
    is_default,
    is_read_only
FROM sys.filegroups
WHERE name IN ('FG2019', 'FG2020', 'FG2021', 'FGMAX');

SELECT 
    df.name AS LogicalName,
    df.physical_name AS PhysicalPath,
    df.size / 128 AS SizeMB,
    fg.name AS FileGroupName
FROM sys.database_files df
JOIN sys.filegroups fg ON df.data_space_id = fg.data_space_id
WHERE fg.name IN ('FG2019', 'FG2020', 'FG2021', 'FGMAX');

SELECT 
    pf.name AS PartitionFunction,
    prv.value AS BoundaryValue
FROM sys.partition_functions pf
JOIN sys.partition_range_values prv ON pf.function_id = prv.function_id
WHERE pf.name = 'pf_SaleDate'
ORDER BY BoundaryValue;

SELECT 
    ps.name AS PartitionScheme,
    fg.name AS FileGroup,
    psf.boundary_id AS PartitionOrder
FROM sys.partition_schemes ps
JOIN sys.destination_data_spaces dds ON ps.data_space_id = dds.partition_scheme_id
JOIN sys.filegroups fg ON dds.data_space_id = fg.data_space_id
JOIN sys.partition_functions pf ON ps.function_id = pf.function_id
JOIN sys.partition_range_values psf ON pf.function_id = psf.function_id
WHERE ps.name = 'ps_SaleDate'
ORDER BY PartitionOrder;

SELECT 
    OBJECT_NAME(p.object_id) AS TableName,
    ps.name AS PartitionScheme,
    pf.name AS PartitionFunction,
    COUNT(*) AS NumberOfPartitions
FROM sys.partitions p
JOIN sys.indexes i ON p.object_id = i.object_id AND p.index_id = i.index_id
JOIN sys.partition_schemes ps ON i.data_space_id = ps.data_space_id
JOIN sys.partition_functions pf ON ps.function_id = pf.function_id
WHERE OBJECT_NAME(p.object_id) = 'Sales'
AND i.index_id <= 1
GROUP BY p.object_id, ps.name, pf.name;

SELECT 
    $PARTITION.pf_SaleDate(SaleDate) AS PartitionNumber,
    COUNT(*) AS RecordCount
FROM Sales
GROUP BY $PARTITION.pf_SaleDate(SaleDate)
ORDER BY PartitionNumber;

SELECT 
    i.name AS IndexName,
    i.type_desc,
    i.is_primary_key,
    i.data_space_id,
    ds.name AS DataSpace,
    ps.name AS PartitionScheme
FROM sys.indexes i
JOIN sys.data_spaces ds ON i.data_space_id = ds.data_space_id
LEFT JOIN sys.partition_schemes ps ON ds.data_space_id = ps.data_space_id
WHERE OBJECT_NAME(i.object_id) = 'Sales';

SELECT 
    OBJECT_NAME(p.object_id) AS TableName,
    p.partition_number,
    COUNT(*) AS RowsInPartition
FROM sys.partitions p
JOIN sys.tables t ON p.object_id = t.object_id
WHERE t.name = 'Sales' AND p.index_id IN (0, 1) -- 0: heap, 1: clustered
GROUP BY p.object_id, p.partition_number
ORDER BY p.partition_number;

-- Drop table if exists
IF OBJECT_ID('ADTDB_PartitionTables') IS NOT NULL 
	DROP TABLE ADTDB_PartitionTables;
GO

-- Recreate table with additional columns
CREATE TABLE ADTDB_PartitionTables (
    ID INT IDENTITY(1,1),
    TableName VARCHAR(100) NOT NULL,
    PartitionColumn VARCHAR(100) NOT NULL,
    PrimaryKeyColumn VARCHAR(200) NOT NULL,
    DefaultValue VARCHAR(100),
	PartitionFunction VARCHAR(25),
	PartitionSchema VARCHAR(25),
	PartitionType INT,  --1-Date Wise, 2-Year
	TableCreateQuery NVARCHAR(MAX),
	BackupTableName VARCHAR(150),
	ColumnNames NVARCHAR(1500),
	NonClusteredIndexScript NVARCHAR(MAX),
	ActiveFlag BIT DEFAULT 1,
	OldBoundaryValues NVARCHAR(10),
    BoundaryValues NVARCHAR(10)
);

DECLARE @ColumnList NVARCHAR(MAX)=''

-- Insert initial table info
INSERT INTO ADTDB_PartitionTables (TableName, PartitionColumn, PrimaryKeyColumn, DefaultValue, PartitionFunction, PartitionSchema,PartitionType,BoundaryValues)
VALUES 
 ('FactInternetSales', 'OrderDate', 'ProductKey', '', 'pf_Date','ps_Date',1,  CONVERT(varchar(10), DATEADD(DAY, -365, GETDATE()), 120) )
,('FactFinance', 'Year', 'FinanceKey', '', 'pf_Year','ps_Year',2,'2024');


;WITH BoundaryCTE AS (
    SELECT 
        pf.name AS PartitionFunction,
        STUFF((
            SELECT ', ' + CONVERT(varchar,value,103)
            FROM sys.partition_range_values prv
            WHERE prv.function_id = pf.function_id
            ORDER BY boundary_id
            FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)')
        , 1, 2, '') AS BoundaryValues
    FROM sys.partition_functions pf
    WHERE pf.name IN (SELECT DISTINCT PartitionFunction FROM ADTDB_PartitionTables)
)
UPDATE PT
SET PT.OldBoundaryValues = B.BoundaryValues
FROM ADTDB_PartitionTables PT
JOIN BoundaryCTE B ON PT.PartitionFunction = B.PartitionFunction;


GO

UPDATE Info 
SET ColumnNames = A.CommaSeperate
FROM ADTDB_PartitionTables Info
CROSS APPLY (
	SELECT STUFF((
		SELECT ', ' + QUOTENAME(c.name)
		FROM sys.columns c
		JOIN sys.tables t ON c.object_id = t.object_id
		JOIN sys.schemas s ON t.schema_id = s.schema_id
		WHERE t.name = Info.TableName
		ORDER BY c.column_id
		FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)')
	, 1, 2, '') AS CommaSeperate
) A


-- Add default value from system defaults
;WITH CTE_DefaultValue AS
(
	SELECT 
		t.name AS TableName,
		c.name AS PartitionColumn,
		d.definition AS DefaultValue
	FROM 
		sys.tables t
	JOIN 
		sys.columns c ON t.object_id = c.object_id
	LEFT JOIN 
		sys.default_constraints d ON c.default_object_id = d.object_id
	WHERE 
		t.name IN (
			'TAMAttendanceDetails',
			'TimeEntry',
			'TimeSheet',
			'EmployeeLeaves',
			'LeaveDates',
			'SpecialEntry',
			'ApprovedOT'
		)
		AND c.name IN (
			'TAMDate', 'InTime', 'SheetDate', 'FromDate', 'LeaveDate', 'OTDate'
		)
)
UPDATE PTI
SET PTI.DefaultValue = DV.DefaultValue
FROM ADTDB_PartitionTables PTI
JOIN CTE_DefaultValue DV 
    ON PTI.TableName = DV.TableName 
   AND PTI.PartitionColumn = DV.PartitionColumn;

-- Update backup-related columns
UPDATE ADTDB_PartitionTables
SET 
	BackupTableName = TableName + '_Backup'

-- Generate CREATE TABLE script with partition schema
DECLARE 
    @TableName SYSNAME,
    @PartitionColumn SYSNAME,
    @PrimaryKeyColumn NVARCHAR(200),
    @CreateScript NVARCHAR(MAX),
    @ColName SYSNAME,
    @DataType NVARCHAR(100),
    @Nullable BIT,
    @IsIdentity BIT,
    @Precision INT,
    @Scale INT,
    @MaxLength INT,
    @DefaultDefinition NVARCHAR(MAX),
    @PartitionSchema SYSNAME,
    @Counter INT = 1,
    @Total INT;

SELECT @Total = COUNT(*) FROM ADTDB_PartitionTables;

WHILE @Counter <= @Total
BEGIN
    SELECT 
        @TableName = TableName, 
        @PartitionColumn = PartitionColumn,
        @PrimaryKeyColumn = PrimaryKeyColumn,
        @PartitionSchema = PartitionSchema
    FROM ADTDB_PartitionTables 
    WHERE ID = @Counter;

    SET @CreateScript = 'CREATE TABLE  (' + CHAR(13);

    DECLARE column_cursor CURSOR FOR
    SELECT 
        c.name, 
        TYPE_NAME(c.user_type_id), 
        c.is_nullable,
        c.is_identity,
        c.precision,
        c.scale,
        c.max_length,
        dc.definition
    FROM 
        sys.columns c
    LEFT JOIN 
        sys.default_constraints dc ON c.default_object_id = dc.object_id
    WHERE 
        c.object_id = OBJECT_ID(@TableName);

    OPEN column_cursor;
    FETCH NEXT FROM column_cursor INTO @ColName, @DataType, @Nullable, @IsIdentity, @Precision, @Scale, @MaxLength, @DefaultDefinition;

    WHILE @@FETCH_STATUS = 0
    BEGIN
        SET @CreateScript += '     ' + @DataType;

        IF @DataType IN ('varchar', 'nvarchar', 'char', 'nchar')
        BEGIN
            IF @MaxLength = -1
                SET @CreateScript += '(MAX)'
            ELSE
                SET @CreateScript += '(' + CAST(@MaxLength AS VARCHAR) + ')'
        END
        ELSE IF @DataType IN ('decimal', 'numeric')
        BEGIN
            SET @CreateScript += '(' + CAST(@Precision AS VARCHAR) + ',' + CAST(@Scale AS VARCHAR) + ')'
        END

        IF @IsIdentity = 1
            SET @CreateScript += ' IDENTITY(1,1)'

        IF @ColName = @PartitionColumn OR @Nullable = 0
            SET @CreateScript += ' NOT NULL'
        ELSE
            SET @CreateScript += ' NULL'

        IF @DefaultDefinition IS NOT NULL
            SET @CreateScript += ' DEFAULT ' + LTRIM(RTRIM(@DefaultDefinition))

        SET @CreateScript += ',' + CHAR(13)

        FETCH NEXT FROM column_cursor INTO @ColName, @DataType, @Nullable, @IsIdentity, @Precision, @Scale, @MaxLength, @DefaultDefinition;
    END

    CLOSE column_cursor;
    DEALLOCATE column_cursor;

    SET @CreateScript = LEFT(@CreateScript, LEN(@CreateScript) - 2) + CHAR(13);
    SET @CreateScript += '    ,CONSTRAINT PK_' + @TableName + ' PRIMARY KEY CLUSTERED ('+@PartitionColumn +','+ @PrimaryKeyColumn + ')' + CHAR(13);

    IF @PartitionSchema IS NOT NULL AND LTRIM(RTRIM(@PartitionSchema)) <> ''
        SET @CreateScript += ') ON ();' + CHAR(13);
    ELSE
        SET @CreateScript += ');' + CHAR(13);

    UPDATE ADTDB_PartitionTables
    SET TableCreateQuery = @CreateScript
    WHERE TableName = @TableName;

    SET @Counter += 1;
END;

DECLARE 
    @PartitionScheme SYSNAME,
    @IndexScript NVARCHAR(MAX),
    @IndexName SYSNAME,
    @IncludeCols NVARCHAR(MAX),
    @IndexCols NVARCHAR(MAX),
    @is_unique BIT,
    @BoundaryValues VARCHAR(50)


	SET @TableName = ''
	SET @PartitionColumn = ''
	SET @Counter = 1

-- Total rows to loop
SELECT @Total = COUNT(*) FROM ADTDB_PartitionTables;

-- Loop over all entries
WHILE @Counter <= @Total
BEGIN
    -- Fetch table metadata
    SELECT 
        @TableName = TableName, 
        @PartitionColumn = PartitionColumn, 
        @PartitionScheme = PartitionSchema,
        @BoundaryValues = BoundaryValues
    FROM ADTDB_PartitionTables 
    WHERE ID = @Counter;

    SET @IndexScript = '';

    -- Fetch all non-clustered indexes for the table
    DECLARE index_cursor CURSOR FOR
    SELECT i.name, i.is_unique
    FROM sys.indexes i
    INNER JOIN sys.tables t ON i.object_id = t.object_id
    WHERE i.type_desc = 'NONCLUSTERED'
        AND i.is_primary_key = 0
        AND i.name IS NOT NULL
        AND t.name = @TableName;

    OPEN index_cursor;
    FETCH NEXT FROM index_cursor INTO @IndexName, @is_unique;

    WHILE @@FETCH_STATUS = 0
    BEGIN
        -- Get index key columns
        SELECT @IndexCols = STUFF((
            SELECT ', ' + QUOTENAME(c.name)
            FROM sys.index_columns ic
            JOIN sys.columns c 
              ON ic.object_id = c.object_id AND ic.column_id = c.column_id
            WHERE ic.object_id = OBJECT_ID(@TableName)
              AND ic.index_id = (SELECT index_id FROM sys.indexes WHERE name = @IndexName AND object_id = OBJECT_ID(@TableName))
              AND ic.is_included_column = 0
            ORDER BY ic.key_ordinal
            FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)'), 1, 2, '');

        -- Get included columns
        SELECT @IncludeCols = STUFF((
            SELECT ', ' + QUOTENAME(c.name)
            FROM sys.index_columns ic
            JOIN sys.columns c 
              ON ic.object_id = c.object_id AND ic.column_id = c.column_id
            WHERE ic.object_id = OBJECT_ID(@TableName)
              AND ic.index_id = (SELECT index_id FROM sys.indexes WHERE name = @IndexName AND object_id = OBJECT_ID(@TableName))
              AND ic.is_included_column = 1
            FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)'), 1, 2, '');

        -- Prepare WHERE clause for filtered index
        DECLARE @WhereClause NVARCHAR(MAX) = '';

        IF @BoundaryValues IS NOT NULL AND LTRIM(RTRIM(@BoundaryValues)) <> ''
        BEGIN
            SET @WhereClause = ' WHERE  < ' +
			CASE 
				WHEN ISNUMERIC(@BoundaryValues) = 1 
					THEN @BoundaryValues
				ELSE '''' + @BoundaryValues + ''''
			END;
        END

        -- Build full CREATE INDEX statement
        SET @IndexScript += 'CREATE ' + 
            CASE WHEN @is_unique = 1 THEN 'UNIQUE ' ELSE '' END +
            'NONCLUSTERED INDEX  ON  (' + @IndexCols + ')';

        IF @IncludeCols IS NOT NULL AND LEN(@IncludeCols) > 0
            SET @IndexScript += ' INCLUDE (' + @IncludeCols + ')';

        IF LEN(@WhereClause) > 0
            SET @IndexScript += @WhereClause;

        IF @PartitionScheme IS NOT NULL AND LTRIM(RTRIM(@PartitionScheme)) <> ''
            SET @IndexScript += ' ON ();' + CHAR(13);
        ELSE
            SET @IndexScript += ';' + CHAR(13);

        FETCH NEXT FROM index_cursor INTO @IndexName, @is_unique;
    END

    CLOSE index_cursor;
    DEALLOCATE index_cursor;

    -- Update NonClusteredIndexScript column
    UPDATE ADTDB_PartitionTables
    SET NonClusteredIndexScript = @IndexScript
    WHERE TableName = @TableName;

    SET @Counter += 1;
END;

IF OBJECT_ID('dbusp_PartitionTables','P') IS NOT NULL
BEGIN
	DROP PROC dbusp_PartitionTables
END 

GO

CREATE PROCEDURE dbusp_PartitionTables
	@FileGroupRecent_Path   VARCHAR(1000) = 'D:\SQLData\',  
	@FileGroupPast_Path VARCHAR(1000) = 'D:\SQLData1\',  
	@IsDefaultPathUsed BIT = 0,
    @InitialSizeinMB INT = 4,
    @FileGrowthinMB INT = 1
AS
BEGIN
    SET NOCOUNT ON;

    BEGIN TRY

	 -- Decide path
		DECLARE @PrimaryPath VARCHAR(1000);
		IF @IsDefaultPathUsed = 1
		BEGIN
			-- Get PRIMARY FG ka path
			SELECT TOP 1 @PrimaryPath = physical_name
			FROM sys.master_files
			WHERE database_id = DB_ID()
			  AND file_id = 1; -- Primary data file

			-- Remove file name to keep only folder
			SET @PrimaryPath = LEFT(@PrimaryPath, LEN(@PrimaryPath) - CHARINDEX('\', REVERSE(@PrimaryPath)) + 1);

			SET @FileGroupRecent_Path = @PrimaryPath;
			SET @FileGroupPast_Path = @PrimaryPath;
		END
		
	    
		DECLARE @DatabaseName VARCHAR(100) = ''
		SET @DatabaseName = DB_NAME()

		DECLARE @FileGroupName_Recent VARCHAR(100) = ''
		DECLARE @FileGroupName_Past VARCHAR(100) = ''
		DECLARE @LogicalFileName1 VARCHAR(100) = '' --old data above 365 data in it file (Partition 1)
        DECLARE @LogicalFileName2 VARCHAR(100) = ''   --Last 365 Days data in it file (Partition 2)
		
		SET @FileGroupName_Past = 'FG_'+DB_NAME()+'_Past'
		SET @LogicalFileName1 = DB_NAME()+'_Past'

		SET @FileGroupName_Recent = 'FG_'+DB_NAME()+'_Recent'
		SET @LogicalFileName2 = DB_NAME()+'_Recent'



        DECLARE @PathExists INT, @PartitionCreationSuccess BIT = 0;
        DECLARE @SQL NVARCHAR(MAX);

  -- Path validation only if @IsDefaultPathUsed = 0
		IF @IsDefaultPathUsed = 0
		BEGIN
	
			DECLARE @PathCheck TABLE (FileExists INT, IsDirectory INT, ParentDirExists INT);

			-- Check Past Path
			DELETE FROM @PathCheck;
			INSERT INTO @PathCheck EXEC master.dbo.xp_fileexist @FileGroupPast_Path;
			IF (SELECT IsDirectory FROM @PathCheck) = 0
			BEGIN
				PRINT 'ERROR: Folder path "' + @FileGroupPast_Path + '" does NOT exist. Please create the directory.';
				GOTO ExitHandler;
			END
				

			-- Check Recent Path
			DELETE FROM @PathCheck;
			INSERT INTO @PathCheck EXEC master.dbo.xp_fileexist @FileGroupRecent_Path;
			IF (SELECT IsDirectory FROM @PathCheck) = 0
			BEGIN
				PRINT 'ERROR: Folder path "' + @FileGroupRecent_Path + '" does NOT exist. Please create the directory.';
				GOTO ExitHandler;
			END

		END


        BEGIN 
            -- Step 2: Add filegroup if not exists
            IF NOT EXISTS (SELECT * FROM sys.filegroups WHERE name = @FileGroupName_Past)
            BEGIN
                SET @SQL = 'ALTER DATABASE  ADD FILEGROUP ;';
                EXEC sp_executesql @SQL;
            END

			IF NOT EXISTS (SELECT * FROM sys.filegroups WHERE name = @FileGroupName_Recent)
            BEGIN
                SET @SQL = 'ALTER DATABASE  ADD FILEGROUP ;';
                EXEC sp_executesql @SQL;
            END


			DECLARE @DropBackup NVARCHAR(MAX)='';
			DECLARE @CreateBackup NVARCHAR(MAX)='';
			DECLARE @DropOriginalTable NVARCHAR(MAX)='';
			DECLARE @RecoveryQuery NVARCHAR(MAX)=''
			DECLARE @CreateTable NVARCHAR(MAX)=''
			DECLARE @InsertQuery NVARCHAR(MAX)=''
			DECLARE @AddConstrant NVARCHAR(MAX)=''
			DECLARE @PartitionFunction VARCHAR(25)=''
			DECLARE @PartitionSchema VARCHAR(25)=''
			DECLARE @PartitionFunctionSQL NVARCHAR(MAX)=''
			DECLARE @PartitionSchemaSQL NVARCHAR(MAX)=''
			DECLARE @DropPartitionSchema NVARCHAR(MAX) =''
			DECLARE @DropPartitionFunction NVARCHAR(MAX) =''

			
							
			SELECT 
				 @DropBackup += 'IF OBJECT_ID(''' + Info.TableName + '_Backup'') IS NOT NULL DROP TABLE ' + TableName + '_Backup; '+ CHAR(13) 
				,@CreateBackup +='SELECT * INTO ' + TableName + '_Backup FROM ' + TableName + '; ' + CHAR(13) 
				,@DropOriginalTable +='IF OBJECT_ID(''' + TableName + ''') IS NOT NULL DROP TABLE ' + TableName + '; '+ CHAR(13) 
				,@RecoveryQuery += ' IF OBJECT_ID('+Info.TableName+') IS NULL AND OBJECT_ID('+Info.BackupTableName+') IS NOT NULL
									BEGIN
										 SELECT * INTO '+Info.TableName+' FROM '+Info.BackupTableName+'; 
									END; '+ CHAR(13) 
				,@CreateTable += Info.TableCreateQuery
				,@InsertQuery += 	'SET IDENTITY_INSERT '+Info.TableName+' ON;
									INSERT INTO '+Info.TableName+' (
										'+Info.ColumnNames+'
									)
									SELECT * FROM '+Info.BackupTableName+'; 
									SET IDENTITY_INSERT '+Info.TableName+' OFF; '+ CHAR(13) 
			
		
			FROM ADTDB_PartitionTables Info
			WHERE Info.ActiveFlag=1
			ORDER BY Info.ID

		

			;WITH CTE AS 
			(
				SELECT DISTINCT  
					N'CREATE PARTITION FUNCTION (' +
						CASE 
							WHEN info.PartitionType = 1 THEN 'smalldatetime'
							WHEN info.PartitionType = 2 THEN 'int'
						END + 
					N')
					AS RANGE RIGHT FOR VALUES (' +
						CASE 
							WHEN info.PartitionType = 1 THEN 
								'''' + CONVERT(varchar(10), DATEADD(DAY, -365, GETDATE()), 120) + ''''  -- date as 'YYYY-MM-DD'
							WHEN info.PartitionType = 2 THEN 
								CAST(YEAR(GETDATE()) - 1 AS VARCHAR(10))  -- year as integer, no quotes
						END + 
					N'); ' + CHAR(13) AS PartitionFunctionSQL
				FROM ADTDB_PartitionTables Info
				WHERE Info.ActiveFlag = 1
			)
			SELECT @PartitionFunctionSQL += PartitionFunctionSQL FROM CTE;


--------------------------------
		;WITH CTE AS 
			(
				SELECT DISTINCT 
					-- Create partition scheme
					N'CREATE PARTITION SCHEME 
					 AS PARTITION  TO ('+@FileGroupName_Past+', '+@FileGroupName_Recent+');' + CHAR(13) AS PartitionSchemaSQL,

					-- Drop partition function if exists
					N'IF EXISTS (SELECT * FROM sys.partition_functions WHERE name = ''' + info.PartitionFunction + ''')
					BEGIN
						DROP PARTITION FUNCTION ;
					END;' + CHAR(13) AS DropPartitionQuery,

					-- Drop partition scheme if exists
					N'IF EXISTS (SELECT * FROM sys.partition_schemes WHERE name = ''' + info.PartitionSchema + ''')
					BEGIN
						DROP PARTITION SCHEME ;
					END;' + CHAR(13) AS DropPartitionSchema

				FROM ADTDB_PartitionTables Info
				WHERE Info.ActiveFlag = 1
			)
			SELECT 
				@PartitionSchemaSQL += PartitionSchemaSQL,
				@DropPartitionFunction += DropPartitionQuery,
				@DropPartitionSchema += DropPartitionSchema
			FROM CTE;



			SELECT 
				@AddConstrant += Info.NonClusteredIndexScript + CHAR(13) 
		
			FROM ADTDB_PartitionTables Info
			WHERE Info.NonClusteredIndexScript<>''
			ORDER BY Info.ID
		
--------------------------------------------------

			BEGIN TRY
				BEGIN TRAN

				-- Step 1: Drop the Backup tables
			    EXEC sp_executesql @DropBackup
		
				-- Step 2: Craete the Backup tables
				EXEC sp_executesql @CreateBackup

				-- Step 3: Drop the original tables
				EXEC sp_executesql @DropOriginalTable

				COMMIT;

				PRINT 'Backup and drop completed successfully.';
			END TRY
			BEGIN CATCH
				IF @@TRANCOUNT > 0
					ROLLBACK;

				PRINT 'Error occurred during backup/drop process.';
				PRINT ERROR_MESSAGE();

				-- Attempt recovery from backups (only if they were created)
				EXEC sp_executesql @RecoveryQuery

				GOTO ExitHandler;
			END CATCH

--------------------------------------------------
		  
            -- Step 3: Handle file removal or add (LogicalFileName1)
            IF EXISTS (SELECT 1 FROM sys.database_files WHERE name = @LogicalFileName1)
            BEGIN
                SET @SQL = 'ALTER DATABASE  REMOVE FILE ;';
                EXEC sp_executesql @SQL;
                PRINT 'File ' + @LogicalFileName1 + ' removed.';
            END

		   SET @SQL = 'ALTER DATABASE  ADD FILE ( NAME =''' + @LogicalFileName1 + '''' + 
                ', FILENAME = ''' + @FileGroupPast_Path + @LogicalFileName1 + '.ndf''' + 
                ', SIZE = ' + CONVERT(VARCHAR(10),@InitialSizeinMB) + 'MB' +
                ', FILEGROWTH = ' + CONVERT(VARCHAR(10),@FileGrowthinMB) + 'MB' +
                ' ) TO FILEGROUP ;';
            EXEC sp_executesql @SQL;
		

            -- Step 4: Handle file removal or add (LogicalFileName2)
            IF EXISTS (SELECT 1 FROM sys.database_files WHERE name = @LogicalFileName2)
            BEGIN
                SET @SQL = 'ALTER DATABASE  REMOVE FILE ;';
                EXEC sp_executesql @SQL;
                PRINT 'File ' + @LogicalFileName2 + ' removed.';
            END

			

            SET @SQL = 'ALTER DATABASE  ADD FILE ( NAME =''' + @LogicalFileName2 + '''' + 
                ', FILENAME = ''' + @FileGroupRecent_Path + @LogicalFileName2 + '.ndf''' + 
                ', SIZE = ' + CONVERT(VARCHAR(10),@InitialSizeinMB) + 'MB' +
                ', FILEGROWTH = ' + CONVERT(VARCHAR(10),@FileGrowthinMB) + 'MB' +
                ' ) TO FILEGROUP ;';
            EXEC sp_executesql @SQL;

			
        -- Step 5: Drop old partition scheme and function if exist
			BEGIN TRY
				EXEC sp_executesql @DropPartitionSchema;
				EXEC sp_executesql @DropPartitionFunction;
				PRINT 'Partition function and scheme Droped.';
			END TRY
			BEGIN CATCH
				PRINT 'Failed to create partition function or scheme: ' + ERROR_MESSAGE();

				EXEC sp_executesql @RecoveryQuery;
				GOTO ExitHandler;
			END CATCH


            -- Step 6: Create new partition function and scheme
			BEGIN TRY
				
				EXEC sp_executesql @PartitionFunctionSQL;
				EXEC sp_executesql @PartitionSchemaSQL;

				PRINT 'Partition function and scheme created.';
			END TRY
			BEGIN CATCH
				PRINT 'Failed to create partition function or scheme: ' + ERROR_MESSAGE();

				EXEC sp_executesql @RecoveryQuery;
				GOTO ExitHandler;
			END CATCH


          BEGIN TRY
			BEGIN TRAN

				EXEC sp_executesql @CreateTable

				EXEC sp_executesql @InsertQuery

				SET @PartitionCreationSuccess = 1;

				print 'Partitioned tables create success'
			
			COMMIT 
			END TRY
            BEGIN CATCH
				IF @@TRANCOUNT > 0
				BEGIN
					  ROLLBACK TRANSACTION;
				END;
				

                EXEC sp_executesql @RecoveryQuery

				PRINT 'Partitioned tables create fails ' + ERROR_MESSAGE();

                GOTO ExitHandler;
            END CATCH


			IF @PartitionCreationSuccess=1
			BEGIN
			   -- PRINT @AddConstrant
				;WITH BoundaryCTE AS (
						SELECT 
							pf.name AS PartitionFunction,
							STUFF((
							    SELECT ', ' + CONVERT(varchar(10),value,120)
								FROM sys.partition_range_values prv
								WHERE prv.function_id = pf.function_id
								ORDER BY boundary_id
								FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)')
							, 1, 2, '') AS BoundaryValues
						FROM sys.partition_functions pf
						WHERE pf.name IN (SELECT DISTINCT PartitionFunction FROM ADTDB_PartitionTables)
					)
					UPDATE PT
					SET PT.OldBoundaryValues = B.BoundaryValues
					FROM ADTDB_PartitionTables PT
					JOIN BoundaryCTE B ON PT.PartitionFunction = B.PartitionFunction;


				EXEC sp_executesql @AddConstrant
				EXEC sp_executesql @DropBackup
			END
           
           
        END

		
ExitHandler:

    END TRY
    BEGIN CATCH
        IF @@TRANCOUNT > 0
            ROLLBACK TRANSACTION;
	
        EXECUTE usp_ErrorDB_AddNew;
        SELECT 3, 'Error in saving data';
        SELECT ERROR_MESSAGE();

		EXEC sp_executesql @RecoveryQuery
    END CATCH

END
GO


EXEC  dbusp_PartitionTables


GO


--SELECT 
--    $PARTITION.pf_Date(TAMDate) AS PartitionNumber,
--	COUNT(*) as TotalRow
--FROM TAMAttendanceDetails
--GROUP BY  $PARTITION.pf_Date(TAMDate)

--SELECT 
--    $PARTITION.pf_Date(SheetDate) AS PartitionNumber,
--	COUNT(*) as TotalRow
--FROM TimeSheet
--GROUP BY  $PARTITION.pf_Date(SheetDate)

--SELECT 
--    $PARTITION.pf_Date(InTime) AS PartitionNumber,
--	COUNT(*) as TotalRow
--FROM TimeEntry
--GROUP BY  $PARTITION.pf_Date(InTime)

--SELECT 
--    $PARTITION.pf_Date(LeaveDate) AS PartitionNumber,
--	COUNT(*) as TotalRow
--FROM LeaveDates
--GROUP BY  $PARTITION.pf_Date(LeaveDate)

--SELECT 
--    $PARTITION.pf_Date(FromDate) AS PartitionNumber,
--	COUNT(*) as TotalRow
--FROM EmployeeLeaves
--GROUP BY  $PARTITION.pf_Date(FromDate)

--SELECT 
--    $PARTITION.pf_Date(FromDate) AS PartitionNumber,
--	COUNT(*) as TotalRow
--FROM SpecialEntry
--GROUP BY  $PARTITION.pf_Date(FromDate)


--SELECT 
--    $PARTITION.pf_Date(ApproveDate) AS PartitionNumber,
--	COUNT(*) as TotalRow
--FROM ApprovedOT
--GROUP BY  $PARTITION.pf_Date(ApproveDate)


--SELECT 
--    $PARTITION.pf_Year(Year) AS PartitionNumber,
--	COUNT(*) as TotalRow
--FROM LeaveTrans
--GROUP BY  $PARTITION.pf_Year(Year) 


--SELECT  
--    $PARTITION.pf_Year(lt.Year) AS PartitionNumber,
--    lt.Year,
--    fg.name AS FileGroupName,
--    f.name AS FileName,
--    SUM(ps.row_count) AS TotalRows
--FROM LeaveTrans lt
--JOIN sys.partitions p
--    ON p.object_id = OBJECT_ID('LeaveTrans') 
--    AND p.partition_number = $PARTITION.pf_Year(lt.Year)
--JOIN sys.allocation_units au 
--    ON p.hobt_id = au.container_id
--JOIN sys.filegroups fg 
--    ON au.data_space_id = fg.data_space_id
--JOIN sys.dm_db_partition_stats ps
--    ON p.partition_id = ps.partition_id
--JOIN sys.indexes i
--    ON p.object_id = i.object_id 
--    AND p.index_id = i.index_id
--JOIN sys.database_files f
--    ON fg.data_space_id = f.data_space_id
--GROUP BY 
--    $PARTITION.pf_Year(lt.Year),
--    lt.Year,
--    fg.name,
--    f.name
--ORDER BY PartitionNumber, lt.Year;