Shulou(Shulou.com)06/01 Report--

Database integrity: refers to the logical consistency, correctness, validity and compatibility of the data in the database

Entity integrity (Entity Integrity row integrity): entity integrity refers to the integrity of rows in a table. It is mainly used to ensure that the data (records) of the operation are not empty, unique and do not repeat. That is, entity integrity requires that each relationship (table) has and only one primary key, each primary key value must be unique, and is not allowed to be "NULL" or duplicate.

Domain integrity (Domain Integrity column integrity): means that the columns in a database table must meet a specific data type or constraint. Among them, the constraint includes the range of value, precision and so on. The CHECK, FOREIGN KEY constraints and DEFAULT, NOT NULL definitions in the table all fall into the category of domain integrity.

Referential integrity (Referential Integrity) is an inter-table rule: for related tables with permanent relationships, changing only one of the records when updating, inserting or deleting records will affect the integrity of the data. If a record of the parent table is deleted, the corresponding records of the child table are not deleted, resulting in these records being called orphaned records.

The referential Integrity Rule (Referential Integrity) requires that if attribute group F is the primary key of relational schema R1 and F is also the foreign key of relational schema R2, then in the relationship of R2, the value of F only allows two possibilities: null or equal to a primary key value in R1 relationship.

Sql Server storage structure, page, area, heap

Page: a contiguous block of disk space used for data storage. The basic unit of data storage in SQL Server is the page, the disk Icano operation is performed at the page level, and the page size is 8KB. At the beginning of each page is a 96-byte header that stores system information about the page, including the page number, page type, free space for the page, and the allocation unit ID; for the object that owns the page. The rest is the data row for storing data and the remaining free space. The structure diagram is as follows (personal drawing)

Interval: an extent is the basic unit of managing space, and an area is a collection of eight physically contiguous pages (that is, 64KB), all of which are stored in the zone. There are two types of zones in SQL Server: unified zone and mixed zone.

Heap: a heap is a table without a clustered index whose data is not stored in any order.

The only structure associated with data in a heap is a bitmap page called an index allocation map (IAM), and when an object is scanned, SQl server uses an IAM page to traverse the object's data.

The data pages and rows in the heap table are not in any particular order and are not linked together. The only logical connection between data pages is the information recorded in the IAM page

Suppose there are 1 million pieces of data in an order schedule, and you need to query the details of an order, as follows:

Select*fromT_EPZ_INOUT_ENTRY_DETAILwhereentry_apply_id='31227000034000090169'

If the query is made in the heap table, SQLServer scans the heap table by scanning the IAM page and compares the entry_apply_id for 1 million times. If the index is indexed by the entry_apply_id field, the index key data must be placed in B-Tree order, so binary search can be used to find the data. That is, if the N power of 2 is greater than the number of records, the data can be found. The power of 20 of 2 is greater than 1 million, so the record can be found after a maximum of 20 searches. Comparing 20 times with 1 million times, you can easily feel the difference in performance.

This leads to the concept of index.

Indexes are divided into clustered index and nonclustered index.

Clustered index: a clustered index means that the physical order of the data in the rows of the database table is the same as the logical (index) order of the key values. A table can have only one clustered index, because there is only one physical order for a table, so there can be only one corresponding clustered index. If an index is not a clustered index, the physical order of the rows in the table does not match the order of the index, and the clustered index has a faster retrieval speed than the nonclustered index.

Nonclustered index: a nonclustered index is an index in which the logical order of the index is different from the physical storage order of the uplink on disk

Visual analogy between clustered index and non-clustered index

The text of a Chinese dictionary is itself a clustered index. For example, if we want to look up the word "an", we will naturally open the first few pages of the dictionary, because the pinyin of "an" is "an", and the dictionary of Chinese characters sorted by pinyin begins with the English letter "a" and ends with "z", so the word "an" is naturally placed at the front of the dictionary. If you have searched all the parts that start with "a" and still can't find the word, it means you don't have the word in your dictionary; similarly, if you look up the word "Zhang", you will turn your dictionary to the last part, because the pinyin of "Zhang" is "zhang". In other words, the body of the dictionary is itself a directory, and you don't need to look in other directories to find what you are looking for. The content of the text itself is a kind of directory arranged according to certain rules called "clustered index". There can be only one clustered index per table because directories can only be sorted in one way

If you know a word, you can quickly look it up from the automatic. But you may also encounter a word you do not know and do not know its pronunciation. At this time, you will not be able to find the word you are looking for according to the previous method, but need to find the word you are looking for according to the "side radical". Then turn directly to a page according to the page number after the word to find the word you are looking for. However, the sorting of the words you find by combining the "radical catalogue" and the "word search list" is not really the sorting method of the text. For example, if you look up the word "Zhang", we can see that the page number of "Zhang" in the word search list after the search is 672 pages, and the top of the word "Zhang" in the word search table is the word "chi", but the page number is 63 pages, the bottom of "Zhang" is the word "crossbow", and the page is 390 pages. Obviously, these words are not really located at the top and bottom of the word "Zhang". The continuous words "Chi, Zhang, and crossbow" you see now are actually their sorting in the nonclustered index. it is the mapping of the words in the dictionary body in the nonclustered index. We can find the words you need in this way, but it takes two processes to find the results in the directory and then turn to the page number you need. We call this kind of catalog purely a catalog, and the sorting method in which the text is purely the text is called a "nonclustered index".

As shown in the figure, the data stored in the table is disorganized and is not sorted by name. We extract the name of the data and create a nonclustered index based on the name. The names in the index are sorted, and the space occupied by the index is far less than that occupied by the data in the table. When we query a piece of data in the table, we will no longer scan the whole table, but scan the index. Get the desired data and locate it to the specific data in the table. But on the nonclustered index, it takes some time to scan a specific name, and further optimize it, adding a Non-leaf level (non-leaf node) on it can be quickly located by the B-tree algorithm. The query speed is greatly improved.

The query of a clustered index is queried by B-tree.

How do I query indexes in a table?

Inidex_id = 0 indicates that there is no index in the table inidex_id = 1. The table is a clustered index, inidex_id = 2 or 3.. Is a nonclustered index.

Problems and techniques encountered in the application of index

Page splitting, fill factor, defragmentation, index statistics

Page splitting: because a new data c is to be inserted in a nonclustered index or ordered data such as a b e f, then c will be placed after f in the physical order, becoming a b e f c, thus causing the page to split.

You can use index collation, or define a fill factor when creating a table (that is, at the beginning of page creation, let the data stored on each page account for the ratio of the page) to solve the case of page splitting.

Dbcc showcontig (Tstudent,non_sname)-- Tstudent indicates that the PK_ study index, query page splitting dbcc indexdefrag (schoolDB,Tstudent,non_sname)-indexing create nonclustered index non_sname on TStudent (sname) with drop_existing,fillfactor = 50 Murray-rebuild the index And make fill factor dbcc show_statistics (tstudent,non_sname)-- View index statistics update statistics schooldb.dbo.tstudent-- manually update the statistics of all indexes in the table update statistics schooldb.dbo.tstudent non_sname-- manually update non_sname index statistics in the table.

In practice, sometimes different indexes are faster than using indexes, when using index queries, but the sql server tool will automatically help you determine

Summary

The above is the whole content of this article, I hope that the content of this article has a certain reference and learning value for your study or work, if you have any questions, you can leave a message and exchange, thank you for your support.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.