SQL Server CREATE index

What is the index of the Chinese Dictionary of the Directory page (index) analogy: Just as Chinese characters are stored by page, the data records in SQL Server are stored on pages, and the capacity of each page is typically 4K. In order to speed up the search, Chinese word (word) code generally have by pinyin, strokes, radicals and other categories of the directory (index), we can choose by pinyin or strokes to find the way to quickly find the desired word (word). Similarly, SQL Server allows users to create indexes in a table, specifying that they are pre-ordered by a column, which greatly improves query speed. Data in SQL Server is also stored by page (4KB) • Index: is an internal method of SQL Server orchestration data. It provides a way for SQL Server to orchestrate query data. • Index page: The data page where the index is stored in the database; The index page is similar to a catalog page sorted by phonetic or stroke in the Chinese word (word) dictionary. • The role of indexing: By using indexes, you can greatly improve the retrieval speed of databases and improve database performance. Index type • Unique index: Unique index does not allow two rows to have the same index value • Primary key index: Defining a primary key for a table automatically creates a primary key index, which is a special type of unique index. The primary key index requires that each value in the primary key be unique and cannot be empty • Clustered index (Clustered): 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 • nonclustered index (Non-Clustered): Nonclustered indexes Specify the logical order of the tables. The data is stored in one place, the index is stored in a different location, and the index contains a pointer to the location of the data store. can have multiple, less than 249 index types: Again, in the Chinese dictionary, I hope you can understand the two concepts of clustered and nonclustered indexes. Unique index: A unique index does not allow two rows to have the same index value. If duplicate key values exist in the existing data, most databases do not allow the newly created unique index to be saved with the table. The database also rejects this data when the new data duplicates the key values in the table. For example, if you create a unique index on the learner ID number (STUID) column in the Stuinfo table, the ID number of all learners cannot be duplicated. Tip: A unique constraint is created and a unique index is created automatically. Although a unique index can help you find information, it is recommended to use a PRIMARY KEY constraint or a unique constraint for best performance. Primary KEY index: Defining a primary key for a table in a database diagram automatically creates a primary key index, which is a special type of unique index. The primary key index requires that each value in the primary key be unique. When a primary key index is used in a query, it also allows for fast access to the data. Clustered index (Clustered Indexin 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 page 1,2，3... 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. Nonclustered indexes (Non-Clusteredif 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. The clustered index is more than the nonclustered index (nonclustered Index) has faster data access speeds. For example, an index sorted by stroke is a nonclustered index, "1"The number of words (words) to be drawn may be more than"3"The number of words (words) to be drawn corresponds to the size of the page (back). Tip: In SQL Server, a table can only create 1 clustered indexes, multiple nonclustered indexes. Sets a column primary key, which defaults to how the clustered index creates an index using T-SQL statement syntax for creating indexes:CREATE [UNIQUE] [clustered| Nonclustered]     INDEXindex_name ontable_name (column_name ...) [With fillfactor=x]Q Unique represents a unique index, optional qCLUSTERED, nonclustered represents a clustered or nonclustered index, and optional Q fillfactor represents a fill factor, specifying a value between 0 and 100 that indicates the percentage of space that the index page fills up Create an index on the Writtenexam column of the Stumarks table: UseStudbGOIF EXISTS(SELECTName fromsysindexesWHEREName= 'Ix_writtenexam')   DROP INDEXStumarks.ix_writtenexam/*--Written test column create nonclustered index: Fill factor is 30%--*/CREATE nonclustered INDEXIx_writtenexam onStumarks (Writtenexam) with FILLFACTOR=  -GO/*-----Specifies the query by index ix_writtenexam----*/SELECT *  fromStumarks (INDEX=Ix_writtenexam)WHEREWrittenexambetween  -  and  -Although we can specify which index SQL Server will query for data, we do not normally need to specify it manually. SQL Server automatically optimizes the query based on the index that we create. Advantages and disadvantages of indexes – speed up access – enhance row uniqueness • Disadvantages – Indexed tables require more storage space in the database – commands that manipulate the data require longer processing time, as Guidelines for creating indexes for which they need to be updated • Select indexed columns According to the following criteria. – This column is used for frequent searches – The column is used to sort data • Do not use the following column to create an index: – The column contains only a few different values. – The table contains only a few rows. Creating an index on a small table may not be a good deal because SQL Server spends more time searching for data in the index than it does in a row -by-line search in the table

SQL Server CREATE index