Course Preparation Notes
The following items will aid you in preparing for teaching the material in this chapter.
Class Time Range Estimate
The material in this chapter should take approximately five 50-minute class periods to cover, or a total of 4 hours and 10 minutes. Topics should break down approximately as shown in the following table:
|
Topic |
Minutes Required |
|
Creating and Altering Databases |
90 minutes |
|
Creating and Altering Database Objects |
80 minutes |
|
Multiple Server Implementations |
30 minutes |
|
Troubleshooting SQL Server Objects |
50 minutes |
Software/Web Requirements
To do the exercises for this chapter in the Training Guide, one of the following operating systems is required:
Microsoft SQL Server 2000 (any edition) must be installed and running to do the step-by-step exercises and the "Apply Your Knowledge" exercises at the end of the chapter.
No Internet connection is required for this chapter.
Hardware Requirements
To efficiently run Microsoft SQL Server 2000, the following system requirements are extant:
PC with an Intel or compatible Pentium 166MHz or higher processor
Minimum of 64MB of RAM (128MB or more recommended)
95–270MB of disk drive space for SQL Server database; approximately 250MB for a typical installation
CD-ROM
VGA or higher resolution monitor
Microsoft Mouse or compatible pointing device
Key Terms
Cascading Actions—Activity that occurs in regard to update or delete activity when an existing Foreign Key value is changed or removed. A cascading update reflects changes in the Parent Table's key column to the corresponding Foreign Key column value in its Child table. A cascading delete ensures all subsidiary records in other tables are also deleted if a key value is deleted.
CHECK Constraints—Represent data design validation rules for maintaining data integrity. Data entered must follow the applied rule, which is constructed as a comparison expression, such as Vol > 10 or State in('MI','IL'). Check constraints may be applied to a column (Column Level Constraint) or to multiple columns (Table Level Constraint).
Clustered Index—An index is a structure that provides rapid access to the rows of a table based on the values of one or more columns. With a clustered index this rapid access is gained through the physical ordering of the data.
Collation Sequence—A set of rules governing the character sets that are used within a database, and the means by which characters are sorted and compared.
Constraint—Additional integrity checks associated with a column or table, that go beyond those implied by that column's datatype. There are five classes of constraints supported by SQL Server 2000: NOT NULL, CHECK, UNIQUE, PRIMARY KEY, and FOREIGN KEY.
Defaults—Allow the Database Designer to specify a value that SQL Server inserts if no value is explicitly entered in a particular field.
Encryption—A mechanism that can be used to secure data, communications, procedures, and other sensitive information. This information is transformed into a non-readable form that must be decrypted to be viewed. While encryption is generally used in the transferring of sensitive data, SQL Server 2000 also uses encryption to disable the viewing of code from the syscomments table with the WITH ENCRYPTION command.
File groups—An administrative mechanism for grouping files within a database so they can be handled as a single unit.
FILLFACTOR—A server configuration option that specifies how full the server should make each page when it creates a new index using existing data. The FILLFACTOR is only used at the time when each index is created, and has no further affect on the database.
Foreign Keys—In a normalized database, relationships exist between different entities. A primary or unique key enforces a situation in which duplicate data is minimized. In order to provide multiple relationships with a particular primary or unique piece of data, a foreign key is implemented. This helps maintain domain integrity by ensuring that all rows of data in a child entity have a matching row of data in the parent entity.
Identity—A property or rule that can be assigned to, or turned on, for one column in a table to ensure that each value will increment (or decrement) by a specified amount from a specified starting point.
Indexes—Allow data to be organized in a way that allows optimal performance when the user retrieves data. In addition to database configuration, the implementation of indexes is a key method of optimizing performance on a database. Indexes do hamper performance when data is modified. For each new entry, an index entry must be built. For each update, an index key requires updating. See also Clustered Index, Non-clustered Index, and UNIQUE Index.
Integrity—Various means of ensuring the quality of data in a database. Entity integrity ensures that a table has a unique way to identify each record. Domain integrity is concerned with the validity of entries for a given column. Referential integrity enforces the defined relationships between two tables when records are added or deleted.
Log—A file that contains the information necessary to recover all transactions in a database.
Merge Replication—Replication Type where the Publishing server may receive and accept changes to its data from its subscribing servers. Changes in the data can be made in the Subscribing server while offline from the Publishing server, and then sent in batch to the Publisher.
Non-clustered Index—An index is a structure that provides rapid access to the rows of a table based on the values of one or more columns. With a non-clustered index, this rapid access is gained through an ordered list that has pointers to the physical location of the data.
Partitioned Views—Let you separate disjointed sets of data into separate tables on separate physical machines, while providing access to all of the tables simultaneously through the view. A view is considered partitioned if
The columns in each SELECT list are in the same order, of the same type, and have the same collation.
One column in each table has a CHECK constraint that is mutually exclusive with the corresponding column in the other tables. This column is called the partitioning column.
The partitioning column is not a computed column.
Indexes are not allowed on computed columns in the view.
The same underlying table does not appear more than once in the view.
Primary Key—A column or combination of columns whose values uniquely identify each row in a table, providing a means of relating the table to other tables in the database. Primary keys enforce entity integrity by not permitting any two rows in a table to have the same key value.
RAID—Redundant Array of Independent/Inexpensive Disks. A software or hardware technology where two or more disk drives can be configured in such a way as to provide larger volumes, increased performance, or data storage redundancy. Although there are six different RAID levels, 0 through 5, only levels 0, 1, and 5 are typically used with SQL Server. RAID level 0 is known as Data Striping, and allows for the highest performance, but has a high cost for failure because there is no redundancy. RAID level 1, or Disk Mirroring, has high redundancy, and reasonable performance. RAID level 5, or striping with parity, has the highest redundancy, but the slowest performance. A common installation technique for relational database management systems is to configure the database on a RAID 0 drive and then place the transaction log on a mirrored drive (RAID 1). In this manner you can get the best disk I/O performance for the database, and maintain data recoverability (assuming regular database backups). This configuration is generally referred to as RAID 10 (1 & 0), even though that is not a defined RAID level.
Recompile—An action that occurs to a stored procedure when an index is created or altered, or the server notes a change in the distribution of the data that has rendered its cached plan obsolete. Recompilation can be forced by using the sp_recompile system stored procedure, or by adding the WITH RECOMPILE option when creating or executing a stored procedure.
Roles—SQL Server has server, database, and application roles defined to identify the functions that different administrators have in regards to those objects. Server roles dictate the permissions that a given user in that role has to affect SQL Server. Database roles indicate the permissions granted or denied a user in relationship to a given database. An application role is a generalized role usually granted to an application or stored procedure that has assigned permissions to particular objects within a database.
Rules—Included in SQL Server mainly for backward compatibility. A rule is equivalent in functionality to a CHECK constraint, but must be defined separately, and then bound to a single column or user-defined datatype. Rules cannot access SQL Server functions that reference database objects.
Schema Binding—Indexed views require that you don't change the definition of any underlying object. Using the WITH SCHEMABINDING option ensures that tables used in an indexed view are not dropped or altered while participating in a view, unless the view is dropped, or changed to no longer require schema binding.
Snapshot Replication—Type of replication that distributes data exactly as it appears at a specific moment in time and does not monitor for updates to the data. Can also be called "Read Only" replication.
Stored Procedures—A set of Transact-SQL statements that can be saved as a database object for future and repeated executions. Stored procedures benefit database users by providing basic development building blocks, speeding up processing, securing data, and reducing bandwidth usage.
Transactional Replication—Type of replication where an initial snapshot of the data is made and published to the subscriber. Data changes are propagated to the subscriber based on a stipulated number of transactions committed on the Publishing database, in the order they were originally committed.
Triggers—A special type of stored procedure that is automatically invoked by SQL Server. Triggers automatically execute whenever a specified change (INSERT, UPDATE, or DELETE) to a data object is attempted, and can be of two types: "after" triggers, and "instead-of" triggers. After triggers are fired after an operation completes. Instead-of triggers define actions to be carried out instead of a requested data modification.
UNIQUE Constraint—Similar to a primary key in that values contained in a particular column, or composite columns, may uniquely define a record. A UNIQUE constraint is generally applied when a column or group of columns within a table is required by design to be unique with the table. Inclusion of a UNIQUE constraint builds a Unique index on the table.
UNIQUE Constraints—Used to ensure that no duplicate values are entered in specific columns. Two different constraints can be used to ensure uniqueness, a PRIMARY KEY, or a UNIQUE constraint. While there can only be one primary key on a table, there can be multiple unique constraints. In addition, unique constraints can be defined on columns that allow null values, whereas a primary key cannot.
UNIQUE Index—An index that is built when a primary key constraint or unique constraint is placed on a column or group of columns in a table. This index helps maintain the domain integrity of the table.
User-defined Functions—Like any function, a user-defined function is a routine that return a value. User-defined functions can return a scalar value, an updateable table of data, or a non-updateable table of data.
Views—A virtual table created by using a select statement, referencing one or more base tables, whose TSQL script is saved and given a name within the database. Views are implemented as a security mechanism, as well as a means of reducing query complexity.