The eight implementations of MSSQL view and index

Database developers often need to improve the performance of queries. SQL Server 2008 allows indexes to be implemented to reduce the execution time of queries. Also, you can restrict the browsing of data to different users by implementing the view This chapter discusses how to create and manage indexes and views. It also discusses how to implement full-text indexing to improve data indexing. Focus ? Creating and Managing views ? Creating and Managing Indexes Preview lessons ? Format and considerations for creating views ? Format and considerations for creating indexes

View

A view is a virtual table, a table that is exported from one or more tables or views whose structure and data are based on queries on the table.

The advantages and effects of using views include:

The view allows the user to be concerned with only certain data that is of interest to them and the specific tasks they are responsible for, while those that are not needed or useless are not displayed in the view.

The L view greatly simplifies the operation of the user on the data.

The L view allows different users to see different or identical datasets in different ways.

In some cases, because the amount of data in the table is too large, the table is often designed to split the tables horizontally or vertically, but changes in the structure of the tables adversely affect the application. The view can be used to reorganize the data so that the outside mode remains intact and the original application can still reload the data through the view.

The view provides a simple and effective security mechanism.

? How to create a view:

SQL Server 2008 provides several ways to create views:

L Create views with SQL Server management platform;

L Create a view with the CreateView command in Transact-SQL statements;

L Use the View template of the SQL Server Management platform to create the view.

L should be aware of the following when creating a view:

The view can only be created in the current database, and only 1024 columns can be referenced in the view, and the limit of the number of records in the view is determined only by the number of records in its base table.

L If the base table or view referenced by the view is deleted, the view can no longer be used until a new base table or view is created.

L If a column in a view is a function, mathematical expression, constant, or column from more than one table and has the same name, you must define a name for the column.

L cannot create an index on a view and cannot reference it in the definition of a rule or trigger.

When querying data through a view, SQL Server checks to make sure that all database objects involved in the statement are present, that each database object is valid in the context of the statement, and that the data modification statement cannot violate the data integrity rules.

The name of the view must follow the rules for identifiers and must be unique for each user. In addition, the name must not be the same as the name of any table that the user owns.

Create a view with the SQL Server Management platform

The steps to create a view with the SQL Server Management platform are as follows:

In SQL Server Management platform, expand the specified server, open the database folder where you want to create the view, select the specified database, right-click the database icon, and select the new → view option from the shortcut menu that appears, 7-1. The Add table, view, Function dialog box appears. As shown in 7-2.

Create a view with the CREATE VIEW command in a Transact-SQL statement

Create a view using the CREATE view in a Transact-SQL statement with the following syntax:

CREATE VIEW [schema_name] view_name [(column[,... n])]
[With <view_attribute> [,... n]]
As
Select_statement
[With CHECK OPTION]
< View_attribute >:: =
{encryption| Schemabinding| View_metadata}

Create a view with the CREATE VIEW command in a Transact-SQL statement

Example 8-1 selects some of the fields and records in table S and SC to create a view, and restricts the records in table S to be a collection of records in the computer system, and the view is defined as view_s.

The list of procedures is as follows:

CREATE VIEW view_s

As

Selects.name,s.age,s.sex,

Sc.cno,sc.scorefrom S,SC

where S.sno=sc.snoand s.dept= ' computer '

Example 8-2 creates a view that contains complex queries.

The list of procedures is as follows:

CREATE Viewexampleview

With SCHEMABINDING

As

SELECT Sno,sum (Score) as Sumscore, COUNT (*) as Countcol from SC

GROUP by Sno

Create a view with the CREATE VIEW command in a Transact-SQL statement

Example "8-3" creates a view that contains the operation of a string.

The list of procedures is as follows:

CREATE Viewv_shyjl (Shyxh, Shj, XM, Jglb, PHR, Bzh) as

Select

Distinctshyxh,substring (shj,1,10) + "" +zhi,

T01_shbshy.xm,t012_shyjl.jglb,t012_shyjl.phr,t012_shyjl.bzh

From T01_shbshy, T012_SHYJL

Wheret012_shyjl.xm=t01_shbshy.xm

Where the SHJ column is taken from the base table and added with an empty string of two columns. These provide great convenience for flexible operations between fields in the base table, allowing us to customize the data that meets our requirements.

Create a view with a template

Using a view template makes it easy to create a view with the following steps:

(1) In SQL Server Management platform, select the "Template Explorer" option in the View menu.

(2) Select the CREATE View option in the Template Explorer option that appears.

(3) Follow the prompts to enter the view name, and after the SELECT statement executes this statement, the view can be created.

? Modify, delete, and rename views

1. Modify the View

2. Renaming a view

3. View information, delete view

