SQL storage principle and relationship of clustered index, nonclustered index, unique index, PRIMARY KEY constraint (complement)

Index type

1. Unique index: Unique index does not allow two rows to have the same index value

2. Primary KEY index: Defining a primary key for a table will automatically create a primary key index, which is a special type of unique index. The primary key index requires that each value in the primary key is unique and cannot be empty

3. Clustered index (Clustered): The physical order of the rows in the table is the same as the logical (indexed) Order of the key values, and each table can have only one

4. Nonclustered index (non-clustered): Nonclustered indexes Specify the logical order of the tables. The data is stored in one place, the index is stored in a different location, and the index contains a pointer to the location of the data store. can have multiple, less than 249

Clustered and nonclustered indexes, which can be either a unique index or a

The primary key index is for the primary key, the composite index is for multiple columns as an index, the clustered index is not special, because he is still using a column index, plus a unique number as the same time ordinal, index sometimes to avoid the use of composite index.

The unique index key can be empty, and the primary key index is not available.

For most database engines, creating a primary key creates a clustered index at the same time as the primary key index.

When you create a unique constraint column, a unique index is automatically created for the column, which is also a nonclustered index.

Automatically created indexes can be deleted, and

Index type: Again, in the Chinese dictionary, I hope you can understand the two concepts of clustered and nonclustered indexes.

Unique index:

A unique index does not allow two rows to have the same index value.

If duplicate key values exist in the existing data, most databases do not allow the newly created unique index to be saved with the table. The database also rejects this data when the new data duplicates the key values in the table. For example, if you create a unique index on the learner ID number (STUID) column in the Stuinfo table, the ID number of all learners cannot be duplicated.

Tip: A unique constraint is created and a unique index is created automatically. Although a unique index can help you find information, it is recommended to use a PRIMARY KEY constraint or a unique constraint for best performance.

Primary KEY index:

Defining a primary key for a table in a database diagram automatically creates a primary key index, which is a special type of unique index. The primary key index requires that each value in the primary key be unique. When a primary key index is used in a query, it also allows for fast access to the data.

Clustered indexes (clustered index)

In a clustered index, the physical order of the rows in the table is the same as the logical (indexed) Order of the key values. A table can contain only one clustered index. For example, the Chinese word (word) code defaults to alphabetical order of each page number in the dictionary. Phonetic Alphabet A,b,c,d......x,y,z is the logical order of the index, and the page number is ... is the physical order. The dictionary, which is sorted by phonetic Alphabet by default, has the same index order and logical order. That is, the phonetic order of the word (word) corresponding to the page number is also larger. If the phonetic "ha" corresponding to the word (Word) page number is more than the phonetic "ba" corresponding to the word (Word) page number.

Nonclustered indexes (non-clustered)

If it is not a clustered index, the physical order of the rows in the table does not match the logical order of the key values. Clustered indexes have faster data access speeds than nonclustered indexes (nonclustered index). For example, an index sorted by strokes is a nonclustered index, and the word "1" (word) corresponds to a page number that corresponds to the number of words (the word) that the "3" is drawn on (back).

Tip: In SQL Server, a table can only create 1 clustered indexes, multiple nonclustered indexes. Set a column's primary key, which defaults to a clustered index

for Oracle databases:

We specify the ID column as the primary key, and the Oracle database automatically creates a unique index with the same name

So what is the case with a unique key constraint? Oracle also automatically creates a unique index with the same name and does not allow the creation of a unique or non-unique index on this column.

The unique key constraint does not have a non-null requirement.

A unique index, like a unique key constraint, does not require a column value to be empty.

If we let the primary KEY constraint or UNIQUE key constraint fail, will oracle automatically create a unique index that is affected? When a primary KEY constraint or UNIQUE key constraint is invalidated, Oracle marks the implicitly created unique index as the delete state.

What if we first create a unique index and then create a primary key or a unique key constraint? Experimental results show that the unique index created first is not affected by the constraint invalidation.

It is summarized as follows: (1) Both the PRIMARY KEY constraint and the unique key constraint implicitly create a unique index with the same name, and when the primary KEY constraint or UNIQUE key constraint is invalidated, the implicitly created unique index is deleted, and (2) The PRIMARY KEY constraint requires that the column value is non-null, and that the unique key constraint and unique index do not ; (3) The same field sequence does not allow duplicate index creation; the same diagram: about the zone:

