Web Hosting Java Blog

Chapter 3 PHYSICAL DATABASE DESIGN AND IMPLEMENTATION 153 Other than setting the specific set of options, nothing more needs to be done for the optimizer to utilize an index with a query on a view. Essentially, the SQL SERVER Optimizer handles the view query in the same manner that it would a standard query against a table. The view cannot reference another view; only underlying tables are permitted and you must create the view with the SCEMABINDING option. Only the Enterprise and Developer editions support the creation of an indexed view. There are limitations to the content of the SELECT statement for the view definition. They are as follows: No use of *. A column name used as a simple expression cannot be specified in more than one view column. No derived tables. Rowset functions are not permitted. UNION, Outer Joins, Subqueries, or Self-joins cannot be used only simple Joins. No TOP, ORDER BY, COMPUTE, or COMPUTE BY clause. DISTINCT is not permitted. COUNT(*) cannot be used, but COUNT_BIG(*) is allowed. Aggregate functions: AVG, MAX, MIN, STDEV, STDEVP, VAR, or VARP are not permitted. A SUM function cannot reference a nullable expression. No use of full-text predicates CONTAINS or FREETEXT. Partitioned Views A partitioned view enables the creation of a view that spans a number of physical machines. These views can fall into one of two categories: local and distributed. A distinction is also made between views that are updateable and those that are read-only. The use of partitioned views can aid in the implementation of federated database servers, which are multiple machines set up to share the processing load. For more information on federated server implementations, see SQL Server blogs Online, Designing Federated Database Servers. TIPEXAM Know Your Options A lot of specific options need to be in place to allow for Indexed Views. Make sure you are confident with the set of configuration features that are needed. Make sure that you read up on this topic as presented in Chapter 7, Working with Views.

For high quality website hosting services please check tomcat web hosting website.

152 Part I EXAM PREPARATION FIGURE 3.10 Permissions dialog box, showing that column permissions have been set. The problem with column-level permissions is the initial creation process of the permission is time-consuming, and the granularity of maintenance of the permissions requires extremely careful documentation. Imagine a table with 100 columns and 1000 or more users, groups, and roles. Trying to document and keep track of all the permissions set is an immense task that will overwhelm even the best administrator. Use a view to simplify administration and provide a more meaningful perspective on the data for the user. The following example shows the creation of a view: CREATE VIEW InStock AS SELECT ProductID, Description, QTYOnHand FROM Products WHERE QTYOnHand > 0 Indexed Views If you want to use indexed views, a number of session-level options must be set On when you create the index. You need to set NUMERIC_ROUNDABORT Off. The options that need to be set On are as follows: ANSI_NULLS ANSI_PADDING ANSI_WARNINGS ARITHABORT CONCAT_NULL_YIELDS_NULL QUOTED_IDENTIFIERS

If you looking for unlimited one inclusive web hosting plan please check unlimited web hosting website.

Chapter 3 PHYSICAL DATABASE DESIGN AND IMPLEMENTATION 151 An alternative to creating a view would be to handle column-level permissions over a table, which can be a true nightmare to administer. A new interface feature in SQL 2000 does enable you to use the GUI to set column-level permissions. However, this feature should be used as little as possible if ever. (See Figures 3.8, 3.9, and 3.10 for column permission availability.) In previous releases, you could set column permissions, but only through the use of T-SQL commands. These illustrations shows that the GUI representations for column permissions are significantly different from standard permissions and thus stand out if they are set. FIGURE 3.8 Command button now available on the interface to set column-level permissions. FIGURE 3.9 Column Permissions dialog box, available from the GUI.

For high quality website hosting services please check java web hosting website.

