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.
