Chapter 13. Practice Exam Two

The actual certification exam is 50 questions. To best simulate exam circumstances, you should try to complete the 50 questions in 90 minutes.

1:	John has just been impressed with the amount of power that full-text searches can provide and how easy they are to implement. Before John actually upgrades to a full-text search, he wants to try out the full-text searching "dream" capabilities by testing them on the Products table of his company's database. John would like to perform flexible searches on a text column in the Products table. Which tool will help him accomplish his task? A. Index Tuning Wizard B. Full-Text Searching Wizard C. Full-Text Indexing Wizard D. MSSearch Index Wizard
2:	David is a database implementer who works for a major car retailer that tracks information on the latest car models available by the various car manufacturers. The company currently searches for the latest car model description using a regular search engine that is not capable of performing the complex searches that full-text searches can perform. David needs to upgrade the company's searches to full-text searches. What must he do before creating a full-text index? A. End all wizards and programs using the MSSearch utility. B. Create an index using the Index Tuning Wizard. C. Run a custom setup to install Microsoft Full-Text Search Engine. D. He doesn't have to do anything.
3:	You are designing a database that will contain customer orders. Customers will be able to order multiple products each time they place an order. You review the database design, which is show here: Customers CustomerID CompanyName Address City State Orders OrderID ProductID OrderDate Quantity CustomerID Product ProductID Description UnitPrice What can you do to achieve quick response times with minimal redundant data? (Each correct answer presents part of the solution. Choose two.) A. Create a new order table named OrdersDetail. Add OrderID, ProductID, and Quantity columns to this table. B. Create a composite PRIMARY KEY constraint on the OrderID and ProductID columns of the Orders table. C. Remove the ProductID and Quantity columns from the Orders table. D. Create a UNIQUE constraint on the OrderID column of the Orders table. E. Move the UnitPrice column from the Products table to the Orders table.
4:	You are a database developer for an IT consulting company. You are designing a database to record information about potential employees. You create a table named ApplicantAttributes for the database. The table is shown here: ApplicantAttributes ApplicantID AttributeID YearsExperience Proficiency How should you uniquely identify the skills for each consultant? A. Create a PRIMARY KEY constraint on the ApplicantID column. B. Create a PRIMARY KEY constraint on the ApplicantID and YearsExperience columns. C. Create a PRIMARY KEY constraint on the ApplicantID and AttributeID columns. D. Create a PRIMARY KEY constraint on the ApplicantID, AttributeID, and YearsExperience columns.
5:	Users inform you that recently they have been receiving error messages frequently as query volume has steadily increased. The following message was captured by one of the users: Transaction was deadlocked on resources with another process and has been chosen as the deadlock victim. Rerun the transaction. What is your next step? A. Use a different transaction isolation level. B. Use SQL Profiler to capture deadlock events. C. Use System Monitor to monitor locks. D. Add more client access licenses to the server.
6:	Lately the end users have been reporting that when performing queries against information on customers, the system has been growing increasingly slow. After examining the system, you determine that the table definition has recently been altered. You want the response time to improve and be similar to what it was before the change. How would you fix the problem? A. Run a DBCC DBREINDEX. B. Drop and re-create the table's clustered index. C. Drop and re-create all indexes. D. Update the index statistics. E. Reboot the server.
7:	Your company has a table named Products. Some time ago you added three nonclustered indexes to the table. You also added a clustered index on the primary key. You monitor the performance on the indexes and notice that the indexes are not as efficient as they were when they were originally created. You decide to rebuild each index in the table. Which method should you use to rebuild all indexes in the fastest and most efficient way? A. Use DBCC DBREINDEX. B. Create a clustered index with Drop-Existing; create a nonclustered index with Drop-Existing. C. Delete all indexes and then re-create them. D. Update the index statistics.
8:	You are building a database and you want to eliminate duplicate entries and minimize data storage wherever possible. You want to track the following information for employees and managers: first name, middle name, last name, employee identification number, address, date of hire, department, salary, and name of manager. Which table design should you use? A. Table1: EmpID, MgrID, Firstname, Middlename, Lastname, Address, Hiredate, Dept, Salary. Table2: MgrID, Firstname, Middlename, Lastname. B. Table1: EmpID, Firstname, Middlename, Lastname, Address, Hiredate, Dept, Salary. Table2: MgrID, Firstname, Middlename, Lastname. Table3: EmpID, MgrID. C. Table1: EmpID, MgrID, Firstname, Middlename, Lastname, Address, Hiredate, Dept, Salary. D. Table1: EmpID, Firstname, Middlename, Lastname, Address, Hiredate, Dept, Salary. Table2: EmpID, MgrID Table3: MgrID.
9:	You have a table that is defined this way: CREATE TABLE Books ( Book_ID char, Description Text, Price Integer, Author char(32)) You write the following: SELECT * FROM Books WHERE CONTAINS (Description, 'Server') You know for a fact that there are matching rows, but you receive an empty resultset when you try to execute the query. What should you do? A. Ensure that there is a non-unique index on Description column. B. Create a unique clustered index on the Description column. C. Populate the FULLTEXT catalog for the Books table. D. Use the sp_fulltext_populate stored procedure.
10:	You are the database developer for a leasing company. Your database includes a table that is defined like this: CREATE TABLE Lease (Id Int IDENTITY NOT NULL CONSTRAINT pk_lesse_id PRIMARY KEY NONCLUSTERED, Lastname varchar(50) NOT NULL, FirstName varchar(50) NOT NULL, SSNo char(9) NOT NULL, Rating char(10) NULL, Limit money NULL) Each SSNo must be unique. You want the data to be physically stored in SSNo sequence. Which constraint should you add to the SSNo column on the Lease table? A. UNIQUE CLUSTERED constraint B. UNIQUE UNCLUSTERED constraint C. PRIMARY KEY CLUSTERED constraint D. PRIMARY KEY UNCLUSTERED constraint
11:	After receiving complaints from many users concerning the data retrieval and modification times, an administrator creates a set of 10 indexes. Performance has increased in some processes but decreased in others. Which of the following statements outlines the consequence of using the numerous indexes you have just done? A. Numerous indexes decrease performance when modifying data. B. Numerous indexes are not permitted; you can create only one clustered and one nonclustered index. C. Numerous indexes result in a very short index life span. D. Numerous indexes decrease performance on queries that select data with the SELECT clause.
12:	You are designing a database that will serve as a back end for several large websites. The websites themselves will communicate with each other and pass data back and forth using XML. You would like to control the data displayed on the user browser based on interactions with the user. In many cases columns and rows need to be eliminated based on the criteria supplied. You would like to minimize round-trips to the server for data-exchange purposes. What technology is the best to apply? A. Use a user-defined function with SCHEMABINDING set to the XML recordsets. B. Create an indexed view of the XML recordset, specifying only the columns needed, and supply a WHERE condition based on the rows selected. C. Create standard views of SQL Server data and export the requested data using XML. D. Send data requests and updates directly from the client machine to the SQL Server using FOR XML and OPENXML options. E. Use HTML and an XML schema to provide the necessary view of the data.
13:	You are creating a set of queries to produce reports for a Human Resources database. Based on the queries, you design and create the indexes. You want to ensure that you have created sufficient indexes. What would you do next? A. Run a SQL Profiler trace. B. Run the Index Tuning Wizard against a workload file. C. Run System Monitor. D. In SQL Query Analyzer use the SHOWPLAN_TEXT option.
14:	You create two transactions to support the data entry of employee information. One transaction inserts the compulsory employee name and address information, and the other inserts optional employee demographics. The server occasionally encounters errors during the process and randomly terminates one of the transactions. You must ensure that the server never terminates the more important transaction. Which of the following is the best solution? A. Set the DEADLOCK_PRIORITY to LOW for the insert of the name and address. B. Set the DEADLOCK_PRIORITY to LOW for the insert of the demographics. C. Add error checking on the insert of the name and address. If terminated, restart the transaction. D. Add the ROWLOCK optimizer to the transaction that inserts the name and address. E. Set the isolation to SERIALIZABLE for the insert of the name and address.
15:	You are setting up a new snapshot replication environment for five large tables. To save disk space, you want to delete the data objects that are generated by snapshot replication. Where can you find these objects? A. Inside the MSSQL2000\Replication\Data directory on the Publisher B. In the sysArticles table in the database that is being published C. In the Mssql\Repldata directory in the Distributor D. In the Mssql\Repldata directory on the Subscriber
16:	Eric is working on setting up a new product application and runs the following: SELECT * FROM Sales1 UPDATE Products SET price= price * 2 GO When Eric runs the batch, he receives an error message stating that the table Sales1 was not found. He also notices that the UPDATE statement didn't run. How could the batch be written so that the UPDATE statement runs even in the event of an error? A. GO SELECT * FROM Sales1 UPDATE Products SET price= price * 2 GO B. SELECT * FROM Sales1 UPDATE Products SET price= price * 2 AS INDEPENDENT GO C. SELECT * FROM Sales1 GO UPDATE Products SET price= price * 2 GO D. SELECT * FROM Sales1 UPDATE Products SET price= price * 2
17:	You work for a small web development shop. Your new apprentice has been trying to write SQL Scripts. He has given you this script to run, but you want to make sure that it doesn't block all the users out of the server and set all the pricing information to zero, like last time. So, given the script DECLARE @Var int SET @var = 1 GO WHILE @Var < 11 BEGIN PRINT @Var * 2 SET @Var= @Var + 1 END what will be printed on the output screen? A. A line of zeros. B. A line of ones. C. The multiples of two. D. An error will occur.
18:	You are a SQL Developer working on an Internet application in SQL Server 2000. You need to write a batch that prints the first 10 multiples of 5. Which of the following gets the job done? A. DECLARE @MyVar int SET @MyVar =1 WHILE @MyVar < 11 BEGIN PRINT @MyVar *5 SET @MyVar = @MyVar +1 END B. DECLARE @MyVar int SET @MyVar =1 WHILE @MyVar < 10 BEGIN PRINT @MyVar *5 SET @MyVar = @MyVar +1 END C. DECLARE @MyVar int SET @MyVar =1 WHILE @MyVar < 11 BEGIN PRINT @MyVar *5 SET @MyVar = 5 END D. DECLARE @MyVar int SET @MyVar =1 GO WHILE @MyVar < 11 BEGIN PRINT @MyVar *5 SET @MyVar = @MyVar +1 END
19:	You are writing a procedure that requires the use of a cursor to return data. You need a statement to retrieve the first record to initiate a loop. Which of the following statements would you use? A. DECLARE B. OPEN C. RETRIEVE D. FETCH
20:	You are hearing complaints from users about their applications hanging, occasionally receiving error messages. You've found a couple of batches that are being run at the time that appear to be causing the errors: Batch 1: Set Transaction Isolation Level Serializable Begin Transaction Declare @ProductID int Select @ProductID = ProdCode From Product where ProductName like 'Deluxe Widget' Update SalesTracker Set ProdCode = @ProductID where SalesID = 19 Commit Transaction Batch 2: Set Transaction Isolation Level Serializable Begin Transaction Declare @SalesID int Select @SalesID from SalesTracker where ProductCode = 42 Update Product Set SalesCode = @SalesID where ProductCode = 42 Commit Transaction What's a likely cause of the problem? A. Data corruption in the Product table due to dirty reads B. Data corruption in the SalesTracker table due to dirty reads C. A deadlock between the Product table and the SalesTracker table D. Invalid use of isolation levels
21:	You are performing some scripting for a database system that requires you to monitor the transaction nesting levels. What is the value of the @@TRANCOUNT function when this code is finished executing? declare @Counter int begin transaction update mytable set value = 42 save transaction Point1 while @Counter < 19 begin begin transaction insert into MyTable values(2, 3, 42, 'hello') set @Counter = 1 commit transaction end rollback Point1 begin transaction Point2 insert into mytable (2, 3,42, 'goodbye') rollback A. -1 B. 0 C. 1 D. 2
22:	Carl needs to write a SQL Script that will change everyone's pay status from part-time to full-time. Which of the following is the best way to accomplish the task? A. declare cursor FixStatus for SELECT ID, Status from employees for update of status declare @ID int, @Status varchar(4) fetch next from FixStatus into @ID, @Status while @@FETCH_STATUS = 0 begin update Employees set Status = 'Full' where current of FixStatus fetch next from FixStatus into @ID,@Status end B. declare cursor FixStatus for SELECT ID, Status from employees for update of status declare @ID int, @Status varchar(4) fetch next from FixStatus into @ID,@Status while @@FETCH_STATUS = 0 begin update Employees set Status = 'Full' where current of FixStatus fetch next from FixStatus into @ID, @Status end close FixStatus Deallocate FixStatus C. begin transaction declare cursor FixStatus for SELECT ID,Status from employees for update of status declare @ID int, @Status varchar(4) fetch next from FixStatus into @ID, @Status while @@FETCH_STATUS = 0 begin update Employees set Status = 'Full' where current of FixStatus fetch next from FixStatus into @ID, @Status end close FixStatus Deallocate FixStatus commit transaction D. update employee set status = 'full'
23:	You are a database administrator at a small corporation. You've just been asked to run a stored procedure, but you're wary because the last time someone did this, it caused massive lock contention and forced a system shutdown. What is printed by the following stored procedure? begin transaction declare @foo int select @foo = id from mytable print @foo rollback transaction A. Nothing. B. There are no database changes to the database, so this causes an error. C. It prints the value of @Foo. D. It prints the value of @Foo twice because of the rollback.
24:	You are developing a database that will be using some advanced trigger functionality in combination with other methods of controlling data. In some instances you believe that triggers are not firing because of other conditions within the design. In what order do these events happen? A. Constraints, BEFORE triggers, INSTEAD OF triggers B. INSTEAD OF triggers, constraints, BEFORE triggers, AFTER triggers C. INSTEAD OF triggers, constraints, AFTER TRiggers D. Constraints, INSTEAD OF TRiggers, AFTER TRiggers
25:	You are attempting to create a trigger that will run transactional information within a database. The code you are attempting to use is as follows: EXEC sp_configure 'remote proc trans', '1' RECONFIGURE CREATE TABLE #MyTempTable (cola INT PRIMARY KEY) BEGIN TRANSACTION UPDATE Server2.dbo.Jolly.CCola Set Bottle = 3 UPDATE Server2.dbo.Jolly.CCola Set Can = 2 COMMIT TRANSACTION ... Which of the following statements cannot be used within a trigger? (Select all that apply.) A. CREATE TABLE B. COMMIT TRANSACTION C. sp_configure D. RECONFIGURE E. UPDATE
26:	You plan on implementing a simple triggering strategy for a large college located in the heart of New York. The college has planned a 30-day trip to Paris for those interested. The college enters the names of those desiring to go and then checks to see whether they have paid the fees. If they have not paid the required fees, their names should not get entered; otherwise, they should be entered. Which type of trigger should the school implement? A. Indirect recursion trigger (IRT) B. Direct recursion trigger (DRT) C. INSERT TRigger D. UPDATE TRigger
27:	You need to develop a database that will be using triggers to assist in the control of the data being entered. Which of the following statements are not true for all types of triggers? A. Triggers can fire on all three eventsUPDATE, DELETE, and INSERT. B. Only one trigger per table is allowed. C. Triggers can execute stored procedures. D. Triggers can be used to validate the data being entered into a table.
28:	Which of the following methods can be used to create a trigger and then later modify its definition? A. sp_createtrigger and sp_altertrigger B. CREATE TRIGGER and MODIFY TRIGGER C. CREATE TRIGGER and ALTER TRIGGER D. CREATE TABLE and ALTER TABLE
29:	You are updating the table structure within a production database on your corporate server. You need to drop a trigger that is no longer desired on one of the tables. What should you do before renaming or dropping a trigger? A. Use sp_freename to ensure that the name of your trigger can be reused. B. Use sp_helpdependants to check for dependent objects. C. Use sp_depends to examine dependencies. D. No additional steps are required.
30:	You are working on a database that has several views designed and configured so that updates cannot be made through the views. You need to implement functionality that will allow for some updates to be performed. You don't want to affect the current functionality that is implemented. How would you implement the update functionality? A. Execute sp_makeupdatableview. B. Create an AFTER trigger. C. Create a DELETE trigger. D. Create an INSTEAD OF TRigger.
31:	You have been assigned to investigate the actions of a trigger on one of the tables in the Products database. Which of the following commands would enable you to read a trigger definition? A. sp_helptext B. sp_helptrigger C. sp_displaydef D. sp_help
32:	You are attempting to explain to an apprentice the different types of situations in which user-defined functions might provide necessary functionality. Which of the following problems would be appropriately implemented with a scalar user-defined function? A. Returning a list of customer names B. Updating a customer name to upper case C. Getting the absolute value of a number D. Calculating a customer's outstanding account balance
33:	You are again trying to explain to an apprentice the appropriate use of user-defined functions. Which of the following is not a valid reason to use a view rather than an inline user-defined function? A. Need to update the recordset directly B. Need to return a recordset with a consistent set of parameters C. Need to combine data from several tables into one resultset D. Need to be able to change the filter on the recordset
34:	Eric is having a problem getting his stored procedure to run with acceptable performance with all the different parameters he can use. Basically, the parameters specify whether a search should be done on last name, first name, or address, or some other parameter in his customer table. What can Eric do that may improve performance? A. Use views rather than stored procedures. B. Create the procedure using WITH RECOMPILE to avoid using the cached plan. C. Create the procedure in Enterprise Manager rather than Query Analyzer. D. Use more user-defined functions to handle the parameter input.
35:	Kyle is writing a system that will monitor the number of customer accounts in the database and, when an account representative has exceeded the number of accounts he is allowed to manage, send an email to the sales manager. He's going to use the SQL Server Agent to schedule the job to run nightly. Which of the following tools can he use to send the email? A. A stored procedure B. A table-valued user-defined function C. A scalar user-defined function D. An inline user-defined function
36:	Stan is trying to figure out why his user-defined function isn't working properly. He keeps getting an incorrect syntax near the keyword 'return' error. What's wrong? create FUNCTION distance ( @x1 int = 0, @y1 int = 0, @x2 int = 0, @y2 int = 0 ) return float as begin declare @distance float set @distance = sqrt(power(@y1-@y2,2)+power(@x1-@x2,2)) return @distance end A. Side effects are caused by returning a value of type float. B. A comma is missing after the @y2 parameter. C. The return float should be returns float. D. A parenthesis is missing on the SET @distance statement.
37:	Kenny is trying to pass values into a stored procedure. Here's what the CREATE PROCEDURE statement looks like: create Procedure CalculateSalesTax @ItemPrice float, @Qty int, @TaxRate float, @SalesTax float OUTPUT Which of the following EXEC statements returns the sales tax into the @SalesTax variable? A. EXEC CalculateSalesTax 2.39, 1, 4, @SalesTax B. EXEC @SalesTax = CalculateSalesTax 2.39, 1, 4, 0 C. EXEC CalculateSalesTax @ItemPrice = 2.39, 1, 4, @SalesTax OUTPUT D. EXEC CalculateSalesTax @ItemPrice = 2.39, @Qty = 1, @TaxRate = .04, @SalesTax = @SalesTax OUTPUT
38:	Wendy has created a stored procedure to insert new values into her database: create procedure InsertPlayer @PlayerID int, @Comment varchar(2000) as begin insert into CommentTracker values (@PlayerID, @Comment) exec xp_sendmail @message = @Comment, @recipients = _'WendyT', @subject = 'Comment Activity' end The problem is that sometimes the insert fails with a constraint violation, and Wendy would like an email when those fail also. Which of the following approaches would enable this to happen? A. Capture the value of @@ERROR and use an IF statement to send an email if the INSERT statement fails. B. Capture the value of @@ERROR and use a WHILE loop to retry the insert in the event of an error. C. Combine the INSERT and the xp_sendmail into a transaction. D. Capture the value of @@LAST_ERROR and use an IF statement to send an email if the INSERT statement fails.
39:	You are responsible for designing a function that will be used to categorize player information for a local sports franchise. You design a user-defined function with the following: create function InsertPlayer (@PlayerID int, @Comment varchar(2000)) returns int begin insert into CommentTracker values (@PlayerID, @Comment) return 1 end What's wrong with this user-defined function? A. The function is correct as is. B. It's missing the keyword AS before the BEGIN. C. The functionality is not valid. D. It is a table-valued function.
40:	Bette is trying to write a stored procedure that will return a subset of a table back to her script. Which of the following methods will not work? A. Use a temporary table defined inside the stored procedure. B. Use a static cursor as an output parameter. C. Insert data into a temporary table that's defined before calling the stored procedure. D. Use a variable of type table as an output parameter.
41:	Consider the following stored procedure declaration: CREATE PROC Test2 @I int, @J float, @K varchar(2000) Which of the following EXEC statements will work? A. EXEC Test2 4.3, 19, 27 B. EXEC Test2 @J=4.3, @I = 20, @K = 'value' C. EXEC Test2 20, @J=4.3, 'value' D. EXEC Test2 20, @J=4.3, @K = 'value' OUTPUT
42:	To properly process an insurance claim, Esther has to have a client ID number and a claim amount. On claims over a certain amount of money, she has to have the name of the adjuster. Which of the following would be the best way to make sure that Esther had entered all the information properly before trying to process the claim? A. A multiline table-valued stored procedure B. An inline function C. A cursor D. A stored procedure
43:	You are writing a procedure that needs to send a return value back from the call. Which of the following accepts the return value from the stored procedure CheckInsuranceClaim? A. declare @RetVal float = CheckInsuranceClaim @ClaimID = 41400 B. declare @RetVal int exec CheckInsuranceClaim @ClaimID = 41400, @RetVal OUTPUT C. declare @RetVal int exec CheckInsuranceClaim @ClaimID = 41400 D. declare @RetVal int exec @RetVal = CheckInsuranceClaim @ClaimID = 41400
44:	Which of the following inline function definitions would correctly return the information for the customer with CustomerID 42? A. Create Function GetCustomerInfo @CustomerID int = 42 returns select * from customer where customerid = @CustomerID B. Create Function GetCustomerInfo (@CustomerID int = 42) returns table as return (select * from customer where customerid = 42) C. Create Inline Function GetCustomerInfo (@CustomerID int = 42 ) returns (select * from customer where customerid = 42) D. Create Function GetCustomerInfo (@CustomerID int = 42) returns @ReturnTable table (CustomerID int, CustomerName varchar(50)) as begin insert into @ReturnTable select * from customer where customerid = @CustomerID return end
45:	After examining his stored procedure that runs one statement, an INSERT, Larry determines that he's going to have a problem with users passing in bad data, which will cause inserts to fail because of constraints. Which of the following is the best approach to handling the situation? A. Use an inline function to check the constraint first. B. Check all the applicable constraints with SELECT statements before performing the insert. C. Attempt the insert and check the @@ERROR variable after the insert. D. Remove the constraints.
46:	You are working for a large international organization that supplies packaging materials for companies that require custom commercial designs. The number of products is becoming too large for the current computer system to handle, and you need to provide a solution that will spread the load over the current server and a new machine coming into the system. Queries need to be performed over a wide variety of products, and there is no predictable pattern to the queries. Which of the following is the most appropriate technique to implement the changes? A. Configure replication using the new machine as a Subscriber and the original machine as the Publisher/Distributor to balance the workload. B. Separate the table into two smaller tables and place one table on each server. Configure a partitioned view and appropriate constraints on each of the machines. C. Implement multiserver clustering so that each of the two servers can respond to data activities, thus achieving a balanced workload. D. Configure log shipping on both servers to have a copy of the data on each of the servers and propagate all changes to the alternate machine.
47:	As a developer for a large healthcare provider, you are assigned the task of developing a process for updating a patient database. When a patient is transferred from one floor to another, an internal identifier, CurrentRoomID, which is used as the primary key, needs to be altered, while the original key, AdmittanceRoomID, is still maintained. If a patient is moved more than once, only the original key and the current key need to be maintained. Several underlying tables have been configured for referential integrity against the patient table. These underlying tables must change in an appropriate manner to match with one or the other of the room keys in the patient table. These relationships will be altered based on different situations in other tables. What method would you use to accommodate the update? A. Use the Cascade Update Related Fields option to have changes in the primary key automatically update the keys in all referenced tables. B. Use an indexed view to enable the user to make changes to multiple tables concurrently. C. Disable the Enforce Relationship for INSERTs and DELETEs option to enable an AFTER TRIGGER to handle the necessary changes. D. Define an INSTEAD OF UPDATE TRIGGER to perform the necessary updates to all related tables.
48:	A large organization needs to maintain IMAGE data on a database server. The data is scanned in from documents received from the federal government. Updates to the images are infrequent. When a change occurs, usually the old row of data is archived out of the system and the new document takes its place. Other column information that contains key identifiers about the nature of the document is frequently queried by an OLAP system. Statistical information on how the data was queried is also stored in additional columns. The actual document itself is rarely needed except in processes that print the image. Which of the following represents an appropriate storage configuration? A. Place the IMAGE data into a filegroup of its own, but on the same volume as the remainder of the data. Place the log onto a volume of its own. B. Place all the data onto one volume in a single file. Configure the volume as a RAID parity set, and place the log into a volume of its own. C. Place the IMAGE onto one volume in a file of its own, and place the data and log files together on a second volume. D. Place the IMAGE into a separate filegroup with the log on one volume and the remainder of the data on a second volume.
49:	You are the administrator of a SQL Server 2000 computer. The server contains your company's Accounts database. Thousands of users access the database each day. You have been experiencing power interruptions, and you want to protect the physical integrity of the Accounts database. You do not want to slow down server operations. What should you do? A. Enable the torn page detection database option for each database. B. Disable write caching on all disk controllers. C. Create a database maintenance plan to check database integrity and make repairs nightly. D. Ensure that the write caching disk controllers have battery backups.
50:	An Internet company sells outdoor hardware online to more than 100,000 clients in various areas of the globe. Servicing the website is a SQL Server whose performance is barely adequate to meet the needs of the site. You would like to apply a business rule to the existing system that will limit the outstanding balance of each customer. The outstanding balance is maintained as a denormalized column within the customer table. Orders are collected in a second table containing a trigger that updates the customer balance based on INSERT, UPDATE, and DELETE activity. Up to this point, care has been taken to remove any data from the table if the client balance is too high, so all data should meet the requirements of your new process. How would you apply the new data check? A. Modify the existing trigger so that an order that allows the balance to exceed the limit is not permitted. B. Create a check constraint with the No Check option enabled on the customer table so that any inappropriate order is refused. C. Create a rule that doesn't permit an order that exceeds the limit, and bind the rule to the Orders table. D. Create a new trigger on the Orders table that refuses an order that causes the balance to exceed the maximum. Apply the new trigger to only INSERT and UPDATE operations.