Chapter 3 PHYSICAL DATABASE DESIGN AND IMPLEMENTATION 147
Chapter 3 PHYSICAL DATABASE DESIGN AND IMPLEMENTATION 147 underlying table might be changed so that the view no longer works. To prevent the underlying table from being changed, the view can be schema-bound to the table. Any table changes, which would break the view, are not allowed. Indexed views require that a view be defined with the binding option and also that any user-defined functions referenced in the view must also be bound. In previous versions of SQL Server, it was not possible to define an index on a view. With the advent of binding, however, meaningful indexes can now be defined over a view that has been bound to the underlying objects. Other system options must be set to define an indexed view. These options are discussed later in the chapter in the Indexed Views section. More information on the use of all types of views can be found in Chapter 7, Working With Views. The following example uses T-SQL of the creation of a schema-bound view: CREATE VIEW SampleBoundView WITH SCHEMABINDING AS SELECT ProductID, Description, PurchPrice, PurchPrice * Markup AS SalesPrice FROM dbo.ProductTable Recompilation of Procedures Adding or altering indexes or changing a stored procedure causes SQL Server to automatically recompile the procedure. This optimization occurs the next time the stored procedure is run, but only after SQL Server is restarted. In instances where you want to force a recompilation, you can use the sp_recompile system-stored procedure. Alternatively, you can use the WITH RECOMPILE option when you create or execute a stored procedure. Stored procedures are dealt with in depth in Chapter 9, Stored Procedures and User- Defined Functions. Extended Stored Procedures These procedures, like many of the system-stored procedures, are loaded automatically when you install SQL Server. Extended stored procedures access DLL files stored on the machine to enable the calling of the functions contained in the DLLs from within a SQL Server application. You might add to this set of procedures stored in the Master database using the sp_addextendedproc procedure as follows: sp_addextendedproc MyFunction , MyFunctionSet.DLL TIPEXAM The Many Meanings of Schema The word schema has several different uses and definitions within SQL Server; the exam will leverage this and attempt to confuse the separate definitions. Make sure you are aware of how the term is used with relation to XML, Indexed Views, and maintaining metadata. For more information about these particulars, you can consult Chapter 5, Advanced Data Retrieval and Modification, in the section on XML schema; Chapter 7, Working With Views, in the section on indexed views; and Chapter 12, Monitoring SQL Server 2000, in the section on metadata.
For high quality java hosting services please check java web hosting website.