Admin: 1 версия

2010-05-26T10:24:52Z

1 версия

Новая страница

== A CREATE VIEW statement that uses TOP and ORDER BY clauses==



<source lang="sql">
4>
5>
6> create table Billings (
7> BankerID INTEGER,
8> BillingNumber INTEGER,
9> BillingDate datetime,
10> BillingTotal INTEGER,
11> TermsID INTEGER,
12> BillingDueDate datetime ,
13> PaymentTotal INTEGER,
14> CreditTotal INTEGER
15>
16> );
17> GO
1>
2> INSERT INTO Billings VALUES (1, 1, "2005-01-22", 165, 1,"2005-04-22",123,321);
3> GO
(1 rows affected)
1> INSERT INTO Billings VALUES (2, 2, "2001-02-21", 165, 1,"2002-02-22",123,321.);
2> GO
(1 rows affected)
1> INSERT INTO Billings VALUES (3, 3, "2003-05-02", 165, 1,"2005-04-12",123,321);
2> GO
(1 rows affected)
1> INSERT INTO Billings VALUES (4, 4, "1999-03-12", 165, 1,"2005-04-18",123,321);
2> GO
(1 rows affected)
1> INSERT INTO Billings VALUES (5, 5, "2000-04-23", 165, 1,"2005-04-17",123,321);
2> GO
(1 rows affected)
1> INSERT INTO Billings VALUES (6, 6, "2001-06-14", 165, 1,"2005-04-18",123,321);
2> GO
(1 rows affected)
1> INSERT INTO Billings VALUES (7, 7, "2002-07-15", 165, 1,"2005-04-19",123,321);
2> GO
(1 rows affected)
1> INSERT INTO Billings VALUES (8, 8, "2003-08-16", 165, 1,"2005-04-20",123,321);
2> GO
(1 rows affected)
1> INSERT INTO Billings VALUES (9, 9, "2004-09-17", 165, 1,"2005-04-21",123,321);
2> GO
(1 rows affected)
1> INSERT INTO Billings VALUES (0, 0, "2005-10-18", 165, 1,"2005-04-22",123,321);
2> GO
(1 rows affected)
1>
2> CREATE VIEW TopBankers
3> AS
4> SELECT TOP 5 PERCENT BankerID, BillingTotal
5> FROM Billings
6> ORDER BY BillingTotal DESC
7> GO
1>
2> drop view TopBankers;
3> GO
1>
2> drop table Billings;
3> GO</source>

== A view is a SELECT statement that"s stored with the database.==



<source lang="sql">
A CREATE VIEW statement for a view named BankersMin
7>
8> create table Bankers(
9> BankerID Integer,
10> BankerName VARCHAR(20),
11> BankerContactLName VARCHAR(20),
12> BankerContactFName VARCHAR(20),
13> BankerCity VARCHAR(20),
14> BankerState VARCHAR(20),
15> BankerZipCode VARCHAR(20),
16> BankerPhone VARCHAR(20)
17> )
18> GO
1>
2> insert into Bankers values (1, "ABC Inc.","Joe","Smith","Vancouver","BC","11111","111-111-1111");
3> GO
(1 rows affected)
1> insert into Bankers values (2, "DEF Inc.","Red","Rice", "New York", "DE","22222","222-222-2222");
2> GO
(1 rows affected)
1> insert into Bankers values (3, "HJI Inc.","Kit","Cat", "Paris", "CA","33333","333-333-3333");
2> GO
(1 rows affected)
1> insert into Bankers values (4, "QWE Inc.","Git","Black","Regina", "ER","44444","444-444-4444");
2> GO
(1 rows affected)
1> insert into Bankers values (5, "RTY Inc.","Wil","Lee", "Toronto", "YU","55555","555-555-5555");
2> GO
(1 rows affected)
1> insert into Bankers values (6, "YUI Inc.","Ted","Larry","Calgary", "TY","66666","666-666-6666");
2> GO
(1 rows affected)
1> insert into Bankers values (7, "OIP Inc.","Yam","Act", "San Franc","FG","77777","777-777-7777");
2> GO
(1 rows affected)
1> insert into Bankers values (8, "SAD Inc.","Hit","Eat", "Orland", "PO","88888","888-888-8888");
2> GO
(1 rows affected)
1> insert into Bankers values (9, "DFG Inc.","Sad","Lee", "Wisler", "PL","99999","999-999-9999");
2> GO
(1 rows affected)
1> insert into Bankers values (0, "GHJ Inc.","Bit","Lee", "Ticker", "MN","00000","000-000-0000");
2> GO
(1 rows affected)
1>
2>
3>
4>
5> CREATE VIEW BankersMin AS
6> SELECT BankerName, BankerState, BankerPhone
7> FROM Bankers
8> GO
1>
2> drop view BankersMin;
3> drop table Bankers;
4> GO</source>

