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. */
--You can read from a view just as you would a table. So you can SELECT from it as follows:
--Now let's put this example in the context of the basic syntax for the CREATE VIEW statement:
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:
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:
/* 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: */
--You drop a view in the same way you would a table:
/* 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:*/
--To explore view metadata using T-SQL, you can query the sys.views catalog view:
--You can also query the INFORMATION_SCHEMA.TABLES system view, but it is slightly more complex:
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:
/* 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:*/
--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.
--To make the WHERE clause more flexible, you can declare a variable and then filter based on the variable:
/* 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: */
--You can query the function but pass the year you want to see:
/*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:*/
--Then the end user can select from Categories without needing to specify a schema:
--The basic syntax for creating a synonym is quite simple:
--You can drop a synonym using the DROP SYNONYM statement:
--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:*/
--Now suppose you add a synonym, called simply Sales.Reports:
--The query is now simplified to: