Chapter 05 - Designing and Creating Views, Inline Functions and Synonyms

Ky leksion 120 minutesh perfshin faqet 299 - 328 te librit te kursit.

Designing and Implementing Views and Inline Functions

/* When you use the SchemaBinding keyword while creating a view or function you bind the structure of any underlying tables or views. It means that as long as that schemabound object exists as a schemabound object you are limited in changes that can be made to the tables or views that it refers to. */

CREATE VIEW Sales.OrderTotalsByYear

WITH SCHEMABINDING

AS

SELECT

YEAR(O.orderdate) AS orderyear,

SUM(OD.qty) AS qty

FROM Sales.Orders AS O

JOIN Sales.OrderDetails AS OD

ON OD.orderid = O.orderid

GROUP BY YEAR(orderdate);

GO

--You can read from a view just as you would a table. So you can SELECT from it as follows:

SELECT orderyear, qty

FROM Sales.OrderTotalsByYear;

--Now let's put this example in the context of the basic syntax for the CREATE VIEW statement:

CREATE VIEW [ schema_name . ] view_name [ (column [ ,...n ] ) ]

[ WITH <view_attribute> [ ,...n ] ]

AS select_statement

[ WITH CHECK OPTION ] [ ; ]

/*

You can specify the set of output columns following the view name. For example, you could rewrite the CREATE VIEW statement for Sales.OrderTotalsByYear and specify the column names right after the view name instead of in the SELECT statement:

*/

CREATE VIEW Sales.OrderTotalsByYear(orderyear, qty)

WITH SCHEMABINDING

AS

SELECT

YEAR(O.orderdate),

SUM(OD.qty)

FROM Sales.Orders AS O

JOIN Sales.OrderDetails AS OD

ON OD.orderid = O.orderid

GROUP BY YEAR(orderdate);

GO

/*

After you have created a view, you can use the ALTER VIEW command to change the view's structure and add or remove the view properties. An ALTER VIEW simply redefines how the view works by re-issuing the entire view definition. For example, you could redefine the Sales.OrderTotalsByYear view to add a new column for the region the order was shipped to, the shipregion column:

*/

ALTER VIEW Sales.OrderTotalsByYear

WITH SCHEMABINDING

AS

SELECT

O.shipregion,

YEAR(O.orderdate) AS orderyear,

SUM(OD.qty) AS qty

FROM Sales.Orders AS O

JOIN Sales.OrderDetails AS OD

ON OD.orderid = O.orderid

GROUP BY YEAR(orderdate), O.shipregion;

GO

/* Now you can change the way you SELECT from the view, just as you would a table to include the new column; and you can optionally order the results with an ORDER BY: */

SELECT shipregion, orderyear, qty

FROM Sales.OrderTotalsByYear

ORDER BY shipregion;

--You drop a view in the same way you would a table:

DROP VIEW Sales.OrderTotalsByYear;

/* When you need to create a new view and conditionally replace the old view, you must first drop the old view and then create the new view. The following example shows one method:*/

IF OBJECT_ID('Sales.OrderTotalsByYear', 'V') IS NOT NULL

DROP VIEW Sales.OrderTotalsByYear;

GO

CREATE VIEW Sales.OrderTotalsByYear

...

--To explore view metadata using T-SQL, you can query the sys.views catalog view:

USE TSQL2012;

GO

SELECT name, object_id, principal_id, schema_id, type

FROM sys.views;

--You can also query the INFORMATION_SCHEMA.TABLES system view, but it is slightly more complex:

SELECT SCHEMA_NAME, TABLE_NAME, TABLE_TYPE

FROM INFORMATION_SCHEMA.TABLES

WHERE TABLE_TYPE = 'VIEW';

/*

Using sys.views is more reliable, and from it you can join to other catalog views such as sys.sql_modules to get further information.

An inline table-valued function returns a row set based on a SELECT statement you coded into the function. In effect, you treat the table-valued function as a table and SELECT FROM it. For example, you can create an inline function that would operate just like the Sales.OrderTotalsByYear view, with no parameters, as follows:

*/

