NEW QUESTION 1

You create a view by using the following code:

Several months after you create the view, users report that the view has started to return unexpected results. You discover that the design of Table2 was modified since you created the view.
You need to ensure that the view returns the correct results. Which code segment should you run?

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

Answer: D

NEW QUESTION 2

You need to modify the Orders table to store the XML data used by the retailers. Which statement should you execute?

• A. ALTER OrdersADD originalOrder XML (ValidateOrder);
• B. ALTER OrdersADD originalOrder XML;
• C. ALTER OrdersADD originalOrder varchar(max);
• D. ALTER OrdersADD originalOrder varbinary(max);

Answer: D

NEW QUESTION 3

You have a SQL Server database.
The recovery model is set to full. The transaction log is backed up every night. You discover that the transaction log never decreases in size.
You execute the DBCC SHRINKFILE statement for the transaction log and you discover that the transaction log is unaffected.
You need to ensure that you can reduce the size of the transaction log. What should you do first?

• A. Truncate the transaction log
• B. Kill long-running transactions
• C. Change the recovery model to bulk-logged
• D. Perform a full backup

Answer: A

Explanation:
The transaction log must be truncated before running the DBCC SHRINKFILE operation.

NEW QUESTION 4

You are planning two stored procedures named SProc1 and SProc2. You identify the following requirements:
SProc1 must return a table.
SProc2 must return a scalar value.
You need to identify which option must be implemented for each stored procedure to return the desired data. Which options should you identify?
To answer, drag the appropriate option to the correct requirement 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:
* a table (a set of rows) can be returned through a SELECT statement
*a scalar can be returned through an output parameter.
* incorrect: TVP is used for input not output.

NEW QUESTION 5

You need to provide referential integrity between the Sessions table and Speakers table. Which code segment should you add at line 47 of Tables.sql?

• 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 plan to deploy two stored procedures named SP1 and SP2 that read data from the database. Your company identifies the following requirements for each stored procedure:
SP1 must allow dirty reads.
SP2 must place range locks on the data to ensure read consistency.
You need to identify which isolation level you must set for each stored procedure. The solution must minimize the number of locks.
Which isolation level should you identify?
To answer, drag the appropriate isolation level to the correct stored procedure 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:
* READ UNCOMMITTED
Specifies that statements can read rows that have been modified by other transactions but not yet committed. Transactions running at the READ UNCOMMITTED level do not issue shared locks to prevent other transactions from modifying data read by the current transaction. READ UNCOMMITTED transactions are also not blocked by exclusive locks that would prevent the current transaction from reading rows that have been modified but not committed by other transactions. When this option is set, it is possible to read uncommitted modifications, which are called dirty reads. Values in the data can be changed and rows can appear or disappear in the data set before the end of the transaction. This option has the same effect as setting NOLOCK on all tables in all SELECT statements in a transaction. This is the least restrictive of the isolation levels.
* SERIALIZABLE
Specifies the following:
Statements cannot read data that has been modified but not yet committed by other transactions.
No other transactions can modify data that has been read by the current transaction until the current transaction completes.
Other transactions cannot insert new rows with key values that would fall in the range of keys read by any statements in the current transaction until the current transaction completes.
Range locks are placed in the range of key values that match the search conditions of each statement executed in a transaction. This blocks other transactions from updating or inserting any rows that would qualify for any of the statements executed by the current transaction. This means that if any of the statements in a transaction are executed a second time, they will read the same set of rows. The range locks are held until the transaction completes. This is the most restrictive of the isolation levels because it locks entire ranges of keys and holds the locks until the transaction completes. Because concurrency is lower, use this option only when necessary.
Reference: SET TRANSACTION ISOLATION LEVEL (Transact-SQL)

NEW QUESTION 7

You need to recommend a solution to ensure that SQL1 supports the auditing requirements of usp_UpdateSpeakerName.
What should you include in the recommendation?

• A. The Distributed Transaction Coordinator (DTC)
• B. Transactional replication
• C. Change data capture
• D. Change tracking

Answer: A

NEW QUESTION 8

