SQL Server indexes and views in MySQL database Learning Series 4

1. What is Index is the list of data in the data table and the corresponding storage location. Using indexes can speed up data search in the table or view. 2. Indexes in Index classification databases are classified into clustered indexes and non-clustered indexes. SQLServer2005 also provides unique indexes, index views, full-text indexes, and xml indexes. Aggregation Cable

1. What is Index is the list of data in the data table and the corresponding storage location. Using indexes can speed up data search in the table or view. 2. Indexes in Index classification databases are classified into clustered indexes and non-clustered indexes. SQL Server 2005 also provides unique indexes, index views, full-text indexes, and xml indexes. Aggregation Cable

1. What is an index?

An index is a list of data in a data table and corresponding storage locations. Using an index can speed up data search in a table or view.

2. Index classification

Indexes in databases are classified into clustered indexes and non-clustered indexes. SQL Server 2005 also provides unique indexes, index views, full-text indexes, and xml indexes. Clustered and non-clustered indexes are the basic types of indexes in the database engine and the basis for understanding other types of indexes.

# Clustered Index

Clustered index indicates that the physical storage order of data rows in the value table is exactly the same as that of the index. Clustered indexes physically rearrange the data inserted into the table according to the index order. Therefore, each table can only create one clustered index. Clustered indexes are often created in columns that are frequently searched or accessed in order in a table. By default, the primary key constraint automatically creates a clustered index.

# Non-clustered Index

Non-clustered indexes do not change the physical storage location of Data columns in the table. The data is stored separately from the index. The addresses pointed to by the index are related to the data in the table.

Non-clustered indexes do not change the location of physical rows in the table. You can use non-clustered indexes in the following situations:

1. If the data uniqueness of a field is relatively high

2. If the queried data volume is small

Difference between clustered index and non-clustered index:

Clustered Index	Non-clustered Index
Only one clustered index can be created for each table.	A maximum of 249 non-clustered indexes can be created.
Physically rearranging the data in a table to meet the index Constraints	Creates a list of key values that point to the location of the data on the data page.
Columns used for frequent data searches	Column Used to find a single value from the table

# Other types of Indexes

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

A. Unique index: If you want different index keys, you can create a unique index. Both clustered and non-clustered indexes can be unique indexes.

B. New column indexes: the maximum number of index columns is 16, and the maximum number of bytes in the index column is 900. If the total number of bytes in multiple columns is greater than 900, you can use the include column index if you want to include indexes in these inferior types.

C. View index: it provides View query efficiency and physical view indexes. That is to say, the result set is permanently stored in the index and view indexes can be created.

D. XML index: it is the index form associated with the xml data, and is the sharded persistent representation of the XML binary blob.

E. Full-text index: a special type of tag-Based Functional function to help search for assigned words in strings

3. Create an index

Syntax

create [unique] [clustered | noclustered]

index index_name

on table_name (column_name ...)

[with fillfactor=x]

Unique index

Clustered Index

Noclustered non-clustered Index

The size of the fillfactor fill factor. The value ranges from 0 to, indicating the percentage of space occupied by the index page.

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);

-- Joint 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

-- Non-clustered Index

create nonclustered index idx_cid

on

student (cid)

With fillFactor = 30; -- 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. Suitable columns for index creation

When a database column is frequently used for database queries, or when this column is used for database sorting, it can be created as an index.

5. Columns not suitable for index creation

If the column contains several different values or the table contains only a few rows of values, it is not recommended to create an index for it. Because the index takes a longer time to search for data than to search for data row by row in a table.

? View

1. What is a view?

A view is a virtual data table. The data records in the data table are obtained by a query statement.

2. Create a view Criterion

To create a view, consider the following guidelines:

# View names must follow the rules of identifiers. They must not be the same as the names of tables in the schema.

# You can create views for other views. Nested views are allowed, but no more than 32 layers can be nested. A view can have a maximum of 1024 fields.

# You cannot associate rules with default definitions in views.

# View query cannot contain the compute clause, compute by clause, or into keyword

# The query of the definition view cannot contain the order by clause, unless there is a top clause in the select statement selection list

In the following cases, you must specify the name of each column in the View:

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

# Two or more columns in the view have the same name (usually because the view definition contains a join, two or more different columns have the same name)

# You want the column in the view to specify a name different from the original column (you can also rename the column in the view ). The View column inherits the Data Type of the original column no matter whether it is renamed or not.

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, sex from student;

4. Modify the view

alter view v_stu

as

select id, name, sex from student;

Alter view v_stu (No., 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

select * from information_schema.views 

where table_name like 'v_stu';