Your SQL Server 2005 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 and optimize maintenance. What should you do？（

第1题：

You work at the regional sales office. You are responsible for importing and exporting data in SQL Server 2005 databases. The main office asks you to send them a text file that contains updated contact information for the customers in your region. The database administrator in the main office asks that the data be sorted by the StateProvince，Surname， and FirstName columns.You need to satisfy these requirements by using the least amount of effort. What should you do？（）

• A、Specify StateProvince， Surname， and FirstName in the ORDER hint in the bcp out command.
• B、Create a format file for the export operation.
• C、Specify StateProvince， Surname， and FirstName in the ORDER BY clause in the bcp queryout command.
• D、Copy the data into a new table that has a clustered index on StateProvince，Surname，and FirstName. Export the data.

第2题：

You are planning to import customer data from the Customer table and the Country table in your SQL Server 2000 database into a SQL Server 2005 database. You want to ensure that during the import process，each value in the CountryCode column of the Customer table has a corresponding record in the CountryCode column in the Country table of the SQL Server 2005 database. You define a foreign key between these two tables. Thus，referential integrity will guarantee that the import routine fails if there are any records where a CountryCode value does not exist in the Country table but does exist in the Customer table. You need to ensure that the import process does not fail if records are encountered that do not exist in the Country table. What should you do？（）

• A、Drop the foreign key. Import the data by using the script. Re-create the foreign key.
• B、Create a CHECK constraint.
• C、Create a DML INSTEAD OF trigger that writes the failed records to a file or table.
• D、Create a DML AFTER trigger that writes the failed records to a file or table.

第3题：

You work in a company which is named Wiikigo Corp. The company uses SQL Server 2008. You are the administrator of the company database. Now you are in charge of a SQL Server 2008 instance. Now according to the company requirement, you are designing a consolidated repository of performance data. You must make sure that the four requirements below are met: the data collector is used to gather performance information; a single database stores performance information for all instances; performance information that is older than 15 days is deleted; reduce the administrative effort to manage performance to the least. So what action should you perform to achieve this goal? （） 

• A、You should create a SQL Agent job process on each instance to store and delete performance data in a single database for all instances. 
• B、You should configure a management data warehouse process on each instance, then use this process to store and delete performance data in a single database for all instances. 
• C、You should configure an automated server-side trace process on each instance, then use this process to store and delete performance data in a single database for all instances. 
• D、You should create and schedule a single Microsoft SQL Service Integration Services (SSIS) package process, then use this process to store and delete performance data in a single database for all instances. 

第4题：

Your network contains a single Active Directory domain. All servers on the network are members of the domain. You have a server named Server1 that runs Windows Server 2003 Service Pack 2 (SP2). Server1 has two NTFS partitions. You create and share a folder named Data in the root of a partition on Server1. You log on to your computer by using the domain Administrator account and discover that you cannot modify files in the Data share. You need to ensure that the Administrator can modify files in the Data share. The solution must use the minimum amount of permissions. What should you do? （）

• A、Modify the NTFS permissions on the Data folder.
• B、Modify the share permissions on the Data share.
• C、Add the domain administrator to the local Administrator’s group on Server1.
• D、Move the Data folder to a new file allocation table (FAT) partition. Share the folder by using the default permissions.

第5题：

You administer a SQL Server 2008 instance. You plan to deploy a new database to the instance. The database will be subject to frequent inserts and updates. The database will have multiple schemas. One of the schemas will contain a large amount of read-only reference data. You need to design the physical database structure for optimal backup performance. What should you do?（） 

• A、Create the database by using a single data file and a single log file.
• B、Create the database by using a single data file and multiple log files.
• C、Create the database by using a single log file and multiple filegroups.
• D、Create the database by using a single log file and a filegroup that has multiple data files.

第6题：

A support engineer reports that inserting new sales transactions in a SQL Server 2005 database results in an error. You investigate the error. You discover that in one of the databases， a developer has accidentally deleted some data in a table that is critical for transaction processing. The database uses the full recovery model. You need to restore the table. You need to achieve this goal without affecting the availability of other data in the database. What should you do？（）

• A、Back up the current transaction log. Restore the database with a different name and stop at the point just before the data loss. Copy the table back into the original database.
• B、Back up the current transaction log. Restore the database to the point just before the data loss.
• C、Restore the database from the existing backup files to a time just before the data loss.
• D、Restore the database to the point of the last full backup.

第7题：

 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.  Which permission set should you use? （）

• A、 Default 
• B、 SAFE 
• C、 EXTERNAL_ACCESS 
• D、 UNSAFE

第8题：

You design a Business Intelligence （BI） solution by using SQL Server 2008. The solution includes a SQL Server 2008 Analysis Services （SSAS） database. The database contains a data mining structure that uses a SQL Server 2008 table as a data source. A table named OrderDetails contains detailed information on product sales. The OrderDetails table includes a column named Markup. You build a data mining model by using the Microsoft Decision Trees algorithm. You classify Markup as discretized content.  The algorithm produces a large number of branches for Markup and results in low confidence ratings on predictable columns. You need to verify whether the Markup values include inaccurate data. What should you do？ （）

