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 basic types of indexes in the database engine and are the basis for understanding other types of indexes.

# Clustered Index

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 physically rearranges the data that the user inserts into the table based on 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 if you want to include indexes on these reap, 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 feature to help search for assigned words in a string

3. Create an index

Grammar

Create [unique] [clustered | noclustered]

Index Index_name

On table_name (column_name ...)

[With Fillfactor=x]

Unique Unique index

Clustered Clustered Index

noclustered Nonclustered Indexes

The fillfactor fill factor size, in the range of 0-100, represents the percentage of space that the index page fills up.

Example

if (exists (SELECT * from sys.indexes where name = ' Idx_stu_name '))

    Drop INDEX Student.idx_stu_name

Go

CREATE INDEX Idx_stu_name

On

Student (name);

--Federated Index

if (exists (SELECT * from sys.indexes where name = ' idx_uqe_clu_stu_name_age '))

    Drop INDEX Student.idx_uqe_clu_stu_name_age

Go

Create unique clustered index Idx_uqe_clu_stu_name_age

On student (name, age);

if (exists (SELECT * from sys.indexes where name = ' idx_cid '))

    Drop INDEX Student.idx_cid

Go

if (exists (SELECT * from sys.indexes where name = ' idx_cid '))

    Drop INDEX Student.idx_cid

Go

--Nonclustered indexes

Create nonclustered index Idx_cid

On

Student (CID)

with fillFactor =;    --fill factor

--Clustered index

if (exists (SELECT * from sys.indexes where name = ' Idx_sex '))

    Drop INDEX Student.idx_sex

Go

Create clustered Index Idx_sex

On

Student (sex);

--Clustered index

if (exists (SELECT * from sys.indexes where name = ' Idx_name '))

    Drop INDEX Student.idx_name

Go

Create UNIQUE index Idx_name

On

Student (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 the 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 a View

--Create a view

if (exists (SELECT * from sys.objects where name = ' V_stu '))

    Drop View V_stu

Go

CREATE VIEW V_stu

As

Select ID, name, age, and sex from student;

4. Modify the View

Alter VIEW V_stu

As

Select ID, name, sex from student;

Alter VIEW V_stu (number, name, gender)

As

    Select ID, name, sex from student

Go

SELECT * from V_stu;

SELECT * from Information_schema.views;

5. Encrypted view

--Encrypted view

if (exists (SELECT * from sys.objects where name = ' V_student_info '))

    Drop View V_student_info

Go

CREATE VIEW V_student_info

With encryption--encryption

As

    Select ID, name, age from student

Go

--view_definition is null

Where table_name like ' v_stu ';

SQL Server Indexes and views