View
Concept:
An index-free view is actually a virtual table without any physical data. It is a piece of code that contains metadata describing it, such as structure and dependency.
1. It can be used for basic data aggregation and other operations.
2. It filters data so that you can only access the view to protect the original table.
3. similarly, a view cannot contain a separate order. because it is regarded as a table, a table is a logical entity, and data in itself is stored randomly, and meters deliberately store data in a certain order.
A few things about views
1. Order by in the view
Order by in a view is not what it meant -- it does not play a role in sorting.
We usually use select Top N * from TB order by col1, where order by not only sorts but also determines which rows to return.
In the view, select Top N * from TB order by col1. Order by can only be used to determine the rows to be returned.
Because the last thing that comes out of our view is essentially a table. When you query this view, the results may not necessarily be sorted by col1.
Because the view is a table, the table is a logical entity, and the data in it is stored randomly.
. View instances:
Create Table K (A int, B INT) <br/> insert K select 1, 2 <br/> insert K select 2, 6 <br/> insert K select 3, 7 <br/> insert K select 4, 2 <br/> insert K select 5, 9 <br/> GP <br/> Create view v_showk <br/> as <br/> select Top 100 percent * <br/> from k <br/> order by B <br/> go <br/> select * From v_showk <br/>/* <br/> A B <br/> ----------- --------- <br/> 1 2 <br/> 2 6 <br/> 3 7 <br/> 4 2 <br/> 5 9 <br/> */
------- The result is not sorted according to the expected B. This shows that order by in the view does not play a sorting role ----------------
2. View refresh problem sp_refreshview
Sometimes you will findAfter you modify the structure of the basic table, the view does not change,The solution is to refresh the view immediately after modification, especially when you try to select *.
Use tempdb; <br/> go <br/> If object_id ('dbo. v1 ') is not null <br/> drop view DBO. v1; <br/> go <br/> If object_id ('dbo. t1 ') is not null <br/> drop table DBO. t1; <br/> go </P> <p> Create Table DBO. t1 (col1 int, col2 INT); <br/> insert into DBO. t1 (col1, col2) values (1, 2); <br/> go <br/> -- create view DBO. v1 <br/> as <br/> select * From DBO. t1; <br/> go <br/> -- view <br/> select * From DBO. v1; <br/> go <br/>/* <br/> col1 col2 <br/> ----------- --------- <br/> 1 2 <br/> */<br/> -- alter table dbo. t1 add col3 int; <br/> go <br/> -- view the view again <br/> select * From DBO. v1; <br/> go <br/>/* <br/> col1 col2 <br/> ----------- --------- <br/> 1 2 <br/> */<br/> -- it is found that the modified structure does not appear in front of you, refresh view <br/> exec sp_refreshview 'dbo. v1 '; <br/> go <br/> -- View again <br/> select * From DBO. v1; <br/> go <br/>/* <br/> col1 col2 col3 <br/> ----------- <br/> 1 2 null <br/> */<br />
------------ Here is a code refresh all views (book )-----------
-- Refresh all views
Select n 'exec sp_refreshview' <br/> + quotename (view_name, ''') + ';' As CMD <br/> from (select quotename (table_schema) <br/> + N '. '+ quotename (table_name) as view_name <br/> from information_schema.views) AS v <br/> where objectproperty (object_id (view_name), 'isschemabound') = 0; <br/> go
3. modular view Solution
If the idea of solving a question is to write a statement that is relatively long and difficult to read, you can try to separate the view and break it down in turn-(the example in the book is lazy, and theft of data indicates the problem)
Set nocount on; <br/> Use tempdb; <br/> go <br/> If object_id ('dbo. sales ') is not null <br/> drop table DBO. sales; <br/> go <br/> Create Table DBO. sales <br/> (<br/> mnth datetime not null primary key, <br/> qty int not null <br/>); <br/> insert into DBO. sales (mnth, qty) values ('000000', 20041201); <br/> insert into DBO. sales (mnth, qty) values ('000000', 20050101); <br/> insert into DBO. sales (mnth, qty) values ('000000', 20050201); <br/> insert into DBO. sales (mnth, qty) values ('000000', 20050301); <br/> insert into DBO. sales (mnth, qty) values ('000000', 20050401); <br/> insert into DBO. sales (mnth, qty) values ('000000', 20050501); <br/> insert into DBO. sales (mnth, qty) values ('000000', 20050601); <br/> insert into DBO. sales (mnth, qty) values ('000000', 20050701); <br/> insert into DBO. sales (mnth, qty) values ('000000', 20050801); <br/> insert into DBO. sales (mnth, qty) values ('000000', 20050901); <br/> insert into DBO. sales (mnth, qty) values ('000000', 20051001); <br/> insert into DBO. sales (mnth, qty) values ('000000', 20051101); <br/> insert into DBO. sales (mnth, qty) values ('000000', 20051201); <br/> insert into DBO. sales (mnth, qty) values ('000000', 20060101); <br/> insert into DBO. sales (mnth, qty) values ('000000', 20060201); <br/> insert into DBO. sales (mnth, qty) values ('000000', 20060301); <br/> insert into DBO. sales (mnth, qty) values ('000000', 20060401); <br/> insert into DBO. sales (mnth, qty) values ('000000', 20060501); <br/> insert into DBO. sales (mnth, qty) values ('000000', 20060601); <br/> insert into DBO. sales (mnth, qty) values ('000000', 20060701); <br/> insert into DBO. sales (mnth, qty) values ('000000', 20060801); <br/> insert into DBO. sales (mnth, qty) values ('000000', 20060901); <br/> insert into DBO. sales (mnth, qty) values ('000000', 20061001); <br/> insert into DBO. sales (mnth, qty) values ('000000', 20061101); <br/> insert into DBO. sales (mnth, qty) values ('000000', 20061201); <br/> go <br/>
----- Target result ----- judge the sales trend based on the difference in the previous month.
/* <Br/> start_range end_range trend <br/> ----------- --------- <br/> 200412 200412 unknown <br/> 200501 200504 up <br/> 200505 200505 same <br/> 200506 200509 down <br/> 200510 200510 up <br/> 200511 200511 down <br/> 200512 200602 up <br/> 200603 200603 down <br/> 200604 200605 same <br/> 200606 200607 200608 up <br/> 200608 200609 down <br/> 200611 200612 up <br/> 200612 down <br/> */
--ID of the vsalesrn view on each month
If object_id ('dbo. vsalesrn ') is not null <br/> drop view DBO. vsalesrn; <br/> go <br/> Create view DBO. vsalesrn <br/> as <br/> select mnth, qty, row_number () over (order by mnth) as rn <br/> from DBO. sales; <br/> go <br/> -- The vsgn view uses the Sign Function and the Self-join function to find the SGN logo trend. <br/> If object_id ('dbo. vsgn') is not null <br/> drop view DBO. vsgn; <br/> go <br/> Create view DBO. vsgn <br/> as <br/> select cur. mnth, cur. qty, isnull (sign (cur. qty-Prv. qty), 2) As SGN <br/> from DBO. vsalesrn as cur <br/> left join DBO. vsalesrn as PRV <br/> On cur. rn = Prv. rn + 1; <br/>
--The vgrp view extracts the group factor GRP of the final calibration range based on the group ID.
If object_id ('dbo. vgrp ') is not null <br/> drop view DBO. vgrp; <br/> go <br/> Create view DBO. vgrp <br/> as </P> <p> select mnth, SGN, <br/> dateadd (month, <br/>-row_number () over (partition by SGN order by mnth), <br/> mnth) as GRP <br/> from DBO. vsgn; <br/> go
--The vtrends view obtains the maximum and minimum values in its range based on the SGN and GRP groups and identifies the corresponding Chinese representation.
If object_id ('dbo. vtrends ') is not null <br/> drop view DBO. vtrends; <br/> go <br/> Create view DBO. vtrends <br/> as </P> <p> select <br/> convert (varchar (6), min (mnth), 112) as start_range, <br/> convert (varchar (6), max (mnth), 112) as end_range, <br/> case SGN <br/> when-1 then 'drop' <br/> When 0 then 'same' <br/> when 1 then 'up' <br/> else 'unknown '<br/> end as trend <br/> from DBO. vgrp <br/> group by SGN, GRP; <br/>
---------- Retrieval view ------------
Select start_range, end_range, trend <br/> from DBO. vtrends <br/> order by start_range;
5. view update
A view can be modified and updated, because operations on it will affect the basic table.
Here I will pay attention to the following points: when your view is queried by two table links and your operations are updated, pay attention to the following:
Set nocount on; <br/> Use tempdb; <br/> go <br/> If object_id ('dbo. vcustorders ') is not null <br/> drop view DBO. vcustorders; <br/> go <br/> If object_id ('dbo. orders ') is not null <br/> drop table DBO. orders; <br/> go <br/> If object_id ('dbo. MERs ') is not null <br/> drop table DBO. customers; <br/> go <br/> --- create a mers MERs table <br/> Create Table DBO. MERs <br/> (<br/> CID int not null primary key, <br/> cname varchar (25) not null <br/>) <br/> insert into DBO. MERs (CID, cname) values (1, 'cust 1 '); <br/> insert into DBO. MERs (CID, cname) values (2, 'cust 2'); <br/> --- create an orders table <br/> Create Table DBO. orders <br/> (<br/> OID int not null primary key, <br/> CID int not null references DBO. customers <br/>) <br/> insert into DBO. orders (OID, CID) values (1001, 1); <br/> insert into DBO. orders (OID, CID) values (1002, 1); <br/> insert into DBO. orders (OID, CID) values (1003, 1); <br/> insert into DBO. orders (OID, CID) values (2001, 2); <br/> insert into DBO. orders (OID, CID) values (2002, 2); <br/> insert into DBO. orders (OID, CID) values (2003, 2); <br/> go <br/> ----- create view vcustorders show link content of table 2 <br/> Create view DBO. vcustorders <br/> as <br/> select C. CID, C. cname, O. oid <br/> from DBO. customers as C <br/> join DBO. orders as O <br/> on O. cid = C. CID; <br/> go
--Show data
Select CID, cname, oid from DBO. vcustorders; <br/> go <br/>/* <br/> CID cname OID <br/> --------------------------------- ----------- <br/> 1 Cust 1 1001 <br/> 1 Cust 1 1002 <br/> 1 Cust 1 1003 <br/> 2 Cust 2 2001 <br/> 2 Cust 2 2002 <br/> 2 Cust 2 2003 <br/> */
-- Update:We want to change the cname name of the company whose order number is 1001. In the initial stage, we just wanted to change this order (this was originally intended)
Update DBO. vcustorders <br/> set cname = 'cust 42' <br/> where OID = 1001; <br/> go <br/> -- query the view again <br/> select CID, cname, oid from DBO. vcustorders; <br/> go <br/>/* <br/> CID cname OID <br/> --------------------------------- ----------- <br/> 1 Cust 42 1001 <br/> 1 Cust 42 1002 <br/> 1 Cust 42 1003 <br/> 2 Cust 2 2001 <br/> 2 Cust 2 2002 <br/> 2 Cust 2 2003 <br/> */< /P> <p>
------ In fact, it is easy to understand here. we modified the table's MERs Mers instead of the view. So we changed the view and the link from the table. Of course, the three of them changed together ----
6. view options
A. Encryption
Attackers can encrypt the created Text of the UDF stored procedure.
But I heard that the decryption method http://www.360doc.com/content/050909/13/717_11438.html has been found
B. schemabinding
When a view is bound to a base object framework, SQL cannot delete the base object or modify the columns referenced in the view.
Create Table Tb (a int) <br/> insert TB values (1) <br/> insert TB values (2) <br/> If object_id ('dbo. vcustswithorders ') is not null <br/> drop view DBO. vcustswithorders; <br/> go <br/> Create view DBO. vcustswithorders with schemabinding <br/> as <br/> select a from DBO. TB ----- DBO is required here. TB to indicate TB tables <br/> go <br/> -- try to modify the table structure <br/> alter table dbo. TB drop column A; <br/> go <br/>/* <br/> Object 'vcustswithord ERS depends on column 'A '. <Br/> */
C. Check Option
The view with this option prevents insert and update operations that conflict with the query filter of the view. For example:
Create Table V (a INT) <br/> insert v Select 8 <br/> insert v select 2 <br/> insert v select 3 <br/> insert v select 4 <br/> go <br/> Create view v_v <br/> as <br/> select * from V where a> 2 <br/> with check option <br/> go <br/> -- insert operation <br/> insert v_v select 6 -- Succeeded <br/> insert v_v select 1 -- failed, view defines check option and specifies a> 2 <br/> -- Update operation <br/> Update v_v set a = 1 where a = 4 -- error, the updated a value is 1 and less than 2, so it does not conform to Figure where condition <br/> Update v_v set a = 3 where a = 4 -- successful <br/> -- the delete operation is successful because it is deleted. <Br/>
4. indexed_view
An index-free view is just a virtual table with an index. A view is equivalent to a new user. The data it contains will be physically converted into a real table.
It is synchronized to the base table, that is, you modify the base table and update the index view at the same time. The modification performance here will be reduced.
However, the index view also improves the performance of some aggregated queries and high-cost links.
Restrictions on index creation:
A. The first index must be a unique clustered index.
B. The view must use schemabinding, which must be viewed above;
C. If the View query is aggregated, its select list must contain the aggregate function count_big (*).
Create view DBO. vemporders with schemabinding <br/> as <br/> select O. employeeid, sum (OD. quantity) as totalqty, count_big (*) as CNT <br/> from DBO. orders as O <br/> join DBO. [Order Details] as od <br/> On OD. orderid = O. orderid <br/> group by O. employeeid; <br/> go <br/> create unique clustered index idx_uc_empid on DBO. vemporders (employeeid); <br/> go
--- The preceding view is a typical index view ---
---The index view has a benefit. For example, if you query an external basic table, note that the basic table does not have an index, but you can use the View index when querying it again.
Select O. employeeid, sum (OD. quantity) as totalqty, AVG (OD. quantity) as avgqty, count_big (*) as CNT <br/> from DBO. orders as O <br/> join DBO. [Order Details] as od <br/> On OD. orderid = O. orderid <br/> group by O. employeeid; <br/>
-- This query uses the clustered index on the view, and the aggregate functions not included in the View of AVG (OD. Quantity) also use -----
D. There are several cases where the index view is used in sql2005:
1. When the where condition or on condition of an external query is a subinterval of an attempt to filter, the query can use the index view.
For example, external query n <100 View query n <200
2. An external query uses a logical filter condition equivalent to a View query.
For example, external N = 100 view 100 = N
3. Insert null values
You want to use the unique constraint to restrict the unique non-null values in the table, but the null value can be inserted multiple times. However, the unique constraint considers that the null value is equal. Therefore, you must use this column in the table.
If a null value already exists, it cannot be inserted again. In this case, you can use the index view to solve this problem.
Use tempdb; <br/> go <br/> If object_id ('dbo. v1 ') is not null <br/> drop view DBO. v1; <br/> go <br/> If object_id ('dbo. t1 ') is not null <br/> drop table DBO. t1; <br/> go <br/> Create Table DBO. t1 <br/> (<br/> keycol int null, <br/> datacol varchar (10) Not null <br/> ); <br/> go <br/> Create view DBO. v1 with schemabinding <br/> as <br/> select keycol from DBO. t1 where keycol is not null -- note the where condition <br/> go <br/> create unique clustered index idx_uc_keycol on DBO. v1 (keycol); <br/> go <br/> -- insert data <br/> insert into DBO. t1 (keycol, datacol) values (1, 'A'); <br/> insert into DBO. t1 (keycol, datacol) values (1, 'B'); -- this failed <br/> insert into DBO. t1 (keycol, datacol) values (null, 'C'); <br/> insert into DBO. t1 (keycol, datacol) values (null, 'D'); <br/> go <br/> -- Query <br/> select keycol, datacol from DBO. t1; <br/> go <br/>/* <br/> keycol datacol <br/> ----------- ---------- <br/> 1 A <br/> null C <br/> null d <br/> */<br/>
---- Duplicate values are not allowed to be inserted in view indexes, but there is no limitation on the repeatability of null because where keycol is not null.
------ Here again, the operation on the basic table affects the View index ------------