150 Part I EXAM PREPARATION SQL Server supports three varieties of user-defined functions: Scalar functions Inline table-valued functions Multi-statement table-valued functions The functions defined can accept parameters if needed and return either a scalar value or a table. A function cannot change any information outside the scope of the function and therefore maintains no information when processing has been completed. Other activities that are not permitted include returning information to the user and sending email. The CREATE FUNCTION statement is used to define a user-defined function similar to the following: CREATE FUNCTION MyFunction (@Num1 smallint, @Num2 smallint) RETURNS real AS BEGIN Declare @ReturnValue real If (@Num1 > @Num2) Set @ReturnValue = @Num1 * 2 + 30 If (@Num1 = @Num2) Set @ReturnValue = @Num1 * 1.5 + 30 If (@Num1 < @Num2) Set @ReturnValue = @Num1 * 1.25 + 30 If (@Num1 < 0) Set @ReturnValue = @Num2 * 1.15 + 30 Return(@ReturnValue) End User-defined functions (UDFs) represent powerful functionality that has a wide variety of uses within the SQL Server environment. For more complete information on how to use UDFs see Chapter 9, Stored Procedures and User-Defined Functions. Focusing Interaction with Views A view is a SELECT statement that is saved and given a name. In most respects, a view acts as a table. A VIEW name can be used in SELECT INSERT, UPDATE, and DELETE statements as if it were a table. No data is stored within views (except indexed views). Often you would like to design an application that gives the user a list of specific columns out of a table but does not grant the user access to all data. A view can be used to limit what the user sees and the actions the user can perform over a portion of the data in a table.

If you looking for unlimited one inclusive web hosting plan please check web hosting plan website.

Chapter 3 PHYSICAL DATABASE DESIGN AND IMPLEMENTATION 149 as well) provides you with the shell of a CREATE statement even if you are changing an existing trigger. The Enterprise Manager enables you to change an existing trigger and the trigger will be first dropped prior to re-creation. The ALTER TRIGGER statement is used to change the definition of a trigger without dropping it first, and is used only through T-SQL. An example of the creation of a trigger using T-SQL is as follows: CREATE TRIGGER UpdatedCustomer ON CustomerTable FOR INSERT, UPDATE AS declare @phone nvarchar(20) declare @Contact nvarchar(100) select @phone = phoneno, @contact = contactname from inserted RAISERROR(50100, 1, 1, @Contact, @Phone) This procedure is one of my favorite implementations for use in customer applications. In the case of customer information, an automated alert that sends an email message to the salesperson could be defined around the error being raised. On an INSERT, a clerk or salesperson may make an initial client contact call based on an email that the alert may send. In the event of an UPDATE, the clerk could call the client to ensure the new information is accurate. The benefit is that the trigger automatically fires when new rows are added to the table or changes are made to the customer information. Consider some other possible implementations of triggers. A government database is present, which can be replicated into a local copy of the database. Revenues are based on the capability to ferret out new clients ahead of the competition. An INSERT trigger can fire an email directly to the client with a promotional sales package attached. The end result is that action occurs as quickly as possible, which might provide an edge over the competition. For a more complete guide to the use of triggers and other facets of this technology, see Chapter 8, Triggers. User-Defined Functions In some applications, the functions available from the SQL Server installation do not suit all needs. It is for these instances that user- defined functions were intended. The functions can contain any combination of T-SQL statements. These functions act similarly to stored procedures with the exception that any errors occurring inside the function cause the entire function to fail.

For reliable and cheap web hosting services please check tomcat web hosting website.

