I'm puzzled about how clustered primary keys work. I understand from the docs that rows are physically stored by primary key. Does this mean that inserting a new row with a key that fits in the middle would take ages because all the following rows would have to be shifted down? If not, how is this avoided?

CodePudding user response：

These clustered indexes are organized into "pages". Pages are linked together in elaborate ways which we application programmers don't have to worry about. Pages often have free space in them. If a new "in the middle" row fits in the appropriate page, the server puts it there. If it doesn't the server performs a so-called "page split" to make room for the new entry. The pages that result from the split will then have more free space in them. Page splitting isn't a terribly expensive operation. But tons and tons of page splits fragment the clustered index and make a table slower to access.

New rows "at the end" generate new pages.

If you want to experience this for yourself, try making and populating table where a guid is the primary key. Pretty much every row you insert goes "in the middle" and you'll get lots of page splits.

Read this for details of index maintenance.