Detailed use of ms SQL View

A view is created for users to display multiple types of data. It is mainly used in the following situations:
(1) restrict users to access the content of specific conditions of a specific table to improve system security.
(2) hiding the table structure and creating multiple forms of data pivoting to meet different user needs.
(3) form a view with complex select statements and table jion to provide a good interface for users.
(4) Create an index view for frequently used Union table aggregation operations to improve program performance.
(5) create a partition view to call remote data for Distributed Data Storage and query, and improve program throughput.
1. View
A view is a virtual table whose content is defined by the query. Like a real table, a view contains a series of columns and row data with names. A view does not exist in the database as a data value storage set, unless it is an index view. Rows and columns are used to define tables referenced by View queries and dynamically generate tables when views are referenced.
Pair
For the referenced basic table, the view function is similar to filtering. The filtering of the definition view can be from one or more tables of the current or other databases, or other views. Distributed queries can also be used to define multiple
View of heterogeneous source data. For example, if multiple servers store data in different regions, and you need to combine the data with similar structures on these servers, this method is very useful.
Ii. view type
In SQL Server 2005, you can create standard views, index views, and partition views.
Standard view
A standard view combines data from one or more tables, allowing you to gain most of the benefits of using a view, including focusing on specific data and simplifying data operations.
Index View
An index view is a specific view that has been computed and stored. You can create an index for a view, that is, create a unique clustered index for the view. The index view can significantly improve the performance of some types of queries. The index view is especially suitable for aggregating queries of many rows. However, they are not suitable for basic datasets that are frequently updated.
Partition View
The partition view connects partition data in a group of tables horizontally between one or more servers. In this way, the data looks like a table. The view that connects to the Member tables in the same SQL server instance is a local partition view.
Iii. View usage

Views are usually used to centralize, simplify, and customize different database understandings for each user. A view can be used as a security mechanism to allow users to access data through a view without authorizing users to directly access the basic view table.
Limits. A view can be used to provide backward compatible interfaces to simulate tables that have existed but their architecture has changed. You can also go to Microsoft SQL Server 2005
Use a view when copying and copying data from it to improve performance and partition the data.
Focusing on specific data views enables users to focus on specific data they are interested in and specific data they are responsible
Task. Unnecessary or sensitive data cannot appear in the view. For example, view vbikes in the adventureworks Sample Database
Allows you to view the names of all bicycles in stock. This view filters out names from the product table
And only returns the name of the finished bicycle, not the name of the bicycle accessories.
Simplify data operations

The view simplifies the way users process data. Common join, projection, Union query, and select
A query is defined as a view so that you do not have to specify all the conditions and conditions for each additional operation on the data. For example, you can execute a subquery, outer join, and aggregate for the purpose of a report from a group
A complex query of data retrieved from a table is created as a view. The view simplifies data access, because you do not need to write or submit basic queries every time you generate a report, but query the view. For details about operations on data,
See basic query knowledge.
Although it is not a complex query, the view vbikes in the adventureworks sample database allows users to focus on specific data without having to construct the join clause required to generate the view.
You can also create a user-defined inline function that runs logically as a parameterized view, or as a view that contains parameters in the WHERE clause search condition or other part of the query.
Backward compatibility
View allows you to create backward compatible interfaces for tables when their schema is changed. For example, an application may reference a non-standardized table with the following Architecture:
Employee (name, birthdate, salary, department, buildingname)
To avoid repeated data storage in the database, you can divide the table into the following two tables to standardize the table:
Employee2 (name, birthdate, salary, deptid)
Department (deptid, buildingname)
To provide a backward compatible interface that can still reference data in the employee, you can delete the old employee table and replace it with the content in the following view:
Custom Data

Views allow users to view data in different ways, even if they use the same data at the same time. This is especially useful when many users with different purposes and technical levels share the same database. For example
Create a view to retrieve only the customer data processed by the customer manager. This view determines which data to Retrieve Based on the login ID of the Customer Manager that uses it.
Export and import data
You can use views to export data to other applications.
For example, you may want to use the customer and salesorderheader tables in the adventureworks database
Analyze sales data in Microsoft Excel. Therefore, you can create a view based on the customer and salesorderheader tables. Then you can use
BCP utility exports view-defined data. If the insert statement can be used to insert rows in some views, you can use the BCP utility or bulk insert statement to import data in the data file to the view.
Cross-server partition data combination

Transact-SQL Union
Set operators can be used in a view to combine the results of two or more queries in a single table into a single result set. This is a separate table, called a partition view. For example, if a table contains tables
Sales data. If the other table contains sales data in California, you can use Union to create a view for the two tables. This view represents the sales data of the two regions
Iv. View Parsing
The SQL Server 2005 query processor treats the index view and the non-index view differently:
The rows in the index view are stored in the database in the table format [bucket is allocated only when an index is created]. If the query optimizer decides to use the index view of the query plan, the index view will be processed according to the base table processing method.
Only the definition of a non-index view is stored, rather than the row of the view. The query optimizer incorporates the logic in the view definition into the execution plan, which is generated for SQL statements that reference the non-index view.
SQL
The logic used by the Server query optimizer to determine when to use the index view is similar to that used to determine when to use the index on the table. If the data in the index view includes all or part of SQL
Statement, and the query optimizer determines that a view index is a low-cost access path. no matter whether the view name is referenced in the query, the query optimizer selects this index.
When an SQL statement references a non-index view, the analyzer and query optimizer analyze the source and view sources of the SQL statement and resolve them to a single execution plan. There is no separate plan for SQL statements or views.
Like all indexes, SQL Server selects view indexes only when the query optimizer determines that the index view is useful in the SQL Server query plan. When the following conditions are met, the SQL Server Query Optimizer uses the index View:
When the following conditions are met, the SQL Server Query Optimizer uses the index View:
The following session options are set to on:
Ansi_nulls
Ansi_padding
Ansi_warnings
Arithabort
Concat_null_yields_null
Quoted_identifier
Set the numeric_roundabort session option to off.
The query optimizer searches for matching between View index columns and elements in the query, for example:
Search Condition predicates in the WHERE clause
Join Operation
Aggregate functions
Group by clause
Table reference
The estimated index usage cost is the lowest cost among all access mechanisms considered by the query optimizer.
Each table referenced in a query (directly accessing its basic table through an expanded view) and corresponding to the table reference in the index view must have the same prompt set applied to the table in this query.
The query optimizer can use the index view without explicitly referencing the index view in the select clause.
V. Design and Implementation View
1. Create a standard view
 