You need to modify the usp_DetectSuspiciousActivity stored procedure.
Which two actions should you perform? Each correct answer presents part of the solution. Choose two.

• A. Option A
• B. Option B
• C. Option C
• D. Option D
• E. Option E
• F. Option F

Answer: DE

Explanation:
Note:
* Move micropayments to dbo.POSException table by using a stored procedure named ups_DetectSuspiciousActivity.

NEW QUESTION 9

You are planning two stored procedures named SProc1 and SProc2. You identify the following requirements:
SProc1 must return a table.
SProc2 must return a status code.
You need to identify which options must be implemented to meet each stored procedure requirement. Which options should you identify?
To answer, drag the appropriate option to the correct requirement 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:

NEW QUESTION 10

You plan to create a stored procedure that inserts data from an XML file to the OrderDetails table. The following is the signature of the stored procedure:

The following is the XSD file used to create the ValidateOrder schema collection:

You develop a code segment that retrieves the number of items and loops through each item. Each time the loop runs, a variable named @itemNumber is incremented.
You need to develop a code segment that retrieves the product ID of each item number in the loop. Which code segment should you develop?

• A. SET @productID = @items.value'/Root/Product/productID', int)
• B. SET @productID = @items.value'/Root/Product['+ @itemNumber+ ']/@productID', int)
• C. SET @productID = @items.value'/Root/Product['+ @itemNumber+ ']/productID', int)
• D. SET @productID = @items.value'/Root/Product/@productID', int)

Answer: B

NEW QUESTION 11

You have a database named Database1. Database1 has two stored procedures named Proc1 and Proc2 and a table named Table1. Table1 has millions of rows.
Proc1 updates data in Table1. Proc2 reads data from Table1.
You discover that when Proc1 is executed to update more than 4,000 rows, Proc2 is blocked. The block affects all rows, including those that are not being updated by Proc1.
You need to ensure that when Proc1 is executing, Proc2 can access the data in Table1 that Proc1 is not updating.
What should you change Proc1 to do?
More than one answer choice may achieve the goal. Select the BEST answer.

• A. Update less than 4,000 rows simultaneously.
• B. Use the PAGLOCK table hint.
• C. Wait for Proc2 to complete.
• D. Use the ROWLOCK table hint.

Answer: A

NEW QUESTION 12

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

Answer: ABE

Explanation:
E: First change the NULLs to ' '.
A: Then set the default to the column to ' '.
B: Finally add the NOT NULL constraint to the column.

NEW QUESTION 13

You have two existing tables, one named COUNTRY and the other named STATES. The tables are defined as follows:

You need to set up a rule that every STATE.Country_Abbr must match an existing record in the COUNTRY table.
Develop the solution by selecting and arranging the required code blocks in the correct order. You may not need all of the code blocks.

• A. Mastered
• B. Not Mastered

Answer: A

Explanation:
Note:
To allow naming of a FOREIGN KEY constraint, and for defining a FOREIGN KEY constraint on multiple columns, use the following SQL syntax:
MySQL / SQL Server / Oracle / MS Access:
ALTER TABLE OrdersADD CONSTRAINT fk_PerOrdersFOREIGN KEY (P_Id)REFERENCES
Persons(P_Id)

NEW QUESTION 14

You need to modify the stored procedure usp_LookupConcurrentUsers. What should you do?

• A. Add a clustered index to the summary table.
• B. Add a nonclustered index to the summary table.
• C. Add a clustered columnstore index to the summary table.
• D. Use a table variable instead of the summary table.

Answer: A

Explanation:
Scenario: Query the current open micropayments for users who own multiple micropayments by using a stored procedure named usp.LookupConcurrentUsers

NEW QUESTION 15

You need to modify usp_GetOrdersAndItems to ensure that an order is NOT retrieved by usp_GetOrdersAndItems while the order is being updated.
What should you add to usp_GetOrdersAndItems?

• A. Add SET TRANSACTION ISOLATION LEVEL SERIALIZABLE to line 03.
• B. Add SET TRANSACTION ISOLATION LEVEL SNAPSHOT to line 03.
• C. Add (UPDLOCK) to the end of line 06.
• D. Add (READPAST) to the end of line 06.

