Mar 2016 updated: Examcollection Microsoft 70-432 testing engine 91-105

70-432 Royal Pack Testengine pdf

100% Actual & Verified — 100% PASS

Unlimited access to the world's largest Dumps library! Try it Free Today!

Get it now →

Product Description:
Exam Number/Code: 70-432
Exam name: TS:MS SQL Server@ 2008,Implementation and Maintenance
n questions with full explanations
Certification: Microsoft Certification
Last updated on Global synchronizing

Free Certification Real IT 70-432 Exam pdf Collection

Question No. 91

You administer a SQL Server 2008 instance. 

You join two tables on a column named CompanyName by using the following query: 

SELECT s.*,i.* 

FROM SensitiveTb1 AS s 

INNER JOIN Insensitive Tb1 AS iON i. CompanyName = s.CompanyName 

When you execute the query, the following error is returned: 

"Msg 468, Level 16, State 9, Line 17 

Cannot resolve the collation conflict between ,SQL_Latinl_General_CPl_CS_AS' and 'SQL_Latin1_General_CP1_CI_AS' in the equal to operation." 

You need to modify the ON clause of the query to successfully perform a case-sensitive join. 

What should you do? 

A. ON UPPER(i.CompanyName) = UPPER(s.CompanyName) 

B. ON LOWER(i.CompanyName) = LOWER(s.CompanyName) 

C. ON i.CompanyName = s.CompanyName COLLATE SQL_Latin1_General_CP1_CI_AS 

D. ON i.CompanyName = s.CompanyName COLLATE SQL_Latin1_General_CP1_CS_AS 

Answer: D


Question No. 92

You maintain a SQL Server 2008 instance. 

You have an existing database maintenance plan that performs the following tasks: 

-Rebuilds indexes. -Checks database integrity. -Writes a report to a text file. 

You need to ensure that an e-mail message is sent to the operators when the maintenance plan fails to execute successfully. 

What should you modify? 

A. The instance. 

B. The SQL Server service. 

C. The SQL Server Agent job. 

D. The database maintenance plan. 

Answer: C


Question No. 93

DRAG DROP 

You administer a Microsoft SQL Server 2008 R2 database instance that supports a sales management application. 

Occasionally, users of the application report that they receive a deadlock error. You plan to troubleshoot this error. 

You need to ensure that the instance is configured to capture troubleshooting information that will allow you to isolate the cause of the error. 

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.) 


Answer:


Note: 

* First set the appropriate flag. Then restart the service. 

* To view deadlock information, the Database Engine provides monitoring tools in the form of two trace flags, and the deadlock graph event in SQL Server Profiler. 

Trace Flag 1204 and Trace Flag 1222 

When deadlocks occur, trace flag 1204 and trace flag 1222 return information that is captured in the SQL Server error log. Trace flag 1204 reports deadlock information formatted by each node involved in the deadlock. Trace flag 1222 formats deadlock information, first by processes and then by resources. It is possible to enable both trace flags to obtain two representations of the same deadlock event. 


Question No. 94

You administer a SQL Server 2008 instance that contains a database named Ad venture Works. 

You are log shipping the Adventureworks database to a remote SQL Server 2008 instance. 

During the weekend, the primary SQL Server instance required a restart. After the weekend, you discover that log shipping has stopped working. 

You need to troubleshoot log shipping of the Adventureworks database. 

What should you do? 

A. Verify whether the SQL Server Agent is started on the primary server. 

B. Verify whether the AdventureWorks database uses the Simple recovery model. 

C. Verify whether the SQL Server Volume Shadow Copy Service (VSS) Writer is started on the primary server. 

D. Execute a DBCC CHECKDB statement on the AdventureWorks database by using the EXTENDED_LOGICAL_CHECKS option. 

Answer: A


Question No. 95

You administer a SQL Server 2008 instance. A database developer named User1 views the definitions of all database objects in a database to read data from all user-defined tables, views, and table-valued functions. You need to set the required permissions for User1. You also need to ensure that the same permissions can be granted to other developers by executing minimum number of Transact-SQL statements. Which Transact-SQL statements should you execute? 

A. GRANT VIEW ANY DEFINITION TO User1;EXEC sp_addrolemember 'db_datareader’, 'User1' 

B. CREATE ROLE Developers;GRANT CONTROL TO Developers;EXEC sp_addrolemember 'Developers', 'User1' 

C. CREATE ROLE Developers;GRANT VIEW DEFINITION TO Developers;GRANT SELECT TO Developers;EXEC sp_addrolemember 'Developers'. 'User1' 

D. CREATE ROLE Developers;EXEC sp_addrolemember 'sp_dbdatareader'. 'Developers'EXEC sp_addrolemember 'sp_dbddladmin\ 'Developers'EXEC sp_addrolemember 'Developers', 'User1' 

Answer: C


Question No. 96

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

Minimally logged operations are performed on the Finance database. 

You need to verify that the database can be restored to a specific point in time. 

What should you do? 

A. Verify that the database uses the full recovery model. 

B. Verify that the database uses the simple recovery model. 

C. Verify that the database uses the bulk-logged recovery model. 

D. Verify that the database uses the checksum page verify option. 

Answer: B


Question No. 97

You are mastering the company database called Testing; you should back up the Testing database fully. You do transaction log once every two hours from 08:00 to 17:00.You should run database snapshots for the Testing database. You should develop database snapshot every day at 07:00. At 8:30 the user removes the whole information on the Testing database which was entered intothe CurrentSpeaker table yesterday. Deletion is the first step for the database. It is now 11:45 and many other changes have happened in the database. You should find solution to solve this problem .Which is the correct answer? 

