SQL Server Indexes and views

Ø Index

1. What is an index

An index is a list of data in a data table and a corresponding storage location that can be used to increase the speed of finding data in a table or view.

2. Index classification

the indexes in the database are divided into two main categories: Clustered and nonclustered indexes . SQL Server 2005 also provides unique indexes, indexed views, full-text indexes, XML indexes, and so on. clustered and nonclustered indexes are the most basic types of indexes in the database engine and are the basis for understanding other types of indexes.

# Clustered index (physically cited)

A clustered index is the exact same order in which the data rows in the value table are stored and the indexes are stored in the same order. The clustered index rearranges the data inserted by the user into the table based on the physical address of the index order, so that only one clustered index can be created per table. Clustered indexes often create columns that are frequently searched in tables or sequentially accessed. By default, a PRIMARY key constraint automatically creates a clustered index.

# Nonclustered Indexes

A nonclustered index does not alter the physical storage location of data columns in a table, data is stored separately from the index, and the address pointed to by the index is associated with the data in the table.

Nonclustered indexes do not change the location of physical rows in the table, and indexes can use nonclustered indexes in the following situations:

First, if the data uniqueness of a field is higher

Second, if the amount of data obtained by the query is less

Differences in clustered and nonclustered indexes:

Clustered index	Nonclustered indexes
Each table allows only one clustered index to be created	A maximum of 249 nonclustered indexes can be
Physically rearrange the data in the table to conform to the index constraints	Create a list of key values that point to the location of the data in the data page
Columns for finding data frequently	Columns used to find a single value from a table

# Other types of indexes

In addition to the above indexes, there are the following types of indexes:

A, unique index: If you want the index keys to be different, you can create a unique index. Both clustered and nonclustered indexes can be unique indexes.

B. Contains a new column index: the maximum number of indexed columns is 16, and the highest value for the total number of bytes in the indexed column is 900. If the total number of bytes in multiple columns is greater than 900, and you want to include indexes in these columns, you can use the included column index.

C, view index: Provides the efficiency of the view query, the index of the view can be physical, that is, the result set is permanently stored in the index, you can create a view index.

d, XML index: is an indexed form associated with XML data and is a split durable representation of an XML binary BLOB.

E, full-text indexing: A special type of markup-based functionality that helps search for assigned words in a string.

3. Create an index

--Grammar--Unique : Single index--Clustered: Clustered index--noclustered: Nonclustered indexes--fillfactor: Fill factor size with a range of 0-100 Direct, representing the percentage of space that the index page fills upCreate [Unique] [Clustered | noclustered]Indexindex_name ontable_name (column_name ...)[With fillfactor=x]--General IndexCreate IndexIdx_stu_name onstudent (name);--Federated IndexCreate Unique Clustered IndexIdx_uqe_clu_stu_name_age onStudent (name, age);--Nonclustered IndexesCreate nonclustered IndexIdx_cid onStudent (CID) with FillFactor =  -;//Fill Factor--Clustered IndexCreate Clustered IndexIdx_sex onstudent (sex);--Clustered IndexCreate Unique IndexIdx_name onStudent (name);

4. The appropriate column to create the index

Indexes can be created when a column of a database is used frequently for database queries, or when the column is used for database sorting

5. Columns that are not suitable for index creation

If there are several different values in the column, or if the table contains only a few rows of values, it is not recommended to create an index for it. Because the index spends more time searching the data than in the table, it takes longer to search for the conversation.

Ø View

1. What is a view

A view is a virtual data table in which data records are obtained from the query results of a query statement.

2. CREATE VIEW guidelines

To create a view, consider the guidelines:

# The name of the view must follow the rules of the identifier, which cannot be the same as the name of the schema's table

# You can create views on other views. Nested views are allowed, but not more than 32 layers in a nested set. Views can have a maximum of 1024 fields

# You cannot associate a rule with the default definition in a view

# View queries cannot contain COMPUTE clauses, COMPUTE by clauses, or into keywords

# A query that defines a view cannot contain an ORDER BY clause unless there is a TOP clause in the SELECT list of a SELECT statement

The name of each column in the view must be specified in the following cases:

# How columns in a view are derived from arithmetic expressions, built-in functions, or constants

# There are two or more columns with the same name in the view (usually because the view definition contains joins, so the same name comes from two or more different columns)

# You want a column in the view to specify a different name from its original column (you can also rename the column in the view). The view column inherits the data type of the original column, regardless of whether it is renamed

3. Create, modify, and encrypt views

--Create a ViewCreate ViewV_stu asSelectID, name, age, sex fromstudent;--Modify a ViewAlter ViewV_stu asSelectID, name, sex fromstudent;Alter Viewv_stu (number, name, gender) asSelectID, name, sex fromStudent--Encrypted ViewCreate ViewV_student_info withEncryption//Encrypt asSelectID, name, age fromStudent

SQL Server Indexes and views