USE TSQL2012;

GO

IF OBJECT_ID (N'Sales.fn_OrderTotalsByYear', N'IF') IS NOT NULL

DROP FUNCTION Sales.fn_OrderTotalsByYear;

GO

CREATE FUNCTION Sales.fn_OrderTotalsByYear ()

RETURNS TABLE

AS

RETURN

(

SELECT

YEAR(O.orderdate) AS orderyear,

SUM(OD.qty) AS qty

FROM Sales.Orders AS O

JOIN Sales.OrderDetails AS OD

ON OD.orderid = O.orderid

GROUP BY YEAR(orderdate)

);

GO

/* In the above example, the SELECT statement was just as complex as the original Sales.OrderTotalsByYear view. If you don't need any additional columns from the table, you could actually simplify the function by selecting from the view directly:*/

USE TSQL2012;

GO

IF OBJECT_ID (N'Sales.fn_OrderTotalsByYear', N'IF') IS NOT NULL

DROP FUNCTION Sales.fn_OrderTotalsByYear;

GO

CREATE FUNCTION Sales.fn_OrderTotalsByYear ()

RETURNS TABLE

AS

RETURN

(

SELECT orderyear, qty FROM Sales.OrderTotalsByYear

);

GO

--Consider that if you only wanted to see the year 2007, you would just put that in a WHERE clause when selecting from the view.

SELECT orderyear, qty

FROM [Sales].[OrderTotalsByYear]

WHERE orderyear = 2007;

--To make the WHERE clause more flexible, you can declare a variable and then filter based on the variable:

DECLARE @orderyear int = 2007;

SELECT orderyear, qty

FROM Sales.OrderTotalsByYear

WHERE orderyear = @orderyear;

/* Keeping this in mind, it is now just a quick step to an inline function. Instead of declaring a variable @orderyear, define the parameter @orderyear in the function while filtering the SELECT statement in the same way as previously: */

USE TSQL2012;

GO

IF OBJECT_ID (N'Sales.fn_OrderTotalsByYear', N'IF') IS NOT NULL

DROP FUNCTION Sales.fn_OrderTotalsByYear;

GO

CREATE FUNCTION Sales.fn_OrderTotalsByYear (@orderyear int)

RETURNS TABLE

AS

RETURN

(

SELECT orderyear, qty FROM Sales.OrderTotalsByYear

WHERE orderyear = @orderyear

);

GO

--You can query the function but pass the year you want to see:

SELECT orderyear, qty FROM Sales.fn_OrderTotalsByYear(2007);

Using Synonyms

/*To create a synonym, you simply assign a synonym name, and specify the name of the database object it will be assigned to. For example, you could define a synonym called Categories and put it in the dbo schema so that users do not need to remember the schema-object name Production.Categories in their queries. You can issue:*/

USE TSQL2012;

GO

CREATE SYNONYM dbo.Categories FOR Production.Categories;

GO

--Then the end user can select from Categories without needing to specify a schema:

SELECT categoryid, categoryname, description *

FROM Categories;

--The basic syntax for creating a synonym is quite simple:

CREATE SYNONYM schema_name.synonym_name FOR object_name

--You can drop a synonym using the DROP SYNONYM statement:

DROP SYNONYM dbo.Categories

--There is no ALTER SYNONYM. As a result, just as with a database schema, to change a synonym you must drop and recreate it.

/*For example, suppose the database DB01 has a view called Sales.Reports, and it is on the same server as TSQL2012. Then to query it from TSQL2012, you must write something like:*/

SELECT report_id, report_name FROM ReportsDB.Sales.Reports

--Now suppose you add a synonym, called simply Sales.Reports:

CREATE SYNONYM Sales.Reports FOR ReportsDB.Sales.Reports

--The query is now simplified to:

SELECT report_id, report_name FROM Sales.Reports