SQL Server Rebuild Index

Most SQL Server tables require an index to increase the speed of data access, and if there is no index, SQL Server will be able to scan every record in the table to find the requested data. Indexes can be divided into clustered and non-clustered indexes, and the cluster index improves data access speed by re-scheduling data in tables, while non-clustered indexes improve data indexing by maintaining data pointers in tables.

Why keep the index of the table constantly maintained? First, briefly describe the architecture of the index. SQL Server stores data in a database file with 8KB pages in the hard disk. By default, these pages and the data they contain are non-organized. In order for chaos to become orderly, an index will be generated. After the index is built, the index page and data page are available, and the data page holds the data information written by the user. The index page holds the list of data values (keywords) used to retrieve the column and the address pointer of the record in the index table for that value. Indexes are clustered and non-clustered, and the clustered index essentially sorts the data in the table, as if it were the index directory of the dictionary. A non-clustered index does not sort data, it only holds the pointer address of the data. Inserting data into a table with a clustered index, when the data page reaches 100%, because the page does not have space to insert a new record, paging occurs, and SQL Server moves about half of the data from the full page to the empty page, resulting in a two-half full page. This will have a lot of data space. A clustered index is a doubly linked list that stores the previous page, the next page address, and the address of the data movement after paging, because the new page may be anywhere in the database file, so the link to the page does not necessarily point to the next physical page of the disk, and the link may point to another area, which forms a block. This slows down the speed of the system. For tables with clustered and non-clustered indexes, the keywords for non-clustered indexes point to the cluster index instead of to the data page itself.
To overcome the negative impact of data chunking, it is time-consuming to refactor the table's index, so it can only be done when needed.

DECLARE @Name varchar( -)    DECLAREAuthors_cursorCURSOR  for    SELECT [Name]  fromSysObjectsWHEREXType='U' ORDER  byIDOPENAuthors_cursorFETCH NEXT  fromAuthors_cursor into @Name     while @ @FETCH_STATUS = 0   BEGIN          /*--fill factor: 90, between 60-90 and 100, the query performance is best, but inserting the data causes the index page migration to affect the performance of the modification.*/DBCCDbreindex (@name,"', -)  FETCH NEXT  fromAuthors_cursor into @Name   END    deallocateAuthors_curso

SQL Server Rebuild Index