Basic SQL server indexing knowledge-Series

Basic SQL server indexing knowledge (1)-basic format of Record Data
Here we have collected a series of database indexing knowledge, which is a summary and review of the indexing knowledge. By summing up, you will find that many vague concepts are much clearer in the past. Whether it is cached data information or physically saved information, their basic unit is data pages. Therefore, understanding data pages is the most basic knowledge point. This article introduces some basic knowledge about data pages related to indexes.
Here we have collected a series of database indexing knowledge, which is a summary and review of the indexing knowledge. By summing up, you will find that many vague concepts are much clearer in the past.

Whether it is cached data information or physically saved information, their basic unit is data pages. Therefore, understanding data pages is the most basic knowledge point. This article introduces some basic knowledge about data pages related to indexes.

Basic knowledge of data pages

The basic unit of data storage in SQL Server is page ). The disk space allocated by data files (. MDF or. NDF) in the database can be logically divided into pages (from 0 to N consecutive numbers ). Disk I/O operations are performed on pages. That is to say, the minimum data unit for SQL Server to read or write data each time is the data page.

Note: log files are not stored in this way, but a series of log records.

The database is divided into logical pages (8 KB for each page). In each file, all pages are consecutively numbered from 0 to X, where X is determined by the file size. You can specify a database ID, a file ID, and a page number to reference any data page. When we use the alter database command to expand a file, the new space will be added to the end of the file. That is to say, the page number of the first data page of the new space of the expanded file is x + 1. When we use the DBCC shrinkdatabase or DBCC shrinkfile command to contract a database, the page will be removed from the page with the highest page number (at the end of the file) in the database and moved to the page with a lower page number. This ensures that the page numbers in a file are always consecutive.

In SQL Server, the page size is 8 KB. This means that each MB of the SQL Server database has 128 pages. And so on. Based on the database file size, we can calculate the number of data pages in the database.

SQL Server 2005 has the following page types:

Page type content
When text in row is set to on, data includes the following options: Text, ntext, image, nvarchar (max), varchar (max), and varbinary (max) and all data rows other than XML data.
Index index entries.
Text/Image Large Object Data Type:

Text, ntext, image, nvarchar (max), varchar (max), varbinary (max), and XML data.

Variable-length data type columns when the data row exceeds 8 KB:

Varchar, nvarchar, varbinary, and SQL _variant
 
Global allocation map and shared global allocation map are information about whether the partition is allocated.
Page free space information about page allocation and Available page space.
Index allocation map information about the partitions used by tables or indexes in each allocation unit.
Bulk changed map information about the region for modifying large-capacity operations after the last backup log statement in each allocation unit.
Differential changed map information about the Zone changed after the last backup database statement in each allocation unit.

Structure of the data page:

Each page starts with a 96-byte header to store system information about the page. This information includes the page number, page type, available space of the page, and the allocation unit ID of the object that owns the page.

On the data page, data rows are placed in sequence followed by headers. The end of the page is a row offset table. For each row on the page, each row Offset Table contains an entry. The distance between the first byte of the corresponding row and the header of each entry record. The order of entries in the row Offset Table is the opposite to that of the row on the page.

　　
I think it's good, so repost. More here: http://database.ctocio.com.cn/tips/146/7791146.shtml

Basic SQL Server Index knowledge (2)-clustered index, non-clustered Index

Basic SQL Server Index knowledge (3)-metrics and cache clearing methods often used in testing

Basic SQL Server Index knowledge (4)-primary key and clustered Index

Basic SQL server indexing knowledge (5)-Understanding newid () and newsequentialid ()

Basic SQL server indexing knowledge (6)-cost of indexing, Use Cases

Basic SQL server indexing knowledge (7)-indexing for and

Basic SQL server indexing knowledge (8)-Basic Data Format supplement

Basic SQL server indexing knowledge (9)-indexing for or

SQL server indexing BASICS (10)-three algorithms for join

 

This article from the csdn blog, reproduced please indicate the source: http://blog.csdn.net/zhou__zhou/archive/2008/02/15/2097296.aspx