Tags: des recognition query mechanism storage space statistics requires SEL dictionaryWhat is an index?
An index is a data structure on a disk that is based on a table or view. Using an index can make data acquisition faster and higher in universities, and also affect some other performance, such as inserting or updating.
There are two main types of indexes: clustered and nonclustered indexes.
Dictionary directory is an index, according to pinyin query the desired word is a clustered index (physical continuous, page number and table one by one corresponds), the radicals are a nonclustered index (logical continuous, page number and directory discontinuity).
Clustered index storage records are physically contiguous, while nonclustered indexes are logically contiguous, and physical storage is not contiguous.
A clustered index can have only one table, and a non-clustered index may have more than one table. The pros and cons of indexes
The index is used to avoid full table scans because full table scans read every data page of a table from disk, and if an index points to a data value, only a few disks are required to read.
Indexed tables take up more space in the database, and it takes longer to add, delete, and change data commands. Storage mechanism of indexes
The catalog in the book is a word and a list of page numbers in which the index in the database is the value in the table and the list where the values are stored.
A clustered index is a new physical space in the database that holds the values he arranges, and when new data is inserted, he rearranges the entire physical storage space.
A nonclustered index contains only the columns of the nonclustered indexes in the original table and a pointer to the actual physical table. Basic structure of the data table
When a new data table is created, the system allocates a contiguous space of 8k in the disk. When a 8k run out of time, the database pointer will automatically allocate a 8k of space, each 8k of space is called a data page, and assigned from 0-7 page number, each file's No. 0 page records the boot information called the page header, every 8 data pages by 64k form the expansion area. The combination of all data pages forms a heap.
SQL Server specifies that rows cannot span data pages, so the maximum number of records per row can only be 8k, which is why char and varchar are limited to 8k in size, and storing more than 8k of data should use the text type. In fact, the text type of the field value can not be directly entered and saved, it is to store a pointer to a number of 8k data page composed of the expansion area, the real data is actually placed in these data pages. When to set the index
Expand the table to create the index, right-click Index, select New index, clustered index, new indexing point, select Columns, select column, and choose Ascending or Descending, enter name, OK.
By default, a clustered index is automatically created at the same time that the primary key is generated. 2. Create a clustered index using T-SQL
create clustered Span style= "Color:rgb (0, 0, 255); >index index_name /* Clustered index name */ on " Span style= "Color:rgb (0, 0, 0); > table_name (ID desc ) with (Drop_existing = /* */
Each table or view can contain only one clustered index, because the clustered index changes the way data is stored and arranged. Whether clustered or nonclustered, the information is stored in a balance tree or B-tree, and B-trees identify and group similar data, precisely because the retrieval in the B-tree is based on key values, so the index can increase the speed of data access. The B-Tree will have a combination of similar keys, so the database engine can find the target record by searching only a few pages. Nonclustered indexes
You can have more than one nonclustered index on each table, you can create an index on a column, or you can create an index on multiple columns that are already part of an existing index.
The SSMs creation method is the same as the T-SQL creation method:
Add Index OptionsCreate nonclustered Index /* Clustered index name */ on defualt ( desc)
FILLFACTOR: Used when creating an index, the data for each index page is a percentage of the index size, default 100. When the table needs to be modified frequently, it is recommended to set to 70-80, which is recommended when updating infrequently. 90.
Pad_index: Used to keep open space on each page in the Index intermediate level. The value cannot be set, and his value inherits from FillFactor.
Create TableCeshi--New Table(IDint Identity(1,1)Primary Key, namevarchar( -), Codevarchar( -), [Date] datetime)--inserting 10w test dataDeclare @n intSet @n = 1 while @n <100000beginInsert intoCeshi (Name,code,[Date]) Values('name'+cast(@n as varchar( -)),'Code'+cast(@n as varchar( -)) , getUTCDate ())Set @n=@n+1End--View index StatusSet StatisticsIo on --View disk IOSet StatisticsTime on --Viewing SQL statements parsing compilation and execution timesSelect * fromCeshi--View index Statusexecsp_helpindex CeshiSelect * fromCeshiwhereName= 'name1'--ctrl+l View Execution Plan clustered index scan cost 100%, consider optimization for index lookup, build nonclustered index on name--To build a nonclustered indexCreate IndexName_index onCeshi (name)--look at the index again. New Nonclustered indexexecsp_helpindex Ceshi--in the run above statementSelect * fromCeshiwhereName= 'name1'--It was obvious that the speed was getting faster, ctrl+l found that clustered and nonclustered indexes accounted for 50%Manage Indexes
execSp_helpindex Ceshi--View the indexes in the tableexecsp_rename'Ceshi.name_index','new_name' --renamingDrop IndexCeshi.new_name--Delete IndexDBCCShowcontig (Ceshi,new_name)--Check for fragmentationDBCCIndexdefrag (Webdb,ceshi,new_name)--Organize the FragmentsUpdate StatisticsCeshi--UPDATE STATISTICS for all indexes in a table
SQL Server Index