== Create a view based on correlated subquery and then query it==



<source lang="sql">
4> CREATE TABLE Orders (
5> OrderID int IDENTITY (1, 1) NOT NULL ,
6> CustomerID nchar (5) NULL ,
7> EmployeeID int NULL ,
8> OrderDate datetime NULL ,
9> RequiredDate datetime NULL ,
10> ShippedDate datetime NULL ,
11> ShipVia int NULL ,
12> Freight money NULL DEFAULT (0),
13> ShipName nvarchar (40) NULL ,
14> ShipAddress nvarchar (60) NULL ,
15> ShipCity nvarchar (15) NULL ,
16> ShipRegion nvarchar (15) NULL ,
17> ShipPostalCode nvarchar (10) NULL ,
18> ShipCountry nvarchar (15) NULL
19>
20>
21> )
22> GO
1>
2> CREATE TABLE Customers (
3> CustomerID nchar (5) NOT NULL ,
4> CompanyName nvarchar (40) NOT NULL ,
5> ContactName nvarchar (30) NULL ,
6> ContactTitle nvarchar (30) NULL ,
7> Address nvarchar (60) NULL ,
8> City nvarchar (15) NULL ,
9> Region nvarchar (15) NULL ,
10> PostalCode nvarchar (10) NULL ,
11> Country nvarchar (15) NULL ,
12> Phone nvarchar (24) NULL ,
13> Fax nvarchar (24) NULL
14> )
15> GO
1>
2> INSERT Customers VALUES("1","A","Maria", "Sales", "Str. 57", "Berlin" ,NULL,"12209", "Germany","111-1111111","111-1111111")
3> INSERT Customers VALUES("2","M","Joe", "Owner", "Ave. 231","Vancouver" ,NULL,"05023", "Mexico", "(222) 222-3332",NULL)
4> INSERT Customers VALUES("3","H","Thomas", "Sales", "Sq. 111","London" ,NULL,"1D00P", "UK", "(444) 444-4444","(444) 444-4444")
5> INSERT Customers VALUES("4","B","Berg", "Order", "Blv 8","Toronto" ,NULL,"00222", "Sweden", "4444-55 55 65","5555-55 55 55")
6> INSERT Customers VALUES("5","S","Moos", "Sales", "Fort 57","New York" ,NULL,"68306", "Germany","6666-66666","6666-77777")
7> INSERT Customers VALUES("6","F","Cite", "Manager","24 ","Dalles" ,NULL,"67000", "France", "88.60.15.31","88.60.15.32")
8> INSERT Customers VALUES("7","C","Sommer", "Owner", "Araq, 67","Paris" ,NULL,"28023", "Spain", "(91) 555 22 82","(91) 555 91 99")
9> INSERT Customers VALUES("8","P","Leb", "Owner", "12 ","Beijing" ,NULL,"13008", "France", "91.24.45.40","91.24.45.41")
10> INSERT Customers VALUES("9","D","Elizabeth","Manager","23 Blvd.","Tsawassen","BC", "T2F8M4","Canada", "(604) 555-4729","(604) 555-3745")
11> go
(1 rows affected)
(1 rows affected)
(1 rows affected)
(1 rows affected)
(1 rows affected)
(1 rows affected)
(1 rows affected)
(1 rows affected)
(1 rows affected)
1>
2>
3> Create VIEW dbo.VCustsWithOrders
4> AS
5> SELECT TOP (100) PERCENT
6> Country, CustomerID, CompanyName, ContactName, ContactTitle,
7> Address, City, Region, PostalCode, Phone, Fax
8> FROM Customers AS C
9> WHERE EXISTS
10> (SELECT * FROM dbo.Orders AS O
11> WHERE O.CustomerID = C.CustomerID)
12> ORDER BY Country;
13> GO
1>
2> SELECT Country, CustomerID, CompanyName
3> FROM dbo.VCustsWithOrders;
4> GO
Country CustomerID CompanyName
--------------- ---------- ----------------------------------------
(0 rows affected)
1>
2>
3> drop VIEW dbo.VCustsWithOrders;
4> GO
1>
2> drop table orders;
3> drop table customers;
4> GO
1></source>

