View makes your IDC query statement simpler

When I first started using the view background as a data center charging system, the master said that the above view, stored procedure, and trigger should be used. I didn't feel much at the time, I don't know when to use it, and where to use it. Just a few days ago, when I made a query statement for two tables, I created a view myself, and then the result was queried. Then I succeeded. I am doing something

When I first started using the view background as a data center charging system, the master said that the above view, stored procedure, and trigger should be used. I didn't feel much at the time, I don't know when to use it, and where to use it. Just a few days ago, when I made a query statement for two tables, I created a view myself, and then the result was queried. Then I succeeded. I am doing something

View background

When I first started charging the data center, the master said that they should use the view, storage process, and trigger. I didn't feel much at the time, I don't know when to use it, and where to use it. Just a few days ago, when I made a query statement for two tables, I created a view myself, and then the result was queried. Then I succeeded. I am doing something too simple and wondering if I have done something wrong. I checked it online and read some blogs. It turns out that it is used to simplify database operations while improving the speed.

View introduction:

A view is a virtual table and an SQL statement stored in the database for query. The related data is not stored in the database. The data displayed in the view is still stored in the base table. A view looks very similar to a physical table in a database and operates on it like any other table. When you modify data through a view, the data in the base table is actually changed. On the contrary, the changes to the base table data are automatically reflected in the view generated by the base table. For logical reasons, some views can modify the corresponding base table, while others cannot (only query ).

View purpose:

-Simplicity: simplifies users' understanding of data and their operations. Frequently Used queries can be defined as views, so that 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. Other data in the database can neither be seen nor retrieved.

-Efficiency: It simplifies data operations and greatly improves the query speed.

-Independence: views can make applications and database tables independent to a certain extent.

How to Create a view?

There are two methods:

1. -- SDirectly create a view in ql Server Management Studio:

(1) Select "View" in the database and select "New View:

(2) Select the table for which you want to create a view:

(3) Select the fields to display information:

(4) directly generated query statements: <喎?http: www.2cto.com kf ware vc " target="_blank" class="keylink"> VcD4KPHA + icagicagicagidxpbwcgc3jjjpq = "http://www.2cto.com/uploadfile/Collfiles/20140623/2014062309113733.jpg" alt = "\">

(5) You can use it after saving the view name.

2 ,--Use the Transant-SQL statement to create a view

(1) SQL statement form for creating a view:

Where:

View_name: The name specified for the newly created view. The view name must comply with the identifier rules.

Column_name: the column name included in the view. You can also specify the column name in the Select statement.

Table_name: name of the base table of the view.

Select_statement: Select the columns into the view.

WHERE search_condition: conditions that must be met when the base table data enters the view.

With check option: forces all data modification statements executed through the view to comply WITH the conditions set in the view definition.

With encryption: encrypts the view definition.

(2) To create a view using an SQL statement:

-Compile the Select statement for creating a view.

-Test the select statement.

-Check whether the test results are correct and are as expected.

-Create a view.

(3) when creating a view, consider the following factors:

-The create view statement cannot contain the order by, compute, or compute by clause, nor contain the into keyword.

-The number of columns in the base table referenced by creating a view can be up to 1024.

-Temporary tables cannot be referenced when creating a view.

-In a batch processing statement, the create view statement cannot be used together with other Tracsact-SQL statements.

-Avoid creating views using external connections whenever possible.

View usage:

A view is used in a program just like a normal table. For example:

'Combined query management student information query view Stu_View connection card and student two tables Public Function CombinQueryStudent (ByVal student As Entity. conbinQueryInfo) As DataTable Implements IDAL. IStudent. combinQueryStudent Dim strSql As String define a query statement String Dim tableName As String define a database table name String Dim table As DataTable = New DataTable Dim helper As Helper. sqlHelper = New Helper. sqlHelper Try tableName = "Student_View" 'strsql = CombinQueryDAL. createSqlString (student, tableName) 'generates the SQL statement table = helper. executeNonQuery (strSql, CommandType. text) Return table Catch ex As Exception Throw ex End Try End Function

Advantages of a view:

When designing a program, you must first understand the advantages and disadvantages of the view. In this way, you can enhance your strengths and circumvent weaknesses. In addition to some of the advantages described above, the view also has some disadvantages, mainly as follows.

● Performance: SQL Server must convert View queries to basic table queries. If this view is defined by a complex multi-Table query, even if it is a simple View query, it also takes some time for SQL Server to turn it into a complex combination.

● Modify restrictions: when you try to modify certain rows of a view, SQL Server must convert it to modify certain rows of the basic table. For a simple view, this is very convenient, but for a complicated view, it may not be modifiable.

Therefore, when defining database objects, you must define the view selectively. You should weigh the advantages and disadvantages of the view and reasonably define the view.