| 1. | You are attempting to create an index on an existing view called SalesOverQuota, created in the SALES database. When the view was initially created, no preparation was done for indexing. You want to maintain the condition that was originally attached to the WHERE clause of the view. How do you implement the index? A. Re-create the view by using WITH CHECK. B. Re-create the view by using WITH SCHEMABINDING. C. Use DROP EXISTING when creating the index. D. Create the index as nonclustered. E. Re-create the view, removing the WHERE clause.
|
| 2. | You are still attempting to create an index on an existing view called SalesOverQuota, created in the SALES database. You would like to create a nonclustered index on the CustomerName column of the view. No other indexes have been created on the view. The underlying table has a clustered index on the primary key. What is the first step in implementing the index? A. Create a suitable clustered index on the view. B. Create a nonclustered index on the view. C. Ensure that the primary key is included in the view. D. Ensure that the primary key is unique. E. Create a unique nonclustered index.
|
| 3. | You are setting up the ODBC connection for a client computer. The server was installed using the default protocol and communications configuration. What TCP/IP port should be configured? A. 433 B. 443 C. 1433 D. 1443 E. 4443
|
| |
| 4. | Within a test environment, you are configuring a connection to an instance of SQL Server that is on the same machine as the application. Protocols have been configured to not allow access to SQL from external machines and applications. What is the most secure way to connect to SQL Server? A. TCP/IP B. SSL C. HTTP D. HTTPS E. Shared memory
|
| 5. | You have started SQL Server from the command prompt by using SQLSERVR.EXE -f to get the server into single-use operations. You notice that users are still able to connect to the server. What is wrong? A. The server has not been paused. B. The server has not been stopped. C. Minimal configuration allows multiple connections. D. The service option was not used in startup.
|
| 6. | You are about to install a new instance of SQL Server. What connection protocols are available for use? (Select all that apply.) |
| 7. | An application needs to query different data, based on user-selected criteria. The interface is presented to the user via a Windows application interface. After the selection is made, the data is drawn from the server and returned to a grid layout on the user's computer. What technology would be used for the implementation? A. Stored procedure using the common language runtime B. Stored procedure using Transact-SQL C. Stored procedure using parameters and the common language runtime D. Stored procedure using parameters and Transact-SQL E. Stored procedure implemented through Web Services
|
| |
| 8. | A procedure being called by an application needs to perform a series of complex calculations. The interface is presented to the user via a Windows application. After the user makes selections, the application calls the procedure. Results are returned to the user's computer. What technology would be used for the implementation? A. Stored procedure using the common language runtime B. Stored procedure using Transact-SQL C. Stored procedure using parameters and the common language runtime D. Stored procedure using parameters and Transact-SQL E. Stored procedure implemented through Web Services
|
| 9. | You are writing a procedure that will create a permanent table within an existing database. Which of the following will you use in the procedure to store the table? |
| 10. | You need a summary listing of all the user objects within a database. What would you use? A. sp_Help B. SELECT * FROM sysobjects C.SELECT * FROM master..sysobjects D.SELECT * FROM sys.database_principals E.SELECT * FROM sys.objects
|
| 11. | A production server has implemented partitioning, using a function that divides the data into four sections and reserves a fifth partition for future expansion; the fifth partition is currently unused. You need to query the utilization of the four currently used partitions. How would you perform the query? A. sp_spaceused B. sp_table_validation C. sp_server_info D. $Partition E. $Identity F. $RowGUID
|
| |
| 12. | An application being developed for a production database requires data to be extracted from a comprehensive table and added to another database. The statement must create a new table every time it is executed. Which of the following commands would be used? A.EXEC ('INSERT INTO ' + @NewTable + ' SELECT * FROM SalesOrders') B. EXEC ('INSERT ' + @NewTable + ' SELECT * FROM SalesOrders') C. EXEC ('SELECT * INTO' + @NewTable + ' FROM SalesOrders') D.EXEC ('SELECT * INTO #NewTable FROM SalesOrders') E. EXEC ('SELECT * INTO ##NewTable FROM SalesOrders')
|
| 13. | You have a text field in an employee table that is used to maintain general information in the form of memos. You would like to make this field searchable. Other tables in the database have the same functionality implemented. What do you need to execute? |
| 14. | You have an application that accesses data on two servers. When you attempt to execute a query on the first server using both data sets, the query fails when it attempts to access the second server. What do you need to do? A. Use four-part names in the query. B. Reference the second database by using an alias. C. Add the second server as a linked server on the first server. D. Add the second server as a remote server on the first server. E. Add the first server as a linked server on the second server.
|
| 15. | You are working with two servers. You have configured linked server operations. Both servers use SQL Server authentication and different logins. How do you handle connectivity between the two servers? A. Change security to use Windows authentication. B. Add additional logins with the same ID and password. C. Hard-code the user ID and password in the application and use pass-through authentication. D. Use the sa user ID and password. E. Configure the linked server to impersonate logins.
|
| 16. | You are about to perform database analysis against a workload that was taken from the production server. You need to perform a complete analysis of all potential index changes. What is the appropriate setting? A. Evaluate Utilization of Existing PDS Only B. Keep All Existing PDS C. All Recommendations Are Offline D. Generate Online Recommendations Where Possible E. Generate Only Online Recommendations
|
| 17. | You need to configure the settings for an HTTP endpoint on a production SQL Server. It is desired to have the connection performed over SSL (HTTPS). The following definition is being used: CREATE ENDPOINT sql_endpoint STATE = STARTED AS HTTP( PATH = '/sql', AUTHENTICATION = (INTEGRATED), PORTS = (CLEAR), SITE = 'SERVER' ) FOR SOAP ( WSDL = DEFAULT, SCHEMA = STANDARD, DATABASE = 'master', NAMESPACE = 'http://tempUri.org/' )
What must be changed? A. AS HTTP should be AS HTTPS. B. AUTHENTICATION should be BASIC. C.PORTS setting requires SSL. D. WSDL should be set to NONE. E. ENCRYPTION = REQUIRED should be added.
|
| |
| 18. | You need to find out information about the scheduling of jobs used to perform automated activities. Specifically, you would like to know when was the last time a job was run. Which procedure would you use? |
| 19. | You are using the Database Engine Tuning Advisor and you would like to select options that give the best recommendations for partitioning performance. What option would you select? |
| 20. | Several users are complaining of problems with updating of data through their application. You suspect that locks placed by other users and applications are preventing updates from occurring. Which of the following will help locate current locks and blocking? (Select all that apply.) A. sys.dm_exec_sessions B. sys.dm_exec_requests C. Activity Monitor, Process Info D. Activity Monitor, Locks by Process E. System Monitor, SQL Server:Locks
|
| 21. | Indexes of a table are heavily fragmented. What should you do to decrease the fragmentation of all indexes while keeping the table available to users? A. Defragment the disk that contains the table. B. Use DBCC CLEANTABLE. C. Use ALTER INDEX ALL REORGANIZE. D. Use ALTER INDEX ALL REBUILD. E. Use DBCC CHECKIDENT.
|
| |
| 22. | You are working with a database named Sales on a server named SELLER. You want to mirror Sales on a second server named SELLER2. Which actions do you perform to prepare Sales on SELLER? (Select three answers, as each correct answer presents part of the solution.) A. Set the recovery model to bulk-logged. B. Set the recovery model to full. C. Back up Sales. Restore the backup on SELLER2 by using NORECOVERY. D. Back up Sales. Restore the backup on SELLER2 by using STANDBY. E. Back up Sales. Restore the backup on SELLER2 by using RECOVERY. F. Create endpoints on both servers. G. Set the Auto Update Statistics property of Sales to false.
|
| 23. | You are moving a database to a new server. The database is used by a data entry application. You need to minimize the amount of time the application is unavailable. What should you do? A. Set up transactional replication between the servers. B. Move the data files and provide the new location by using ALTER DATABASE. C. Back up the database. Restore the database to the new server. D. Detach the current database. Copy the data files to the new server. Attach the files. E. Move the database to the new server by using the SMO method in the Copy Database Wizard.
|
| 24. | You want to use Service Broker to manage data requests. After creation of the broker queue, you need to make other changes and want to ensure that no messages can be received by the service. What should you do? A. Create the queue with STATUS OFF. B. Create the queue with ACTIVATION STATUS OFF. C. Create the queue with only the queue_name argument. D. Create the queue with MAX_QUEUE_READERS = 0. E. Create the queue with RETENTION OFF.
|
| |
| 25. | One of the daily scheduled jobs aggregates data from multiple sources for reports. The job consists of multiple steps that aggregate data for a specific report. A user reports that the data for some of the reports has not been updated. You need to ensure that every step of the job executes, even when errors occur. What should you do? A. Configure each step as a separate job. B. Change the On Failure action to go to the next step. C. Combine all the steps into a single step that runs once a day. D. Create a notification that alerts you when an error occurs so that you can correct the error and restart the job. E. Configure the job to retry the step.
|
| 26. | You are creating a view to join the Customers and Orders tables. You need to ensure that the view cannot be affected by modifications to underlying table schemas. You want to use the least possible overhead. What should you do? A. Create a DML trigger to roll back any changes to the tables. B. Create CHECK constraints on the tables. C. Create a DDL trigger to roll back any changes to the tables. D. Create the view, specifying the WITH CHECK option. E. Create the view, specifying the WITH SCHEMABINDING option.
|
| 27. | The database schema for an order entry application needs additions. A new column is to be added. You will supply the column with an initial value. You need the column to be modified as necessary. What should you do? A. Create a DEFAULT constraint. B. Create an UPDATE trigger. C. Create a CHECK constraint. D. Create an INSERT TRigger. E. Create an INSTEAD OF trigger.
|
| 28. | Your database contains a table that has 500 million rows of data. Some of the data is historical and some is current. You need to partition the data on a single server to increase performance. What should you do? A. Implement horizontal partitioning. B. Implement vertical partitioning. C. Implement a raw partition. D. Implement distributed partitioning. E. Implement index partitioning.
|
| |
| 29. | You are implementing views that are used in ad hoc queries. The views are used to enforce application security policy. Some of these views perform slowly. You create indexes on those views to increase performance but still maintain the security policy. One of the views returns the current date as one of the columns. The view returns the current date by using the GEtdATE() function. This view does not allow you to create an index. Which two actions should you perform? (Select two answers, as each correct answer presents part of the solution.) A. Remove all deterministic function calls from within the view. B. Remove all date fields from the view. C. Create the view and specify the WITH CHECK OPTION clause. D. Remove all nondeterministic function calls from within the view. E. Schema-bind all functions that are called from within the view.
|
| 30. | The company website includes a page that customers use to send feedback. Data is stored in the Comments column of a table named Feedback. You need to implement full-text searching so that you can run reports on the comments. Which two actions should you perform? (Select two answers, as each correct answer presents part of the solution.) A. Create a clustered index on the Comments column. B. Create a full-text catalog. C. Create a full-text index on the Comments column. D. Create a nonclustered index on the Comments column. E. Execute the USE T-SQL statement against the master.
|
| 31. | You are creating a database to support a new web-based application that will handle up to 10,000 simultaneous users. This application must quickly display the results of calculation-intensive operations. You need to ensure that the database processes calculations as quickly and efficiently as possible. What should you do? A. Implement CLR stored procedures in the database. B. Implement distributed web services. C. Implement Transact-SQL stored procedures in the database. D. Have all calculations performed on the client machine. E. Implement parameterized Transact-SQL queries in the application.
|
| |
| 32. | You work for a bank that processes 50,000 transactions every day. The application requires a clustered index on the TRansactionID column. You need to create a table that supports an efficient reporting solution that queries the transactions by date. What are the two ways to achieve this goal? (Select two answers.) A. Place a nonclustered index on the date column. B. Create a partitioning scheme that partitions the data by date. C. Add a unique clustered index on the date column. D. Map each partition to a filegroup, with each filegroup accessing a different physical drive. E. Create a Value() secondary index.
|
| 33. | Users report with increasing frequency that they receive deadlock error messages. You need to monitor which objects and SQL Server session IDs are involved when deadlock conditions occur. You want information about each participant in the deadlock. What should you do? A. Trace the Lock:Timeout event by using SQL Server Profiler. B. Observe the SQLServer:Locks - Number of Deadlocks/sec counter by using System Monitor. C. Observe the SQLServer:DeadLocks - Number of Deadlocks/sec counter by using System Monitor. D. Trace the Lock:Deadlock event by using SQL Server Profiler. E. Trace the Lock:Deadlock Chain event by using SQL Server Profiler.
|
| 34. | You manage a database that contains a table that has many indexes. You notice that data modification performance has degraded over time. You suspect that some of the indexes are unused. You need to identify which indexes were not used by any queries since the last time SQL Server 2005 started. Which dynamic management view should you use? A. sys.dm_fts_index_population B. sys.dm_exec_query_stats C. sys.dm_db_index_physical_stats D. sys.dm_db_physical_stats E. sys.dm_db_index_usage_stats
|
| |
| 35. | You are modifying a table. You want to add a new Unicode character column that is 35 positions wide. The table currently contains data. All data is not yet known for the content of the column, yet the column will be required. You want to add this new column by using the least amount of effort. What should you do? A. Define the new column as NULL. Update the column to the same value as the primary key column. Alter the column to be NOT NULL. B. Define the new column as NULL. Use application logic to enforce the data constraint. C. Define the new column as NULL with a default value of Undefined. D. Wait until all data content is known before adding the new column. E. Define the new column as NOT NULL with a default value of Undefined.
|
| 36. | A routinely used view that joins the Customers and Sales tables is used to aggregate total sales by customer by month. You need to increase the performance of the view. What should you do? A. Create two separate views that do not contain any joins. B. Create a stored procedure to use in place of the view. C. Create a clustered index on the view. D. Develop a CLR procedure in place of the views. E. Update the view to use an outer join.
|
| 37. | You create an assembly that contains a CLR function. This function reads data from a spreadsheet, performs some calculations, and returns the data to SQL Server. You need to register the assembly by using the CREATE ASSEMBLY statement with the least privileged security permission set. Which permission set should you use? A. The default B. EXTERNAL_ACCESS C. SAFE D. UNSAFE E. BLENDER
|
| |
| 38. | Customer data from your trading partners is imported every night. You need to ensure that the customer record is updated if it already exists. If the record does not exist, the data for the customer needs to be inserted into the Customers table. What should you create? A. An AFTER TRigger B. A FOR TRigger C. A DDL trigger D. An INSTEAD OF trigger E. A CLR TRigger
|
| 39. | You are creating an application that will store original documents as XML documents on a file server. You need to insert the documents into the database. The documents will be retrieved from the database and must be identical to the originals. You need to design a table to store the document data. What should you do? A. Store the XML in a text column. B. Store the XML in a varchar(8000) column. C. Store the XML in an XML column. D. Shred the XML and store it in a relational structure. E. Store the XML in an nvarchar(max) column.
|
| 40. | You are configuring backup jobs. Backup files are written to a tape drive. In the future, backups will go to a new server. Backup files will be written to disk on the new server. To simplify maintenance, you need to configure the backup jobs so that minimal changes will be necessary to back up to the new server. What should you do? A. Have jobs write to an internal disk. When the new backup server is available, alter the backup jobs. B. Have jobs write to backup devices. Alter the devices when the new server is available. C. Have jobs write to the tape drive. Move the tape drive to the new backup server. D. Have jobs write to an internal disk. Alter the backup jobs to use backup devices after the new server is in place. E. Have the jobs write to the tape drive. Delete the jobs and re-create them, writing to disk on the new server.
|