== Creating an Indexed View (CLUSTERED, NONCLUSTERED)==



<source lang="sql">
3>
4> CREATE TABLE dbo.employee(
5> id INTEGER NOT NULL PRIMARY KEY,
6> first_name VARCHAR(10),
7> last_name VARCHAR(10),
8> salary DECIMAL(10,2),
9> start_Date DATETIME,
10> region VARCHAR(10),
11> city VARCHAR(20),
12> managerid INTEGER
13> );
14> GO
1> INSERT INTO dbo.employee VALUES (1, "Jason" , "Martin", 5890,"2005-03-22","North","Vancouver",3);
2> GO
(1 rows affected)
1> INSERT INTO dbo.employee VALUES (2, "Alison", "Mathews",4789,"2003-07-21","South","Utown",4);
2> GO
(1 rows affected)
1> INSERT INTO dbo.employee VALUES (3, "James" , "Smith", 6678,"2001-12-01","North","Paris",5);
2> GO
(1 rows affected)
1> INSERT INTO dbo.employee VALUES (4, "Celia" , "Rice", 5567,"2006-03-03","South","London",6);
2> GO
(1 rows affected)
1> INSERT INTO dbo.employee VALUES (5, "Robert", "Black", 4467,"2004-07-02","East","Newton",7);
2> GO
(1 rows affected)
1> INSERT INTO dbo.employee VALUES (6, "Linda" , "Green" , 6456,"2002-05-19","East","Calgary",8);
2> GO
(1 rows affected)
1> INSERT INTO dbo.employee VALUES (7, "David" , "Larry", 5345,"2008-03-18","West","New York",9);
2> GO
(1 rows affected)
1> INSERT INTO dbo.employee VALUES (8, "James" , "Cat", 4234,"2007-07-17","West","Regina",9);
2> GO
(1 rows affected)
1> INSERT INTO dbo.employee VALUES (9, "Joan" , "Act", 6123,"2001-04-16","North","Toronto",10);
2> GO
(1 rows affected)
1>
2> select * from dbo.employee;
3> GO
id first_name last_name salary start_Date region city managerid
----------- ---------- ---------- ------------ ----------------------- ---------- -------------------- -----------
1 Jason Martin 5890.00 2005-03-22 00:00:00.000 North Vancouver 3
2 Alison Mathews 4789.00 2003-07-21 00:00:00.000 South Utown 4
3 James Smith 6678.00 2001-12-01 00:00:00.000 North Paris 5
4 Celia Rice 5567.00 2006-03-03 00:00:00.000 South London 6
5 Robert Black 4467.00 2004-07-02 00:00:00.000 East Newton 7
6 Linda Green 6456.00 2002-05-19 00:00:00.000 East Calgary 8
7 David Larry 5345.00 2008-03-18 00:00:00.000 West New York 9
8 James Cat 4234.00 2007-07-17 00:00:00.000 West Regina 9
9 Joan Act 6123.00 2001-04-16 00:00:00.000 North Toronto 10
(9 rows affected)
1>
2>
3>
4> CREATE VIEW dbo.v_employee
5> WITH SCHEMABINDING
6> AS
7> SELECT ID, first_name
8> FROM dbo.employee
9> GO
1>
2> SET STATISTICS IO ON
3> GO
1> SELECT TOP 5 ID, first_name
2> FROM dbo.v_employee
3>
4> CREATE UNIQUE CLUSTERED INDEX UCI_v_employee
5> ON dbo.v_employee (ID)
6> GO
ID first_name
----------- ----------
1 Jason
2 Alison
3 James
4 Celia
5 Robert
(5 rows affected)
Table "employee". Scan count 1, logical reads 2, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical
reads 0, lob read-ahead reads 0.
1>
2> CREATE NONCLUSTERED INDEX NI_v_employee
3> ON dbo.v_employee (First_Name)
4> GO
1>
2> SELECT TOP 5 ID, first_name
3> FROM dbo.v_employee
4>
5> SET STATISTICS IO OFF
6> GO
ID first_name
----------- ----------
1 Jason
2 Alison
3 James
4 Celia
5 Robert
(5 rows affected)
Table "employee". Scan count 1, logical reads 2, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical
reads 0, lob read-ahead reads 0.
1>
2>
3> drop view dbo.v_employee;
4> GO
1> drop table dbo.employee;
2> GO
1>
2>
3></source>

