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
- Add the data type by using a SQL script that you run after creating each database.
- Add the data type to the Model system database.
- Add the data type to the Master system database.
- Add the data type to a user-defined database and copy the user-defined database each time you build a new database.
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:
- Rebuild the Primary file or filegroup as it was originally built, then restore the database.
- Rebuild all the database files and filegroups in their original order, then restore the database.
- Rebuild all the devices and databases in their original order, then restore the database.
- Restore the database without rebuilding files.
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):
- Creating a Primary file for the tables and a Secondary file for the indexes.
- Creating the tables in the Primary filegroup and creating a Secondary filegroup for the indexes.
- Placing the index on the same file as the transaction log, which is on a separate physical disk from the table.
- Creating a Secondary filegroup for this table and another Secondary filegroup for the indexes.
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):
- Storing the image files on the disk array with pointers to their file locations in the database.
- Making the disk array part of the Primary filegroup and letting the database expand onto it. The images are then stored in the Primary filegroup.
- Making the disk array a Secondary filegroup and designating it for text and image data with the TEXTIMAGE_ON filegroup option of the CREATE TABLE statement.
- Making the disk array a Secondary filegroup and placing the image data into a separate table on this filegroup.
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.