Modify a View

There are two ways to modify a view:

(1) in the SQL Server Management platform, right-click the view you want to modify, choose Design View from the popup shortcut menu, and the View Modify dialog box appears. The dialog box is the same as the dialog box when you created the view, and you can modify the view by creating the view.

(2) Use the Alterview statement to modify the view, but first you must have permission to use the view before you can use the ALTER VIEW statement, which has the following syntax:

ALTER VIEW view_name

[(column[,... n])]

[WITH Encryption]

As

Select_statement
[With CHECK OPTION

Example "8-4" modifies the view v_employees, adds a new field employees.salary to the view, and defines a new field name E_salary.

The list of procedures is as follows:

Alter View dbo.employees (Number,name,age,e_salary)

As

Select Number,name,age,salary

From Employees

Where Name= ' Zhang San '

? There are two ways to rename a view by renaming the views:

(1). In the SQL Server Management platform, select the view for which you want to modify the name, and right-click the view and choose the Rename option from the shortcut menu that appears. Or you can modify the name of the view by clicking it again on the view. Then the name of the view becomes an input state, and you can enter the new view name directly.

(2). Use the system stored procedure sp_rename to modify the name of the view, the syntax for this procedure is as follows:

Sp_rename Old_name,new_name

Example "8-5" renames the View V_all to V_part.

The list of procedures is as follows:

Sp_rename V_all,v_part

? Viewing View information

You can use system stored procedures sp_help display view features, use sp_helptext to display the definition of a view in a system table, and use sp_depends to display the objects on which the view depends. You can easily display view property information by using SQL Server Query Analyzer. The diagram shows the CREATE statement that displays the view using the sp_helptext stored procedure.

Use the SELECT statement or the SQL Server Management platform to view the output data for the view. In the SQL Server Management platform, right-click the name of a view and choose the Open view option from the popup shortcut menu, which displays the output data for that view in SQL Server Management platform, as shown in 7-9.

? Delete a view

For views that are no longer in use, you can use the SQL Server Management platform or the Dropview command in the Transact-SQL statement to remove it.

Delete view using the Transact-SQL statement drop view, which has the following syntax:

DROP VIEW {view_name} [,... N]

You can use this command to delete multiple views at once, separated by commas between the names of the views you want to delete.

Example "8-6" deletes both view v_student and V_teacher.

The list of procedures is as follows:

Drop View V_student,v_teacher

? Modifying records through a view

When you use views to modify data, you need to be aware of the following points:

When you modify data in a view, you cannot modify two or more base tables at the same time, and you can modify views based on two or more base tables or views, but each modification affects only one base table.

L cannot modify fields that are computed, such as fields that contain calculated values or aggregate functions.

L If you specify the WITH CHECK option when creating a view, you must ensure that the modified data meets the scope of the view definition when you use the view to modify the database information.

When you execute the update, delete command, the data that is deleted and updated must be included in the result set of the view.

L If the view references more than one table, you cannot delete the data with the Delete command, and if you use the Update command, the updated column must belong to the same table as the insert operation.

Inserting data records

Example "8-7" creates a new view v_employees based on table employees.

The list of procedures is as follows:

Create View v_employees (number, name, age, sex, salary)

As

Select number, name, age, sex, salary

From Employees

Where Name= ' Zhang San '

Execute the following statement to add a new data record to the table employees:

Insert into V_employees

Values (001, ' Lili ', +, ' m ', 2000)

Example "8-8" first creates a view v_employee2 with a restriction condition of >2000, then inserts a record that does not meet the restrictions, and then retrieves the view and table with the SELECT statement.

The list of procedures is as follows:

CREATE VIEW V_employee2

As

SELECT * FROM Employee

where wage >2000

Go

INSERT INTO V_employee2

VALUES (002, ' King ', ', ' F ', 1000)

Go

SELECT * FROM Employee

Go

SELECT * FROM V_employee2

Go

Example "8-9" adds the WITH CHECK option on the basis of example 8-8.

The list of procedures is as follows:

CREATE VIEW V_employee3

As

SELECT * FROM Employee

where wage >2000

With CHECK option

Go

INSERT INTO V_employee3

VALUES (002, ' King ', ', ' F ', 1000)

Go

SELECT * FROM V_employee3

Go

Running the program will display the following error message:

Server:msg 550,level, State 1, line 1

The Attemptedinsert or update failed because the target view either specifies with Checkoption or spans a view that Specif IES with CHECK option and one or more rowsresulting from the operation do not qualify under the CHECK option constraint.

The statement hasbeen terminated.

updating and deleting data records

You can use views to update data records, but it should be noted that only the base tables in the database are updated. Use the view to delete records, you can delete records from any base table, and delete the records directly using the DELETE statement. It should be noted, however, that you must specify fields that are defined in the view to delete records.

Example "8-10" creates a view v_employees based on table employees, and then modifies the records in the table employees through the view.

The list of procedures is as follows:

CREATE VIEW V_employees

As

SELECT * FROM Employees

Update V_employees

Set Name= ' Zhang ran '

Where Name= ' Zhang San '

Example "8-11" uses the view V_employees to delete a record named Zhang ran in table employees.

The list of procedures is as follows:

Delete from V_employees

Where Name= ' Zhang ran '

Index

? Knowledge Index:

An index is a physical structure associated with a table or view that can be used to speed up the retrieval of data rows from a table or view.

Why do you create an index? This is because creating an index can greatly improve the performance of the system. First, by creating a unique index, you can guarantee the uniqueness of each row of data. Second, the speed of data retrieval can be greatly accelerated, which is the main reason for indexing. Thirdly, the connection between tables and tables can be accelerated, particularly in terms of achieving referential integrity of the data. Finally, when using the order BY and GROUP BY clauses for data retrieval, the time to group and sort in the query can also be significantly reduced. By using an index, you can improve the performance of your system by using an optimized hidden device in the process of querying.

Because of these reasons, you should add indexes to the table

Question one:

Maybe someone will ask. There are so many advantages to adding indexes, so why not build an index on each column in the table?

Although indexes have many advantages, it is unwise to add indexes to each column in the table. This is because increasing the index also has its downside.

First, it is time-consuming to create indexes and maintain indexes.

Second, the index needs to occupy physical space, in addition to the data table to occupy the data space, each index also occupies a certain amount of physical space. If you are building a clustered index, you will need more space.

Thirdly, when the data in the table is added, deleted and modified, the index should be maintained dynamically, thus reducing the maintenance speed of the data.

Question two:

Where is the index created?

Create indexes on columns that often need to be searched;

Create an index on the primary key;

Create indexes on columns that are frequently used for connections, that is, indexes on foreign keys;

Create an index on a column that often needs to be searched by scope (because the index is sorted and its specified range is continuous);

Create indexes on columns that often need to be sorted (because the index is sorted so that the query can use the sorting of the index to speed up the sorting query time);

Create an index on a column that is often used in a WHERE clause.

? Types and characteristics of indexes:

There are two basic types of indexes in the Microsoft SQL Server 2008 System: Clustered and nonclustered indexes.

In addition, there are unique indexes, indexed views, full-text indexes, and XML indexes. In these index types, clustered and nonclustered indexes are the basic types of indexes in the database engine and are the basis for understanding unique indexes, indexed views,

This section focuses on these two types of indexes.

Clustered index

A clustered index is an index in which the physical order of a data table is the same as the index, and a nonclustered index is an index in which the physical order of the data table is different from the index order.

The leaf and non-leaf levels of a clustered index constitute a special type of B-tree structure. Each page in a B-tree structure is called an index node. The lowest-level node of an index is a leaf-level node.

In a clustered index, the data page of a table is a leaf level, and the index page above the leaf level is non-leaf. In a clustered index, the order of the pages is ordered. You should create clustered indexes on columns that are frequently searched in the table or on columns that are accessed sequentially. Where the root_page for specifying the first page address information for the clustered index is from the Sys.system_internal_allocation_units system view. Structure of the clustered index

Nonclustered indexes:

A nonclustered index has the same B-tree structure as a clustered index, but in a nonclustered index, the data rows of the underlying table are not sorted and stored in the order of the nonclustered keys, and the leaf level of the nonclustered index is made up of index pages rather than data pages.

A nonclustered index can be defined either on a clustered index of a table or view, or on the heap of a table or view. Each index row in a nonclustered index is comprised of a nonclustered key value and a row locator that points to a clustered index or a data row in the heap that contains the key value. If there is no clustered index (heap) in the table or view, the row locator is a pointer to the row RID, and the RID is generated by the file identifier ID, page number, and number of rows on the page. If there is a clustered index on the table or view, the row locator is the clustered index key for the row. Structure of nonclustered indexes

Other types of indexes:

In addition to clustered and nonclustered indexes, the Microsoft SQL Server 2008 system provides a number of other types of index or index representations, including uniqueness indexes, inclusive column indexes, indexed views, full-text indexes, and XML indexes.

When you create a clustered or nonclustered index, the index keys can be different or contain duplicate values. If you want the index keys to be different, you must create a unique index. Of course, when you create a clustered or nonclustered index, you can specify that the index is unique in nature. This uniqueness is associated with the previously mentioned primary KEY constraint, and to some extent, the primary KEY constraint equals the unique clustered index.

If the total number of bytes for multiple columns is greater than 900 bytes and you want to include these columns in the index, you can use the included column index.

If you want to improve the query efficiency of a view, you can physically index the view, which means that the result set is permanently stored in the index.

A full-text index is a special type of tag-based index that is created, used, and maintained by Microsoft SQL Server's full-text engine service to provide users with efficient search of complex words in string data. The structure of this index differs from the B-tree structure used by the database engine for clustered or nonclustered indexes.

An XML index is an indexed form associated with XML data and is a split durable representation of an XML binary BLOB. XML indexes can also be divided into primary and secondary indexes.

How to access data:

When you access data in a database, you can use two methods, table scan and index lookup.

The first method is a table scan, which means that the system places the pointer on the data page where the table header data resides, and then scans the data page in the order in which it is sorted, and then pages back and forth from the previous page until all the records in the table are scanned. When scanning, if you find a record that matches your query criteria, select the record. Finally, the records that are all selected to match the conditions of the query statement are displayed.

The second method is to use an index lookup. An index is a tree structure that stores a keyword and a pointer to a data page that contains the record where the keyword is located. When you use an index lookup, the system will follow the index's tree structure to find the records that match the query criteria based on the keywords and pointers in the index. Finally, all the records found matching the conditions of the query statement are displayed. When the system looks up along the index value, the search value is used to compare with the index value. This comparative judgment continues until the following two conditions are met:

--The search value is not greater than or equal to the index value.

--The search value is greater than or equal to the last value on the index page.

? To create an index:

In a Microsoft SQL Server 2008 system, you can either create an index directly or create an index indirectly. When you create an index directly, you can use the CREATE INDEX statement, or you can use the Graphics tool.

The methods for creating an index are:

Direct methods and Indirect methods:

The way to create an index directly is to create an index directly using commands and tools.

Indirectly creating an index creates an index by creating additional objects, such as defining a PRIMARY KEY constraint or a uniqueness constraint in a table, and also creating an index. Although both of these methods can create indexes, the specifics of what they create are different.

Creating an index using the CREATE INDEX statement or using the Make Indexing Wizard is the most basic way to create an index, and this method is most flexible and can be customized to create an index that fits your needs.

You can also create an index indirectly by defining a primary KEY constraint or a uniqueness constraint.

CREATE Index statement

CREATE [UNIQUE] [CLUSTERED | Nonclustered] INDEX index_name

On table_or_view_name (column [ASC | DESC] [,... N])

[INCLUDE (column_name[, ... n])]

[With

(Pad_index = {on | OFF}

| FILLFACTOR = FILLFACTOR

| sort_in_tempdb = {on | OFF}

| Ignore_dup_key = {on | OFF}

| Statistics_norecompute = {on | OFF}

| drop_existing = {on | OFF}

| ONLINE = {on | OFF}

| Allow_row_locks = {on | OFF}

| Allow_page_locks = {on | OFF}

| MAXDOP = max_degree_of_parallelism) [, ... n]]

On {partition_schema_name (column_name) | filegroup_name | default}

Example 8-12 creating a unique clustered index using the CREATE INDEX statement

? Index maintenance:

After the index has been created, the index page has been broken because of data additions, deletions, updates, and so on, in order to improve the performance of the system, the indexes must be maintained.

These maintenance include viewing fragment information, maintaining statistics, analyzing index performance, and deleting rebuild indexes.

To view index statistics:

Index statistics are the underlying data that the query optimizer uses to analyze and evaluate queries and determine the optimal query plan. Generally, users can access the statistics of the specified index in a common way. One way is to use the Dbccshow_statistics command, and the other is to use a graphical tool.

The DBCC show_statistics command can be used to return statistics for specific objects of a specified table or view, which can be indexes, columns, and so on.

"Example 8-17" View index statistics

1. Explain the working process of cluster index

2. Which system function is used to detect fragmentation on the index of the table

3. Which option is used for CREATE VIEW statement encrypted view text

4. What is the difference between a standard view and an indexed view?

5. Why we need to create an index on an attempt

Summary

1, the index is created to enhance the performance of the query.

2. There are two types of indexes: Clustered index and non-clustered index.

3. Use the CREATE INDEX statement.

4, the cluster index is established on the attribute, the value of the attribute is unique, and does not change frequently. The data is physically sorted in the cluster index.

5. A view is a virtual table that produces data from one or more tables that are called base tables or containing tables.

6. View as a security mechanism, while protecting the data in the base table.

7. When using a view, SQL Server only allows data to be modified in one of the included tables, even if the view is generated by multiple included tables.

The eight implementations of MSSQL view and index