== Encrypting aView==



<source lang="sql">
4>
5>
6> CREATE TABLE employee(
7> id INTEGER NOT NULL PRIMARY KEY,
8> first_name VARCHAR(10),
9> last_name VARCHAR(10),
10> salary DECIMAL(10,2),
11> start_Date DATETIME,
12> region VARCHAR(10),
13> city VARCHAR(20),
14> managerid INTEGER
15> );
16> GO
1> INSERT INTO employee VALUES (1, "Jason" , "Martin", 5890,"2005-03-22","North","Vancouver",3);
2> GO
(1 rows affected)
1> INSERT INTO employee VALUES (2, "Alison", "Mathews",4789,"2003-07-21","South","Utown",4);
2> GO
(1 rows affected)
1> INSERT INTO employee VALUES (3, "James" , "Smith", 6678,"2001-12-01","North","Paris",5);
2> GO
(1 rows affected)
1> INSERT INTO employee VALUES (4, "Celia" , "Rice", 5567,"2006-03-03","South","London",6);
2> GO
(1 rows affected)
1> INSERT INTO employee VALUES (5, "Robert", "Black", 4467,"2004-07-02","East","Newton",7);
2> GO
(1 rows affected)
1> INSERT INTO employee VALUES (6, "Linda" , "Green" , 6456,"2002-05-19","East","Calgary",8);
2> GO
(1 rows affected)
1> INSERT INTO employee VALUES (7, "David" , "Larry", 5345,"2008-03-18","West","New York",9);
2> GO
(1 rows affected)
1> INSERT INTO employee VALUES (8, "James" , "Cat", 4234,"2007-07-17","West","Regina",9);
2> GO
(1 rows affected)
1> INSERT INTO employee VALUES (9, "Joan" , "Act", 6123,"2001-04-16","North","Toronto",10);
2> GO
(1 rows affected)
1>
2> select * from employee;
3> GO
id first_name last_name salary start_Date region city managerid
----------- ---------- ---------- ------------ ----------------------- ---------- -------------------- -----------
1 Jason Martin 5890.00 2005-03-22 00:00:00.000 North Vancouver 3
2 Alison Mathews 4789.00 2003-07-21 00:00:00.000 South Utown 4
3 James Smith 6678.00 2001-12-01 00:00:00.000 North Paris 5
4 Celia Rice 5567.00 2006-03-03 00:00:00.000 South London 6
5 Robert Black 4467.00 2004-07-02 00:00:00.000 East Newton 7
6 Linda Green 6456.00 2002-05-19 00:00:00.000 East Calgary 8
7 David Larry 5345.00 2008-03-18 00:00:00.000 West New York 9
8 James Cat 4234.00 2007-07-17 00:00:00.000 West Regina 9
9 Joan Act 6123.00 2001-04-16 00:00:00.000 North Toronto 10
(9 rows affected)
1>
2>
3>
4> CREATE VIEW v_employee
5> WITH ENCRYPTION
6> AS
7> SELECT TOP 5
8> ID,
9> first_name
10> FROM employee
11> ORDER BY salary DESC
12> GO
1>
2> SELECT definition
3> FROM sys.sql_modules
4> WHERE object_id = OBJECT_ID("v_employee")
5> GO
definition