Answer: D

NEW QUESTION 16

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 17

You plan to modify a stored procedure to use temporary data. The stored procedure must meet the following requirements:
Favor physical memory when physical memory is available.
Be able to roll back changes to the temporary data.
You need to recommend which object to add to the stored procedure. Which T-SQL command should you recommend?

• A. CREATE TABLE ##Table...
• B. CREATE TABLE Table...
• C. CREATE VIEW Table...
• D. CREATE PARTITION SCHEME Table...
• E. DECLARE TABLE @ Table...

Answer: A

Explanation:
Temporary Tables
You can create local and global temporary tables. Local temporary tables are visible only in the current session, and global temporary tables are visible to all sessions. Temporary tables cannot be partitioned.
Prefix local temporary table names with single number sign (#table_name), and prefix global temporary table names with a double number sign (##table_name)

NEW QUESTION 18

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 19

You need to create the usp.AssignUser stored procedure.
Develop the solution by selecting and arranging the required code blocks in the correct order. You may not need all of the code blocks.

• A. Mastered
• B. Not Mastered

Answer: A

Explanation:
Box 1:

Box 2:

Box 3:

Box 4:

Box 5:

Box 6:

Box 7:

Note:
* From scenario: The mobile application will need to meet the following requirements:
/Communicate with web services that assign a new user to a micropayment by using a stored procedure named usp_AssignUser.
* Example:
create procedure dbo.OrderInsert(@OrdNo integer, @CustCode nvarchar(5))
with native_compilation, schemabinding, execute as owner as
begin atomic with
(transaction isolation level = snapshot, language = N'English')
declare @OrdDate datetime = getdate();
insert into dbo.Ord (OrdNo, CustCode, OrdDate) values (@OrdNo, @CustCode, @OrdDate); end
go
* Natively compiled stored procedures are Transact-SQL stored procedures compiled to native code that access memory-optimized tables. Natively compiled stored procedures allow for efficient execution of the queries and business logic in the stored procedure.
* READ COMITTED versus REPEATABLE READ
Read committed is an isolation level that guarantees that any data read was committed at the moment is read. It simply restricts the reader from seeing any intermediate, uncommitted, 'dirty' read. IT makes no promise whatsoever that if the transaction re-issues the read, will find the Same data, data is free to change after it was read.
Repeatable read is a higher isolation level, that in addition to the guarantees of the read committed level, it also guarantees that any data read cannot change, if the transaction reads the same data again, it will find the previously read data in place, unchanged, and available to read.
* Both RAISERROR and THROW statements are used to raise an error in Sql Server.
The journey of RAISERROR started from Sql Server 7.0, where as the journey of THROW statement has just began with Sql Server 2012. obviously, Microsoft suggesting us to start using THROW statement instead of RAISERROR. THROW statement seems to be simple and easy to use than RAISERROR.
* Explicit transactions. The user starts the transaction through an explicit BEGIN TRAN or BEGIN ATOMIC. The transaction is completed following the corresponding COMMIT and ROLLBACK or END (in the case of an atomic block).

NEW QUESTION 20

You have a database named Database1. You execute the following code:

You have the following query. (Line numbers are included for reference only.)

Users report that the query takes a long time to complete. You create a full-text index for the Notes column.
You need to recommend changes to the query to reduce the amount of time it takes for the query to complete. Which code segment should you use to replace line 03?

• A. WHERE FREETEXT(notes, '%call%') AND
• B. INNER JOIN FREETEXTTABLE(dbo.table1, notes, 'call') AS t2 ON dbo.table1.ID = t2.key WHERE
• C. WHERE CONTAINS(notes, 'call*') AND
• D. WHERE CONTAINS(notes,*%call%'> AND

Answer: A

NEW QUESTION 21

You execute the following code:

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

You need to prevent the key lookup. What should you modify?
More than one answer choice may achieve the goal. Select the BEST answer.

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

Answer: C

NEW QUESTION 22
......