最新的MCP認證 70-464考試題庫

問題1

You have a SQL Azure database.
You need to identify which keyword must be used to create a view that will be indexed.
Which keyword should you identify?

A. SCHEMABINDING

B. DEFAULT

C. VIEW_METADATA

D. DISTINCT

正確答案: A

說明：（僅 VCESoft 成員可見）

問題2

You plan to create a new column in a table. The column must meet the following requirements:
* Be able to store images that are larger than 1 MB each.
* Be able to access the images from Microsoft .NET Framework applications.
You need to recommend which data type must be used in the column.
Which data type should you recommend?
More than one answer choice may achieve the goal. Select the BEST answer.

A. nvarchar

B. FileStream

C. image

D. varbinary

正確答案: B

問題3

You have a SQL Server 2012 instance.
You plan to create an application that uses spatial data.
You need to create an object that will support the representation of the surface area of all the oceans.
Which code segment should you use?

A. Option B

B. Option D

C. Option C

D. Option A

正確答案: D

問題4

While testing the CategoryFromType function, you discover that the function is returning 'Other'.
You need to update CategoryFromType to return the category name.
Which line of code should you modify in CategoryFromType.sql?

A. 04

B. 12

C. 14

D. 05

正確答案: D

問題5

You need to provide referential integrity between the Offices table and Employees table.
Which code segment or segments should you add at line 27 of Tables.sql? (Each correct answer presents part of the solution. Choose all that apply.)

A. Option B

B. Option D

C. Option C

D. Option A

正確答案: B,C

說明：（僅 VCESoft 成員可見）

問題6

You need to create a script that automates the export of the XML data. The script must meet the integration requirements.
What should you include in the script?

A. The CREATE CREDENTIAL command and the sp_reassign_proxy, sp_add_job, sp_add_jobstep, and sp_grant_login_to_proxy system stored procedures.

B. The CREATE SERVER ROLE command and the sp_reassign_proxy, sp_add_job, sp_add_jobstep, and sp_grant_login_to_proxy system stored procedures.

C. The CREATE CREDENTIAL command and the sp_add_proxy, sp_add_job, sp_add_jobstep, and sp_grant_proxy_to_subsystem system stored procedures.

D. The CREATE SERVER ROLE command and the sp_add_proxy, sp_add_job, sp_add_jobstep, and sp_grant_proxy_to_subsystem system stored procedures.

正確答案: C

問題7

You are evaluating the index design.
You need to recommend a change to Indexes.sql that minimizes the amount of time it takes for usp_SelectEmployessByName to execute.
Which line of code should you use to replace line 08 of Indexes.sql?

A. (LastName) INCLUDE (FirstName);

B. (LastName, EmployeeID);

C. (FirstName, LastName);

D. (EmployeeID) INCLUDE (LastName);

正確答案: D

說明：（僅 VCESoft 成員可見）

問題8

You have a database that contains three tables. The tables are configured as shown in the following table.

You have the following query:

The execution plan for the query is shown in the exhibit. (Click the Exhibit button.)

You need to create one index to minimize the amount of time it takes to execute the query.
What should you do?
To answer, drag the appropriate columns to the correct locations in the answer area. (Answer choices may be used once, more than once, or not at all.)

正確答案:

Explanation:
Note:
Covering index: A type of index that includes all the columns that are needed to process a particular query. For example, your query might retrieve the FirstName and LastName columns from a table, based on a value in the ContactID column. You can create a covering index that includes all three columns.

問題9

You need to optimize the index structure that is used by the tables that support the fraud detection services.
What should you do?

A. Add a hashed clustered index on POSTransactionId and CreateDate.
The fraud detection service will need to meet the following requirement (among others):
* Detect micropayments that are flagged with a StatusId value that is greater than 3 and that occurred within the last minute.

B. Add a hashed nonclustered index to CreateDate.

C. Add a not hash nonclustered index to CreateDate.

D. Add a not hash clustered index on POSTransactionId and CreateDate.

正確答案: B

問題10

