Learning Notes (ix)--Database storage structure: page, clustered index, nonclustered index

1. Page

SQL Server uses 8KB pages to store data, and disk I/O operations are performed at the page level in SQL Server. In other words, SQL Server reads or writes all data pages. Pages have different types, like data pages, Gam,sgam, and so on. First understand the structure of the data page.

SQL Server puts the data record in the data page. Data records are rows of leaf nodes in the heap table and in the clustered index.

The data page consists of 3 parts. Header (header), data area (data row and free space), and row offset array.

Let's execute the following command:

DBCC IND ('internalstorageformat','Customers',-1)

The results are as follows

you can see 2 records, a page with page type (PageType) of 10, and a page with a record of page type (PageType) of 1. Page type (PageType) 10 is an IAM page, page type (PageType) 1 is a data page, and its page ID is 79.

2. The comparison of clustered index and fee aggregation index

Nonclustered indexes have the same tree structure as clustered indexes, and the significant difference between them is the following two points:

The data rows of the underlying table are not sorted and stored in the order of the nonclustered keys.

The leaf layer of a nonclustered index is made up of index pages rather than data pages.

3. Clustered index: 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.

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.

4. Nonclustered index: Nonclustered index Specifies the logical order of the table, one location of the data store, and the index is stored in another location, and the index contains a pointer to the data, which can have multiple. 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 indexeshave 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).

Tips: In SQL Server, a table can only create 1 clustered indexes, multiple nonclustered indexes. Sets a column primary key, which defaults to a clustered index.

5. Create a clustered index query

The query table is as follows:

Then build the clustered index in the DateKey column:

CREATE clusteredindex Index_key on  DimDate (DateKey)

Execution results are as follows

Drop INDEX Dimdate.index_key    ---- Delete indexes    CREATE CLUSTERED index index_key on Dimdate (    datekey)-- Re-rebuilding the column-clustered index

Then execute the query statement:

　　Select Top 3 * from person

Notice the same statement that the return has changed. A clustered index can be the order of the table, affecting the top statement.

6. Create a nonclustered index

Use [pratice] gocreate TABLE Department8 (DepartmentIDintIDENTITY (1,1) not NULL, Name NVARCHAR ( $) not NULL, GroupName NVARCHAR ( $) isn't NULL, company NVARCHAR ( -), ModifiedDate datetime not NULL DEFAULT (getdate ())) The CREATE nonclustered INDEX ncl_name_groupname on [dbo]. [Department8] (Name,[groupname]) DECLARE @i intset @i=1While @i< -BEGIN INSERT into Department8 (name, [company], GroupName) VALUES ('Sales Department'+cast (@i as VARCHAR ( $)),'China Hello Co., Ltd. XX branch','Sales Group'+cast (@i as VARCHAR ( $))) SET @i= @i +1END SELECT*From [dbo]. [Department8]--TRUNCATE TABLE [dbo]. [Dbccresult] INSERT into Dbccresult EXEC ('DBCC IND (pratice,department8,-1)') SELECT* FROM [dbo]. [Dbccresult] ORDER by [PageType] DESC

Learning Notes (ix)--Database storage structure: page, clustered index, nonclustered index