Web Hosting Java Blog

144 Part I EXAM PREPARATION Stored Procedures A stored procedure is a set of T-SQL statements that can be saved as a database object for future and repeated executions. With stored procedures, you can enable a lot of the development and processing to be performed on the server, producing much more efficient and lightweight front-end applications. Any commands that can be entered via SQL Query tools can be included in a stored procedure. Using stored procedures is a powerful and flexible technique for performing tasks within an application. A stored procedure, when it is first used, is compiled into an execution plan that remains in the procedure cache. This provides for some of the performance over ad- hoc operations. The performance improvements in SQL 7 and 2000 are not as drastic as in previous versions because changes in the way that other operations now execute provides them with some of the same benefits as stored procedures. A stored procedure can accept parameters, process operations against any number of databases, and return results to the calling process. Performance will be discussed in more detail in Chapter 12, Monitoring SQL Server 2000. The SQL Server 2000 implementation has many other capabilities that speed processing, secure data, reduce bandwidth usage, and enable advanced operations to be performed. Procedures that are repeatedly used will be held in memory in the SQL Server procedure cache for faster execution. A stored procedure, like other operations, can be encrypted to protect the details of the operation (the following section covers encryption). An application might need to send several operations across a network and respond conditionally to the results. This can be handled with a single call if the logic is contained in a single stored procedure. The use of local and global cursors can expose information to the application or other applications as needed, giving provisions for complex development processes with conversations between separate processes. Temporary stored procedures used frequently in earlier versions are still supported by SQL Server, although improvements in other areas should eliminate or reduce the need for their use. The most significant improvement is the capability to compile and maintain most SQL operations in cache for prolonged periods. Many system-stored procedures have already been created and are available upon installation of SQL Server. Extended stored

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

Chapter 3 PHYSICAL DATABASE DESIGN AND IMPLEMENTATION 143 FIGURE 3.7 Primary Key/Foreign Key referential integrity. Using Cascade Action to Maintain Integrity New to SQL Server with the 2000 release is a cascading action feature that many other database environments have been enjoying for quite some time. Cascading actions affect update and delete activity where an existing Foreign Key value is changed or removed. Cascade action is controlled through the CREATE and ALTER TABLE statements, with clauses for ON DELETE and ON UPDATE. You can also select these features using the Enterprise Manager. In a cascading update, when you change the value of a key in a situation where a Foreign Key in another table references the key value, those changed values are reflected back to the other tables. A similar thing happens with a delete operation: if a record is deleted, then all subsidiary records in other tables are also deleted. For example, if an invoice record is deleted from an invoice table that has invoice details stored in another table and referenced by a Foreign Key, then the details would also be removed. A series of cascading actions could easily result from the update or deletion of important keys. For example, the deletion of a customer could cause the deletion of all that customer s orders, which could cause the deletion of all its invoices, which in turn could cause the deletion of all the customer s invoice details. For this reason, careful system design is important and the potential archival of data through the use of triggers should be considered. In the case of multiple cascading actions, all the triggers to be fired by the effects of the original deletion fire first. AFTER triggers then fire on the original table and then the AFTER triggers in the table chain subsequently fire. TIPEXAM Cascading Actions Is a New Feature You can expect that something about it will be asked on the exam. Also be prepared for the exam by knowing all the results and implications of cascading actions. For example, you might be asked what occurs when a record contained in the parent table is deleted, or has its key value changed.

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

