默认题库 - MCTS(70-431)

单选题

你发现SQL Server 2005数据库中sales表的所有索引的碎片都很严重。现在，你要将sales表全部索引的碎片减到最小，同时却不影响任何用户使用sales表。该怎样做？（）

A. 对包含销售表的磁盘进行碎片整理
B. 执行以下语句：USE master； GO DBCC CLEAN TABLE （‘DB1’.’sales’）；GO
C. 执行以下语句：USE DB1；GO ALTER INDEX ALL ON sales REORGANIZE WITH LOB_COMPACTION=ON）；GO
D. 执行以下语句：USE DB1；GO ALTER INDEX ALL ON sales REBUILD；GO

查看答案

单选题

你正在SQL SERVER 2005 服务器上一个名为DB1的数据库上进行着工作。你发现由于对DB1中Table1表的扫描导致了查询执行缓慢。已知，Table是一个使用频繁而且非常巨大的表，同时你又确定了查询中包含如下的语句：SELECT col1，col2 FROM table1 WHERE col3 = 。在表保持对所有用户有效的情况下，为了最大化的提高查询性能，你应该怎么做？（）

A. 更新DB1数据库中表Table1的所有统计信息
B. 在DB1数据库中，使用CREATE STATISTICS 语句为表Table1的列col3创建缺少的统计信息
C. 设置优先值提升选项的值为1
D. 执行如下语句：USE DB1 GO CREATE INDEX index1 ON table1（col3） INCLUDE（col1， col2） WITH （ONLINE = ON） GO
E. 执行如下语句 USE DB1GOCREATE INDEX index1 ON table1（col3， col2，col1）

查看答案

单选题

你正在使用一个连接到SQL Server 2005数据库DB1的订单管理应用程序App1。因为一些操作的执行时间超出三秒，用户反映使用App1时性能缓慢。你想通过使用SQL Server Profiler来跟踪监视此应用程序。现在，你只需要捕获App1在DB1中响应时间超过两秒的哪些操作。你该如何做？（）

A. 添加ApplicationName列，并创建过滤器：Duration大于等于2000；ApplicationName类似于Appl DatabaseName类似于DB1
B. 添加ApplicationName列，并创建过滤器：Duration大于等于2，ApplicaitionName类似于App1，DatabaseName类似于DB1
C. 添加CPU列，并创建过滤器：CPU大于等于2000，TextData类似于Appl，DatabaseName类似于DB1
D. 添加ApplicationName列和CPU列，并创建过滤器：CPU大于等于2；ApplicaitionName类似于App1；TextData类似于DB1

查看答案

单选题

You are responsible for maintaining a SQL Server 2005 database. Business analysts in the company routinely use a view named v CustomerSales to join the Customers and Sales tables in the database. They use the view to aggregate total sales by customer by month. You need to increase the performance of the view. What should you do？（）

A. Update the view to use an outer join between the Customers and Sales tables.
B. Create a clustered index on the v CustomerSales view.
Create two separate views that do not contain any joinsone view named Customers for the Customers table and another one named v Sales for the Sales table.
D. Create a stored procedure for the business analysts that uses the v CustomerSales view.

查看答案

多选题

You work for a bank that uses a SQL Server 2005 database to store line items from customer banking transactions. The bank 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？（）

A. Place a nonclustered index on the date column.
B. Add a unique clustered index on the date column.
C. Map each partition to a filegroup， with each filegroup accessing a different physical drive.
D. Create a partitioning scheme that partitions the data by date.

查看答案

多选题

TestKing.com uses SQL Server 2005. A user reports that an order processing application stopped responding in the middle of an order transaction. The users SQL Server session ID is 54. You need to find out if session 54 is blocked by another connection. If it is,you need to identify the blocking session ID. What are two possible ways to achieve this goal？（）

A. In SQL Server Management Studio， open the Activity Monitor. Open the Process Info page. View the blockedBy column for session 54.
B. In SQL Server Management Studio，open the Activity Monitor. Open the Locks by Process page. View the Request Mode column for session 54.
C. In SQL Server Management Studio， open a new query window and execute the following statement.SELECT * FROM sys.dm exec requests WHERE session id = 54View the blocking session id column.
D. In SQL Server Management Studio， open a new query window and execute the following statement.SELECT * FROM sys.dm exec sessionsWHERE session id = 54View the status column.

查看答案

单选题

You manage a database named DB1， which is located on a SQL Server 2005 computer. You receive a warning that the drive on which the DB1 log file is located is near capacity.Although the transaction log is backed up every five minutes， you observe that it is steadily growing. You think that an uncommitted transaction might be the cause and you want to investigate. You need to identify both the server process ID and the start time of the oldest active transaction in DB1. What should you do？（）

A. Connect to the DB1 database. Execute DBCC OPENTRAView the SPID and Starttime rows.
B. Connect to the master database. Execute DBCC OPENTRAView the SPID andStart time rows.
C. In SQL Server Management Studio,open the Activity Monitor. Select the Process Info page and apply the following filter settings, Database=DB1 Open Transactions=YesView the Process ID and Last Batch columns.
D. Open a query window. Connect to the master database.Execute the following statement.SELECT TOP 1 spid.last batch FROM sys.sysprocesses WHERE dbid = db id（’DB1’）AND open tran>0 ORDER BY last batch

查看答案

单选题

A full backup of your database named DB1 is created automatically at midnight every day.Differential backups of DB1 occur twice each day at 10：00 and at 16：00. A database snapshot is created every day at noon. A developer reports that he accidentally dropped the Pricelist table in DB1 at 12：30. The last update to Pricelist occurred one week ago. You need to recover the Pricelist table. You want to achieve this goal by using the minimum amount of administrative effort. You must also minimize the amount of data that is lost. What should you do？（）

A. Restore the most recent backup into a new database named DB1bak. Apply the most recent differential backup. Copy the Pricelist table from DB1bak to DB1.
B. Delete all database snapshots except the most recent one. Restore DB1 from the most recent database snapshot.
C. Recover DB1 from the most recent backup. Apply the most recent differential backup.
D. Copy the Pricelist table from the most recent database snapshot into DB1.

查看答案

多选题

You are implementing transaction log shipping for a database named DB1 from a server named SQL1 to a server named SQL2. Because DB1 is 100 GB in size， it is too big to transfer over the network in a reasonable amount of time. You need to minimize the impact on the network while you initialize the secondary database. Which two actions should you perform？（）

A. Specify the simple recovery model for DB1.
B. Specify either the full or the bulk-logged recovery model for DB1.
C. Perform a complete backup of DB1 to portable media. Restore the secondary database from that backup； specify the RECOVERY option.
D. Perform a complete backup of DB1 to portable media. Restore the secondary database from that backup； specify the STANDBY option.
E. Before you activate transaction log shipping to the secondary database， execute the following statement on the primary server.BACKUP LOG DB1 WITH TRUNCATE ONLY

查看答案

单选题

You are creating an online catalog application that will display product information on the company Web site. The product data is stored in a SQL Server 2005 database. The data is stored as relational data but must be passed to the application as an XML document by using FOR XML. You test your application and notice that not all of the items matching your query appear in the XML document. Only those products that have values for all elements in the schema appear. You need to modify your Transact-SQL statement so that all products matching your query appear in the XML document. What should you do？（）

Add an XML index to the table that contains the product data.
B. Add the XSINIL argument to the ELEMENTS directive in the query.
C. Add a HAVING clause to the query.
D. Add the replace value of clause to the query.

查看答案