A brief analysis of SQL Server databases using views to handle complex data query relationships _mssql

SQL Server database using views to handle complex data query relationships is the main content of this article, this is the idea: in the auxiliary educational system that piece, I do a page is a single teacher and the whole college teacher workload query, This operation was designed to three tables in this database and a teacher information table in a different database. If the ordinary SQL statement is very difficult to achieve, because I just started to do the video playback system, the database table is relatively small, not involved in such a complex processing relationship, just beginning to feel very difficult.

Later thought of using a view can solve the complex relationship of multiple tables, but another table is different database, whether still can operate, after testing, incredibly can be different database two tables to establish the view relationship, so the view is a virtual table, We'll put together the tables in the different databases that need to be queried, or the tables in the same database, then select the fields we need, recreate a new virtual table, and then the view can act as a new table. This will provide us with a lot of convenience.

A view is a continuation of a table that is poured from one or more tables or views, and whose structure and data are based on a query to a table.

Benefits of view :

1. Views allow users to select certain data and/or specific tasks, while unwanted or unwanted data can no longer be displayed in the view.

2. View greatly simplifies the operation of the database, you can do the operation of the table through the view operation.

3. Views allow different users to see different or identical datasets in different ways, which is quite handy

4. In some cases, because the amount of data in the table is too large, therefore, tables are often designed to be horizontally or vertically divided, but the structure of the table has a negative impact on the application, and the view can be used to rearrange the data so that the external mode remains unchanged, and the original application can overload the data through the view.

5. The view provides a simple and effective security mechanism.

Disadvantages of the view :

If this view handles a very large amount of data, it brings a lot of pressure on the SQL database, execution speed is relatively slow, as stored procedures, so if you can use stored procedures, priority to use stored procedures to create

The main way to create a view:

1. Create a view with SQL Server Management platform

2. Create a view using the CREATE VIEW command in a Transact-SQL statement

3. Use the SQL Sever Management Platform View template to create a view

When creating a view, be aware that:

1. A view can only be created in the current database, with a maximum of 1024 cases in the view, and a number of records in the view the prophet is determined only by the number of records in its base table.

2. If the base table or view referenced by the view is deleted, the view can no longer be used, knowing that creating a new base table or view

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

4. No more indexes on the view, no more rules, defaults, references to views in triggers

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

6. The name of the view must follow the rules of the identifier and must be unique for each user, in addition, the name should not have any table with the same name as the user, which is the established view, where Teacherinfo is added from another database.

The following is a datasheet query from the view "select * from Queryworkinfobyfaculty"

About the SQL Server database with views to handle complex data query relationships with the relevant knowledge about this, I hope this introduction can be harvested for you!