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…

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…