CODEWITHSUNDEEP
sqldatabaseindexingclustered-index
Cartesius00
Cartesius00

Reputation: 24384

Insert into table with clustered index

If I am not mistaken clustered index sorts the rows of the table physically on disk depending on some custom order. I assume tables are stored in files (maybe one file per table?). Well but then, assume that somebody inserts a "new row" into the table and it should be placed, say, at the beginning. Database then needs to shift the whole table (all rows) physically in the file? Isn't that too slow or is there any optimization technique how to achieve sorted order after insertion?

Upvotes: 5

Views: 2127

Answers (1)

ramazan polat
ramazan polat

Reputation: 7900

Tables are not necessarily stored as files. Logically, they are stored in pages. Suppose that every page has 100 rows and can hold extra 20 rows when needed. Then all pages are linked together to make the whole table data.

In your case, when you add a new row to beginning, you just change the first page, just 100 rows are shifted. And if you do it over and over again, when the row count of that page exceeds 120, the page is split into two new pages which are linked together.

Upvotes: 5

Related Questions