Microsoft SQL Server 7.0 Database Implementation Online Training Kit

Objective 4: Creating a Physical Database

The Creating a Physical Database objective domain concentrates on the physical implementation of a Microsoft SQL Server 7.0 database. A database is a collection of information, tables, and other objects organized and presented to facilitate the managing and storing of data. SQL Server databases are stored in files. The structure of each database includes at least one data file and one transaction log file. Once you have created a database, you can begin creating the objects in that database. For example, you must create tables to store data as collections of rows and columns. You can also create user-defined data types to ensure consistency when working with common data elements in different tables. After you have created tables and defined the columns that make up the table, you can create indexes on specific columns within the tables. Indexes are database objects that provide fast access to data in the rows of a table. Another type of index is one that supports full-text search. Full-text search allows you to query character-based data in the table. Full-text indexes are stored as files in catalogs. Unlike standard SQL Server indexes, they are not stored as objects in the database. SQL Server also provides several methods for importing external data into tables within SQL Server databases, including the bulk copy program (bcp) utility, the BULK INSERT Transact-SQL statement, and Data Transformation Services (DTS), a powerful set of tools that allow you to import, export, or transform data between SQL Server and any OLE DB, open database connectivity (ODBC), or text file format.

Tested Skills and Suggested Practices

The skills that you need to successfully master the Creating a Physical Database objective domain on the Designing and Implementing Databases with Microsoft SQL Server 7.0 exam include:

Creating and modifying SQL Server database files.

• Practice 1: Use the CREATE DATABASE Transact-SQL statement to create a database on a SQL Server computer. Be sure to name the database, the primary file, and the log file, and specify the size of the files. Once you've created the database, use SQL Server Enterprise Manager to verify that the database has been created. Then verify that the new database files have been created and are listed with the appropriate file extensions.
• Practice 2: Use the ALTER DATA13ASE statement to expand the size of the database files that you created in Practice 1. Expand the primary file and ther the log file. Then use the ALTER DATABASE statement to add a secondary data file to your database. Be sure to verify that the new database file has been created and is listed with the appropriate file extension.
• Practice 3: Use the DBCCC SHRINKDATABASE statement to shrink the entire database. Shrink the database only a small percentage so you can use other methods to shrink the database files. Next, try using the DBCC SHRINKFILE statement to shrink the secondary data file. Then use the sp-dboption system stored procedure to set the database to shrink automatically.

Creating tables in a SQL Server database and defining the columns within those tables.

• Practice 1: Use the CREATE TABLE statement to create several tables in a SQL Server database. You can use the database that you created in the previous practices, or you can use one of the sample databases that ship with SQL Server, such as Northwind. When you create your tables, be certain to define at least one column that uses the IDENTITY property. In another table, define a column that uses the uniqueidentifier data type and the NEWID( function. In addition, at least one table should be defined with a primary key and another with a foreign key.
• Practice 2: Create several user-defined data types. Be certain that the data types you create are based on different system data types. After you've created these data types, use SQL Server Enterprise Manager to verify that the data types have been added to the database.
• Practice 3: Use the DROP TABLE statement to drop one of the tables that you created in Practice 1. You should drop the table on which you defined a foreign key or any other table on which you defined a dependency to another object. Before you drop the table, use the sp_depends system stored procedure to view existing dependencies between the table and other objects. After you've dropped the table, use SQL Server Enterprise Manager to verify that the table has been dropped from the database.
• Practice 4: Use the ALTER TABLE statement to add a column to one of the tables in the database. After you've added the column, use the ALTER TABLE statement to change the data type that you originally defined on the column. After you've altered the column, use the ALTER TABLE statement once more to drop that column.

Creating and maintaining indexes on tables in a SQL Server database.

• Practice 1: Use the CREATE INDEX statement to create a unique, clustered composite index on a table in a SQL Server database. You should use a table that you created in an earlier practice. Remember that a composite index is created on two or more columns, so be certain to define the most unique column first.
• Practice 2: Use the CREATE INDEX statement to create a nonclustered index. Include in your statement the FILLFACTOR option and the PAD-INDEX option to create leaf-level pages that are 20 percent full.
• Practice 3: Use the sp_helpindex system stored procedure to obtain information about the indexes that you just created. Try using this stored procedure on indexes for other tables. When you are finished, use SQL Server Enterprise Manager to view the same information.
• Practice 4: Use the CREATE INDEX statement with the DROP EXISTING option to change the characteristics of the nonclustered index that you created in Practice 2. First change the FILLFACTOR percentage to 30 percent. Then change the index to be unique.

Populating tables in a SQL Server database with data from an external data source.

• Practice 1: Use the bulk copy program (bcp) utility to import data into a SQL Server table. You should create a data text file that contains values that can be inserted into one of the tables that you created in an earlier practice. After you've run the bcp utility, use a SELECT statement to verify that the data was inserted correctly into the table.
• Practice 2: Use the DELETE statement to delete the records that you just added to the table in Practice 1. Now use the BULK INSERT statement to copy the same data from the data text file into the table. After you've executed the BULK INSERT statement, use a SELECT statement to verify that the same data has been entered into the table that was entered when you used the bcp utility.
• Practice 3: Once again, use the DELETE statement to delete the records that you just added to the table. Now use the DTS Import Wizard to import the data from the data text file into the table. Then use a SELECT statement to verify that the same data has once again been entered into the table.

Implementing full-text searches on tables in a SQL Server database.

• Practice 1: Use the Full-Text Indexing Wizard to set up full-text search on one of the tables in your database. Note that you must have the Full-Text Search component of SQL Server installed on your Microsoft Windows NT Server computer, and the Microsoft Search service must be running. Once you have set up full-text indexing, use the sp_help_ fulltext _ tables system stored proce- dure to verify that full-text indexing has been enabled on the table.
• Practice 2: Use SQL Server Enterprise Manager to populate the full-text index that you just set up. When you are finished, view the properties for that index to verify that the catalog contains approximately the correct number of unique words.
• Practice 3: Use SELECT statements to write several full-text queries against the table for which you created a full-text index. Use the different keywords, as appropriate, that are available for these kinds of queries, such as CONTAINS or FREETEXT...