------------------------------------------------------------------------------------------------------------------------
------------------------------------------------------------------------------------------------------------------------
----------------
NULL

(1 rows affected)
1>
2>
3> drop view v_employee;
4> GO
1> drop table employee;
2> GO</source>

== Supplying Column Names in the Header of the View==



<source lang="sql">
18>
19> CREATE TABLE employee(
20> id INTEGER NOT NULL PRIMARY KEY,
21> first_name VARCHAR(10),
22> last_name VARCHAR(10),
23> salary DECIMAL(10,2),
24> start_Date DATETIME,
25> region VARCHAR(10),
26> city VARCHAR(20),
27> managerid INTEGER
28> );
29> GO
1> INSERT INTO employee VALUES (1, "Jason" , "Martin", 5890,"2005-03-22","North","Vancouver",3);
2> GO
(1 rows affected)
1> INSERT INTO employee VALUES (2, "Alison", "Mathews",4789,"2003-07-21","South","Utown",4);
2> GO
(1 rows affected)
1> INSERT INTO employee VALUES (3, "James" , "Smith", 6678,"2001-12-01","North","Paris",5);
2> GO
(1 rows affected)
1> INSERT INTO employee VALUES (4, "Celia" , "Rice", 5567,"2006-03-03","South","London",6);
2> GO
(1 rows affected)
1> INSERT INTO employee VALUES (5, "Robert", "Black", 4467,"2004-07-02","East","Newton",7);
2> GO
(1 rows affected)
1> INSERT INTO employee VALUES (6, "Linda" , "Green" , 6456,"2002-05-19","East","Calgary",8);
2> GO
(1 rows affected)
1> INSERT INTO employee VALUES (7, "David" , "Larry", 5345,"2008-03-18","West","New York",9);
2> GO
(1 rows affected)
1> INSERT INTO employee VALUES (8, "James" , "Cat", 4234,"2007-07-17","West","Regina",9);
2> GO
(1 rows affected)
1> INSERT INTO employee VALUES (9, "Joan" , "Act", 6123,"2001-04-16","North","Toronto",10);
2> GO
(1 rows affected)
1>
2> select * from employee;
3> GO
id first_name last_name salary start_Date region city managerid
----------- ---------- ---------- ------------ ----------------------- ---------- -------------------- -----------
1 Jason Martin 5890.00 2005-03-22 00:00:00.000 North Vancouver 3
2 Alison Mathews 4789.00 2003-07-21 00:00:00.000 South Utown 4
3 James Smith 6678.00 2001-12-01 00:00:00.000 North Paris 5
4 Celia Rice 5567.00 2006-03-03 00:00:00.000 South London 6
5 Robert Black 4467.00 2004-07-02 00:00:00.000 East Newton 7
6 Linda Green 6456.00 2002-05-19 00:00:00.000 East Calgary 8
7 David Larry 5345.00 2008-03-18 00:00:00.000 West New York 9
8 James Cat 4234.00 2007-07-17 00:00:00.000 West Regina 9
9 Joan Act 6123.00 2001-04-16 00:00:00.000 North Toronto 10
(9 rows affected)
1>
2>
3>
4> CREATE VIEW MyView(id, name)
5> AS
6> SELECT
7> id , first_name
8> FROM
9> Employee
10> GO
1>
2> select * from MyView
3> GO
id name
----------- ----------
1 Jason
2 Alison
3 James
4 Celia
5 Robert
6 Linda
7 David
8 James
9 Joan
(9 rows affected)
1>
2> drop view MyView
3> GO
1>
2>
3> drop table employee;
4> GO</source>

