SQL SERVER 2008 index, data storage basic theory "original"

A high level of understanding of SQL Server concurrency processing, indexing, GC, and caching is essential in the process of advanced programmer progress, which has recently been discovered. Therefore, to prepare for the future, decided to make a copy of the relevant documents.

Hope to oneself, to Bo friends have certain help.

A: Basic concepts

1. Purpose of the index: provides an additional way for the database to find data and quickly find where the data is stored. We can think of data in SQL Server as data stored in some sort of hierarchy.

2. Files: database-related files include two kinds:. mdf files and. ldf files.

The ①.mdf file is the primary physical database file, where the data is eventually stored, and the secondary file (. ndf file) can be added, and the secondary file does not need to reside on the same primary physical drive as the primary file.

②.ldf file, day to file.

3. Section: A section is a basic storage unit used to allocate space for an index in a table and a given file, which consists of 8 contiguous 64KB data pages.

About the sector has with three points of view:

① once a section is full, the next record will occupy a full segment size, rather than the size of the record itself.

② by pre-allocating space and taking a full chunk size, SQL Server saves time allocating new space for each record.

③ the space allocated to the database is the space that disappears from the free space in the hard disk, and the space of the segment is simply the space allocated in the entire space obtained by the individual database files.

4. Page: Like a section is an allocation unit in a database, a page is an allocation unit in a specific section, and the page is the last level to reach a true data row. The number of rows in the page is not fixed, and the rows are not allowed to spread across pages.

For each row that is inserted, in order to indicate where the data for a particular row begins in the page, note that we place the row offset at the end of the page.

4.1 Index pages: Non-leaf-level pages and leaf-level pages that hold nonclustered indexes, and non-leaf-level pages of clustered indexes.

SQL SERVER 2008 index, data storage basic theory "original"