SQL Server Topic One: Index (top)

write in front of the crap : Indexing is a commonplace issue, although it is often said, but as I have not used the index (very scary bar), I as a MS-BI implementation engineer incredibly useless index, speak naturally not emboldened. Prior to the understanding of the index to stay in the "know" point of view, with the passage of time feel not really to apply the index is simply slag! End of the crap, start indexing the topic, in order to thoroughly (I am not a DBA, can use the index to understand proficiency, so do not thoroughly) understand the index, learn from the data page! This article is the main source of MS online documentation!

• Page and District system structure

  Page: The basic unit of data storage in SQL Server is the page. The disk space allocated for data files in the database can be logically divided into pages (numbered from 0 to n consecutively). The I/O operations on the disk are performed at the page level. This means that SQL Server reads or writes all data pages.

  How to understand it? As a storage unit, here's an inappropriate metaphor: a pig farm with many pigsty, each pigsty as a page for storing data. As a breeder, I suppose I'm buying and selling pig pigs, and I'm doing the pig right, but actually we're buying and selling on a pigsty, first of all, I'll number the pigsty, and then I'll buy the pigs in different numbers. So it could be understood that I kept the pig (data) in a pigsty (page) with different numbers (logically divided into 0-to-n sequential numbers), and I traded (I/O operations) The pigs, all from each pigsty.

  The size of the page is 8K. The beginning of each page is a 96-byte header that stores 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.

  This sentence good understanding: Each pigsty can raise a few pigs (8k), each pigsty has a pigsty card, pigsty card write something? Of course to write: The number of pigsty, is the male pigsty, female pigsty, or Piggy Circle, has raised a few pigs and the head of the pigsty contact.

  The following is a list of the types of data pages in MSSQL (male pigsty, female pigsty, or Piggy circle):

  Page type	Content
  Data	When the text in row is set to ON, contains data rows for all data except text, ntext, image, nvarchar (max), varchar (max), varbinary (max), and XML data.
  Index	The index entry.
  Text/image	large object data type: text ntext, image, nvarchar (max), varchar (max), varbinary (max), and XML data. data row is a variable-length data type column when it exceeds 8 KB:            varchar
  Global Allocation Map , Shared Global Allocation Map	Information about whether the zone is assigned.
  Page Free Space	Information about page assignments and the available space for the page.
  Index Allocation Map	Information about the extents used by the table or index in each allocation unit.
  Bulk Changed Map	Information about the extents modified by bulk operations in each allocation unit since the last BACKUP LOG statement.
  Differential Changed Map	Information about the extents in each allocation unit that were changed since the last BACKUP DATABASE statement.

  Come down and look at the pigsty:

  650) this.width=650; "src=" Http://s3.51cto.com/wyfs02/M01/71/87/wKioL1XTT5GC9sFSAABnicuNgrs645.jpg "title=" Pigsty figure. png "alt=" wkiol1xtt5gc9sfsaabnicungrs645.jpg "/>

  above is a sample diagram of a page: note that the data page, The data rows are followed by the header in order, the end of the page is the row offset table, and for each row in the page, each row offset table contains an entry, and each entry records the distance between the first byte of the corresponding row and the top of the page (the line offset has not been understood before, and now I understand that Assuming that each pig does not move in the pigsty, each pig is at the distance from the Pigsty gate is the offset, of course, this distance-the line offset is also like a pigsty card is recorded on another " brand ", but it does not follow the order of 123 records, is flashbacks). The order of the entries in the row offset table is the opposite of the order of the Rows in the page (as for why it is the opposite order, I guess from the data structure, and so on a deep point of view, for the moment to write this line offset pig brand, standing in the end of the pigsty, first saw the last pig, so inverted record, not quasi- ）。

At this point, this pig sty seems to be able to raise pigs, but, an extreme situation? This pig, mutated, pig leg super big, a pigsty can't keep what to do! Attention, attention, look at Ms How to do : line can not cross the page, but the line of the part may move the page, so the actual line may be very large (see a pig without legs, do not abandon it small, the leg is thicker than the elephant!) ）。 Yes, cut off the legs of the pig and put it alone ... Here's how MS is done: the maximum amount of data and overhead in a single row of a page is 8060 bytes (8K), but does not include data stored with the Text/image page type (why not include these types of pages?). The reason is simple, some pig pens are specially designed to store super-large pigs, is the whole pig, not a pig leg. ), tables that include varchar, nvarchar,varbinary, or sql_variant columns are not constrained by this restriction. When the total size of all fixed and variable columns in a table exceeds the limit of 8k, SQL Server dynamically moves one or more variable-length columns to the page in the Row_overflow_data allocation unit, starting from the largest column. (Readme: Move a column in a table to a different page). This will be done whenever the insert or update operation increases the total size of the row to a limit of 8,060 bytes. When you move a column to a page in a Row_overflow_data allocation unit, a 24-byte pointer is maintained on the original page in the In_row_data allocation unit. If subsequent operations reduce the size of the rows, SQL Server dynamically moves the columns back to the original data page.

This article is from the "Seasky" blog, make sure to keep this source http://seasky09.blog.51cto.com/7243998/1685740

SQL Server Topic One: Index (top)