SQL Server/T-SQL Tutorial/View/Create View — различия между версиями
Admin (обсуждение | вклад) м (1 версия) |
|
(нет различий)
| |
Версия 13:46, 26 мая 2010
Содержание
- 1 A CREATE VIEW statement that uses TOP and ORDER BY clauses
- 2 A view is a SELECT statement that"s stored with the database.
- 3 Create a view based on correlated subquery and then query it
- 4 Creating an Indexed View (CLUSTERED, NONCLUSTERED)
- 5 Encrypting aView
- 6 Supplying Column Names in the Header of the View
- 7 The syntax of the CREATE VIEW statement
- 8 Using the ORDER BY Clause in a View with TOP 100 PERCENT
- 9 Views have a couple of limitations.
- 10 WITH SCHEMABINDING clause protects a view by binding it to the database structure, or schema.
A CREATE VIEW statement that uses TOP and ORDER BY clauses
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
A view is a SELECT statement that"s stored with the database.
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
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>
Creating an Indexed View (CLUSTERED, NONCLUSTERED)
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>
Encrypting aView
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
Supplying Column Names in the Header of the View
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
The syntax of the CREATE VIEW statement
CREATE VIEW view_name [(column_name_1 [, column_name_2]...)]
[WITH {ENCRYPTION|SCHEMABINDING|ENCRYPTION,SCHEMABINDING}]
AS
select_statement
[WITH CHECK OPTION]
Using the ORDER BY Clause in a View with TOP 100 PERCENT
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>
Views have a couple of limitations.
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
WITH SCHEMABINDING clause protects a view by binding it to the database structure, or schema.
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>