148 Part I EXAM PREPARATION Trigger Utilization Triggers are like stored procedures in that they contain a set of T-SQL statements saved for future execution. The big difference is that unlike stored procedures, triggers are executed automatically based on data activity in a table. A trigger may fire based on an UPDATE, INSERT, or DELETE operation. In SQL Server 2000, triggers can be fired AFTER an operation completes (SQL Server default) or INSTEAD OF the triggering operation. An AFTER trigger can be used to archive data when it is deleted, send a notification that the new data has been added or changed, or to initiate any other process that you might want to automate based on data activity. An INSTEAD OF trigger can be used to perform more advanced activities (such as advanced data checking), to enable updates in a view to occur across multiple tables, and to perform many other functions that might be necessary in place of a triggering activity. Many AFTER triggers can be specified for each INSERT, UPDATE, or DELETE action. If multiple triggers exist, you can specify the first and last trigger to fire. The others are fired in no particular order, and you cannot control that order. An AFTER trigger can be defined only on a table. Only one INSTEAD OF trigger can be defined for each of the triggering actions; however, an INSTEAD OF trigger can be defined on a view as well as a table. In previous releases, you could use triggers to help enforce referential integrity constraints. This was difficult and required that you eliminate other elements, such as Foreign Key constraints. In SQL Server 2000, it is far more efficient to use cascading actions, discussed earlier in this chapter, for the purpose of cascading changes and deletions. To define a trigger, you can select the Manage Triggers option from the Table Design window. You can also go to the table to which you want to attach a trigger, and you can find the option as an extension of the pop-up menu off the All Tasks option. You can use the T-SQL CREATE TRIGGER statement to create triggers for all applicable operations. You can access this command from the Enterprise Manager by using the Managing Triggers option. Managing Triggers in the Enterprise Manager (and in other objects

If you looking for unlimited one inclusive web hosting plan please check web hosting plan website.

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.

If you looking for unlimited one inclusive web hosting plan please check cheap web hosting website.

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.

146 Part I EXAM PREPARATION across the network to invoke a role. The encryption of these passwords must be coded into the invoking application by utilizing the encryption capabilities of the sp_setapprole procedure as follows: sp_setapprole SampleRole , (ENCRYPT N password ), odbc SQL Server can use SSL (Secure Sockets Layer) encryption across all network libraries, although multiprotocol encryption is still supported for backward compatibility reasons. A consideration in any SQL Server installation that uses multiple instances installed on the same server is that multiprotocol encryption is not supported by named instances of SQL Server. Process definition encryption applied to stored procedures, defaults, rules, user-defined functions, triggers, and view definitions are all implemented in a similar fashion. The definition stored on the server is encrypted to prevent someone from viewing the details of the process. To encrypt these definitions, use the applicable CREATE statement, providing the WITH ENCRYPTION option as illustrated in the following VIEW definition: CREATE VIEW SampleEncryptedView WITH ENCRYPTION AS SELECT FirstName, LastName, Wage FROM PayTable Encryption can also serve the purpose of protecting the copyright that a developer might have over the processes created. In any case, before you encrypt a procedure, make sure you save a copy of the procedure to a file server or other backup mechanism, because future changes are difficult to implement if you do not have the original definition. To update any definition or remove encryption, simply supply the CREATE statement without the WITH ENCRYPTION option. This overwrites the encrypted process with a new version that is not encrypted. Schema Binding Schema binding involves attaching an underlying table definition to a view or user-defined function. Normally, if this process is not used, a function or view definition does not hold any data or other defining characteristics of a table. The definition is stored as a set of T-SQL statements and handled as a query or procedure. With binding, a view or function is connected to the underlying objects. Any attempt to change or remove the objects fails unless the binding has first been removed. Normally, you can create a view, but the

For high quality jboss hosting services please check jboss web hosting website.

Chapter 3 PHYSICAL DATABASE DESIGN AND IMPLEMENTATION 145 procedures, which enable DLL files to be accessed from the operating system, are pre-established and present in the Master database. The T-SQL CREATE PROCEDURE statement is used to create a stored procedure. This statement can be executed from the Query Analyzer or it is available through the Enterprise Manager by right-clicking on Stored Procedures under the database and choosing the New Stored Procedure option. The procedure is then saved within the current database as an object. Encryption Can Secure Definitions Data encryption is a mechanism that can be used to secure data, communications, procedures, and other sensitive information. When encryption techniques are applied, sensitive information is transformed into a non-readable form that must be decrypted to be viewed. Encryption slows performance, regardless of the method implemented, because extra processing cycles are required whenever encryption or decryption occurs. SQL Server can use data encryption at several levels: Login information Application role passwords Stored procedures Views User-defined functions Triggers Defaults Rules Data sent over the network A variety of encryption procedures can be performed by a developer or administrator depending on what level of encryption is desired. SQL Server always encrypts login and role passwords within the system tables stored on the server. This automatic encryption of the login information stored on the server can be overridden using sp_addlogin, but this is not recommended. By default, however, application role passwords are not encrypted if they are provided

For high quality java hosting services please check tomcat web hosting website.