Certifiable Q&A for June 30, 2000

Welcome to Certifiable, your exam prep headquarters. Here you'll find questions about some of the tricky areas that are fair game for the certification exams. Following the questions, you'll find the correct answers and explanatory text. We will change the questions biweekly, and you will be able to access previous question sets.

Test Questions (June 30, 2000)
Test Answers (June 30, 2000)

Questions (June 30, 2000)

This week's questions focus on Exam 70-028: SQL Server 7.0 Administration, which continues to be among the most popular MCSE electives. It's also one exam that's not expiring at the end of this year.

Question 1
To improve the performance of critical queries when accessing your inventory table, you decide to move the inventory table to a separate physical disk. What's the best way to accomplish this? Your database currently resides on one data file only.

For the correct answer and an explanation, see the Answers section.

Question 2
You're in charge of inventory management for your company, which stores its data in a SQL Server 7.0 database, and you want to notify your supplier when your stock on hand for any of four products falls below a certain threshold. Which of the following tools lets you most conveniently track the inventory and send an email to your supplier when it’s time to reorder?

For the correct answer and an explanation, see the Answers section.

Question 3
User Joe is the owner of a table named PROSPECTS in the SALES database. Sue, who works in marketing, must be able to add new potential customers to the PROSPECTS table but shouldn’t have any other rights to the table. Joe grants the INSERT permission for the PROSPECTS table to Connor, who creates a stored procedure, NEWPROSPECT, that performs all the required error checking and then adds a new prospect to the PROSPECTS table. Connor grants the EXEC permission for the procedure to Sue, but Sue says she receives a permission-denied error when she tries to run NEWPROSPECT. Which of the following solutions would best enable Sue to run NEWPROSPECT?

For the correct answer and an explanation, see the Answers section.

Answers (June 30, 2000)

Answer to Question 1
The correct answer is C—use ALTER DATABASE to create a new filegroup; use ALTER DATABASE again to add a file on the new disk to the filegroup; create a clustered index for the table on the new filegroup. If you create a clustered index on the new filegroup, the table data will reside on the new filegroup because the data is on the leaf level of a clustered index. If there is already a clustered index on the table, you can use the DROP_EXISTING option to create the new index, and you won't have to rebuild any of the nonclustered indexes.

Although Answer A might seem like an easy solution from a user's standpoint, remember that Enterprise Manager can't do anything that SQL Server itself doesn't allow. There's really no way to move a table. When you use Enterprise Manager to put the table on a new filegroup behind the scenes, Enterprise Manager actually sends SQL Server the commands to drop and completely rebuild the table on the new filegroup and then copy all the data and drop the original table. SQL Server then has to recreate all the indexes, triggers, constraints, and permissions. For a large table, this process can take a long time.

Answer B is incorrect because you can't place a table or an index on a file—only on a file group. Within one file group, SQL Server spreads data evenly over all the files in the filegroup. Answer D won't work for the same reason.

Answer to Question 2
The correct answer is B--Enterprise Manager. With Enterprise Manager, you can define four alerts from the Management folder using SQL Server Agent. You can base the alerts on the performance counters User Counter 1, User Counter 2, User Counter 3, and User Counter 4, selecting them from the SQL:User Setting object. SQL Server provides 10 stored procedures that can generate values for 10 user-defined counters, sp_user_counter1 through sp_user_counter10. Unlike in SQL Server 6.5, SQL Server 7.0's user-defined counters don't execute at regular intervals but let you set a value that's available either to SQL Server alert engine or to Performance Monitor. The value changes only when you decide to execute the corresponding user counter procedure and pass a parameter of the value you want to set the counter to. You don't edit the procedure’s code. So, you can separately create a job that runs four queries against the inventory tables and returns the four integer numbers that represent the available inventory for the four products. Next, you can execute the sp_user_counter1 procedure with the first of the four numbers as a parameter, then sp_user_counter2 with the second number as a parameter, and so on. Setting an alert on a counter through Enterprise Manager lets you execute a SQL statement if the counter falls below a specified value, so you can run xp_sendmail to notify your supplier when inventory diminishes. As an alternative, you can define a trigger on the inventory table so that each time the table changes, you can determine whether the inventory has changed for any of the four products and execute the appropriate user counter procedure from the trigger. This solution is similar to Answer A, which I discuss next, but you perform all the setup and management from within SQL Server. You don't need to start up Performance Monitor; you don’t even need to have it on the SQL Server machine. And, you don’t have to use a command-line email system because SQL Server has its own email application.

Answer A works, but it's not the most efficient solution. You can use Performance Monitor to monitor four user-defined counters that track any information you specify. You can select User Counter 1, User Counter 2, and so on, from the SQL:User Setting object. Performance Monitor lets you set an alert on any counter it's monitoring and execute a program if the counter falls below a specified value, so you can set a command-line email program to run and notify your supplier when inventory diminishes.

Answer C is incorrect because SQL Server Profiler has no way to monitor data values in your tables and no way to initiate any action if it encounters a condition.

Actually, the best solution isn't listed in the answers. You can use the trigger suggestion from the discussion under Answer A but without Performance Monitor or alerts. A trigger on the inventory table can execute xp_sendmail to the supplier when it detects that inventory has fallen below a specified level.

Answer to Question 3
The correct answer is C—a DBO should take ownership of both the PROSPECT TABLE and the NEWPROSPECT procedure. This solution avoids broken ownership chains, and the DBO is always the default owner of an object. If Joe owns the procedure, and Sue issues the statement EXEC NEWPROSPECT, she will receive an error and might think that she has a permission problem. In fact, the error will tell her that the object NEWPROSPECT could not be found because without a specified owner name, SQL Server looks for an object that Sue (the user executing the statement) or the DBO owns. Everyone can easily access objects that the DBO owns, and if you specify that the DBO owns all the objects in a database, you avoid the confusion that results when different users own objects of the same name.

Answer A is incorrect because Connor can't grant permission on Joe’s table. This solution might work if Joe grants Sue permission, but you would lose some of the benefits of stored procedures. If Sue can insert directly into the NEWPROSPECT table, she doesn’t have to go through the procedure with all its error checking.

Answer B could work, but it's not the best solution. When a stored procedure owner also owns all the objects that the procedure references, SQL Server checks only whether the EXEC permission for the procedure applies to the current user. However, when a procedure references objects that belong to other users, a broken ownership chain results. If the owner of a dependent object is different, SQL Server must check the permission on those dependent objects. Because the table's owner, Joe, is different from the procedure’s owner, Connor, SQL Server checks to determine whether Sue has permission on Joe's table. If Connor drops the procedure and Joe creates it, Joe then owns both the procedure and the table, and there's no longer a broken ownership chain.

Answer D is incorrect because Joe can't give permission to Sue to execute Connor’s procedure.

Answer E is incorrect because giving Sue ownership of the procedure doesn't change anything. She already has execute permission. The problem arises when the procedure accesses the table that a different user owns.