142 Part I EXAM PREPARATION WARNINGTIPEXAM Documentation Discrepancy The capability to set a relationship to any unique column is not noted in most SQL Server documentation. SQL Server blogs Online reports that a Foreign Key must be set to a Primary Key or a UNIQUE constraint. In SQL Server, you can create a relationship against a Primary Key, unique index,or a UNIQUE constraint. You need not have a Primary Key or constraint. You can even set a unique index to ignore duplicates and the operation will still be permitted. Go with the Documentation If you run into this on the exam, the correct answer is likely to be one chosen based on the documentation and not on actual functionality. The capability to set a relationship to any unique column is not noted in documentation. The correct technique to use when answering an exam question would be one that involves a Foreign Key set to a Primary Key or Unique constraint. Primary Key Constraint A Primary Key constraint enforces entity integrity in that it does not permit any two rows in a table to have the same key value. This enables each row to be uniquely defined in its own right. Although a Primary Key should be created when a table is initially created, it can be added or changed at any time after creation. A Primary Key cannot have NULL content nor can there be any duplicate values. SQL Server automatically creates a unique index to enforce the exclusiveness of each value. If a Primary Key is referenced by a Foreign Key in another table, the Primary Key cannot be removed unless the Foreign Key relationship is removed first. A Primary Key is easily assigned in the table design window by either of the following actions: Right-click on the desired column name and select the Set Primary Key option. To select a compound key based on more than one column, hold down the Ctrl key while selecting multiple columns before right-clicking. Select the desired field and click the Set Primary Key button on the toolbar. To select a compound key based on more than one column, hold down the Ctrl key while selecting the appropriate columns. Foreign Key Constraint A Foreign Key constraint is defined so that a primary and subsidiary table can be linked together by a common value. A Foreign Key can be linked to any unique column in the main table; it does not necessarily have to be linked to the Primary Key. It can be linked to any column that is associated with a unique index. With a Foreign Key defined, you cannot add a value to the Foreign Key column if a matching value is not present in the primary table. For instructions on setting a Foreign Key constraint, see the section on referential integrity, earlier in this chapter. Note in the example shown in Figure 3.7 that there are matching Order IDs in the child Order Details table for only those Order IDs included in the parent Orders table. An Order ID must match from a child to a parent. If a child entry with an ID were not found in the parent table, then that is known as an orphan child and would be a breach of referential integrity rules.

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

142 Part I EXAM PREPARATION WARNINGTIPEXAM Documentation Discrepancy The capability to set a relationship to any unique column is not noted in most SQL Server documentation. SQL Server blogs Online reports that a Foreign Key must be set to a Primary Key or a UNIQUE constraint. In SQL Server, you can create a relationship against a Primary Key, unique index,or a UNIQUE constraint. You need not have a Primary Key or constraint. You can even set a unique index to ignore duplicates and the operation will still be permitted. Go with the Documentation If you run into this on the exam, the correct answer is likely to be one chosen based on the documentation and not on actual functionality. The capability to set a relationship to any unique column is not noted in documentation. The correct technique to use when answering an exam question would be one that involves a Foreign Key set to a Primary Key or Unique constraint. Primary Key Constraint A Primary Key constraint enforces entity integrity in that it does not permit any two rows in a table to have the same key value. This enables each row to be uniquely defined in its own right. Although a Primary Key should be created when a table is initially created, it can be added or changed at any time after creation. A Primary Key cannot have NULL content nor can there be any duplicate values. SQL Server automatically creates a unique index to enforce the exclusiveness of each value. If a Primary Key is referenced by a Foreign Key in another table, the Primary Key cannot be removed unless the Foreign Key relationship is removed first. A Primary Key is easily assigned in the table design window by either of the following actions: Right-click on the desired column name and select the Set Primary Key option. To select a compound key based on more than one column, hold down the Ctrl key while selecting multiple columns before right-clicking. Select the desired field and click the Set Primary Key button on the toolbar. To select a compound key based on more than one column, hold down the Ctrl key while selecting the appropriate columns. Foreign Key Constraint A Foreign Key constraint is defined so that a primary and subsidiary table can be linked together by a common value. A Foreign Key can be linked to any unique column in the main table; it does not necessarily have to be linked to the Primary Key. It can be linked to any column that is associated with a unique index. With a Foreign Key defined, you cannot add a value to the Foreign Key column if a matching value is not present in the primary table. For instructions on setting a Foreign Key constraint, see the section on referential integrity, earlier in this chapter. Note in the example shown in Figure 3.7 that there are matching Order IDs in the child Order Details table for only those Order IDs included in the parent Orders table. An Order ID must match from a child to a parent. If a child entry with an ID were not found in the parent table, then that is known as an orphan child and would be a breach of referential integrity rules.

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

