NEW QUESTION 1

You use SQL Server 2014 to maintain the data used by applications at your company. You need to run two separate SQL statements.
You must guarantee that the following three things happen:
1. Either BOTH statements succeed or BOTH statements fail as a batch.
2. If an error occurs on the first statement, SQL should not attempt to run the second statement.
3. Error information should be returned to the client. What should you do?

• A. Option A
• B. Option B
• C. Option C
• D. Option D

Answer: A

Explanation:
* SET XACT_ABORT
When SET XACT_ABORT is ON, if a Transact-SQL statement raises a run-time error, the entire transaction is terminated and rolled back.
When SET XACT_ABORT is OFF, in some cases only the Transact-SQL statement that raised the error is rolled back and the transaction continues processing.

NEW QUESTION 2

You need to implement a solution that solves the performance issues of usp_GetOrdersAndItems.
Which statements should you execute?

• A. CREATE INDEX IX_Orders_Active ON Orders(ShipDate, DeliveryDate, Amount)
• B. CREATE INDEX IX_Orders_Active ON Orders(DeliveryDate) INCLUDE(Amount)WHERE ShipDate IS NULL
• C. CREATE INDEX IX_Orders_Active ON Orders(DeliveryDate, Amount) WHERE ShipDate IS NULL
• D. CREATE INDEX IX_Orders_Active ON Orders(ShipDate, DeliveryDate) INCLUDE( Amount)

Answer: B

NEW QUESTION 3

You administer an instance of SQL Server 2014.
You are tasked with tuning a common set of queries. You have the results of several test executions, along with query plans. The schema and the data for all database object(s) used remain unchanged between executions. The QueryTime column is defined as a computed column that uses the GETDATE() system function. The query plans and results are shown below:


You need to make an initial diagnosis of the situation, based solely on this input
Which two statements can you make about the performance characteristics of this query? Each correct answer presents a complete solution. Choose two.

• A. The queries would perform better if the index named AccountNumber included the Name and QueryTime column.
• B. The queries would perform worse if the index named AccountNumber included the NameColumn.
• C. The queries would perform better if the index named AccountNumber included the Name column.
• D. The object Account is a table, with an index having a leading column of AccountNumber and a Clustered Index named PKAccount.
• E. The object Account is an indexed view, with an index having a leading column of AccountNumber and a Clustered Index named PKAccount.
• F. The object Account is a view, joining the Account-AccountNumber and Account.PKAccount objects together.

Answer: BD

NEW QUESTION 4

You are testing disaster recovery procedures.
When you attempt to restore ProductsDB to another server, you receive the following error message: "Msg 33111, Level 16, State 3, Line 5
Cannot find server certificate with thumbprint ' 0x9D876A3468B911ElBA4CFCBF4724019B Msg 3013, Level 16, State 1, Line 5
RESTORE DATABASE is terminating abnormally."
You need to ensure that you can restore ProductsDB to another server. Which code segment should you execute on the other server?

• A. Option A
• B. Option B
• C. Option C
• D. Option D

Answer: D

NEW QUESTION 5

You are planning the ManufacturingSteps table.
You need to define the ProductID column in the CREATE TABLE statement. Which code segment should you use?

• A. Option A
• B. Option B
• C. Option C
• D. Option D

Answer: B

Explanation:
http://msdn.microsoft.com/en-us/library/ms189049.aspx http://msdn.microsoft.com/en-us/library/ms179610.aspx http://msdn.microsoft.com/en-us/library/ff878370.aspx

NEW QUESTION 6

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

• A. Mastered
• B. Not Mastered

Answer: A

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.

NEW QUESTION 7

You are testing disaster recovery procedures.
You attempt to restore DB1 to a different server and you receive the following error message: "Msg 33111. Level 16, State 3, Line 1
Cannot find server certificate with thumbprint
,0xA694FBEA88C9354E5E2567C30A2A69E8FB4C44A9
Msg 3013, Level 16, State 1, Line 1
RESTORE DATABASE is terminating abnormally."
You need to ensure that you can restore DB1 to a different server. Which code segment should you execute?

• A. Option A
• B. Option B
• C. Option C
• D. Option D

Answer: B

NEW QUESTION 8

You plan to create a new table that will contain a column named Salary. Salary will contain highly sensitive data.
Salary must meet the following requirements:
Contain numeric data.
Contain only encrypted data that remains encrypted in memory.
You need to identify which encryption type and data type must be used for Salary. Which encryption type and data type should you identity?
To answer, drag the appropriate encryption type and data type to the correct identifier in the answer area.

