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…

Code to view the partition table data. use Test_Tbl_Partition go /* -- find no.of partition table select object_schema_name(i.object_id) as [schema], object_name(i.object_id) as [object], i.name as [index], s.name as [partition_scheme] from sys.indexes i join sys.partition_schemes s on i.data_space_id = s.data_space_id */ go SELECT ISNULL(quotename(ix.name),'Heap') as IndexName ,ix.type_desc as type ,prt.partition_number ,case when ix.index_id < 2 then prt.rows else 0 END as Rows ,cast (rv.value as datetime2(7)) as [Upper_Boundary] ,prt.data_compression_desc ,ps.name as PartitionScheme ,pf.name as PartitionFunction ,fg.name as FilegroupName ,au.TotalMB ,au.UsedMB ,case when pf.boundary_value_on_right = 1 then 'less than value column' when pf.boundary_value_on_right is null then '' else 'less than or equal to value column' End as Comparison ,fg.name as FileGroup FROM…

What is table partitioning? Table partitioning is a way to divide a large table into smaller pieces as a partition logical unit. By default a table will be stored into single partition that is partition number 1. The data of partitioned tables and indexes divided into logical units that may be spread across more than one file and filegroup in case if we use file and filegroup method in the database creation, otherwise it will be stored into a single default MDF Primary file and filegroup. Having multiple filegroup or single file group is depends on the application requirement it is not going to do anything with table partition for…