You have a table named ORDERS that contains 10,514,003 Orders. The ORDERS table has an IDENTITY(1,1) column named ORDER_ID. ORDER_ID is the UNIQUE CLUSTERED INDEX and PRIMARY KEY for the table. The first ORDER_ID is 1. There are no missing ORDER_IDs in the set.
Based on table usage patterns, you decide to use partitioning on this table based off of the ORDER_ID column.
You need to create the following partitions:

Which Transact-SQL statement should you run?

A. CREATE PARTITION FUNCTION pfOrderIDRange (int)
AS RANGE LEFT FOR VALUES (0,7500000,10000000)

B. CREATE PARTITION FUNCTION pfOrderIDRange (int)
AS RANGE RIGHT FOR VALUES (0,7500000,10000000)

C. CREATE PARTITION FUNCTION pfOrderIDRange (int)
AS RANGE RIGHT FOR VALUES (7500000,10000000)

D. CREATE PARTITION FUNCTION pfOrderIDRange (int)
AS RANGE LEFT FOR VALUES (7500000,10000000)

正確答案: D

說明：（僅 VCESoft 成員可見）

問題11

You are designing a new database table that will be used for reporting.
You define the table by using the following statement.

You need to store the data in the table by using the least amount of storage space possible.
Which storage option should you use?

A. a clustered columnstore index

B. a clustered index

C. In-Memory OLTP

D. a nonclustered index

正確答案: A

說明：（僅 VCESoft 成員可見）

問題12

You use SQL Server 2014. The physical server is a dedicated database server that has 120GB of RAM available. There is approximately 50GB of storage space available on a slow local disk.
You create a new stored procedure. You decide you need to temporarily hold approximately 300,000 rows from two tables, from which you will compute two complex business scores.
The stored procedure will use temporary storage defined as follows:

The code will make several passes through the data, applying complex calculations before writing the data to a permanent disk-based table in the same database from which it reads the data.
For this stored procedure, you need to deal with temporary data in the most efficient way to minimize physical disk pressure.
What should you do? More than one answer choice may achieve the goal. Select the BEST answer.

A. Option B

B. Option D

C. Option C

D. Option A

正確答案: D

說明：（僅 VCESoft 成員可見）

問題13

You need to convert the functionality of Dynamic.sql to use a stored procedure.
Which Transact SQL statement should you add to the stored procedure contain?

A. CREATE PROC Production.ProductsAfterDate(
@sqlstring AS nvarchar(1000),
OUTPUT @ProductID AS varchar(11),
OUTPUT @CreationDate AS date)
AS
...

B. CREATE PROC Production.ProductsAfterDate (@ProductID
AS varchar(11), @CreationDate AS date)
AS
...

C. CREATE PROC Production.ProductsAfterDate(
@sqlstring AS nvarchar(1000),
@ProductID AS varchar(11),
@CreationDate AS date)
AS
...

D. CREATE PROC Production.ProductsAfterDate(
@sqlstring AS nvarchar(1000))
AS
...

正確答案: A

說明：（僅 VCESoft 成員可見）

問題14

You discover that usp.SelectSpeakersByName executes slowly if usp_UpdateSpeakerName executes simultaneously.
You need to minimize the execution time of usp.SelectSpeakersByName. The solution must not affect the performance of the other stored procedures.
What should you update?

A. Usp_UpdateSpeakerName to use snapshot isolation

B. Usp_SelectSpeakersByName to use snapshot isolation

C. Usp_UpdateSpeakerName to use the NOLOCK query hint

D. Usp_SelectSpeakersByName to use the NOLOCK query hint

正確答案: D

說明：（僅 VCESoft 成員可見）

問題15

The database contains a disk-based table named ContentTable that has 1 million rows and a column named Fax. Fax allows null values.
You need to update Fax to meet the following requirements:
Prevent null values from being used.
Always use an empty string instead of a null value.
Which statement or statements should you execute? (Each correct answer presents part of the solution. Choose all that apply.)

A. Option B

B. Option D

C. Option C

D. Option A

E. Option E

正確答案: A,D,E

說明：（僅 VCESoft 成員可見）

最新的Microsoft Developing Microsoft SQL Server Databases - 70-464免費考試真題

最新更新

站內鏈接

聯系我們