Basic usage of views and Indexes

ArticleDirectory

• Note:

View Concept

View is a transformation of the original database data, and is another way to view the data in the table.
Method. You can think of a view as a moving window through which you can see the data you are interested in.

A view is obtained from one or more actual tables whose data is stored in the data
Library. The tables used to generate a view are called the base tables of the view. A view can also be
Generated in another view.

The view definition exists in the database, and the data related to this definition is not saved again
In the database. The data displayed in the view is stored in the base table.

The view looks like a physical table of a database, and its operations are the same as any other tables.
Same. When data is modified through a view, the data in the base table is actually changed.
The changes to the base table data are automatically reflected in the view generated by the base table. Because of the logic
Some views can modify the corresponding base table, while others cannot (only query ).

Role of a view

Simplicity. What you see is what you need. A view not only simplifies User Data Management
To simplify their operations. Frequently Used queries can be defined as views,
Therefore, you do not have to specify all the conditions for each subsequent operation.

Security. Users can only query and modify the data they can see through the view. Quantity
Other data in the database is invisible or not available. Database authorization commands enable
The user's retrieval of the database is restricted to specific database objects, but cannot be authorized to the database feature
Fixed Rows and specific columns. Through views, users can be restricted to different subsets of data:

• The permission can be restricted to a subset of rows in the base table.
• The permission can be restricted to a subset of columns in the base table.
• The permission can be restricted to the row and column subsets of the base table.
• The permission can be restricted to the rows restricted by the connection of multiple base tables.
• The permission can be restricted to the Statistical Summary of data in the base table.
• The permission can be restricted to a subset of another view, or a subset of some views and merged base tables.

 

Why use a view?

Because a view is a convenient method, it only gives users access to a table.

The view can restrict the returned records and available fields. All authorized users

Access Permissions are not restricted by the table itself. The view is also used

Hide complex statements and display only a simple table result set to end users.

Note:

1. In the create view statement, the order by, compute, or compute by clause cannot contain the into keyword.
2. The number of columns in the base table referenced by creating a view can be up to 1024 columns.
3. Creating a view cannot refer to a temporary table
4. Avoid creating views using external connections whenever possible
5. In a batch statement, the create view statement cannot be used together with other TRANSACT-SQL statements

Use the create view in the T-SQL statement to create a view.

 
Create ViewView_name[(Coiumn_list)]AsSelect_statement (SelectStatement)
 

The parameter meanings in creating a view are as follows:

• View_name: specify a name for the newly created view.
• Coiumn_list: name of the column in the current table. If this option is selected, the column name in the current base table is automatically used.
• Select_statement: defines the SELECT statement for retrieving travel and columns in one or more tables.

Note: Select statements in the view cannot contain order clauses or into clauses. In addition, temporary tables cannot be referenced in queries.

Instance In the Internet cafe billing system, we create a view of the on-board record table

 /*  Check whether the view named view_recordinfo exists. The view is stored in the system table sysobjects.  */  If   Exists ( Select   *   From SysobjectsWhere Name =  '  View_recordinfo  '  )  Drop   View View_recordinfo --  Delete this view  Create   View  View_recordinfo  As       Select Record Number = Recordid, membership card number = CardNumber, computer number = P. PCID, Machine Time = Begintime, down time = Endtime, machine cost =  Free  From Recordinfo R Join Cardinfo C On R. cardid =  C. cardid  Join Pcinfo P On R. cardid=  P. PCID  Go  /*  Using a view, a view is a virtual table and can be used like a physical table.  */  Select   *   From View_recordinfo

Advantages of using a view

• Centralized view: This allows users to focus only on specific data they are interested in and the specific tasks they are responsible.
• Simplified operations: views greatly simplify user data operations.
• Custom Data: views allow different users to view different or identical datasets in different ways.
• Security: A view can be used as a security mechanism. Users can view and modify only the data they can view. Other databases or tables are neither visible nor accessible.

The index concept index is a separate, physical database structure. It is a list of values contained in a table in the database, indicating the storage location of each value in the table. In SQL Server, indexes can be classified into clustered indexes, non-clustered indexes, unique indexes, composite indexes, view indexes, full-text indexes, and XML indexes.

• Unique Index: The unique index does not allow two rows to have the same index value;
• Clustered Index(Clustered): The physical order of each row in the table is the same as the logic (INDEX) Order of the key value. Each table can have only one;
• Non-clustered Index(Non-clustered): Non-clustered index specifies the logical sequence of the table. The data is stored in one location, and the index is stored in another location. The index contains a pointer to the data storage location. There can be more than 249.

How to create an index

Index creation syntax

Create [Unique] [Clustered | nonclustered]IndexIndex_nameOnTable_name (column_name ...)[With fillfactor = x]
 

 

Instance

In the Internet cafe billing system, we create an index for the user's query balance.

 Create   Nonclustered   Index  Index_cardinfo_cardbalance On  Cardinfo (cardbalance)  With     Fillfactor   =  40  Go  /*  Use indexes to specify index_cardinfo_cardbalance  */  Select   *   From  Cardinfo  With ( Index =  Index_cardinfo_cardbalance)  /*  Query the balance between 50 and 100 yuan  */    Where Cardbalance Between   50   And   100 

Advantages and disadvantages of Indexes

"Water can carry boat, it can also ride boat ",The same is true for indexes. Indexing helps improve the search performance, but too many or improper indexing will also lead to low system efficiency.

Because the user does not create an index in the table, more work is required for the database. Too many indexes may even cause index fragmentation.

Advantages of Indexes

• By creating a unique index, You can ensure the uniqueness of each row of data in the database table.
• This can greatly speed up data retrieval, which is also the main reason for creating an index.
• It can accelerate the connection between tables, especially for Data Reference integrity.
• When you use grouping and sorting clauses to retrieve data, you can also significantly reduce the time for grouping and sorting in queries.
• By using indexes, you can use the optimizer during the query process to improve system performance.

 

Index disadvantages

• It takes time to create and maintain indexes. This time increases with the increase of data volume.
• Indexes occupy physical space. In addition to data tables, each index occupies a certain amount of physical space. To create a clustered index, the required space is larger.
• When adding, deleting, and modifying data in a table, indexes must be maintained dynamically, which reduces the Data Maintenance speed.