• A、 Modify the content type of Markup as Continuous.
• B、 Create a data mining dimension in the SSAS database from OrderDetails.
• C、 Create a data profile by using SQL Server 2008 Integration Services （SSIS）.
• D、 Create a cube in SSAS. Use OrderDetails as a measure group. Recreate the data mining structure and mining model from the cube data.

第9题：

You work in a company which uses SQL Server 2008. You are the administrator of the company database. Now you are in charge of a SQL Server 2008 instance. There is a n On-Line Analytical Processing （OLAP） database named in the instance. The database contains a dimension table named Clients. Ever hour backup of data of the Clients table is performed. But the Clinets table contains redundant data. You must keep the disk space used to store the Clients table.  In the options below， which compression technology should you use？（）

• A、You should use row compression 
• B、You should use page compression 
• C、You should use backup compression 
• D、You should use windows NTFS file system compression

第10题：

第11题：

第12题：

第13题：

You need to migrate the DeliveryVans， AirlinePreferences， and SeasonalContractors databases into the data center. What should you do？（）

• A、Install a separate instance of SQL Server 2005 for each of the databases on a single server.
• B、Create a SQL Server database for each of the databases in a single instance of SQL Server 2005.
• C、Import the data from each of the databases into a single database in an instance of SQL Server 2005.
• D、Install a default instance of SQL Server 2005 on three separate servers. Use one server for each of the databases.

第14题：

Your network contains a single Active Directory domain. All servers on the network are members of the domain. You have a server named Server1 that runs Windows Server 2003 Service Pack 2 (SP2). Server1 has two NTFS partitions. You create and share a folder named Data in the root of a partition on Server1. You log on to your computer by using the domain Administrator account and discover that you cannot modify files in the Data share. You need to ensure that the Administrator can modify files in the Data share. The solution must use the minimum amount of permissions. What should you do? （）

• A、Modify the NTFS permissions on the Data folder.
• B、Modify the share permissions on the Data share.
• C、Add the domain administrator to the local Administrator’s group on Server1.
• D、Move the Data folder to a new file allocation table (FAT) partition. Share the folder by using the default permissions.

第15题：

You are a database administrator for your company. The company uses a vendor company’s application that is based on a proprietary database. You query data from the application database and import the data into a SQL Server 2005 database. The application vendor provides you with a new OLE DB driver to be used when querying the application database. Company policy prohibits connections between the SQL Server database and the application database， except when querying this data. You need to provide a way to query data from the application database. What should you？（）

• A、Configure a Linked Server connection to the application database.
• B、Write a query that uses an OPENDATASOURCE command.
• C、Write a query that uses an OPENQUERY command.
• D、Configure a Remote Server connection to the application database.

第16题：

You are a professional level SQL Server 2005 database administrator in an international corporation named Wiikigo. You are experienced in implementing high-availability solutions， monitoring the database server， and design deployment. In the company， you major job is the deployment， maintenance and support of Microsoft SQL Server 2005. You work as a database administrator for your company. A SQL Server 2005 application is utilized by the company. A query is executed by the application to return three columns of the nvarchar data type and one column of the XML data type. A WHERE clause is utilized by the query， and all three of the nvarchar columns are contained by the WHERE clause. 5 million rows are included by the table， and 50，000 rows are returned by the query. The performance of the query should be enhanced. Which action should be performed to achieve the goal？（）

• A、A primary XML index should be created on the XML column.
• B、Statistics should be created on the nvarchar columns.
• C、A view should be created， and only the four columns that the query utilizes are contained by a view.
• D、A composite index should be created on the three nvarchar columns， and INCLUDE should be specified for the XML column.

第17题：

You work in a company which uses SQL Server 2008. You are the administrator of the company database. Now you are in charge of a SQL Server 2008 instance. There is a management data warehouse that uses the data collector to collect performance data. You intend to maintain the management data. You want to implement a data collection process. The process can gather and upload data in the management data warehouse on different schedules.  What process should you implement？（） 

• A、You should create a cached data collection
• B、You should create an on-demand non-cached data collection 
• C、You should create a scheduled non-cached data collection 
• D、You should create two different SQL Agent jobs that are scheduled stimultaneously

第18题：

Your company has a network that has 100 servers. A server named Server1 is configured as a file server. Server1 is connected to a SAN and has 15 logical drives. You want to automatically run a data archiving script if the free space on any of the logical drives is below 30 percent. You need to automate the script execution. You create a new Data Collector Set. What should you do next？（）

• A、 Add the Event trace data collector
• B、 Add the Performance counter alert
• C、 Add the Performance counter data collector
• D、 Add the System configuration data collector

第19题：

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 INSTEAD OF trigger. 
• C、 Create an AFTER trigger. 
• D、 Create a DDL trigger.

第20题：

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 usage stats
• D、sys.dm db index physical stats

第21题：

第22题：

第23题：