SQL Server Interview: Difference between Filtered Index and Table Partition

If you are SQL DBA and if I am taking your interview, definitely I will ask this question to you.
If you don’t know about Filtered Index and how to create a Table Partition in SQL Server, please visit below articles:

SQL Server: Filtered Indexes – Improved the query performance

SQL Server 2016: Introduce New TRUNCATE by Partitions Number

Filtered Index:

  • You can store a portion of your dataset in Filtered Index. For example, Created a filtered index on Gender column for only ‘Male’, then it stores only ‘Male’ detailed index data.
  • Filtered index only applies to a subset of the base table which is not allowing a fast switching.
  • You can reduce the CPU I/O by reading a particular value of a Filtered Index.
  • The Filtered index stores only keys and reference of data.
  • Once you create a Filtered Index on the PartitionScheme, you have to create same Filtered Index for every new table partitions which create a problem because a combination of Filtered indexes may contain the different value.
  • If you already apply table partition, you should not apply filtered indexes on those partitions.

Table Partition:

  • The Table Partitioning breaks your main table into small groups of tables based on defined partitioning key.
    The data physically divided for better management and fast retrieval.
  • For example, you have a 20GB of a table, and it contains total 5 years of data so you can partition your table base on years which will create five table partitions with each size of 4GB.
  • To implement Table partitioning, you need to create .ndf files for each partition, partition function, partition scheme.
  • It stores all columns of your table in partitions; it is not just storing the key of data like Filtered Index.
    You can define indexes on each table partitions.
  • The maintenance of Table Partitions is costly and time taking.
Anvesh Patel