• A. Mastered
• B. Not Mastered

Answer: A

Explanation:

NEW QUESTION 9

Which code segment should you use to define the ProductDetails column?

• A. ProductDetails xml (DOCUMENT Production.ProductDetailsSchema) NULL
• B. ProductDetails xml NULL
• C. ProductDetails xml (CONTENT Production.ProductDetailsSchema) NULL
• D. ProductDetails varchar(MAX) NULL

Answer: D

NEW QUESTION 10

You have a SQL Server 2012 database named Database1. Database1 has a data file named database1_data.mdf and a transaction log file named database1_Log.ldf. Database1_Data.mdf is
1.5 GB.
Database1_Log.ldf is 1.5 terabytes. A full backup of Database1 is performed every day.
You need to reduce the size of the log file. The solution must ensure that you can perform transaction log
backups in the future. Which code segment should you execute? To answer, move the appropriate code segments from the list of code segments to the answer area and arrange them in the correct order.

• A. Mastered
• B. Not Mastered

Answer: A

Explanation:
Shrinking a log file to a specified target size
The following example shrinks the log file in the AdventureWorks database to 1 MB. To allow the DBCC SHRINKFILE command to shrink the file, the file is first truncated by setting the database recovery model to SIMPLE.
Transact-SQL
USE AdventureWorks2012; GO
-- Truncate the log by changing the database recovery model to SIMPLE. ALTER DATABASE AdventureWorks2012
SET RECOVERY SIMPLE; GO
-- Shrink the truncated log file to 1 MB.
DBCC SHRINKFILE (AdventureWorks2012_Log, 1); GO
-- Reset the database recovery model. ALTER DATABASE AdventureWorks2012 SET RECOVERY FULL;
GO
http://msdn.microsoft.com/en-gb/library/ms189493.aspx

NEW QUESTION 11

You need to ensure that a new execution plan is used by usp_GetOrdersByProduct each time the stored procedure runs.
What should you do?

• A. Execute sp_help 'usp_GetOrdersByProduct'.
• B. Execute sp_recompile 'usp_GetOrdersByProduct'.
• C. Add WITH RECOMPILE to line 03 in usp_GetOrdersByProduct.
• D. Add WITH (FORCESEEK) to line 07 in usp_GetOrdersByProduct.

Answer: C

Explanation:
Ref: http://msdn.microsoft.com/en-us/librAry/ms190439(v=sql.90).aspx

NEW QUESTION 12

You have a table named Table1 that contains 1 million rows. Table1 contains a column named Column1 that stores sensitive information. Column1 uses the nvarchar(16) data type.
You have a certificate named Cert1.
You need to replace Column1 with a new encrypted column that uses two-way encryption. Which code segment should you execute before you remove Column1?
To answer, move the appropriate code segments from the list of code segments to the answer area and arrange them in the correct order.

• A. Mastered
• B. Not Mastered

Answer: A

Explanation:
Note:
* Use AES_256 for two-way encryption.
* Use varbinary to store key.
* CLOSE SYMMETRIC KEY (Transact-SQL)
Closes a symmetric key, or closes all symmetric keys open in the current session.
* Example:
CREATE SYMMETRIC KEY CreditCards_Key11 WITH ALGORITHM = AES_256
ENCRYPTION BY CERTIFICATE Sales09; GO
-- Create a column in which to store the encrypted data. ALTER TABLE Sales.CreditCard
ADD CardNumber_Encrypted varbinary(128); GO
-- Open the symmetric key with which to encrypt the data. OPEN SYMMETRIC KEY CreditCards_Key11 DECRYPTION BY CERTIFICATE Sales09;
-- Encrypt the value in column CardNumber using the
-- symmetric key CreditCards_Key11.
-- Save the result in column CardNumber_Encrypted. UPDATE Sales.CreditCard
SET CardNumber_Encrypted = EncryptByKey(Key_GUID('CreditCards_Key11')
, CardNumber, 1, HashBytes('SHA1', CONVERT( varbinary
, CreditCardID))); GO

NEW QUESTION 13

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. varbinary
• C. image
• D. FileStream

Answer: D

NEW QUESTION 14

You have a Microsoft SQL Azure database. You have the following stored procedure:

You discover that the stored procedure periodically fails to update Person.Contact.
You need to ensure that Person.Contact is always updated when UpdateContact executes. The solution must minimize the amount of time required for the stored procedure to execute and the number of locks held.
What should you do?

