SQL Server Interview: Difference between Filtered Index and Table Partition

This article is half-done without your Comment! *** Please share your thoughts via Comment ***

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.