Remove Table Partition. --Remove table partition completely -- Drop PK or CI DROP INDEX Cx_tbl_partition_test on [tbl_partition_test] /* drop the clustered if already exists if not create new CI on primary FG*/ ALTER TABLE [dbo].[tbl_partition_test] DROP CONSTRAINT [PK_C1810429_tbl_partition_test] WITH ( ONLINE = OFF ) /* Create clustered index on the table to free it from the partitioning scheme; Here, the "ON [primary]" or Dummy part is key to removing the partition scheme from the table ! */ CREATE CLUSTERED INDEX Cx_tbl_partition_test ON [tbl_partition_test] (ID,Date_Col) ON FG_Dummy DROP PARTITION SCHEME PS_myPartitionScheme DROP PARTITION FUNCTION PF_myDateRange /* drop the dummy clustered index you created, as it was only created to free the…

Index aligned vs non aligned To have an Aligned index it should have same FG as partition resides and index structure should be match or the index column and partition key column of source table should match to switch in and out. In our switch example, I have created non aligned index and switch was failed with the following error. Msg 7733, Level 16, State 4, Line 147 ‘ALTER TABLE SWITCH’ statement failed. The table ‘Test_Tbl_Partition.dbo.tbl_partition_test’ is partitioned while index ‘Ix_tbl_partition_test’ is not partitioned.   Partition Elimination Data partition elimination refers to the query optimizer is ability to determine, based on query predicates, that only a subset of the data…

How do we insert or load new data into partition table.   use Test_Tbl_Partition select min(Date_Col),max(Date_Col) from [tbl_partition_test] -- 2025-01-01 00:00:00.0000000 2025-01-15 00:00:00.0000000 select * from [tbl_partition_test] -- create target table CREATE TABLE tbl_partition_Staging ( ID Int identity NOT NULL, Date_Col datetime2(7) NOT NULL, CONSTRAINT [PK_C1810429_tbl_partition_Staging] PRIMARY KEY CLUSTERED ( [ID] ASC, [Date_Col] ASC ) ) ON [PRIMARY]; insert into tbl_partition_Staging ([Date_Col]) values ('2025-01-15 00:05:00.0000000') insert into tbl_partition_Staging ([Date_Col]) values ('2025-01-15 00:06:00.0000000') insert into tbl_partition_Staging ([Date_Col]) values ('2025-01-15 00:07:00.0000000') insert into tbl_partition_Staging ([Date_Col]) values ('2025-01-15 00:08:00.0000000') insert into tbl_partition_Staging ([Date_Col]) values ('2025-01-15 00:09:00.0000000') select * from tbl_partition_Staging --Get min date as new partition's split point(left bound) SELECT MIN(Date_Col) FROM tbl_partition_Staging…

Merge Merge partition will drop the existing partition and merge the partition to the next partition for right range and take the merged last partition boundary value, make sure the partition is empty before merge otherwise both data will merge together. MSDN: MERGE [ RANGE ( boundary_value) ] Drops a partition and merges any values that exist in the partition into a remaining partition. RANGE (boundary_value) must be an existing boundary value, of the partition to be dropped. This argument removes the filegroup that originally held boundary_value from the partition scheme unless a remaining partition uses it, or marks it with the NEXT USED property. The merged partition exists in…