Some basic conceptual areas, pages, and rows of SQL Server 2008 databases

Original address: http://www.cnblogs.com/liuzhendong/archive/2011/10/11/2207361.html

These basic concepts are not always understood before, and are now organized as follows:

1. District:

Zone is the basic unit of administrative space in SQL Server.

A zone is eight physically contiguous pages (that is, ten KB), and all pages are stored in a zone, which means that there are 16 extents per megabyte in the SQL Server database.
Once a section is full, the next data SQL Server allocates a section space to prevent each addition from allocating space.

2. Pages (page):

A page is the basic unit of data storage in SQL Server, which is the allocation unit of a section, a page 8K, which is the data row, but the number of rows per page varies, depending on the size of the data row.

The disk space allocated by the data file (. mdf or. ndf) in the database can be logically divided into pages (numbered from 0 to n consecutively). Disk I/O operations are performed at the page level. That is, the smallest unit of data that SQL Server reads or writes to data every time is the data page.

The beginning of each page is a 96-byte header that stores system information about the page. This information includes the page number, the page type, the available space for the page, and the allocation unit ID of the object that owns the page.

In SQL Server, the size of the page is 8 KB. This means that there are 128 pages per megabyte in the SQL Server database. In turn. Depending on the file size of the database, we can figure out how many data pages the database has.

3. Types of Pages: data pages, index pages, blob pages, and so on.

4. Line: Because the row is stored in the page, the size of the row is typically up to 8K (8060 characters, the upper limit of one page), and the maximum number of columns in a row is 1024 columns (fields).
In the case of varchar (max), text, image, you can span more than one page with a maximum of 2GB, at which point the original row is used to hold pointers and other columns.

5. Full-Text catalogs: Although together with SQL Server, the directory is actually stored independently on disk.

6. Index: Is the structure on the disk associated with a table or view, which speeds up the retrieval of rows from a table or view.

It contains keys that are generated by one or more columns in a table or view. These keys are stored in a structure (B-tree), allowing SQL Server to quickly and efficiently find the rows associated with the key value.

7. Type of index: clustered, non-clustered.

There is also a unique index, a unique index that ensures that the index key does not contain duplicate values, so that each row in the table or view is to some extent unique.

Both clustered and nonclustered indexes can be unique indexes.

8. Clustered index: Sorts and stores data rows in a table or view based on the key values of the data rows.

Each table can have only one clustered index, because the data rows themselves can only be sorted in one order.

Data rows in a table are stored in sorted order only when the table contains a clustered index. If the table has a clustered index, the table is called a clustered table.

If a table does not have a clustered index, its data rows are stored in an unordered structure called a heap.

A clustered table is a table with a clustered index, and a heap is a table with no clustered index.

Indexed views have the same storage structure as clustered tables.

The leaf node of a clustered index is the actual data page.

The average size of a clustered index is about 5% of the size of the table.

9. Nonclustered index: This piece of MSDN written by Foggy, will not copy.

Each table can have a maximum of 249 nonclustered indexes.

10. Maintenance of the Index: the index of the table or view is maintained automatically whenever the table data is modified.

11. Indexes and Constraints: Indexes are automatically created when PRIMARY KEY constraints and UNIQUE constraints are defined on a table column.

For example, if you create a table and identify a specific column as the primary key, the database engine automatically creates a PRIMARY key constraint and index on that column.

12. Partition Table

I haven't figured it out yet, and I haven't actually used it.

13. Go home and study the B-tree, and not quite understand the principles of indexing.

Reference Posts:

SQL Server Index Basics (1)---The basic format for recording data.
http://blog.csdn.net/ghj1976/article/details/2010366