Exam Prep Questions

1:	You have an accounting SQL Server database application that is accessed by 50 users on your company network. When a user inserts or updates a record, you want to make sure that all the required columns have appropriate values. Which of the following would be best for this situation? A. A stored procedure and a trigger B. A batch and a trigger C. An UPDATE TRigger and an INSERT trigger D. One trigger by itself
A1:	Answer: D. A single trigger can be used to perform validation on more than one event, such as an INSERT and an UPDATE. For more information about the differences between trigger types, see the "Alternative Coding Mechanisms" section of this chapter.
2:	You are a developer for a database. Currently the structure of data and the usage have been causing high CPU utilization on the server. You have decided that you must add several triggers to your database to validate data and generate alerts based on data modifications. You are worried about the impact of the triggers on your already heavily utilized server. Generally, how will triggers affect the performance of your server? A. They will incur low performance overhead, most of it involved in referencing tables. B. They will severely impact database performance. C. They do not impact performance whatsoever. D. They consume less resources than any other type of resource in SQL Server; execution of programming logic uses most of the performance.
A2:	Answer: A. Triggers usually require very little CPU time. Most of the time involved in executing a trigger comes from referencing tables, which might have to be read from the disk. Execution time is usually low, but actual impact can be hampered depending on what is done in the programming code of the trigger. You might code CPU-intensive tasks into your trigger by calling on certain stored procedures or commands. These commands might not be typical in a trigger. For more information about triggers, see the section "Code Responses to Actions."
3:	You have a database that contains several FOREIGN KEY and CHECK constraints. Users are having problems with data entry on the database, because the data that they are adding is constantly in violation of the CHECK constraints. Corporate policy regarding database design prevents you from modifying the current constraints, so you decide to implement your changes via a trigger. Which types of triggers would be best suited for this task? A. UPDATE, DELETE, and INSERT triggers. B. Just UPDATE and INSERT triggers. C. INSTEAD OF triggers. D. Triggers cannot be used in this circumstance.
A3:	Answer: C. INSTEAD OF triggers would be required for this task, because you must check for constraint violations before the update occurs. If there are constraint violations, AFTER triggers will not fire. Most likely you will be implementing INSTEAD OF INSERT or INSTEAD OF INSERT, UPDATE triggers. When trigger actions are listed, such as an INSERT TRigger, you cannot know for sure whether it is an INSTEAD OF or AFTER TRigger, but you should assume that it is a FOR or AFTER trigger if not specifically mentioned. For more information about the order in which triggers and constraints are applied, see the section "Trigger Firing Order."
4:	You are working for a medical agency that tracks statistics for doctors throughout the country; these statistics are later involved in economic decisions. This year, the medical agency plans on creating statistics for the salaries of doctors and storing them in a SalarySurvey table. To get more accurate statistics, the agency does not include values of salaries that are greater than $200,000 or smaller than $10,000. Which of the following is the best way to implement this plan? All these examples are set to support only single-row inserts, which would not be typical on a production database. A. The following code: CREATE TRIGGER SalaryCheck ON SalarySurvey FOR INSERT AS IF (SELECT MonthlySalary FROM inserted) > 200,000 or (SELECT MonthlySalary FROM inserted) < 10,000 BEGIN RAISERROR('Cant Enter Salary: Range Error', 16, 1) WITH LOG ROLLBACK TRANSACTION END B. The following code: CREATE TRIGGER SalaryCheck ON SalarySurvey FOR INSERT AS IF (SELECT MonthlySalary FROM updated) > 200,000 or (SELECT MonthlySalary FROM updated) < 10,000 BEGIN RAISERROR('Cant Enter Salary: Range Error', 16, 1) WITH LOG REVERSE TRANSACTION END C. The following code: CREATE TRIGGER SalaryCheck ON SalarySurvey AFTER UPDATE AS IF (SELECT MonthlySalary FROM inserted) > 200,000 or (SELECT MonthlySalary FROM inserted) < 10,000 BEGIN RAISERROR ( 'Cannot Enter Salary - out of range', 16, 1) WITH LOG ROLLBACK TRANSACTION END D. The following code: CREATE TRIGGER SalaryCheck ON SalarySurvey FOR INSERT AS IF (SELECT MonthlySalary FROM inserted) > 200,000 or (SELECT MonthlySalary FROM inserted) > 10,000 BEGIN RAISERROR ( 'Cannot Enter Salary - out of range', 16, 1) WITH LOG ROLLBACK TRANSACTION END
A4:	Answer: A. This is the only answer choice that is implemented as an INSERT trigger and has correct use of ROLLBACK TRANSACTION. For more information about INSERT, UPDATE, and DELETE triggers, see the section "Code Responses to Actions."
5:	Paul has a five-batch script, and he is creating a T-SQL cursor in batch one. He wants to use the cursor in batches two, three, and four. What must Paul do to access the cursor from any batch? Choose the best answer. A. Use DECLARE CURSOR in every batch and then repopulate them. B. Creating this type of cursor is not possible in SQL Server 2000. C. Create the cursor using the GLOBAL keyword. D. Create the cursor using the PUBLIC keyword.
A5:	Answer: C. Global cursors are available to later batches from the same connection. By the way, there is no such thing as a public cursor. This is a good example of an exam question because none of the answers looks quite right and you'll spend way too much time trying to figure it out. Remember, the instructions are to pick the best answer. There's some information on cursor scope in the section "Traditional Data Processing" that may help if you had trouble with this question.
6:	Bob has just finished using his cursor and he will not need it for the rest of the time he is connected. What can Bob do to fully release all system resources held up by the cursor? A. Run DBCC_CURSOR_PERFORMANCE B. Run the DEALLOCATE command C. Run the CLOSE CURSOR command D. Run the ALL SYSTEMS statement with the CURSOR argument
A6:	Answer: B. The DEALLOCATE command releases all the resources used by a cursor. This information is covered in the section "Traditional Data Processing."
7:	Paul needs to create a cursor that is sensitive to data updates and deletes. He knows that the number of rows in the query he's using should be around 50. Paul subsequently uses the @@CURSOR_ROWS global variable to check for the number of rows in the cursor, which apparently should be around 50. To his surprise, he notices another value. What is this value? A. 0 B. 1 C. -1 D. NULL
A7:	Answer: C. The value of the @@CURSOR_ROWS global variable is -1 only when the cursor is declared as dynamic, and Paul's cursor was declared as dynamic. Information about using global variables is located in the section "Server Provided Global Values," and how cursor status works is in the section "Traditional Data Processing."
8:	Stan runs a query and receives an error message with a severity level of 17. How serious is this error? A. The error was not that serious; the user should rerun the query. B. The query contained one or more typographical errors. C. The error was severe, probably caused by a hardware or software fault. D. The severity level has nothing to do with how serious the error was.
A8:	Answer: C. The severity levels from 17 to 19 designate hardware and/or software problems. Subsequent processing may be stopped. For more information, check out the section "Traditional Data Processing."