Microsoft 70-431 exam - 10 Sample Questions.
Certification Disclaimer and Tips - Please Read Me First
Sample Q1
You are preparing for a new installation of SQL Server 2005 in Trevdogcertified.com. You need to select the protocols that client computers might use to connect to the server. Which two protocols can you use to achieve this goal? (Each correct answer presents a complete solution. Choose two.)
[A] Shared Memory.
[B] Virtual Interface Adapter (VIA).
[C] Multiprotocol.
[D] Named Pipes.
[E] TCP/IP
Sample Q2
A power failure occurs on the storage area network (SAN) in Trevdogcertified.com where the SQL Server 2005 database server is located. You need to check the allocation as well as the tructural and logical integrity of all databases, including their system catalogs. What should you do?
[A] Execute DBCC CHECKDB
[B] Execute DBCC CHECKFILEGROUP for each filegroup
[C] Execute DBCC CHECKCATALOG
[D] Execute DBCC CHECKTABLE for each table
Sample Q3
Trevdogcertified.com has a server named SQL1 that runs SQL Server 2005 Enterprise Edition. SQL1 has 2 GB of RAM, 1.6 GB of which are used by the default SQL Server database engine instance. Theaverage data growth of all databases combined is 100 MB a month. Users state that report execution times are increasing. You want to assess whether more RAM is needed. You need to use System Monitor to create a counter log that will help you decide whether to add RAM. Which performance object should you add to the counter log?
[A] ResMSAS 2005:Cache.
[B] SQLServer:Buffer Manager.
[C] MSAS 2005:Memory.
[D] MSAS 2005:Proactive Caching.
[E] SQLServer:General Statistics
[F] SQLServer:SQL Statistics
Sample Q4
Trevdogcertified.com uses a SQL Server 2005 database. This database contains a trigger named tdc_InsGoodsOrders, which fires when order data is inserted into the Orders table. The trigger is responsible for ensuring that a customer exists in the Customers table before data is inserted into the Orders table. You need to configure the trigger to prevent it from firing during the data import process. You must accomplish this goal while using the least amount of administrative effort. Which two Transact-SQL statements can you use to achieve this goal? (Each correct answer presents a complete solution. Choose two.)
[A] ALTER TABLE OrdersDISABLE TRIGGER tdc_InsGoodsOrders.
[B] DROP TRIGGER tdc_InsGoodsOrders.
[C] ALTER TRIGGER tdc_InsGoodsOrders ON Orders NOT FOR REPLICATION.
[D] sp_settriggerorder@triggername= 'tdc_InsGoodsOrders', @order='None'.
[E] DISABLE TRIGGER tdc_InsGoodsOrders ON Orders
Sample Q5
Your company uses SQL Server 2005. You are implementing a series of views that are used in ad hoc queries. The views are used to enforce your companys security policy of abstracting data. Some of these views perform slowly. You create indexes on those views to increase performance, while still maintaining the companys 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. You need to create an index on the view. Which two actions should you perform? (Each correct answer presents part of the solution. Choose two.)
[A] Remove all deterministic function calls from within the view.
[B] Remove all nondeterministic function calls from within the view.
[C] Create the view and specify the WITH CHECK OPTION clause.
[D] Schema-bind all functions that are called from within the view.
Sample Q6
A new employee needs access to a SQL Server 2005 database that is located on a server named SQL1. You create a login named ajones by using the following Transact-SQL statement. CREATE LOGIN ajones WITH PASSWORD = 'SQLServer$1' The new employee reports that when he logs in, he receives the following error message: "Login failed. The user is not associated with a trusted SQL Server connection." You need to resolve the error and allow the new employee to gain access to SQL1. What should you do?
[A] Ensure that the login name is created with square brackets ([]).
[B] Give the login access to a specific database by using the CREATE USER Transact-SQL statement.
[C] Change the SQL Server security mode from Windows Authentication mode to SQL Server and Windows Authentication mode.
[D] Change the SQL Server security mode from SQL Server and Windows Authentication mode to Windows Authentication mode.
Sample Q7
Application developers in your company create an assembly that contains a CLR function. This CLR function reads data from a spreadsheet, performs some calculations, and returns the data to a SQL Server 2005 computer. You need to register the assembly with SQL Server 2005 by using the CREATE ASSEMBLY statement and the least privileged security permission set.
[A] EXTERNAL_ACCESS
[B] Default
[C] SAFE
[D] UNSAFE
Sample Q8
Your company uses a SQL Server 2005 database. Customer data from your trading partners is imported into the Customers table every night. You need to ensure that the customer record is updated if it already exists in the Customers table. If the record does not exist, the data needs to be inserted into the Customers table. What should you do?
[A] Create a FOR trigger.
[B] Create an AFTER trigger.
[C] Create a DDL trigger.
[D] Create an INSTEAD OF trigger.
Sample Q9
You manage a SQL Server 2005 database that contains a table with 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_index_usage_stats
Sample Q10
You discover that the msdb database on a SQL Server 2005 computer is corrupt and must be restored. Databases are backed up daily. The database backup files are written to a network share, but the file names do not clearly indicate which databases are in each file. You need to
locate the correct backup file as quickly as possible. The first file in the list is named DB_Backup.bak. Which Transact-SQL statement should you use?
[A] RESTORE LABELONLYFROM DISK = N\\Server1\Backup\DB_Backup.bak
[B] RESTORE VERIFYONLYFROM DISK = N\\Server1\Backup\DB_Backup.bak
[C] RESTORE HEADERONLYFROM DISK = N\\Server1\Backup\DB_Backup.bak
[D] RESTORE DATABASE MSDBFROM DISK = N\\Server1\Backup\DB_Backup.bak
Answers
Q1 : D, E
Q2 : A
Q3 : B
Q4 : A, E
Q5 : B, D
Q6 : C
Q7 : A
Q8 : D
Q9 : D
Q10 : C