A. You should utilize the SELECT subquery in the INSERT statement to move the deleted rows from this morning??s database snapshot to the CurrentSpeaker table. 

B. You should utilize the Delete subquery in the INSERT statement to transfer the deleted rows from yesterday mornings database snapshot to the CurrentSpeaker 

C. You should reserve last nights Full Backup and all transaction log backups until the 10:00 backup. Use the STOP AT statement on the some restores to prevent 

D. You should reserve last nights Full Backup and all transaction log backups until the 10:00 backup. Utilize the STOP AT statement on the last restore to prevent 

Answer: A


Question No. 98

You administer a SQL Server 2008 instance. You plan to install a SQL Server Analysis Services (SSAS) instance. The SSAS instance will be accessed by an Internet Information Services application by using anonymous access. You need to ensure that the application can access the SSAS instance. What should you do? 

A. Set the Security\RequireClientAuthentication server configuration to True. 

B. Set the Security\RequireClientAuthentication server configuration to False. 

C. Add the Kerberos Security Support Provider Interface (SSPI) provider to the Security\SecurityPackageList server configuration. 

D. Add the NTLM Security Support Provider Interface (SSPI) provider to the Security\SecurityPackageList server configuration. 

Answer: B


Question No. 99

You administer a SQL Server 2008 instance. A payroll application is used to store sensitive data in a database named PayrollDB. 

You plan to implement the following security measure: 

-All the data files, log files, and backup files of the database are automatically encrypted. 

You need to devise a method to encrypt data. What should you include in your solution? 

A. Use a certificate to protect the data encryption key. Export the certificate to a file. 

B. Use cell-level encryption to protect sensitive data. 

C. Use a symmetric key provisioned through Extensible Key Management (EKM). 

D. Use Transparent Data Encryption (TDE) for the PayrollDB database. 

Answer:


Question No. 100

You administer a SQL Server 2008 instance. 

The security policy permits members of a Windows group named CONTOSO\Sales to establish new connections to the SQL Server instance only during business horns between 07:00 horns and 19:00 hours. Other users may connect to the SQL Server instance any time. 

You write the following Transact-SQL statements. 


You need to implement the company security policy that is stored in the RestrictedLogonHours table. 

What should you do? 

A. Create a logon trigger that denies the connection to the CONTOSO\Sales group during non-business hours. 

B. Create a SQL Server Agent job that causes the SQL Server Windows service to pause during non-business hours. 

C. Create a SQL Server Agent job that periodically looks for and kills connections made by the CONTOSO\Sales group during non-business hours. 

D. Create a policy that uses a condition based on the Server Audit facet. Use the policy to deny the connection to the CONTOSO\Sales group during non-business hours. 

Answer: A


Question No. 101

You administer a Microsoft SQL Server 2008 R2 database that contains an OrderItems table. The table has the following definition: 


Currently, the table is not partitioned and contains no indexes. 

You need to partition the table by year. 

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 TABLEstatement to remove the COLLATEoption. 

D. Execute the DBCC CLEANTABLEcommand on the OrderItems table. 

E. . Create a new filegroup. 

. Create a new database file. 

. Use the ALTER PARTITION SCHEME statement along with the 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 the NEXT USED clause. 

. Use the 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 the 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 =’OrderItem3', 

@OptionName= ‘PartltionByYear’; 

@OptionValue= 'true’; 

Answer: H


Question No. 102

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. Drop the XML index on the table. Modify the primary key. Recreate the XML index. 

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

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

Answer: B


Question No. 103

You maintain a SQL Server 2008 Enterprise Edition instance that contains a database named Finance. You need to reduce the size of the full database backup files of the Finance database. Which Transact-SQL statement should you use? 

A. BACKUP DATABASE FINANCE TO DISK = .t:\backups\finance.bak*; 

B. BACKUP DATABASE FINANCE TO DISK = ,t:\backups\finance.bak' WITH COMPRESSION: 

C. BACKUP DATABASE FINANCE TO DISK = ,t:\backups\finance.bak' WITH DIFFERENTIAL: 

D. BACKUP DATABASE FINANCE TO DISK = 't:\backups\finance.bak' WITH COMPRESSION-DIFFERENTIAL; 

Answer: B


Question No. 104

You maintain a SQL Server 2008 instance that contains a database named OrderEntry. The OrderEntry database contains a table named OrderDelivery. 

You add a column named DeliveryPoint to the OrderDelivery table. The DeliveryPoint column is of the Geography data type. The OrderDelivery table does not contain indexes. 

You need to create a spatial index on the DeliveryPoint column. 

What should you do first? 

A. Define a primary key for the OrderDelivery table. 

B. Create a clustered index for the OrderDelivery table. 

C. Ensure that the DeliveryPoint column does not allow NULL values. 

D. Copy the OrderDelivery data to a temporary table and truncate the existing OrderDelivery table. 

Answer: A


Question No. 105

You administer a Microsoft SQL Server 2008 R2 database that contains an OrderItems table. The table has the following definition: Currently, the table is partitioned by year with each year in its own filegroup. 


You need to add a new partition for the upcoming year. 

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 TABLEstatement to remove the COLLATEoption. 

D. Execute the DBCC CLEANTABLEcommand on the OrderItems table. 

E. . Create a new filegroup. 

. Create a new database file. 

. Use the ALTER PARTITION SCHEME statement along with the 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 the NEXT USED clause. 

. Use the 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 the 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 =’OrderItem3', 

@OptionName= ‘PartltionByYear’; 

@OptionValue= 'true’; 

Answer: F