Chapter 3 PHYSICAL DATABASE DESIGN AND IMPLEMENTATION 141 FIGURE 3.6 Create Relationship options dialog as seen from the table diagram window. Utilizing referential integrity guidelines helps maintain the accuracy of data entered into the system. A database system uses referential integrity rules to prohibit subsidiary elements from being entered into the system unless a matching unique element is in the referenced table. The system also protects the data from changes and deletions, assuming that cascading actions (defined later in this chapter) have been carefully and properly implemented. Primary and Foreign Keys The definition of a Primary Key for each table, though not a requirement of the SQL Server database environment, is recommended. A Primary Key helps records maintain their identities as unique rows of a table and also provides a means of relating tables to other tables in the database to maintain normal forms. (For further information on normalization and normal forms, see Chapter 2, Data Modeling. ) A Foreign Key is defined in a subsidiary table as a pointer to the Primary Key or other unique value in the primary table. Both Primary and Foreign Keys are defined in the form of a constraint. The pair of keys work together to accommodate table relationships. A Foreign Key refers back to the Primary Key in the parent table, forming a one-to-one or one-to-many relationship. (To see more about relationships, refer to Chapter 2.)

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

140 Part I EXAM PREPARATION Step by Step 3.6 shows you how to define a relationship from the Table Design Properties box: STEP BY STEP 3.6 Using the Table Designer to Create a Relationship 1. From the Enterprise Manager, select the table that contains the Foreign Key element of the relationship. 2. Right-click on the table name that will represent the subsidiary table to be referenced, and select Design Table from the pop-up menu. 3. Select Table and Index Properties. 4. Click on the Relationships tab and click New. 5. Select the desired options from the dialog, and click Close to establish the relationship (see Figure 3.6). 6. Exit and save changes to the table. You can define a relationship when creating or altering a table definition. The following example defines a relationship using T-SQL: CREATE TABLE OrderDetails ( DetailsID smallint, OrderID smallint FOREIGN KEY (OrderID) REFERENCES Orders(OrderID), QtyOrdered bigint, WarehouseLocation smallint ) The most common relationships are one-to-many, in which the unique value in one table has many subsidiary records in the second table. Another form of relationship, which is usually used to split a table with an extraordinary number of columns, is a one-to-one relationship. The use of one-to-one splits a table and associates a single unique value in one table with the same unique value in a second table. A many-to-many relationship can also be defined, but this form of referencing requires three tables and is really two separate one-to-many relationships.

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

Chapter 3 PHYSICAL DATABASE DESIGN AND IMPLEMENTATION 139 create a relationship between two tables. This can be done through the database diagram feature of the Enterprise Manager or through the CREATE and ALTER TABLE T-SQL statements. Normally, you relate the referencing or Foreign Key of one table to the Primary Key or other unique value of a second table. Step by Step 3.5 shows you how to use a database diagram to define a relationship: STEP BY STEP 3.5 Using the Wizard to Create a Relationship 1. From the Enterprise Manager, expand the tree view of the database you want to use and select Diagrams. 2. Right-click Diagrams and select New Database Diagram from the pop-up menu. 3. A wizard asks you for the tables to use in the diagram. Select the tables to be related and follow the wizard to completion. 4. Select the column to be related from the subsidiary table, and drag the column to the primary table. 5. Complete the desired options from the dialog, and press OK to establish the relationship (see Figure 3.5). 6. Exit the diagram, and select Yes to save the changes to the respective tables. FIGURE 3.5 The Create Relationship dialog box as seen from the database diagram window.

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

