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