Latest MCTS 70-432 Dumps Real Exam Download 21-30

Ensurepass

QUESTION 21
Note:
This question is part of a series of questions that use the same or similar answer choices. An answer choice may be correct for more than one question in the series. Each question is independent of the other questions in this series. Information and details provided in a question apply only to that question.

You administer a Microsoft SQL Server 2008 R2 database that contains an OrderItems table.

The table has the following definition:

CREATE TABLE [OrderItems]

(OrderID INT NOT NULL,

OrderDate DATETIME NOT NULL,

OrderLine INT NOT NULL,

ProductID INT NOT NULL,

Quantity INT NOT NULL,

PriceEach MONEY NOT NULL,

);

Currently, the table is partitioned by year with each year in its own filegroup.

You need to move the data from the oldest year into a new table in a different Schema to archive the data.

What should you do?

 

A. Remove the clustered index from the table.

B.            Use the ALTER PARTITION FUNCTION … SPLIT RANGE statement.

C.            Use the ALTER TABLE statement to remove the COLLATE option.

D.            Execute the DBCC CLEANTABLE command on the OrderItems table.

E. Create a new Filegroup. Create a new database file. Use the ALTER PARTITION SCHEME statement along with NEXT USED clause. Use ALTER INDEX REORGANIZE statement.

F.             Create a new Filegroup. Create a new database file. Use the ALTER PARTITION SCHEME statement along with NEXT USED clause Use ALTER PARTITION FUNCTION statement along with the SPLIT RANGE clause

G. Create a new table. Use the ALTER TABLE statement along with the SWITCH PARTITION clause. Use ALTER PARTITION FUNCTION statement along with the MERGE RANGE clause.

H. Create a new partition function. Create a new partition scheme. Add a clustered index to place the data onto the partition scheme.

I.             Run the following statement: CREATE PARTITION SCHEME SEC_FG AS PARTITION FUNC_FG ALL TO ( SECONDARY );

J.             Run the following statement: EXECUTE sp_tableoption @TableNamePattern =’OrderItems’, @OptionName= ‘PartitionByYear’, @OptionValue= ‘true’;.

 

Correct Answer: G

Explanation/Reference:

Feedback by Rafe:

Create an empty historical table on the same filegroup where the obsolete data resides, perhaps called

Filegroup_1989.

Switch data from the fact table to the historical table.

Merge the first partition thereby removing the boundary point for 1989.

Move data from the historical table to a different database. Then drop the historical table.

http://sqlserverpedia.com/wiki/Switching_Partitions_-_Example

 

 

QUESTION 22

You administer a Microsoft SQL Server 2008 R2 database instance. The instance has a Products table that is currently in the DevUser schema. The Appusers database is assigned Read and Update permissions to the table.

The DevUser schema is owned by a database login that has been renamed.

You need to move the Products table from the DevUser schema to the dbo schema.

Which two actions should you perform in sequence? (To answer, move the appropriate actions from the list of actions to the answer area and arrange them in the correct order.)

Select and Place: 

clip_image002

Correct Answer: clip_image004

Explanation/Reference:

Feedback:

The way I remember this is as follows:

ALTER SCHEMA [TO NEW <SCHEMA> TO BE MODIFIED] TRANSFER [FROM OLD <SCHEMA.

TABLE> to be moved into the new schema] within the same database.

So following my above formula, the correct answer would be:

ALTER SCHEMA dbo TRANSFER DevUser.Products

To change the schema of a table or view by using SQL Server Management Studio, in Object Explorer, right-click the table or view and then click Design. Press F4 to open the Properties window. In the Schema box, select a new schema from the drop down arrow.

 

 

QUESTION 23

You administer a SQL Server 2008 instance.

You need to identify the network protocol used by the current connection.

What should you do?

 

A. Use the T-SQL template in Profiler.

B. Use a Dynamic Management View.

C. Set a trace flag.

D. View the SQL error log.

 

Correct Answer: B

Explanation/Reference:

To retrieve your current connection properties you’d run the DMV:

select * from sys.dm_exec_connectionswhere session_id = @@SPID

The field values depend on the protocol used to connect (shared memory, named pipes or tcp) but all contain information about authentication method used, protocol and client net address.

 

 

QUESTION 24

You install a 64-bit version of a SQL Server 2008 instance by using the default setup and configuration

settings. You receive the following error message:

“Your SQL Server installation completed with failures.”

You close the error window before noting the location where additional information about the error is

recorded. You need to located the additional information.

Where would you search?

 

A. The Event Viewer System log file

B. The C:Program FilesMicrosoft SQL ServerMSSQLLOG folder

C. The C:Program FilesMicrosoft SQL Server100Setup BootstrapLog folder

D. The Event Viewer Application log file

 

Correct Answer: C

Explanation/Reference:

Look at the Configuration log in C:Program FilesMicrosoft SQL Server100Setup BootstrapLog[date and time of installation]Detail.txt

 

 

QUESTION 25

You administer a Microsoft SQL Server 2008 R2 database that hosts an order-processing application.

