SQL Getting Started Classic (vi) view

A view is actually a storage query that focuses on the ability to mix and match data from a base table (or other view) to create the same effect in many ways as another normal table. You can create a simple query that selects only a few columns or rows from one table (another view) and ignores other rows or columns, or you can create a complex query. Connect several table queries to make these connection queries look more like a table.

The use of a view is often either too much or not enough-rarely happens. After learning the view, you should be able to use the view to achieve the following:

1. Reduce significant data complexity for end users.

2. Prevent sensitive columns from being selected, but still provide access to other data.

3. Adding additional indexes to the database improves query performance-even in the view on which the index is not being used.

View basic Syntax : CREATE view <view name> as <select statement>. It's not as simple as it looks. The above syntax, of course, represents the most simplified syntax for most. It's enough. The syntax for the extension is as follows: CREATE VIEW <view name> [with [encryption][,] schmabinding][[,] view_metadata] as <selete statement> [With CHECK OPTION] [;]

Each of the above code is described below, but the simplest view is shown first. Extremely simple view.

 UseAdventureWorks--We'll use the AdventureWorks database .CREATE VIEWVw_contactphone--Create a view that returns a user's phone asSELECT(FirstName+'.'+LastName) asUsername,phone fromPerson.Contact--The command was successfully completed. SELECT *  fromVw_contactphone--use view (looks like query table)

Creating a view does not really change anything. Just by filtering the data that is being accessed, the benefit is to reduce data complexity for end users, and there are a lot of tools in the current era that make it easier for users. This doesn't look great, but it does reduce complexity for the user.

Note: be aware that, by default, there is no special need to do things for views. A view is like a command line that executes a query command like a cloud letter. There is no pre-optimized process. This means that an additional layer of overhead is provided between the request for the data and the data being delivered, and the view is always running faster than the directly running internal SELECT statement. That is, the view, the existence of a view for a reason, that is, for the user is safe and simplified. So find a balance between demand and system overhead to fit a particular situation

To use the filter view:

SELECT *  from WHERE  like ' 334% ' -- using the previous CREATE view, query area code starts with a%334 number (10 results)

use more complex views : Managers want simple queries to understand how customers order those orders, which are placed over those parts. And the account that was ordered. Here is a view that creates a very simple query.

 UseAdventureWorksGOCREATE VIEWvw_customerorders asSELECTSc. Accountnumber,soh. Salesorderid,soh. Orderdate,sod. Productid,pp. Name,sod. Orderqty,sod. UnitPrice, (Sod. Unitpricediscount*Sod. UnitPrice*Sod. OrderQty) asTatoldiscount,sod. LineTotal fromSales.Customer asSCInner JoinSales.SalesOrderHeader asSoh onSc. CustomerID=Soh. CustomerIDInner JoinSales.SalesOrderDetail asSod onSoh. SalesOrderID=Sod. SalesOrderIDInner JoinProduction.Product asPp onSod. ProductID=Pp. ProductID

There is no need for too much training, and managers can get the data they want.

Restricts the content that is 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 use views to update or insert data. The result must conform to display in the view results. Again, the inserted or updated row must satisfy the use where condition in the SELECT statement in the view. There is no limit to the base table.

Delete view: Drop view <view name>,[<view name>,<......,n>];

Let the view and table create the index. Only add: With SCHEMABINDING in front of AS

CREATE VIEW -- Create a view that can be indexed back to the user's phone  with SCHEMABINDING  as SELECT (FirstName+'. ' +  as  from -- The command was successfully completed. 

Protect code Encryption : only need to add: with encryption in front of AS, remember to back up your own data.

CREATE VIEWVw_contactphoneencry--Create an encryption index to return the view of the user's phone withEncryption asSELECT(FirstName+'.'+LastName) asUsername,phone fromPerson.Contact--Command completed successfully: GOexecSp_helptext vw_contactphoneencry--the text of the object ' Vw_contactphoneencry ' is encrypted. 

SQL Getting Started Classic (vi) view