T-SQL view, t-SQL View

Source: Internet
Author: User
Tags sqlite query

T-SQL view, t-SQL View

View

[Younger brother build view previously is visual operation rarely write T-SQL statement, but in the work of release version is necessary, write some T-SQL statements, so we still write more points better]

1. What is a view?

Only an SQLite statement stored in the database using the relevant name. A View is actually a combination of tables that exist in the predefined SQLite Query Form.

 

2. View creation considerations

To create a view, consider the following guidelines:

  • It is more natural or intuitive for users or user groups to find structural data.

  • Restrict data access. You can only view limited data, not the complete table.

  • Summarize the data in various tables for report generation.

  • Rules and default cannot be associated with views.

  • You can create views for other views. Nested views are allowed, but no more than 32 layers can be nested. A view can have a maximum of 1024 fields.

  • View queries cannot contain compute clauses, compute by clauses, or into keywords.

  • The query of the definition view cannot contain the order by clause, unless there is a top clause in the select statement selection list

In the following cases, you must specify the name of each column in the View:

# How columns in a view are derived from arithmetic expressions, built-in functions, or constants

# Two or more columns in the view have the same name (usually because the view definition contains a join, two or more different columns have the same name)

# You want the column in the view to specify a name different from the original column (you can also rename the column in the view ). The View column inherits the Data Type of the original column no matter whether it is renamed or not.

 

3. Create a view

-- Create a view
Create VIEW [dbo]. [FC7Exp] asselect convert (varchar (12),. shipDate, 23) AS 'shipment date', d. GName AS 'department ', B. saftLockID AS 'id', B. saftLockPN AS 'p/N', <span style = "white-space: pre"> </span> B. orderNum AS 'order number', B. interfaceType AS 'interface type', B. FCCom AS 'components', B. outNumber AS 'number', e. cusName AS 'customer name', B. amount AS 'total price ', ''as 'amount', ''as 'cost', f. UName AS 'salesman' FROM dbo. admin_FCOut AS a left outer join dbo. admin_FCOutDt AS B ON B. lastID =. idleft outer join dbo. ug_User_Group AS c ON c. UId =. shipper left outer join dbo. g_group AS d ON c. GId = d. GIdLEFT outer join dbo. crm_custom AS e ON e. cusId = B. cusIdLEFT outer join dbo. u_user AS f ON f. UId =. shipper ---- the query statement you want to operate on


4. Modify the view
Alter view [dbo]. [FC7Exp] AS query statement GO


 

5. Encrypted View

-- If you want to modify the created view and encrypt it, execute the following code: alter view name with encryption ---- (view encryption) begin ---- the query statement you want to operate on end ------ if you want to create a new view and encrypt it, execute the following code: create view name with encryption ---- (view encryption) begin ---- end of the query statement you want to operate on


Delete View
Drop view name
 

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.