138 Part I EXAM PREPARATION page split occurs when a new row is inserted into a table that has no empty space for its placement. As the storage pages fill, page splits occur, which can hamper performance and increase fragmentation. IN THE FIELD MORE ON THE FILL FACTOR You will normally find that queries (the reading of existing data) outweigh data updates by a substantial margin. Providing the extra room slows down the query process. Therefore, you might not want to adjust the fill factor value at all. Equally, setting the fill factor too low hampers read performance because the server must negotiate a series of empty pages to actually fetch the desired data. It is beneficial to specify a fill factor when you create an index on a table that already has data and will have a high volume of inserts. If you do not specify this setting when creating an index, the server default fill factor setting is chosen. The fill factor for the server is a configuration option set through the Enterprise Manager or the sp_configure stored procedure. The percentage value for the fill factor is not maintained over time; it applies only at the time of creation. Therefore, if inserts into a table occur frequently, it s important to take maintenance measures for rebuilding the indexes to ensure that the empty space is put back in place. A specific index can be rebuilt using the CREATE INDEX T-SQL command with the DROP EXISTING option. Indexes can also be de-fragmented using the DBCC INDEXDEFRAG command, which also reapplies the fill factor. The Pad Index setting is closely related to the setting for fill factor to allow space to be left in non-leaf levels. Pad Index cannot be specified by itself and can be used only if you supply a fill factor. You do not provide a value to this setting; it matches the setting given for the fill factor. Maintaining Referential Integrity When multiple tables maintained in a database are related to each other, some measures should be taken to ensure that the reliability of these relationships stays intact. To enforce referential integrity, you

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

Chapter 3 PHYSICAL DATABASE DESIGN AND IMPLEMENTATION 137 The basis for the index usually is determined by the order in which the majority of applications and queries want their output. The clustered index values are also present in other indexes and the size of the defined index should be kept as small as possible. When you select a clustering key, try to utilize a numeric data type because character types cause index storage to occupy much more space. Always define a clustered index first before you define any of the nonclustered indexes. If you do these tasks in reverse order, then all nonclustered indexes rebuild themselves upon creation of the clustered index. Nonclustered Indexing Nonclustered indexes provide a means of retrieving the data from the database in an order other than that in which the data is physically stored. The only alternative to the use of these indexes would be provisions for a sort operation that would place undue overhead on the client system and might not produce the desired response times. A data sort implementation is usually performed only for one-time operations or for applications that will have very limited usage. Although the creation of indexes saves time and resources in a lot of cases, avoid the creation of indexes that will rarely be utilized. Each time a record is added to a table, all indexes in the table must be updated, and this might also cause undue system overhead. For that reason, careful planning of index usage is necessary. Unique Indexing At times when indexes are created, it is important to guarantee that each value is distinctive. This is particularly important for a primary key. SQL Server automatically applies a unique index to a primary key to ensure that each key value uniquely defines a row in the table. You might want to create additional unique indexes for columns that are not going to be defined as the primary key. Leaving Space for Inserts Fill factor is the percent at which SQL Server fills leaf-level pages upon creation of indexes. Provision for empty pages enables the server to insert additional rows without performing a page-split operation. A

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

136 Part I EXAM PREPARATION continued 2. Right-click on the table name and select Design Table from the pop-up menu. 3. Select Table and Index Properties. 4. Click on the Indexes/Keys tab. 5. Click on the New button. 6. From the drop-down list boxes, select the column on which to base the index and whether the index is to be ascending or descending. If you are creating a compound index based on a number of columns, then add additional columns as needed. 7. Select a filegroup for the storage of the index. 8. If desired, you can make the index unique, supply fill factor and pad index values, make the index clustered (default is nonclustered), and choose to not recalculate statistics for the index. 9. Close the dialog box to save the index and exit table design properties and select OK to save the changes. Create an index using the T-SQL CREATE INDEX. The following example creates a compound, nonclustered index that is 75% full: CREATE INDEX IXProductItem ON ProductOrderLine (OrderMateKey, ProductLineKey) WITH FILLFACTOR = 75 Clustered Indexing The selection of the appropriate column(s) on which to base a clustered index is important for a number of reasons. As previously mentioned, a clustered index represents the order in which the data is physically stored on the disk. For this reason, you can define only a single clustered index for any table. If you choose not to use a clustered index in a table, the data on disk will be stored in a heap. A clustered index, if present, has clustering keys that are used by all nonclustered indexes to determine the physical location of the data.

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