Set ansi_nulls on
Go
Set quoted_identifier on
Go
Create view [DBO]. [product_name]
As
Select ID, name, cateid from product where cateid = 0
Go
Set ansi_nulls off
Go
Set quoted_identifier off
Go

Note for creating a view:
(1) The order by statement cannot be used unless the Top N option is used;
(2) into statements cannot be used;
(3) columns in the temporary table cannot be used.
2. Key Points of designing an index view.
Try to create an index view on the basic data table with few updates (add, delete, modify.
Queries involving a large number of aggregation or joins
Small Data Volume
3. Create an index View

Set ansi_nulls on
Go
Set quoted_identifier on
Go
-- Create an index View
Create view [DBO]. [product_name]
With schemabinding
As
Select ID, name, cateid from [DBO]. Product where cateid = 3
Go
Set ansi_nulls off
Go
Set quoted_identifier off
Go
Go
-- Create a clustered Index
Set arithabort on
Go
Set concat_null_yields_null on
Go
Set quoted_identifier on
Go
Set ansi_nulls on
Go
Set ansi_padding on
Go
Set ansi_warnings on
Go
Set numeric_roundabort off
Go
/** // ****** Object: Index [product_viewindex_id] script Date: 09/04/2007 23:25:33 ******/
Create unique clustered index [product_viewindex_id] on [DBO]. [product_name]
(
[ID] ASC
) With (sort_in_tempdb = OFF, drop_existing = OFF, ignore_dup_key = OFF, online = OFF) on [primary]
Go
-- Create a non-clustered Index
Create nonclustered index [product_viewindex_name] on [DBO]. [product_name]
(
[Name] ASC
)

Notes for creating an index View:
When executing the create view statement, the options ansi_nulls and quoted_identifier must be set to on.
To execute all the create table statements to create a table referenced by the view, the ansi_nulls option must be set to on.
A view cannot reference any other view. It can only reference a base table.
All base tables referenced by a view must be in the same database as the view, and the owner must be the same as the view.
You must use the schemabinding option to create a view. The schema binding binds the view to the schema of the base table.
You must have created a user-defined function referenced by the view using the schemabinding option.
A table and a user-defined function must be referenced by a name consisting of two parts in the view. It is not allowed to reference them by names of parts, three parts, and four parts.
All functions referenced by expressions in the view must be definite.
If the view definition uses an aggregate function, the select list must also include count_big (*).
The Data Access attribute of user-defined functions must be no SQL, and the external access attribute must be no
The Common Language Runtime (CLR) function can appear in the view selection list, but cannot be part of the clustered index key definition. CLR functions cannot appear in the WHERE clause of the view or the on clause of the join operation in the view.
The SELECT statement in the view cannot contain the following Transact-SQL syntax elements:
* Or table_name. * syntax of the specified column. The column name must be specified.
You cannot specify the table column name used as a simple expression in multiple view columns. If all (or all except one reference) references to a column are part of a complex expression or a function parameter, You can reference the column multiple times.
Derived table.
Common table expressions (CTE ).
Collection Functions
Union, except T, or intersect Operator
Subquery
External join or self join
Top clause
Order by clause
Distinct keyword
Count (*) (count_big (*) is allowed (*).)
AVG, Max, Min, STDev, stdevp, VAR, or varp aggregate function.
Reference the sum function that can be a null expression.
Includes the over clause of ranking or aggregate window functions.
CLR user-defined aggregate functions.
Full-text predicates contains or freetext.
Compute or compute by clause.
Cross apply or outer apply operator.
Operator.
Table prompt (only applicable to 90 or higher compatibility levels ).
Connection prompt.
Direct reference to XQuery expressions
After group by is specified, the view select list must contain the count_big (*) expression, and the view definition cannot specify having, cube, or rollup.
4. Create a partition view.
The partition view connects partition data in a group of tables horizontally between one or more servers to make the data look like it comes from a table.
-- Create a linked server

Exec
Sp_addmediaserver
@ Srvproduct = n'', @ Server = 'localserver', @ provider = 'msdasql ', @ provstr = 'driver = {SQL
Server}; server = 192.168.1.253; uid = sa; Pwd = 123'
-- Create a login ing for the linked server
Exec sp_add1_srvlogin @ rmtsrvname = 'localserver', @ useself = 'false', @ locallogin = 'sa ', @ rmtuser = 'sa', @ rmtpassword = '2016'
Go
-- Create a partition Query
Create view distributview
As
Select * from [localserver]. Test. DBO. [A]
Union all
Select * from

Vi. View usage example
1. Use View
Select * From product_name
2. modify data through views
Update product_name set cateid = 5 where id = 60
3. delete data from a view
Delete from product_name where id = 61