In the previous article I introduced the pages in the data file, including the page structure and some page types. Now I want to explain how the page is organized into a district (extent). A zone consists of 8 contiguous pages in the data file. The zone starts with the header of the data file and is always 64K aligned (that is, 8-page alignment). The zone and its properties are identical in SQL SERVER 2000 and 2005.

There are two types of zones in SQL SERVER: mixed zone and uniform zone.

Mixed zone

The first 8 pages assigned to either an IAM chain (an index in SQL SERVER 2000 or an allocation unit in 2005) are single pages, which are called mixed pages. This means that each time a separate page is allocated instead of a zone. This allows very small tables to spend the smallest amount of space. Once an IAM chain crosses the threshold of the first 8 pages, it assigns a unified area and never assigns a mixed page again.

Mixed pages that are allocated from a mixed area do not need to be assigned to a specific IAM chain. Because these zones are globally assigned tracking (via the GAM page), they are not assigned to an IAM chain. If there are any pages in the mixed area that are not allocated, the area is also tracked by the SGAM page. When a hybrid page needs to be allocated, the system checks to see if the SGAM page still has such a zone. If not, a new mixed area is assigned and a page is allocated from it, and the area is tracked by the SGAM page until all pages are assigned.

Because the mixed zone is not assigned to a specific IAM chain, it is possible that it is assigned to 8 different IAM chains. No matter how many pages are assigned to an IAM chain, the IAM page itself is always a mixed page. This means that a mixed area can have multiple page types, including IAM pages, data pages, index pages, or text pages.

Unified Zone

Once you've crossed the 8-page threshold, you'll later be assigned to the IAM chain from the unified zone. This means assigning one area at a time to an IAM chain and labeling it on an IAM page on the IAM chain--regardless of who mapped the GAM interval in which the area resides. The zone is also tracked by the associated GAM page, so that other IAM chains are no longer allocated.

All pages of a unified zone must be assigned to the same IAM chain. Then, they do not need to be the same type of page. For example, a clustered index will have both data and index pages. When a zone is assigned to an IAM chain, pages in that area are not all allocated at once (except for large data operations), which are usually on demand, and each page is assigned a PFS page trace.

When all pages of a unified zone are freed, the zone itself is freed from the IAM chain that owns it, and can be reassigned to other IAM chains, or to a mixed zone.

To track changes to the backup area

There are two areas in the system that are used to track changes:

1. All areas changed since the last full backup have a related differential bitmap page (Differential bitmap page) tracking. This allows for a differential backup to know which zones need to be backed up instead of backing up the entire database. When the next full backup, all the differential bitmap pages are reset.

2. Since the last full, differential, or log backup a zone has undergone a large log operation in the bulk-logged recovery model with the associated minimal Log bitmap page (minimally-logged bitmap page) trace. Any log backups after a large log operation will contain all of these tracked extents. When the next backup is taken, all of the minimum log bitmap pages will be reset.

Here are the principles and functions of all types: http://blog.csdn.net/misterliwei/article/details/5939524

and get content from here: http://www.cnblogs.com/lyhabc/p/3196479.html

This is a typical paging situation for a clustered index table

The Red box section is a concern.

PageType Paging Type: 1: Data page; 2: index page; 3:lob_mixed_page;4:lob_tree_page;10:iam page

IndexID index ID: 0 for Heap, 1 for clustered index, 2-250 for nonclustered index, or greater than 250 for text or image fields

For clustered indexes:

The second: The IndexID of each data page is 1, not that the data page becomes the index page, but that the data page is now part of the clustered index, not in the heap.

The third: The indexlevel of each data page is 0, which means that the data page is at the bottom of the clustered index

Indexlevel refers to the B-tree level, the lowest layer is 0, and the upper layer increments sequentially.

FID is Fileid or partition Id,pid for pageid,1:13856 refers to the position of 1 zone 13856PID, and finally can have a row value 1:13856:2 the meaning of the second row.

Iampid is the physical sequential ID, and pagepid is the logical sequential ID. 0 means null, that is, No.

SQL storage principle and relationship of clustered index, nonclustered index, unique index, PRIMARY KEY constraint (complement)