SQL Server View

A view is actually a storage query, with the emphasis on mixing and matching data from a base table (or other view) to create objects that work in many ways, like another base table. You can create a simple query that selects only a few columns from one table and ignores the other columns, or you can create a complex query that joins several tables to make those tables look like a table.

first, the simple view

The syntax for the view is as follows:

<view name>as<SELECT statement>     

Specific syntax:

CREATEVIEW[schema_name]. <view name> [(<column name list>)][with [encryption] [, SCHEMABINDING][, View_metadata]]<SELECT statement > with CHECK OPTION                   

Example:

VIEW columnnamepath_vwascol_name fromnx_column   

This form of view can be achieved by masking a portion of the column information to a specific user, showing only the columns he needs, for sensitive column masking.

Another way is to filter the view, by using the WHERE clause, to block the information of a branch, only display the rows he needs, for sensitive row masking.

Two, the view of the complex point

Complex point of view, in fact, it is not complicated where to go, nothing but to add some inner join,left join and so on.

In addition, you can do this by using a variety of functions, returning only one day of data, or formatting data, and so on.

Views work like a table, but there are some differences. Here are some things to keep in mind when executing insert,update and DELETE statements on a view

• If the view contains a connection, in most cases, you cannot perform an INSERT or delete operation on the data unless you use the instead OF trigger. Sometimes (as long as only the columns from a single table are updated), update does not work with instead OF triggers, but this requires some planning, or you will encounter problems soon.
• If the view simply refers to a single table, then all required fields in the table are in the view or have default values, and you can insert the data by using the view instead of instead OF triggers. When for a view of a single table, if a column does not appear in a view that does not have a default value, you must use the instead OF trigger if you want to allow the insert operation.
• You can limit the ability to insert or update content in a view within a limited range.

1, the data processing view changes with the connection

If the view has more than one table, it is not permissible to use the view to modify the data in many cases-unless you use the inseead of trigger.

2. Required fields must appear in the view or have default values

By default, if you use a view to insert data (an internal query must have a select operation for a single table, or at least a restriction that inserts affect only one table, and all required columns appear), you must be able to provide some value for all required fields (fields that are not allowed to be empty). However, realize that any columns that do not have data and do not accept null values need to appear in the view. To perform an insert operation through a view.

3. Restrict the content inserted into the view by using the WITH CHECK option

With CHECK option is one of the lesser known features in SQL Server. The rules are simple-in order to update or insert data by using a view, the result row must meet the requirements to appear in the view results.

Example:

CREATEVIEWPortlandareaaddress_vwAsSELECTAddressid,addressline1,city,stateproviceid,postalcode,modifieddateFromPerson.Addresswhere PostalCode like 970% or PostalCode Span style= "color: #808080;" >like  ' 971% "or PostalCode   ' or PostalCode  Like  ' 986[6-9]% "with check option      

If you try to update a row by using the above view, set PostalCode to start with 97 or 98:

　　UPDATE portlandareaaddress_vw '33333' --not starting with 97 98      

SQL Server will error

The insert or update for the view has failed because a view that is spanned by the target or target view specifies with CHECK option, and one or more result rows of the operation do not conform to the CHECK option constraint.

Statement has been terminated.

The WHERE clause filters the contents of the view so that only the zip code between 970, 971, 972, or 9866~9869 is displayed, and with CHECK option indicates that any INSERT or UPDATE statement must satisfy the condition between the WHERE ( And 33333 This postcode does not satisfy this condition).

The above statement is normal if it is executed with a table.

third, use T-SQL Edit View

When you use the T-SQL Edit view, keep in mind that this is completely replacing the existing view. The difference between using the ALTER VIEW statement and the CREATE VIEW statement has the following main points:

• ALTER View expects to find an existing view, while create is not.
• ALTER View retains any permissions that have been established on the view.
• ALTER view retains any dependency information.

Keep in mind that 2nd, if you delete the view and then create it, the effect is basically the same as the ALTER VIEW statement, except that the permission information is all rebuilt.

The syntax for deleting a view is as follows:

<view name;,[<view NAME>,[...N]]    

Iv. review: Show existing code

There are two ways to get the actual view definition:

• Sp_helptext
• Sys.modules meta-data functions

sp_helptext Example:

EXEC sp_helptext PORTLANDAREAADDRESS_VW

SQL Server returns the code that created the view

Sys.modules Example:

The main problem with this function is that all objects are encoded with an object ID, which is an internal method of tracking things by SQL Server, which is an integer value instead of the name of the object. You can avoid this problem by using the object_id () function.

　　* from sys.sql_modules WHERE object_id ('dbo. PORTLANDAREAADDRESS_VW')      

Get the code for SQL Server to create the view again, just like sp_helptext.

v. Protection code: Encrypted View

Encrypted view all you have to do is use the WITH ENCRYPTION option. Skills:

• With encryption follows the view name, but before the AS keyword.
• With encryption do not use the OPTION keyword

If you use the ALTER VIEW statement, it means that you have completely replaced the existing view in addition to the access permissions. That means that the encryption method is also replaced. If you want to encrypt a changed view, you must use the WITH ENCRYPTION clause in the ALTER VIEW statement.

Example:

　　VIEW CUSTOMERORDERS_VW with encryption as SELECT     .....

Now look at the information in the view above:

　　EXEC sp_helptext CUSTOMERORDERS_VW

SQL Server prompts the following:

　　The text of the object ' CUSTOMERORDERS_VW ' is encrypted.

Note: Before using with encryption, you must back up the source code, once the source code is encrypted, there is no way to recover. If you do not store the code elsewhere, you need to change the code to rewrite it.

VI. Pattern Binding

Pattern binding is essentially "binding" a view to a view (a table or other view) on which it depends. The important point is that no one can modify those objects (CREATE, ALTER) unless the schema-bound view is first removed.

The function is as follows:

• You can prevent the use of view "orphaned" when modifying the underlying object. If the table is deleted, but the view is not taken into account.
• To allow the creation of indexed views: If you want to create an index on a view, you must use the SCHEMABINDING option to create the view.
• If you are creating a schema-bound user-defined function to reference a view, the view must also be bound.

　　Summary of view considerations:

• Avoid building views based on views-instead, you should apply the appropriate query information from the first view to the new view.
• Remember that views with CHECK option provide the flexibility that some ordinary CHECK constraints do not have
• If you do not want others to see your source code, encrypt the view, but remember to back up unencrypted code, because it cannot be restored to encrypted code after encryption.
• In addition to permissions, using ALTER VIEW means that the existing view is completely replaced. This means that if the encryption and restrictions in the modified view are still valid, the with encryption and with CHECK option clauses must be included in the ALTER statement.
• Use Sel_helptext to display supported code for the view-avoid using system tables.
• Minimize the users who use the view for production queries-because they add additional overhead and compromise performance.

　　Views are typically used for the following scenarios:

• Filter rows
• Protect sensitive data
• Reduce database complexity
• Abstract multiple physical databases into one logical database

SQL Server View