== The syntax of the CREATE VIEW statement==



<source lang="sql">
CREATE VIEW view_name [(column_name_1 [, column_name_2]...)]
[WITH {ENCRYPTION|SCHEMABINDING|ENCRYPTION,SCHEMABINDING}]
AS
select_statement
[WITH CHECK OPTION]</source>

== Using the ORDER BY Clause in a View with TOP 100 PERCENT==



<source lang="sql">
4>
5> CREATE TABLE employee(
6> id INTEGER NOT NULL PRIMARY KEY,
7> first_name VARCHAR(10),
8> last_name VARCHAR(10),
9> salary DECIMAL(10,2),
10> start_Date DATETIME,
11> region VARCHAR(10),
12> city VARCHAR(20),
13> managerid INTEGER
14> );
15> GO
1> INSERT INTO employee VALUES (1, "Jason" , "Martin", 5890,"2005-03-22","North","Vancouver",3);
2> GO
(1 rows affected)
1> INSERT INTO employee VALUES (2, "Alison", "Mathews",4789,"2003-07-21","South","Utown",4);
2> GO
(1 rows affected)
1> INSERT INTO employee VALUES (3, "James" , "Smith", 6678,"2001-12-01","North","Paris",5);
2> GO
(1 rows affected)
1> INSERT INTO employee VALUES (4, "Celia" , "Rice", 5567,"2006-03-03","South","London",6);
2> GO
(1 rows affected)
1> INSERT INTO employee VALUES (5, "Robert", "Black", 4467,"2004-07-02","East","Newton",7);
2> GO
(1 rows affected)
1> INSERT INTO employee VALUES (6, "Linda" , "Green" , 6456,"2002-05-19","East","Calgary",8);
2> GO
(1 rows affected)
1> INSERT INTO employee VALUES (7, "David" , "Larry", 5345,"2008-03-18","West","New York",9);
2> GO
(1 rows affected)
1> INSERT INTO employee VALUES (8, "James" , "Cat", 4234,"2007-07-17","West","Regina",9);
2> GO
(1 rows affected)
1> INSERT INTO employee VALUES (9, "Joan" , "Act", 6123,"2001-04-16","North","Toronto",10);
2> GO
(1 rows affected)
1>
2> select * from employee;
3> GO
id first_name last_name salary start_Date region city managerid
----------- ---------- ---------- ------------ ----------------------- ---------- -------------------- -----------
1 Jason Martin 5890.00 2005-03-22 00:00:00.000 North Vancouver 3
2 Alison Mathews 4789.00 2003-07-21 00:00:00.000 South Utown 4
3 James Smith 6678.00 2001-12-01 00:00:00.000 North Paris 5
4 Celia Rice 5567.00 2006-03-03 00:00:00.000 South London 6
5 Robert Black 4467.00 2004-07-02 00:00:00.000 East Newton 7
6 Linda Green 6456.00 2002-05-19 00:00:00.000 East Calgary 8
7 David Larry 5345.00 2008-03-18 00:00:00.000 West New York 9
8 James Cat 4234.00 2007-07-17 00:00:00.000 West Regina 9
9 Joan Act 6123.00 2001-04-16 00:00:00.000 North Toronto 10
(9 rows affected)
1>
2>
3>
4> CREATE VIEW MyView
5> AS
6> SELECT
7> first_name
8> FROM
9> Employee
10> GO
1>
2>
3> ALTER VIEW MyView
4> AS
5> SELECT TOP 100 PERCENT
6> first_name
7> FROM
8> Employee
9> GO
1>
2>
3> SELECT * FROM MyView ORDER BY first_name
4> GO
first_name
----------
Alison
Celia
David
James
James
Jason
Joan
Linda
Robert
(9 rows affected)
1>
2> drop view MyView
3> GO
1>
2>
3> drop table employee;
4> GO
1>
2></source>

