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.

Please share your ideas and opinions about this topic with me, your contribution will add true value to this topic.
If anyone has doubts on this topic then please do let me know by leaving comments or send me an email.

If you like this post, then please share it with others.
Please follow dbrnd.com, I will share my experience towards the success of Database Research and Development Activity.

I put up a post every day, please keep reading and learning.
Discover Yourself, Happy Blogging !
Anvesh M. Patel.

More from dbrnd.com

Leave a Reply

Be the First to Comment!

Notify of