5

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?

Cartesius00
  • 23,584
  • 43
  • 124
  • 195
  • If you're talking about **SQL Server** - then the data is held in 8K pages organized in a balanced b-tree structure. Where in the table a new row gets inserted is almost irrelevant - there might or might not be some steps that need to be taken, but it's **definitely** not a "sequential" file-like structure that needs to be shifted to the back when a new row is inserted... – marc_s Jul 22 '12 at 15:59

1 Answers1

5

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.

ramazan polat
  • 7,111
  • 1
  • 48
  • 76
  • I would just add . . . "tables are not necessarily stored as files". Databases can store tables however they like, and some databases that support SQL might choose to put a table in a file. Most do not, but that is a design decision for the people writing the engine. – Gordon Linoff Jul 22 '12 at 16:13
  • Good point. Of course any database can save tables to files. I was giving a general perspective to the question. But in most cases and most DBMSs like Oracle, DB2 or SQL server, it goes like this: Rows -> Pages -> Tables -> File or Disk. I will edit my response according to your point. – ramazan polat Jul 22 '12 at 19:14