== Views have a couple of limitations.==



<source lang="sql">
they must be static, so they cannot contain any variables in the WHERE clause.
that they cannot be sorted with ORDER BY unless the TOP command is also used.
9>
10> CREATE TABLE employee(
11> id INTEGER NOT NULL PRIMARY KEY,
12> first_name VARCHAR(10),
13> last_name VARCHAR(10),
14> salary DECIMAL(10,2),
15> start_Date DATETIME,
16> region VARCHAR(10),
17> city VARCHAR(20),
18> managerid INTEGER
19> );
20> GO
1> INSERT INTO employee VALUES (1, "Jason" , "Martin", 5890,"2005-03-22","North","Vancouver",3);
2> GO
(1 rows affected)
1> INSERT INTO employee VALUES (2, "Alison", "Mathews",4789,"2003-07-21","South","Utown",4);
2> GO
(1 rows affected)
1> INSERT INTO employee VALUES (3, "James" , "Smith", 6678,"2001-12-01","North","Paris",5);
2> GO
(1 rows affected)
1> INSERT INTO employee VALUES (4, "Celia" , "Rice", 5567,"2006-03-03","South","London",6);
2> GO
(1 rows affected)
1> INSERT INTO employee VALUES (5, "Robert", "Black", 4467,"2004-07-02","East","Newton",7);
2> GO
(1 rows affected)
1> INSERT INTO employee VALUES (6, "Linda" , "Green" , 6456,"2002-05-19","East","Calgary",8);
2> GO
(1 rows affected)
1> INSERT INTO employee VALUES (7, "David" , "Larry", 5345,"2008-03-18","West","New York",9);
2> GO
(1 rows affected)
1> INSERT INTO employee VALUES (8, "James" , "Cat", 4234,"2007-07-17","West","Regina",9);
2> GO
(1 rows affected)
1> INSERT INTO employee VALUES (9, "Joan" , "Act", 6123,"2001-04-16","North","Toronto",10);
2> GO
(1 rows affected)
1>
2> select * from employee;
3> GO
id first_name last_name salary start_Date region city managerid
----------- ---------- ---------- ------------ ----------------------- ---------- -------------------- -----------
1 Jason Martin 5890.00 2005-03-22 00:00:00.000 North Vancouver 3
2 Alison Mathews 4789.00 2003-07-21 00:00:00.000 South Utown 4
3 James Smith 6678.00 2001-12-01 00:00:00.000 North Paris 5
4 Celia Rice 5567.00 2006-03-03 00:00:00.000 South London 6
5 Robert Black 4467.00 2004-07-02 00:00:00.000 East Newton 7
6 Linda Green 6456.00 2002-05-19 00:00:00.000 East Calgary 8
7 David Larry 5345.00 2008-03-18 00:00:00.000 West New York 9
8 James Cat 4234.00 2007-07-17 00:00:00.000 West Regina 9
9 Joan Act 6123.00 2001-04-16 00:00:00.000 North Toronto 10
(9 rows affected)
1>
2>
3> CREATE VIEW vwFinishedEmployeeAndCategories
4> As
5> SELECT TOP 5 PERCENT ID, First_Name from Employee;
6> GO
1>
2>
3> drop view vwFinishedEmployeeAndCategories;
4> GO
1>
2> drop table employee;
3> GO</source>

== WITH SCHEMABINDING clause protects a view by binding it to the database structure, or schema. ==



