| 1. | You are creating a database that will store sales order information. Orders will be entered in a client/server application and over the Internet via an ASP.NET application. Each time a new order is placed in the system, a unique order number must be assigned, and the order numbers must be in ascending sequence. It is expected that the system will generate in excess of 100,000 orders weekly. You create a new table named Orders and a related table named Order Details. How should you create the order number to provide the required functionality in the simplest manner? A. Use a UniqueIdentifier data type. B. Use an Integer data type and set the IDENTITY property for the column. C. Use a TimeStamp data type and create a user-defined function that sets the order number. D. Create a table to hold key values and assign order numbers from this table. E. Have the front-end application assign sequential integer order numbers.
|
| 2. | You are designing a query that must return related data from two tables. You must return only data that matches between the tables. One column of data allows null entries, and you are not to return data from either table in this case. Which of the following SQL items is needed? (Select two answers.) A. JOIN B. UNION C. IN D. BETWEEN E. ISNULL
|
| 3. | You are preparing a table design for a complex business database application. After data entry, it is a requirement of one of these fields that a series of complex business logic procedures must be performed to obtain the value for the field. Which of the following would you implement? |
| |
| 4. | A database that has been used in production is in need of analysis to attempt to make performance improvements. Which of the following is likely to improve performance, if correctly implemented? (Choose all that apply.) A. Table partitioning B. Covering indexes C. Triggers D. RAID 5 E. C2 security
|
| 5. | You have implemented a database for an international research organization and are performing test queries against the tables within the database. You have some date fields in the database that store only date information. No time information is maintained within these columns. You would like to have a listing of the data from only the year 2005. Which of the following queries represents the best solution to the problem? A. SELECT * FROM RTab WHERE RDate BETWEEN '01/01/2005' AND '01/01/2006' B. SELECT * FROM RTab WHERE RDate BETWEEN '12/31/2004' AND '12/31/2004' C. SELECT * FROM RTab WHERE RDate BETWEEN '12/31/2004' AND '01/01/2005' D. SELECT * FROM RTab WHERE RDate BETWEEN '01/01/2005' AND '12/31/2005' E. SELECT * FROM RTab WHERE RDate BETWEEN '12/31/2005' AND '01/01/2005'
|
| 6. | You have entered a query using a TOP function to limit the number of records being viewed to five. When you see the results of the query, the dates being viewed are not the first five in the data. What is the most likely source of the problem? A. The result set has not been grouped. B. The data contains NULL values. C. There is an incorrect ORDER BY. D. Table aliases were used. E. Schema binding has been applied.
|
| 7. | Your accounting system works with string variables that are aligned to the left in some cases and to the right in others. Space filling is used on all fields in the interface. You need to remove excess spaces from the strings prior to accepting the value into a table. What functions would you use? (Choose all that apply.) A. TRIM B. LTRIM C. RTRIM D. REPLACE E. STR
|
| 8. | You are working on a view that was created when the database was first implemented several years ago. You believe that indexing the view would improve its performance. When you try to implement the index, you are unsuccessful because an ANSI_NULLS error occurs. What must you do to implement the index? A. Create the index with ANSI_WARNINGS set to OFF. B. Create the index with ANSI_WARNINGS set to ON. C. Re-create the view without SCHEMABINDING. D. Re-create the view with ANSI_NULLS set to OFF. E. Re-create the view with ANSI_NULLS set to ON.
|
| 9. | A production database is accepting sales orders from an online catalog order business. Inserts to the sales table occur frequently. When it was created, the table had a FillFactor setting of 75%. You want to inspect size and fragmentation of the indexes in the table. What do you need to do? (Choose two answers.) A. Check the Extended Properties page of the table from within SQL Server Management Studio. B. Query sys.dm_fts_indexpopulation. C. Query sys.dm_exec_query_stats. D. Query sys.dm_db_index_physical_stats. E. Check the Fragmentation page of the index from within SQL Server Management Studio.
|
| |
| 10. | You need to import a large amount of data into an existing table. To speed up the process, you would like to disable all indexing and enable it once again when the process is complete. How would you implement the process? A. Use ALTER INDEX DISABLE and ALTER INDEX ENABLE. B. Use DISABLE INDEX and ENABLE INDEX. C. Use DROP INDEX and CREATE INDEX. D. Use ALTER INDEX DISABLE and ALTER INDEX REBUILD.
|
| 11. | You are diagnosing performance problems with one particular table in a major production database. A large amount of data has just been imported. You would like to repair any fragmentation and integrity issues and provide query response based on the newly imported data. Which statements would you use? (Choose three answers.) |
| 12. | You need to map a local server login to a remote server login. Which process should you use? A. sp_helplogins B. sp_addlogin C. sp_adduser D. sp_addlinkedsrvlogin E. sp_grantlogin F. SSIS transfer logins
|
| 13. | A special process developed for periodic maintenance needs to run under a system administrator's account. The process will be run by individuals who lack the permissions to perform the tasks contained in the process. What would you do? A. Have a system administrator run the task when needed. B. Use sp_change_users_login Auto_Fix. C. Use sp_change_users_login Update_One. D. Have the process run using EXECUTE AS. E. As a system administrator, schedule the task to run and set the process to run.
|
| |
| 14. | You have upgraded a database from SQL Server 2000 and are now accessing the tables and other objects within the database. You need to supply the appropriate schema to be used for procedures within the upgraded system. Which schema would you use? |
| 15. | As deletions are made from one table within the system, you would like to automatically delete detail records within another table. How would you implement this functionality? A. Use a DDL trigger. B. Use a DML trigger. C. Use a stored procedure. D. Use a user-defined function. E. Alter options within the table definition.
|
| 16. | Someone in the IT department has been creating, deleting, and modifying tables in a production database without first having them tested in the lab environment. You would like to find out who is making changes to the databases. How would you implement this process? A. Use a DDL trigger. B. Use a DML trigger. C. Schedule a stored procedure. D. Change the permissions to only allow one ID to change the production system and implement password policies. E. Alter the permissions of the schema.
|
| 17. | You are implementing a partitioning plan to improve the performance and scalability of a production database. Which command do you execute first? |
| |
| 18. | You are inspecting the transaction handling for an application that updates a number of tables from the SALES database. You would like to know how many transactions are currently open and not committed. What is the easiest way to solve this? A. Run DBCC OPENTRAN ('master'). B. Run DBCC OPENTRAN ('SALES'). C. Open the Activity Monitor and view open transactions in Process Info. D. Execute a query against sys.sysprocesses. E. Use sp_helptransaction.
|
| 19. | You are creating a backup job and want to ensure that a new header is placed on the tape you are using. What option do you use to perform this task? A. INIT B. FORMAT C. INIT with SKIP D. INIT with NOSKIP E. NOINIT with NOSKIP
|
| 20. | You would like to assign the same schedule to two separate jobs that perform unrelated activities. Sometimes each job will be run on a different schedule, as well. How do you implement this? A. Create one schedule and assign it to both jobs. B. Create two schedules, one for each job. C. Create a job that executes the other two jobs. Create one schedule for the newly created job. D. Create a job that executes the other two jobs. Create two schedules, one for each job.
|
| 21. | Someone has created 60 jobs on the server to perform a variety of functions. You need to create a backup of the jobs so that they can be easily re-created in the event of a failure. What should you do? A. Back up the master database. B. Back up the model database. C. Back up all system databases. D. Create scripts for each of the jobs. E. Back up the entire server.
|
| |
| 22. | You have 50,000 records in a database file, and you know you want to add 25,000 records in the next month. Note that a new index is to be created, and you will change your FILLFACTOR; you also want fast input into the tables. What value should you specify for FILLFACTOR to maximize performance? A. 0 (default setting) B. 100 C. 70 D. 50
|
| 23. | You are configuring log shipping on two servers. The source machine houses the internal accounting, human resources, and other related systems. The destination machine is in the human resources department. You want to set up the HR database at the destination so that it can be used for reporting purposes. How do you set up the procedure? (Select all that apply.) A. Set the source to simple recovery. B. Set the source to full recovery. C. Perform a full backup and a log backup of the source. D. Perform a full backup and a differential backup of the source. E. Restore both backups at the destination. Restore the full backup with no recovery, and restore the second with recovery. F. Restore both backups at the destination. Restore the full backup with no recovery, and restore the second with standby.
|
| 24. | Last month, a database snapshot was created on the SALES database. You would now like to get a single table from the snapshot. You need to maintain the current copy of the database and not lose any data in the process. How do you access the table? (Select all that apply.) A. Perform a full backup. B. Create a new snapshot. C. Restore from the backup. D. Revert to the new snapshot. E. Revert to the original snapshot. F. Restore the snapshot to a different database.
|
| |
| 25. | What is true about the WITH SCHEMABINDING argument of the CREATE INDEX statement? (Choose all that apply.) A. It must be specified to create an indexed view. B. It allows a view's name and other properties to be changed dynamically. C. It prevents the dropping and altering of tables participating in the view. D. It has to be specified only when you are creating a unique clustered index on text data.
|
| 26. | You would like to alter the content of a column in the SALES table that stores data in the XML type. Which method should you use? A. exist() B. modify() C. nodes() D. query() E. value()
|
| 27. | You are a database developer for a computer manufacturing company. For a limited time, the company ships free software with the purchase of any desktop computer or notebook. The software titles, descriptions, values, and other information are located in the Software table. You configure full-text indexing on the Software_Description column that contains more than 2,000 rows and is located in the Software table. After executing a search by using FREETEXT for the word Windows, you notice an empty result set in the results pane. Why is this happening? A. The catalog is not populated. B. FREETEXT is not a valid keyword recognized by SQL Server 2005. C. FREETEXT is not allowed for columns that contain 2,000 or more rows. D. You didn't create a nonclustered index.
|
| 28. | You are importing a large amount of data into the Customer table. The table has a trigger that sends emails to client representatives when new customers are added to the table. You would like to prevent the emails from being sent during the import. What should you use to accommodate the import? A. DISABLE TRIGGER and ENABLE TRIGGER B. ALTER TABLE DISABLE TRIGGER and ALTER TABLE ENABLE TRIGGER C. DROP TRIGGER and CREATE TRIGGER D. ALTER TRIGGER NOT FOR REPLICATION E. ALTER TRIGGER DISABLE and ALTER TRIGGER ENABLE
|
| 29. | A database is about to be set up to be replicated to the human resources department. One of the tables contains a trigger that you do not want to fire when the data hits the destination database. How do you accommodate this? A. Use DISABLE TRIGGER at the destination after replication setup has been completed. B. Use DROP TRIGGER at the destination after replication setup has been completed. C. Use sp_settriggerorder at the source before setting up replication. D. Use sp_settriggerorder at the destination after setting up replication. E. Use ALTER TRIGGER NOT FOR REPLICATION before setting up replication.
|
| 30. | A small scientific laboratory needs a powerful database server to perform analysis of complex measures performed on scientists' regular experiments. The lab requires exact accuracy with all calculations because the results determine the fracture points of various metals. Which data type offers the most accurate results? A. smallmoney B. money C. float D. real E. decimal
|
| 31. | You want to import a large amount of data from text files. You would like to speed the operation by having the import be performed in primary key sequence. How can you do this? A. Use an order hint with BCP. B. Use an order element in the format file. C. Use an order hint with BULK INSERT. D. Use ORDER BY when creating the text files. E. Use the SORT option with the bcp command.
|
| |
| 32. | You are designing a database that will serve as a back end for several large websites. The websites will communicate with each other and pass data back and forth by using XML. You would like to control the data displayed on the user's 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 should you 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 by using FOR XML. D. Send data requests and updates directly from the client machine to the server by using the FOR XML and OPENXML options. E. Use HTML and an XML schema to provide the necessary view of the data.
|
| 33. | You are performing a BCP operation to import data from text files prepared on another system. The pipe (|) character has been used as a field delimiter, and a colon (:) is the record delimiter. How do you accommodate this file? A. Reformat the file as a tab-delimited text file. B. Reformat the file as a comma-delimited text file. C. Use the bcp command with all defaults. D. Use BULK INSERT with all defaults. E. Use a format file.
|
| 34. | You are creating an application in which accuracy is important. Which data type will give the greatest possible precision? A. int B. bigint C. decimal D. real E. float
|
| |
| 35. | Users inform you that recently, they have frequently been receiving error messages as query volume has steadily increased. One of the users captured the following message: 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.
|
| 36. | You are creating a remote service binding for use by the Service Broker in connecting to another machine. You would like the service to run in the context of a system administrator and require authentication to be performed within the connection. What command options should you use? A. USER = SysAdmin, ANONYMOUS = ON B. AUTHORIZATION = sa, ANONYMOUS = ON C. AUTHORIZATION = sa, USER = SysAdmin D. USER = SysAdmin, ANONYMOUS = OFF E. AUTHORIZATION = sa, ANONYMOUS = OFF
|
| 37. | You are creating an index for an existing table. There is already a nonclustered index on the primary key. You would like to put the data into address sequence, which is made up of several fields. The address is not the primary key. What should you do? A. Change the primary key index to clustered and create a nonclustered index on the address. B. Create a nonclustered index on the address. C. Create a clustered index on the address. D. Change the primary key to the address and make the index clustered. E. Change the primary key to the address and make the index nonclustered.
|
| |
| 38. | You want to create an XML index on the Customer table of the SALES database. What do you do? (Select two answers.) A. Create a standard primary clustered index. B. Create a standard primary nonclustered index. C. Create an XML primary index. D. Create an XML secondary index. E. Create a standard extended index.
|
| 39. | You are testing the database mirroring functionality. You would like to try automatic failover. Which of the following are required? (Select all that apply.) |
| 40. | You have a statistical database that has scheduled snapshots being created every night. To run a series of reports, you would like to get the database back to the point where it was two weeks ago. What do you do? (Select all that apply.) A. Restore from the appropriate snapshot. B. Restore from backup. C. Create an additional snapshot. D. Delete all snapshots. E. Delete all snapshots except the one from two weeks ago.
|
