USE Northwind;
-----------------------------------
-- Create a view to format data for
-- mailing labels
-----------------------------------
CREATE
VIEW dbo.vwEmployeeAddressLabels
AS
SELECT
Address
FirstName + SPACE(1) + LastName + Char(13) + Char(10) + + Char(13) + Char(10) +
City
+
CASE WHEN Region IS NULL THEN ' ' + PostalCodeELSE ', ' + Region + ' ' + PostalCodeEND
+ Char(13) + Char(10) + CountryAS Address
FROM
dbo.Employees;
GO
-- Select from the view:
-- (best viewed with results to text)
SELECT
* FROM dbo.vwEmployeeAddressLabels;
-----------------------------------
-- Create a view to show
-- customers with the most orders
-----------------------------------
CREATE
SELECT
VIEW dbo.vwCustomersTopTen AS TOP (10) WITH TIES
c
.CustomerID,
c
.CompanyName,
COUNT(*) AS NumOrders
FROM
dbo.Customers AS cINNER JOIN dbo.Orders AS oON c.CustomerID=o.CustomerID
GROUP
BY c.CustomerID,
c
.CompanyName
ORDER
BY NumOrders DESC;
GO
-- Select from the view:
SELECT
CustomerID, CompanyName, NumOrders
FROM
dbo.vwCustomersTopTen
ORDER
BY NumOrders DESC;
--------------------------------------
-- Create a view that checks to see
-- if any Orders have details
-- with prices under 3 dollars.
-- Includes an example of using
-- a correlated subquery.
--------------------------------------
CREATE
VIEW dbo.vwOrderLowPrices
AS
SELECT OrderID, OrderDateFROM dbo.Orders AS OWHERE EXISTS
(SELECT OD.OrderID FROM dbo.[Order Details] AS OD WHERE O.OrderID = OD.OrderIDAND OD.UnitPrice < 3)
-- Select from the view:
SELECT
OrderID, OrderDate
FROM
GO
dbo.vwOrderLowPrices
--------------------------
-- The same query using
-- a JOIN instead of a subquery
--------------------------
CREATE
VIEW dbo.vwOrderLowPricesJoin
AS
GO
SELECT O.OrderID, O.OrderDateFROM dbo.Orders AS O INNER JOIN dbo.[Order Details] AS OD ON O.OrderID = OD.OrderIDWHERE OD.UnitPrice < 1
-- Select from the view:
SELECT
OrderID, OrderDate
FROM
dbo.vwOrderLowPricesJoin;
-------------------------------------
-- Derived tables and nesting views
-------------------------------------
SELECT
Derived
P.ProductName, P.UnitPrice, .AvgPrice, C.CategoryName
FROM
dbo.Products AS P
INNER
JOIN
(SELECT CategoryID, AVG(UnitPrice) AS AvgPriceFROM dbo.ProductsGROUP BY CategoryID)
Derived
AS Derived ON .CategoryID = P.CategoryID
INNER
JOIN
dbo
P
.Categories AS C ON .CategoryID = C.CategoryID
ORDER
BY P.UnitPrice DESC
-- Here are 2 views to replace the derived table
CREATE
VIEW dbo.vwAvgByCategory
AS
GO
SELECT CategoryID, AVG(UnitPrice) AS AvgPriceFROM dbo.ProductsGROUP BY CategoryID
CREATE
VIEW dbo.vwPriceListWithAvg
AS
V
SELECT P.ProductName, P.UnitPrice, .AvgPrice,
C
.CategoryNameFROM dbo.Products AS PINNER JOIN
dbo
.vwAvgByCategory AS VON V.CategoryID = P.CategoryIDINNER JOIN
dbo
P
GO
.Categories AS C ON .CategoryID = C.CategoryID
--Select from the outer view
SELECT
* FROM dbo.vwPriceListWithAvg
ORDER
BY UnitPRice DESC;
----------------------------------------
-- Using a Common Table Expression (CTE)
----------------------------------------
WITH
CategoryAverages(CategoryID, AvgPrice)
AS
(
SELECT CategoryID, AVG(UnitPrice) AS AvgPriceFROM dbo.ProductsGROUP BY CategoryID
)
SELECT
CA
P.ProductName, P.UnitPrice, .AvgPrice, C.CategoryName
FROM
dbo.Products AS P
INNER
JOIN
CategoryAverages
AS CA ON CA.CategoryID = P.CategoryID
INNER
JOIN
dbo
P
.Categories AS C ON .CategoryID = C.CategoryID
ORDER
BY P.UnitPrice DESC;
------------------------------
-- Encrypting view definitions
------------------------------
CREATE
VIEW dbo.vwEncrypted
WITH
ENCRYPTION
AS
SELECT
CustomerID, CompanyName
FROM
dbo.Customers
WHERE
City='Paris';
GO
SELECT
* FROM vwEncrypted;
-- Try getting the T-SQL back
EXEC
sp_helptext 'dbo.vwEncrypted';
-- But it works on an unencrypted view
EXEC
sp_helptext 'dbo.vwEmployeeAddressLabels';
-- Same for the new OBJECT_DEFINITION function
SELECT
OBJECT_DEFINITION(OBJECT_ID('dbo.vwEncrypted'));
SELECT
OBJECT_DEFINITION(OBJECT_ID('dbo.vwEmployeeAddressLabels'));
------------------------------
-- Using WITH CHECK OPTION
------------------------------
CREATE
VIEW dbo.vwOrdersWithCheck
AS
SELECT
OrderID, OrderDate, EmployeeID
FROM
dbo.Orders
WHERE
EmployeeID = 5
WITH
CHECK OPTION;
GO
UPDATE
dbo.vwOrdersWithCheck
SET
EmployeeID = 6;
------------------------------
-- Updating limitations
------------------------------
CREATE
VIEW dbo.vwCustomerAddresses
AS
SELECT
CustomerID, ContactName,
CompanyName
Address
+ Char(13) + Char(10) + + Char(13) + Char(10) +
City
+
CASE WHEN Region IS NULL THEN ' ' + PostalCodeELSE ', ' + Region + ' ' + PostalCodeEND
+ Char(13) + Char(10) + CountryAS Address
FROM
GO
dbo.Customers
SELECT
* FROM dbo.vwCustomerAddresses
WHERE
CustomerID='GREAL';
-- This update works
UPDATE
dbo.vwCustomerAddresses
SET
ContactName = 'Gomer Snyder'
WHERE
CustomerID = 'GREAL';
-- This update will fail, because
-- Address is a calculated column.
UPDATE
dbo.vwCustomerAddresses SET Address = 'Test Address' WHERE CustomerID = 'GREAL';
----------------------------
-- Updating views with joins
----------------------------
CREATE
VIEW dbo.vwProductByCategory
AS
SELECT C.CategoryName,
P
.ProductID, P.ProductNameFROM dbo.Products AS P INNER JOIN
dbo
.Categories AS C ON
P
GO
.CategoryID = C.CategoryID
-- Both of these will work:
UPDATE
dbo.vwProductByCategorySET ProductName = 'Tofu'
WHERE ProductID = 14
UPDATE
dbo.vwProductByCategorySET CategoryName = 'Produce'
WHERE ProductID = 14
-- But not this:
UPDATE
dbo.vwProductByCategorySET ProductName = 'Tofu', CategoryName = 'Produce'
WHERE ProductID = 14
----------------------------
-- Creating computed columns
----------------------------
-- Create the table with the expression
CREATE
TABLE dbo.LineItems
(
LineID
int IDENTITY(1,1) NOT NULL,
Price
money NOT NULL,
Quantity
smallint NOT NULL,
Total
)
AS (Price * Quantity) CONSTRAINT PK PRIMARY KEY CLUSTERED (LineID ASC)
GO
--Insert some data into the table
INSERT
INTO dbo.LineItems VALUES(35.95, 4);
INSERT
INTO dbo.LineItems VALUES(7.99, 12);
INSERT
INTO dbo.LineItems VALUES(12.5, 70);
--View the result set
SELECT
* FROM dbo.LineItems;
--------------------------------------
-- Creating and using an Indexed View
--------------------------------------
--Set the options to support indexed views.
SET
NUMERIC_ROUNDABORT OFF;
SET
ANSI_PADDING, ANSI_WARNINGS, CONCAT_NULL_YIELDS_NULL, ARITHABORT,
QUOTED_IDENTIFIER, ANSI_NULLS ON;
GO
--Create the view with schema binding.
CREATE
VIEW dbo.vwSalesByDateByProduct
WITH
SCHEMABINDING
AS
SELECT OrderDate, ProductID, SUM(UnitPrice*Quantity*(1.00-Discount)) AS Sales,
GO
COUNT_BIG(*) AS LineItemCountFROM dbo.[Order Details] OD JOIN dbo.Orders O ON OD.OrderID=O.OrderID GROUP BY OrderDate, ProductID
--Get the data
SELECT
GO
* FROM dbo.vwSalesByDateByProduct
--Create the index on the view.
CREATE
UNIQUE CLUSTERED INDEX idx_vwdSalesByDateByProduct
ON
dbo.vwSalesByDateByProduct (OrderDate, ProductID)
GO
--This query will use the view index.
SELECT
SUM(UnitPrice*Quantity*(1.00-Discount)) AS Sales, OrderDate, ProductID
FROM
dbo.[Order Details] OD JOIN dbo.Orders O
ON
OD.OrderID = O.OrderID
WHERE
ProductID in (7, 89, 22, 34)
AND OrderDate >= '05/01/1998'
GROUP
BY OrderDate, ProductID
ORDER
BY Sales DESC;
--This query will use the view index too.
SELECT
OrderDate, SUM(UnitPrice*Quantity*(1.00-Discount)) AS Mar98Sales
FROM
dbo.[Order Details] OD JOIN dbo.Orders O
ON
OD.OrderID = O.OrderID
WHERE
DATEPART(mm,OrderDate)= 3AND DATEPART(yy,OrderDate) = 1998
GROUP
BY OrderDate
ORDER
BY OrderDate;
----------
--Cleanup
----------
DROP
TABLE dbo.LineItems;
DROP
VIEW dbo.vwEmployeeAddressLabels;
DROP
VIEW dbo.vwCustomersTopTen;
DROP
VIEW dbo.vwOrderLowPrices;
DROP
VIEW dbo.vwOrderLowPricesJoin;
DROP
VIEW dbo.vwAvgByCategory;
DROP
VIEW dbo.vwPriceListWithAvg;
DROP
VIEW dbo.vwEncrypted;
DROP
VIEW dbo.vwOrdersWithCheck;
DROP
VIEW dbo.vwCustomerAddresses;
DROP
VIEW dbo.vwProductByCategory;
DROP
VIEW dbo.vwSalesByD