<source lang="sql">
This prevents the underlying base tables from being deleted or modified in any way that affects the view.
3>
4> CREATE TABLE dbo.employee(
5> id INTEGER NOT NULL PRIMARY KEY,
6> first_name VARCHAR(10),
7> last_name VARCHAR(10),
8> salary DECIMAL(10,2),
9> start_Date DATETIME,
10> region VARCHAR(10),
11> city VARCHAR(20),
12> managerid INTEGER
13> );
14> GO
1> INSERT INTO dbo.employee VALUES (1, "Jason" , "Martin", 5890,"2005-03-22","North","Vancouver",3);
2> GO
(1 rows affected)
1> INSERT INTO dbo.employee VALUES (2, "Alison", "Mathews",4789,"2003-07-21","South","Utown",4);
2> GO
(1 rows affected)
1> INSERT INTO dbo.employee VALUES (3, "James" , "Smith", 6678,"2001-12-01","North","Paris",5);
2> GO
(1 rows affected)
1> INSERT INTO dbo.employee VALUES (4, "Celia" , "Rice", 5567,"2006-03-03","South","London",6);
2> GO
(1 rows affected)
1> INSERT INTO dbo.employee VALUES (5, "Robert", "Black", 4467,"2004-07-02","East","Newton",7);
2> GO
(1 rows affected)
1> INSERT INTO dbo.employee VALUES (6, "Linda" , "Green" , 6456,"2002-05-19","East","Calgary",8);
2> GO
(1 rows affected)
1> INSERT INTO dbo.employee VALUES (7, "David" , "Larry", 5345,"2008-03-18","West","New York",9);
2> GO
(1 rows affected)
1> INSERT INTO dbo.employee VALUES (8, "James" , "Cat", 4234,"2007-07-17","West","Regina",9);
2> GO
(1 rows affected)
1> INSERT INTO dbo.employee VALUES (9, "Joan" , "Act", 6123,"2001-04-16","North","Toronto",10);
2> GO
(1 rows affected)
1>
2> select * from dbo.employee;
3> GO
id first_name last_name salary start_Date region city managerid
----------- ---------- ---------- ------------ ----------------------- ---------- -------------------- -----------
1 Jason Martin 5890.00 2005-03-22 00:00:00.000 North Vancouver 3
2 Alison Mathews 4789.00 2003-07-21 00:00:00.000 South Utown 4
3 James Smith 6678.00 2001-12-01 00:00:00.000 North Paris 5
4 Celia Rice 5567.00 2006-03-03 00:00:00.000 South London 6
5 Robert Black 4467.00 2004-07-02 00:00:00.000 East Newton 7
6 Linda Green 6456.00 2002-05-19 00:00:00.000 East Calgary 8
7 David Larry 5345.00 2008-03-18 00:00:00.000 West New York 9
8 James Cat 4234.00 2007-07-17 00:00:00.000 West Regina 9
9 Joan Act 6123.00 2001-04-16 00:00:00.000 North Toronto 10
(9 rows affected)
1>
2>
3>
4> CREATE VIEW dbo.v_employee
5> WITH SCHEMABINDING
6> AS
7> SELECT ID, first_name
8> FROM dbo.employee
9> GO
1>
2> SET STATISTICS IO ON
3> GO
1> SELECT TOP 5 ID, first_name
2> FROM dbo.v_employee
3>
4> CREATE UNIQUE CLUSTERED INDEX UCI_v_employee
5> ON dbo.v_employee (ID)
6> GO
ID first_name
----------- ----------
1 Jason
2 Alison
3 James
4 Celia
5 Robert
(5 rows affected)
Table "employee". Scan count 1, logical reads 2, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical
reads 0, lob read-ahead reads 0.
1>
2> CREATE NONCLUSTERED INDEX NI_v_employee
3> ON dbo.v_employee (First_Name)
4> GO
1>
2> SELECT TOP 5 ID, first_name
3> FROM dbo.v_employee
4>
5> SET STATISTICS IO OFF
6> GO
ID first_name
----------- ----------
1 Jason
2 Alison
3 James
4 Celia
5 Robert
(5 rows affected)
Table "employee". Scan count 1, logical reads 2, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical
reads 0, lob read-ahead reads 0.
1>
2>
3> drop view dbo.v_employee;
4> GO
1> drop table dbo.employee;
2> GO
1>
2>
3></source>

SQL Server/T-SQL Tutorial/View/Create View - История изменений

в 13:46, 26 мая 2010

Admin: 1 версия

← Предыдущая	Версия 13:46, 26 мая 2010
(нет различий)