In one of the recent interviews I was asked DB question - There is a DB with ta table having million rows. Over time will it's performance degrade. Why and how? My answer was due to fragmentation (We already discussed indexes and other performance impacts large table has). What the interviewer was primarily interested is to know how data is stored in blocks for a DB and how fragmentation really affects the performance.
He also gave an example saying lets say there are 3 records. 1- > 2 -> 3 and lets say 2 gets deleted will 1->3 or 1 will still point to 2 which on knowing its deleted will move to 3.
I would appreciate if someone who has faced such a similar problem is actual DB and know how it works please do help me understand.