• A. Add the following line of code to line 12: WITH (UPDLOCK)
• B. Add the following line of code to line 05:SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
• C. Add the following line of code to line 08: WITH (UPDLOCK)
• D. Add the following line of code to line 05:SET TRANSACTION ISOLATION LEVEL SNAPSHOT

Answer: C

Explanation:
* Overall, you should use UPDLOCK when you read a value that you plan to update later in the same transaction to prevent the value from changing.
* UPDLOCK
Specifies that update locks are to be taken and held until the transaction completes. UPDLOCK takes update locks for read operations only at the row-level or page-level. If UPDLOCK is combined with TABLOCK, or a table-level lock is taken for some other reason, an exclusive (X) lock will be taken instead.
When UPDLOCK is specified, the READCOMMITTED and READCOMMITTEDLOCK isolation level hints are ignored. For example, if the isolation level of the session is set to SERIALIZABLE and a query specifies (UPDLOCK, READCOMMITTED), the READCOMMITTED hint is ignored and the transaction is run using the SERIALIZABLE isolation level.

NEW QUESTION 15

You execute IndexManagement.sql and you receive the following error message: "Msg 512, Level 16, State 1, Line 12 Subquery returned more than 1 value. This is not permitted when the subquery follows =,! =, <, <= ,>, > = or when the subquery is used as an expression."
You need to ensure that IndexManagement.sql executes properly. Which WHILE statement should you use at line 18?

• A. WHILE SUM(@RowNumber) < (SELECT @counter FROM @indextable)
• B. WHILE @counter < (SELECT COUNT(RowNumber) FROM @indextable)
• C. WHILE COUNT(@RowNumber) < (SELECT @counter FROM @indextable)
• D. WHILE @counter < (SELECT SUM(RowNumber) FROM @indextabie)

Answer: B

NEW QUESTION 16

Your network contains a server that has SQL Server 2014 installed. You create a table by using the following script:

You need to recommend a solution to ensure that each combination of ProductName and ProductManufacturer is not duplicated.
What should you recommend creating?

• A. A UNIQUE constraint
• B. A filtered index
• C. A columnstore index
• D. A CHECK constraint

Answer: A

NEW QUESTION 17

Your company has a SQL Azure subscription.
You implement a database named Database1. Database1 has two tables named Table1 and Table2. You create a stored procedure named sp1. Sp1 reads data from Table1 and inserts data into Table2. A user named User1 informs you that he is unable to run sp1.
You verify that User1 has the SELECT permission on Table1 and Table2.
You need to ensure that User1 can run sp1. The solution must minimize the number of permissions assigned to User1.
What should you do?

• A. Change sp1 to run as the saUser.
• B. Grant User1 the EXECUTE permission on sp1.
• C. Add User1 to the db_datawriter role.
• D. Grant User1 the INSERT permission on Table2.

Answer: B

Explanation:
http://msdn.microsoft.com/en-us/library/ms191291.aspx

NEW QUESTION 18

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 A
• B. Option B
• C. Option C
• D. Option D

Answer: CD

Explanation:
http://msdn.microsoft.com/en-us/library/ms189049.aspx

NEW QUESTION 19

You have a Microsoft SQL Azure database. You have the following stored procedure:

You discover that the stored procedure periodically fails to update HR.Employees.
You need to ensure that HR.Employees is always updated when up_employees executes.
The solution must minimize the amount of time required for the stored procedure to execute and the number of locks held.
What should you do?

• A. Add the following line of code to line 05:SET TRANSACTION ISOLATION LEVEL SNAPSHOT
• B. Add the following line of code to line 13: WITH (UPDLOCK)
• C. Add the following line of code to line 05:SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
• D. Add the following line of code to line 08: WITH (UPDLOCK)

Answer: D

NEW QUESTION 20

You have a Microsoft Azure SQL Database instance. The database contains a table named Table1 that has one million rows and a column named Column1. Column1 allows null values.
You need to update Column1 to meet the following requirements: Prevent null values from being used
Always use a value of zero instead of a null value
Which three Transact-SQL statements should you run? Each correct answer presents part of the solution.

• A. ALTER TABLE dbo.Table1 ADD CONSTRAINTDF_Table1_Column1 DEFAULT 0 FOR Column1
• B. ALTER TABLE Table1 DROP COLUMN Column1
• C. ALTER TABLE Table1 ALTER COLUMNColumn int NOT NULL
• D. ALTER TABLE Table1 ADD COLUMNColumn1 int NOT NULL
• E. UPDATE Table1SET Column1 = 0WHERE Column1 IS NULL

Answer: ACE

NEW QUESTION 21
......