Answers to November Practice Questions: Creating SQL Server 7.0 Databases

Q1. You have a requirement for a user-defined data type that must be in every new database you build. The easiest way to implement this addition is

The correct answer is (b). SQL Server 7.0 builds new databases by copying Model, then expanding it to the required size. Answer (a) works for new and existing databases, but it isn't the easiest way. Adding a data type to Master, as suggested in (c), won't help. And answer (d) is incorrect because copying a database is possible but more complex.

Q2. To restore a database to a SQL Server 7.0 system, you will generally:

The correct answer is (d): SQL Server 7.0 automatically rebuilds all the files and filegroups. So (a) and (b) are incorrect because although you could rebuild the database files and then restore, it's unnecessary work, and generally you can simply let SQL Server rebuild the files. Answer (c) is correct for SQL Server 6.5 but not for 7.0, which no longer has devices.

Q3. You want to place a table and its indexes on different physical disks. You can do so by (select all correct answers):

The correct answers are (b) and (d). Answer (a) is incorrect because the two files are by default in the same (Primary) filegroup. You can't force tables or indexes to use a specific file in a filegroup, so the table and index are interleaved across both files. Answer (c) is wrong because the log has a separate filegroup that it doesn't share with the data. Answer (b) or (d) would work because you can control where the table and index are placed.

Q4. You need to store large image files in your database. The primary file for your database is on a disk that has only 100MB left, but you have a large disk array on which you could store images. You can use this disk array to store the images by (select the best answer):

The best answer is (c), although all answers work. But (a) doesn't store the images in the database, as the question suggested. Answer (b) works, but you end up with the database fragmented and mixed in with the pages for the image data. Answer (d) would also work, but it involves a join to retrieve the image data, which gives a slight performance penalty. Answer (c) keeps the images on the disk array and doesn't interfere with the database on the main disk.