1. After creating some triggers on a table, you realized that they execute in the wrong order. What can you do to have the triggers executing in the right order?
Drop the triggers, and re-create them in the corresponding order
Use the sp_settriggerorder system stored procedure
Execute an ALTER TABLE statement with ALTER TRIGGER to change the trigger order
Create the triggers with _x where x is the trigger order number
2. Your database server is configured using the default settings, and the user databases have the default options. One of your applications updates a table named tblCustomers. The update fires a trigger named UpdateCustomerDetails that will modify the tblCustomerDetails table. The modification of table tblCustomerDetails fires a trigger named UpdateCustomer that will modify the tblCustomers table. This behavior generates recursion. Which option allows this behavior?
The RECURSIVE_TRIGGERS database option set to ON
The RECURSIVE_TRIGGERS database option set to OFF
The nested triggers’ server configuration option set to 0
The nested triggers’ server configuration option set to 1
3. You are the database administrator of your company. One of your company’s applications should maintain data integrity and return custom error messages when the entry data is incorrect. How can you achieve that with minimum effort?
Add check constraints to necessary columns
Use a DDL trigger
Use a CLR trigger
Use a DML trigger
4. You have a large table with several thousand rows of product data. You have just imported a whole new catalog of seasonal data that has changed most of the data in the products table. You have a nonclustered index on the table that is now out-of-date. You need to bring it back up-to-date quickly. What can you do?
Re-create the index using STATISTICS_NORECOMPUTE = ON
Re-create the index using DROP_EXISTING = ON
Drop the existing index manually, and re-create it
Do nothing; SQL Server will bring the index back up-to-date automatically
5. You have a server with several hard disk sets; all of your system databases, including TempDB, Master, and Model, are on one disk set, and a user database that you will be updating is on a disk set of its own. You need to create an index on a large table, and you want to make it as fast as possible. You are not worried about system resources such as disk space, processor time, or memory used. What can you do to create this index as fast as possible?
Create the index with MAXDOP = 0
Create the index with STATISTICS_NORECOMPUTE = ON
Create the index with SORT_IN_TEMPDB = ON
Create the index with PAD_INDEX = ON
6. Which of these page types is used to store information about changes to the database since the last BACKUP DATABASE statement was executed?
Index Allocation Map page
Global Allocation Map page
Differential Changed Map page
Page Free Space page
Bulk Changed Map page
7. One of your third-party applications has been certified to run on SQL Server 2000 but not 2005. Your company has just bought a new application that requires SQL Server 2005 to run. How can you run both of these applications with minimal overhead?
Buy a second server, and install SQL Server 2005 on the new machine
You can’t run both applications; you will have to wait until the older application is certified to run on SQL Server 2005
Install SQL Server 2005 as a named instance, and configure your new application to use the new instance
Install SQL Server 2005 as the default instance, and configure your new application to use the new instance
8. You have a machine that has a 3.2GHz Pentium Xeon processor with 4GB of RAM and a 320GB hard drive running Windows 2003 Enterprise Edition. Which editions of SQL Server 2005 can you install? (Choose all that apply)
9. You are required to log every change to the customer data in a separate table named customer_history. How can you achieve this?
Create a DML trigger on the customer table
Create a DDL trigger on the customer table
Use database snapshots
Use database mirroring
10. You have a table that contains employee information. Human Resources frequently queries this table using a query similar to the following: SELECT FirstName, LastName, Address, Phone FROM Employees WHERE SSN = ‘ssn’
You need to create an index to speed up this query. What type should you create?
Create a clustered index on the SSN column
Create a nonclustered index on the SSN column
Create a full-text index on the SSN column