You need to ensure that the database allows full-text searches on the Customers table. You also need to ensure that the full-text index is ready for use by the users.

You execute a Transact-SQL statement to create the full-text index on the Customers table by using the

CHANGE_TRACKING OFF and NO POPULATION clauses in the CREATE FULLTEXT INDEX statement. You execute an ALTER FULLTEXT INDEX.

Which command or commands should you use next?

 

A. ALTER INDEX Customers REORGANIZE

B. ALTER FULLTEXT INDEX on Customers START FULL POPULATION

C. DROP FULLTEXT INDEX

D. ALTER FULLTEXT INDEX on Customers ENABLE

 

Correct Answer: B

Explanation/Reference:

 

 

QUESTION 26

You administer a SQL Server 2008 instance.

You configure a single Database Mail profile that is used to send reports to all the employees of your organization. The sp_send_dbmail stored procedure is used to send a large volume of reports during business hours.

The reports to the manager take a long time to be delivered.

You need to ensure that the reports to the manager are sent as quickly as possible.

What should you do?

 

A. Modify the SMTP relay settings.

B. Modify the parameters of the email that contains the reports.

C. Create a separate Database Mail profile for the reports.

D. Configure the security settings of the Database Mail system.

 

Correct Answer: C

Explanation/Reference:

A Database Mail profile is a collection of Database Mail accounts.

Profiles improve reliability in cases where an e-mail server becomes unreachable, by providing alternative

Database Mail accounts. At least one Database Mail account is required.

 

 

QUESTION 27

You administer a SQL Server 2008 instance.

You need to configure the instance to use a single thread for queries that have an estimated execution cost of less than 3.

Which sp_configure configuration option should you set?

 

A.            cost threshold for parallelism

B.            precompute rank

C.            max worker threads

D.            priority boost

E.            query governor cost limit

 

Correct Answer: A

Explanation/Reference:

 

 

QUESTION 28

You maintain a SQL Server 2008 instance that contains a database named AdventureWorks.

The AdventureWorks database contains a table named OrderSpecs. The OrderSpecs table has a clustered primary key named OrderID on the OrderID column. The OrderSpecs table has a single XML column named ProductSpecs that stores XML data, and there is an XML index on the same XML column.

You add a new column named ProductID to the OrderSpecs table.

You need to include ProductId in the primary key.

What should you do?

 

A.            Alter the XML index and set the ALLOW_ROW_LOCKS = OFF option. Alter the primary key and set the ALLOW_ROW_LOCKS = ON option.

B. Disable the XML index on the ProductSpecs column. Modify the primary key. Enable the XML index on the ProductSpecs column.

C. Drop the XML index on the table. Modify the primary key. Recreate the XML index.

D. Move the XML data to a temporary. Create the XML data from the original table by setting the ProductsSpecs column to NULL. Modify the primary key. Repopulate the ProductsSpecs column.

 

Correct Answer: C

Explanation/Reference:

 

 

QUESTION 29

You maintain a SQL Server 2008 instance that contains a database named Sales. The Sales database contains a table named CompanyName.

The current database collation is SQL_Latin1_General_Cp1_CI_AS.

You need to ensure that the indexes which include the CompanyName column are case-sensitive. You also need to ensure that other case-insensitive queries are unaffected.

What should you do?

 

A. Drop all indexes on the Customer table that include the CompanyName column in the index key. Modify the column to specify the SQL_Latin1_General_Cp1_CS_AS collation. Recreate the CompanyName indexes on the Customer table.

B. Use the UPPER function in the filter criteria on all queries that filter on the CompanyName column.

C. Use the CAST function in the filter criteria on all queries that filter on the CompanyName column.

D. Drop all indexes on the Sales database. Modify the column to specify the SQL_Latin1_General_Cp1_CS_AS collation. Rebuild all the indexes.

 

Correct Answer: A

Explanation/Reference:

 

 

QUESTION 30

You maintain a SQL Server 2008 instance that contains a database named AdventureWorks.

You plan to use data compression to conserve disk space. You compress a table named VeryLarge that has a clustered index named Index1 and a non-clustered index named Index2. Both indexes are partitioned.

You execute the following Transact_SQL statement.

ALTER INDEX Index2 ON VeryLarge REBUILD WITH (DATA_COMPRESSION = ROW);

You need to ensure that all the indexes of the VeryLarge table are compressed by using row-level compression. You need to accomplish this goal by using minimum resources.

Which Transact-SQL statement should you execute?

 

A. ALTER INDEX Index1 ON VeryLarge REBUILD WITH (DATA_COMPRESSION = ROW);

B. ALTER INDEX ALL ON VeryLarge REBUILD PARTITION=ALL WITH (DATA_COMPRESSION = ROW);

C. ALTER INDEX Index1 ON VeryLarge REORGANIZE WITH (LOB_COMPACTION = ON);

D. ALTER INDEX ALL ON VeryLarge REBUILD WITH (DATA_COMPRESSION = ROW);

 

Correct Answer: A

Explanation/Reference:

 

 

Download Latest MCTS 70-432 Real Free Tests , help you to pass exam 100%.