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…

Split When we have data stored in wrong partition we need to split it to the right partition. This is usually comes in last partition in right range if we forgot to add next partition for the given boundary. We can do this in two ways 1) with data split and 2) without data split. Method 1 is easy, we can do this for small tables only, since Split will grow the log file very large. Method 2 is proper one this will not grow the log file bigger for this we need to move(Switch) data from partition to another table then create split and we need t o move…

Truncate Truncate partition will truncate the partition and leave as empty then we can merge it. MSDN: Specifies the partitions to truncate or from which all rows are removed. If the table isn’t partitioned, the WITH PARTITIONS argument generates an error. If the WITH PARTITIONS clause isn’t provided, the entire table is truncated. Test Code: Use the same script used in part 1 for creating database and table. -- delete b/w 1 to 6 TRUNCATE TABLE [tbl_partition_test] WITH (PARTITIONS (2 TO 5)); GO -- delete only 3,5 & 6 TRUNCATE TABLE [tbl_partition_test] WITH (PARTITIONS (3,5,6)) -- delete only 4 TRUNCATE TABLE [tbl_partition_test] WITH (PARTITIONS (4)); -- The truncate partition no…

  Switch in and out Switch partition is move data in and out between a table using switch feature. Rules: To switch partition IN and OUT, the partition or target table must be empty. To switch partition we should have 2 different tables, not same table. The source and target tables (or partitions) must have identical columns, ordering of column, indexes and use the same partition column. The source and target tables (or partitions) must exist on the same filegroup To switch a partition table from the partition table to a non partition table we should have check constraint created based on the data boundary. The receiving table must already…