70-469 281 Q&S Recertification for MCSE: Data Platform

What three tasks should you perform next?

You plan to create a custom aggregation function named Median.
You plan to deploy Median to a SQL Server 2014 server named Server1.
You need to ensure that Median can access a web service named WebApp1. The solution must minimize the number of changes made to the database.
You create a Microsoft .NET Framework class that contains the definition of Median.
You upload a certificate to Server1.
What three tasks should you perform next?
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:



Answer Clike
How should you complete the table definition to reduce contention on the table structure?

You administer a SQL Server 2014 instance.
You have been assigned to determine the cause of frequent long-running transactions that have been tracked to the dbo.Account table, where there are many cases of blocking and deadlocks. The dbo.Account table contains more than one million rows.
Users and processes frequently search for and update data by using the AccountId column, and less frequently the AccountNumber and GovernmentId columns, all of which contain only unique values. Users frequently get lists of AccountNumber values by searching on Last Name and then
First Name.
You need to modify the structure of the dbo.Account table to alleviate the issues.
How should you complete the table definition to reduce contention on the table structure? To answer, drag the appropriate code snippets to the correct locations in the CREATE TABLE statement. Each code snippet may be used once, more than once, or not at all. You may need to drag the split bar between panes or scroll to view content.
Select and Place:



Answer Clike
Which five code segments should you use?

You create a disk-based table

You need to prevent duplicate values in the SKU field.
Which five code segments should you use?
To answer, move the appropriate code segments from the list of code segments to the answer area and arrange them in the correct order.
Select and Place:



Answer Clike
You administer a SQL Server 2014 instance.

You administer a SQL Server 2014 instance.
The server is capable of 10000 IO/second (IOPS). During the time period when the second process executes, the disk IO can reach 7000 IOPS, and CPU use can average 30% over the eight processors.
The first process summarizes the day’s activity executed by a login of [SummaryReportLogin]. The second process submits transactions executed by a login of [ETLLogin].
A Resource Governor classifier function has been created to return WG_Low for connections from the [ETLLogin] and [SummaryReportLogin].
You need to set up the Resource Group and Workgroup Pools on the instance.
You have the following requirements:
Both processes must never use more than 50 percent of the CPU at any one time.
The number of active queries that these processes can execute simultaneously should be limited to a maximum of 10.
The SummaryReportLogin process must always achieve the minimum IOPS required to be minimally affected during executing the ETLLogin processes.
Develop the solution by selecting and arranging the required code blocks in the correct order.
You may not need all of the code blocks.
Select and Place:



Answer Clike
Which code segment should you execute before you remove Column1?

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.
Select and Place:



Answer Clike
Which dynamic management objects should you identify?

You have a SQL Server 2014 database.
You plan to create a stored procedure that will retrieve the following information:
The XML content of the query plans that is stored in memory
The number of times each query plan is used
You need to identify which dynamic management objects must be used to retrieve the required information for the stored procedure.
Which dynamic management objects should you identify?
To answer, drag the appropriate dynamic management object to the correct requirement in the answer area.
Select and Place:



Answer Clike
You are a SQL Server 2014 Developer.

You are a SQL Server 2014 Developer. A database that you work on contains two tables that are defined as follows:

Product is an important table that has sensitive audit requirements.
You need to create a trigger that supports the following requirements:
1. Every row that is inserted or updated in Product will reflect its actual LastUpdatedDate and LastUpdatedBy values in the Product table.
2. Any row that is updated or deleted must write a new record reflecting the OLD values into the ProductAudit table.
3. Any error that occurs during the course of the trigger’s execution must prevent the changes from happening.
Develop the solution by selecting and arranging the required code blocks in the correct order.
You may not need all of the code blocks.
Select and Place:



Answer Clike
Which technologies should you identify?

You are designing a database for a university. The database will contain two tables named Classes and StudentGrades that have the following specifications:
Classes will store brochures in the XPS format.
The brochures must be structured in folders and must be accessible byusing UNC paths.
StudentGrades must be backed up on a separate schedule than the rest ofthe database.
You need to identify which SQL Server technology meets the specifications of each table. Which technologies should you identify? To answer, drag the appropriate technology to the correct table in the answer area.
Select and Place:



Answer Clike
Which data types should you recommend for each column?

You have a SQL Azure database named Database1. You need to design the schema for a table named table1.
Table1 will have less than one million rows. Table1 will contain the following information for each row:

The solution must minimize the amount of space used to store each row. Which data types should you recommend for each column? To answer, drag the appropriate data type to the correct column in the answer area.
Select and Place:



Answer Clike
Which three code segments should you execute?

Your network contains a server named Server1 that runs SQL Server 2012. Server1 contains an instance named Instance1. Instance1 contains a database named ContentDatabase.
ContentDatabase uses transaction log backups.
The recovery model of ContentDatabase is set to FULL.
You need to shrink the ContentDatabase_Log log file to 10 MB. The solution must ensure that you can continue to back up the transaction log.
Which three code segments 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.
Select and Place:



Answer Clike
You have two existing tables, one named COUNTRY and the other named STATES.

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.
Select and Place:



Answer Clike
Which isolation level should you identify?

You plan to deploy two stored procedures name USP_1 and USP_2 that read data from a database.
Your company identifies the following requirements for each stored procedure:
USP_1 cannot allow dirty reads.
USP_2 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.)
Select and Place:



Answer Clike
Which options should you recommend?

You plan to deploy SQL Server 2012. You must create two tables named Table1 and Table2 that will have the following specifications:
Table1 will contain a date column named Column1 that will contain a null value approximately 80 percent of the time.
Table2 will contain a column named Column2 that is the product of two other columns in Table2.
Both Table1 and Table2 will contain more than 1 million rows.
You need to recommend which options must be defined for the columns. The solution must minimize the storage requirements for the tables. Which options should you recommend? To answer, drag the appropriate options to the correct column in the answer area.
Select and Place:



Answer Clike
Which options should you identify?

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.)
Select and Place:



Answer Clike
Which code segment should you execute?

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.
Select and Place:



Answer Clike
Which encryption type and data type should you identity?

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.
Select and Place:



Answer Clike
Which type of index should you create for each table?

Your network contains a SQL Server 2012 instance named SQL1. SQL1 contains a database named DB1. DB1 contains three tables.
The tables are configured as shown in the following table.

You plan to create indexes for the tables.
You need to identify which type of index must be created for each table. The solution must minimize the amount of time required to return information from the tables.
Which type of index should you create for each table? To answer, drag the appropriate index type to the correct table in the answer area.
Select and Place:



Answer Clike
Which five code segments should you use?

You create a table that contains the following script:

You need to prevent duplicate values in the EmployeeID field.
Which five code segments should you use?
To answer, move the appropriate code segments from the list of code segments to the answer area and arrange them in the correct order.
Select and Place:



Answer Clike
What should you use?

You have a database named database1. Each table in database1 has one index per column.
Users often report that creating items takes a long time.
You need to perform the following maintenance tasks:
• Identify unused indexes.
• Identify indexes that need to be defragmented.
What should you use?
To answer, drag the appropriate function to the correct management task in the answer area.
(Answer choices may be used once, more than once, or not at all.)
Select and Place:



Answer Clike
What should you do?

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.)
Select and Place:



Answer Clike
Which isolation level should you identify?

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.)
Select and Place:



Answer Clike
Which code segment should you execute?

You have a SQL Server 2012 database named DB1. DB1 contains four filegroups named FG1, FG2, FG3, and FG4. You execute the following code:

Two million rows are added to dbo.Sales.
You need to move the data from the first partition to a new table named SalesHistory and, starting on December 31, 2012, repartition dbo.Sales to support new sales data for three months.
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.
Select and Place:



Answer Clike
What should you use?

You have a database named database1. Each table in database1 has one index per column.
Users often report that creating items takes a long time.
You need to perform the following maintenance tasks:
• Identify unused indexes.
• Identify indexes that need to be defragmented.
What should you use?
To answer, drag the appropriate function to the correct management task in the answer area.
(Answer choices may be used once, more than once, or not at all.)
Select and Place:



Answer Clike
What should you create?

You execute the following code:

You discover that the Customers table was created in the dbo schema.
You need to create a code segment to move the table to another schema named Schema2.
What should you create?
To answer, drag the appropriate code segments to the correct location in the answer area. (Answer choices may be used once, more than once, or not at all.)
Select and Place:



Answer Clike
Which code segment should you execute?

You have a SQL Server 2012 database named Database1. Database1 has a data file named Database1_data.mdf and a transaction log named Database1jog.ldf. Database1_data.mdf is 1.5 GB.
Database1jog.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.
Select and Place:



Answer Clike
What should you do?

You have a table named Customers that has a clustered index defined on the ID column.
You write a script to create a stored procedure.
You need to complete the script for the stored procedure. The solution must minimize the number of locks and deadlocks.
What should you do?
To answer, drag the appropriate option to the correct location in the answer area. (Answer choices may be used once, more than once, or not at all.)
Select and Place:



Answer Clike
Which technologies should you identify?

You are designing two stored procedures named Procedure1 and Procedure2.
You identify the following requirements:
• Procedure1 must take a parameter that ensures that multiple rows of data can pass into the stored procedure.
• Procedure2 must use business logic that resides in a Microsoft .NET
Framework assembly.
You need to identify the appropriate technology for each stored procedure.
Which technologies should you identify?
To answer, drag the appropriate technology to the correct stored procedure in the answer area.
(Answer choices may be used once, more than once, or not at all.)
Select and Place:



Answer Clike
Which options should you identify?

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.)
Select and Place:



Answer Clike
What three tasks should you perform next?

You plan to create a custom aggregation function named Function1.
You plan to deploy Function1 to SQL Server 2012.
You need to ensure that Function1 can access a web service. The solution must minimize the number of changes made to the database.
You create a Microsoft .NET Framework class that contains the definition of Function1.
You upload a certificate to SQL Server.
What three tasks should you perform next?
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:



Answer Clike
What should you do?

You have a SQL Server 2012 database named database1.
Users report that queries that usually take less than one second to execute, take more than 30 seconds to execute.
You need to view the server resource consumption when the queries are executed.
What should you do?
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:



Answer Clike
Which code segment should you execute before you remove Column1?

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 named Column2 that uses one-way hashing.
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.
Select and Place:



Answer Clike
Which code segment should you execute?

You run the following code segment:

After you add 10,000 rows to Customers, you discover that the index is fragmented.
You need to defragment the index in the least amount of time.
Which code segment should you execute?
To answer, drag the appropriate value to the correct location in the code segment in the answer area.
(Answer choices may be used once, more than once, or not at all.)
Select and Place:



Answer Clike
What should you do?

You have a table named Table1. Table1 has 1 million rows.
Table1 has a columnstore index for a column named Column1.
You need to import data to Table1. The solution must minimize the amount of time it takes to import the data.
What should you do?
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:



Answer Clike
What should you do?

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


Answer Clike
What should you recommend creating?

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 Clike
What should you do?

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 Clike
Which statement or statements should you execute?

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 Clike
What should you create?

You have a Microsoft SQL Azure database named DBAzurel. DBAzurel contains a table named Orders that stores sales data.
Each order has a sales total that can only be discovered by querying multiple tables.
You need to ensure that the value of the sales total is returned by executing a query on Orders.
What should you create?
A. A calculated column that uses a scalar function
B. A trigger that uses a table-valued function
C. A calculated column that uses a table-valued function
D. A trigger that uses a ranking function


Answer Clike
Which two statements can you make about the performance characteristics of this query?

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 Clike
What should you create?

You have a database that is accessed by 300 concurrent users.
You need to log all of the queries that become deadlocked. The solution must meet the following requirements:
Provide a representation of the deadlock in XML format.
Minimize the impact on the server.
What should you create?
A. A SQL Server Profiler trace
B. A script that enables trace flags
C. A SQL Server Agent job that retrieves information from the sys.dm_tran_active_transactions dynamic management views
D. A SQL Server Agent job that retrieves information from the sys.dm_tran_session_transactions dynamic management views


Answer Clike
What should you do?

You have the following query on a disk-based table:

You discover that the query takes a long time to complete.
The execution plan is shown in the Execution Plan exhibit. (Click the Exhibit button.)

The index usage is show in the Index Usage exhibit. (Click the Exhibit button.)

You need to reduce the amount of time it takes to complete the query. You must achieve this goal as quickly as possible.
What should you do?
A. Reorganize the index.
B. Update statistics.
C. Create an index on LastName.
D. Rebuild the index.


Answer Clike
What should you do?

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 Clike
What should you do?

You have a SQL Server 2012 environment that contains two servers. The servers are configured as shown in the following table.

After the failover is complete, a user receives the following error message when connecting to DB1 on Server2: "Msg 916, Level 14, State 1, Line 1
The server principal "Account1" is not able to access the database "DB1" under the current security context."
You verify that there is a server login for Account1 on Server2.
You need to ensure that Account1 can connect to DB1.
What should you do?
More than one answer choice may achieve the goal. Select the BEST answer.
A. Update the SID for Account1 on DB1.
B. Add Account1 to the db_datareader role.
C. Create a new database user on DB1.
D. Implement Windows authentication.


Answer Clike
Which code segment should you use?

You have a SQL Server 2012 database that contains a table named Users. The Users table contains usernames and passwords.
You need to ensure that all new records have a password.
Which code segment should you use?
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 Clike
Which T-SQL command should you recommend?

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 Clike
What should you do?

You have a Microsoft SQL Azure database that contains a table named Employees.

You create a non-clustered index named EmployeeName on the name column.

You write the following query to retrieve all of the employees that have a name that starts with the letters JOH:
You discover that the query performs a table scan.
You need to ensure that the query uses EmployeeName.
What should you do?
A. Recreate EmployeeName as a unique index
B. Recreate EmployeeName as a clustered index
C. Replace LEFT(name,3) = ‘JOH’ by using name like ‘JOH%’
D. Replace LEFT(name,3) = ‘JOH’ by using substring(name, 1, 3) = ‘JOH’


Answer Clike
What should you create?

You have a table named Table1 that stores customer data.
Each customer has a credit limit that can only be discovered by querying multiple tables.
You need to ensure that the value of the credit limit is returned by executing a query on Table1.
What should you create?
A. A trigger that uses a ranking function
B. A trigger that uses a table-valued function
C. A calculated column that uses a table-valued function
D. A calculated column that uses a scalar function


Answer Clike
Which code segment should you use to create the table?

You are creating a stored procedure named usp1. Usp1 will create a table that will be used during the execution of usp1. Only usp1 will be allowed to access the table.
You need to write the code required to create the table for usp1. The solution must minimize the need to recompile the stored procedure.
Which code segment should you use to create the table?
A. CREATE TABLE oneTable
B. CREATE TABLE ##oneTable
C. CREATE TABLE #oneTable
D. DECLARE oneTable TABLE


Answer Clike
What should you do?

Your network contains a server named SQL1 that has SQL Server 2012 installed. SQL1 contains a database name DB1 and a table named Customers.
You add an additional server named SQL2 that runs SQL Server 2012.
You need to create a distributed partitioned view. The solution must minimize the amount of network traffic.
What should you do? (Each correct answer presents part of the solution. Choose all that apply.)
A. Add SQL2 as a Distributor.
B. Add the Customers table to SQL2.
C. Add SQL2 as a linked server.
D. Create the view on SQL1.
E. Remove the Customers table from SQL1.
F. Create the view on SQL2.


Answer Clike
What should you do?

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

You insert 3 million rows into Sales.
You need to reduce the amount of time it takes to execute Proc1.
What should you do?

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


Answer Clike
Which type of object should you use?

You need to encapsulate a T-SQL script into a reusable user-defined object.
The object must meet the following requirements:
Permit insertions into a table variable.
Support structured exception handling.
Prevent changes to the definition of referenced objects.
Support the use of the APPLY operator on the output of the object.
Which type of object should you use?
A. An inline table-valued function
B. A stored procedure
C. A scalar user-defined function
D. A multi-statement table-valued function


Answer Clike
Which system stored procedures should you use?

You plan to migrate an instance of SQL Server 2008 to a new installation of SQL Server 2012.
You need to migrate alerts and e-mail notifications.
Which system stored procedures should you use? (Each correct answer presents part of the solution. Choose all that apply.)
A. sp_syspolicy_create_job
B. sp_add_operator
C. sp_audit_write
D. sp_add_alert


Answer Clike
What should you add to the query?

You have a Microsoft SQL Azure database that contains a table named Customers.
You have a table-valued function named TopCustomers that returns a list of all the customers that have purchased items during the last 12 months. The ID of the customer is passed as an argument to the TopCustomers function.
You need to create a query that returns a list of all the Customer names and the purchase dates.
The solution must return only customers that have purchased an item during the last 12 months.
What should you add to the query?
A. OUTER JOIN
B. CROSS JOIN
C. CROSS APPLY
D. OUTER APPLY


Answer Clike
What should you do?

Your company has a main office in London and a branch office in New York.
Your network contains a server named Server5 that has SQL Server 2012 installed. Server5 contains a database name ContentDB and a table named ContentTable.
You add an additional server named Server9 that runs SQL Server 2012.
You need to create a distributed partitioned view. The solution must minimize the amount of network traffic.
What should you do? (Each correct answer presents part of the solution. Choose all that apply.)
A. Create the view on Server5.
B. Add Server9 as a linked server.
C. Create the view on Server9.
D. Add the Customers table to Server9.
E. Add Server9 as a Distributor.
F. Remove the Customers table from Server5.


Answer Clike
Which code segment should you execute?

You have a SQL Server 2012 database named Database1. Database1 contains a table named OrderDetails.
For a given sales order, you need to retrieve the OrderID, Quantity, and LineTotal columns for all of the items in the OrderDetails table. The solution must ensure that the results can be joined to other tables.
Which code segment should you execute?

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


Answer Clike
Which code segment should you run?

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 Clike
Which code segment should you execute?

You have a SQL Server 2012 database named DB1. You have a backup device named Device1.
You discover that the log file for the database is full.
You need to ensure that DB1 can complete transactions. The solution must not affect the chain of log sequence numbers (LSNs).
Which code segment should you execute?
A. BACKUP LOG DB1 TO Device1 WITH COPY_ONLY
B. BACKUP LOG DB1 TO Device1 WITH NORECOVERY
C. BACKUP LOG DB1 TO Device1 WITH TRUNCATE_ONLY
D. BACKUP LOG DB1 TO Device1


Answer Clike
What should you recommend creating?

You have a Microsoft SQL Azure database named DBAzure1.
You create a table in DBAzure1 by using the following script:

You need to recommend a solution to ensure that each combination of CustomerContact and CustomerDetails is not duplicated.
What should you recommend creating?
A. A CHECK constraint
B. A filtered index
C. A columnstore index
D. A UNIQUE constraint


Answer Clike
Which option should you include?

You plan to create a new table that has the following requirements:
Uses a GUID data type as the primary key.
Uses a clustered index as the primary key.
Minimizes fragmentation.
You need to recommend which option to include in the CREATE statement.
Which option should you include?
More than one answer choice may achieve the goal. Select the BEST answer.
A. NEWID
B. @@IDENTITY
C. NEWSEQUENTIALID
D. IDENTITY


Answer Clike
Which type of index defragmentation solution should you include in the recommendation?

You have a SQL Server 2012 instance named SQLInstance1. Instance1 contains a database named Database1.
You need to recommend an index defragmentation solution for an index named ContentIndex.
ContentIndex must meet the following requirements:
Remain online during the defragmentation.
Update distribution statistics.
Perform defragmentation as quickly as possible.
Which type of index defragmentation solution should you include in the recommendation? More than one answer choice may achieve the goal. Select the BEST answer.
A. DBCC DBREINDEX
B. REORGANIZE
C. REBUILD
D. DBCC INDEXDEFRAG


Answer Clike
Which keyword should you identify?

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. VIEW_METADATA
C. DISTINCT
D. DEFAULT


Answer Clike
Which tool should you use?

You have a server that has SQL Server 2012 installed.
You need to identify which parallel execution plans are running in serial.
Which tool should you use?
A. Performance Monitor
B. Database Engine Tuning Advisor
C. Data Profile Viewer
D. Extended Events


Answer Clike
Which transaction isolation level should you use in sp1?

You use SQL Server 2012 to store data used by an e-commerce application.
You develop a stored procedure named sp1. Sp1 is used to read the price of all the products sold on the e-commerce site.
You need to ensure that sp1 can read data even while another transaction is modifying the price of a product. Sp1 must only read committed data.
Which transaction isolation level should you use in sp1?
A. Serializable
B. Snapshot
C. Repeatable read
D. Read committed


Answer Clike
Which advanced server option should you modify?

You have a SQL Server 2012 instance that hosts a single-user database. The database does not contain user-created stored procedures or usercreated functions.
You need to minimize the amount of memory used for query plan caching.
Which advanced server option should you modify?
A. Scan for Startup Procs
B. Enable Contained Databases
C. Optimize for Ad hoc Workloads
D. Allow Triggers to Fire Others


Answer Clike
What should you do?

You execute the following code.

After populating the Employees table with 10,000 rows, you execute the following query:

You need to reduce the amount of time it takes to execute the query.
What should you do?
A. Partition the table and use the JobTitle column for the partition scheme.
B. Change SUBSTRING(JobTitle,l,l) = ‘C to JobTitle LIKE ‘c%’
C. Change SUBSTRING (JobTitle, 1,1] = ‘c’ to LEFT(JobTitle ,1) = ‘c’.
D. Replace IX_Employees with a clustered index.


Answer Clike
What should you create?

You have a SQL Server 2012 database named DB1 that is accessed by 650 concurrent users.
You need to log all of the queries to DB1 that become deadlocked.
The solution must meet the following requirements:
Provide a representation of the deadlock in XML format.
Minimize the impact on the server.
What should you create?
A. A SQL Server Profiler trace
B. A SQL Server Agent job that retrieves information from the sys.dm_tran_session_transactions dynamic management views
C. A SQL Server Agent job that retrieves information from the sys.dm_tran_active_transactions dynamic management views
D. A script that enables trace flags


Answer Clike
Which code segment should you use to replace line 03?

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 Clike
Which code segment should you use?

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


Answer Clike
What should you do?

You have a database named DB1.
You plan to configure change data capture on the existing tables in DB1.
The database file settings for the DB1 are shown in the exhibit. (Click the Exhibit button.)

You need to minimize disk contention caused by change data capture.
What should you do?
More than one answer choice may achieve the goal. Select the BEST answer.
A. Increase the autogrowth value of the database file.
B. Set the database recovery model to simple.
C. Increase the autogrowth value of the log file.
D. Configure change data capture to use to a secondary filegroup.


Answer Clike
What should you do?

You have database objects that were created by using the following script:

The dbo.Customers table has 1 million rows.
You discover that usp_GetCustomersByDate takes a long time to complete. The query plan used by the stored procedure is shown in the exhibit. (Click the Exhibit button.)

You need to ensure that usp_GetCustomersByDate completes as quickly as possible.
What should you do?
A. Modify the stored procedure to include the OPTIMIZE FOR UNKNOWN query hint.
B. Execute the sp_recompile ‘dbo.GetCustomersByDate’ statement.
C. Execute the ALTER INDEXIX_Customers_CreationDate WITH REBUILD statement.
D. Modify the stored procedure to include the OPTIMIZE FOR(‘1/1/2008’) query hint.


Answer Clike
What should you set from the index options?

You have an index for a table in a SQL Azure database. The database is used for Online Transaction Processing (OLTP).
You discover that many page splits occur when records are inserted or updated in the table.
You need to minimize the number of page splits.
What should you set from the index options?
A. FILLFACTOR = 0
B. STATISTICS_NORECOMPUTE = OFF
C. STATISTICS_NORECOMPUTE = ON
D. FILLFACTOR = 80


Answer Clike
What should you do?

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

You insert 3 million rows into Sales.
You need to reduce the amount of time it takes to execute Proc1.
What should you do?

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


Answer Clike
What should you do?

You have a text file that contains an XML Schema Definition (XSD).
You have a table named Schema1.Table1.
You have a stored procedure named Schema1.Proc1 that accepts an XML parameter named Param1.
You need to store validated XML data in Schema1.Table1. The solution must ensure that only valid XML data is accepted by Param1.
What should you do? (Each correct answer presents part of the solution. Choose all that apply.)
A. Define an XML column in Table1 by using an XML schema collection.
B. Create an XML schema collection in the database from the text file.
C. Declare Param1 var1 as type XML and associate the variable to the XML schema collection.
D. use the modify method to insert the XML schema into each row of the XML column in Table1.


Answer Clike
Which code segment should you execute?

You have a SQL Azure database.
You execute the following code:

The Sales.Customers table will contain 100,000 rows. You expect the FaxNumber column to contain a null value for 70 percent of the rows.
You need to create an index to support Sales.CustomersByFaxNumber. The solution must minimize the disk storage requirements.
Which code segment should you execute?
A. CREATE INDEX IX_Customers ON Customers (FaxNumber)
WHERE FaxNumber IS NOT NULL
B. CREATE INDEX IX_Customers ON Customers (FaxNumber)
WITH FILLFACTOR=0
C. CREATE INDEX IX_Customers ON Customers (CustomerName)
INCLUDE (FaxNumber)
D. CREATE INDEX IX_Customers ON Customers (FaxNumber)
E. CREATE INDEX IX_Customers ON Customers (FaxNumber)
WHERE FaxNumber IS NULL


Answer Clike
Which data type should you recommend?

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 Clike
Which type of table should you identify?

You plan to modify a procedure that contains hundreds of lines of code.
The modification must support the following guidelines:
Use only tables that are not persistent in the database.
Minimize the amount of time required to execute and recompile procedures.
You need to identify which type of table must be used to support the planned modification.
Which type of table should you identify?
A. A system table
B. A partitioned table
C. A table variable
D. A temporary table


Answer Clike
Which code segment should you execute?

You have a SQL Azure database.
You execute the following script:

You add 1 million rows to Table1. Approximately 85 percent of all the rows have a null value for Column2.
You plan to deploy an application that will search Column2.
You need to create an index on Table1 to support the planned deployment. The solution must minimize the storage requirements.
Which code segment should you execute?
A. CREATE INDEX IX_Table1 ON Table1 (Column2)
WITH FILLFACTOR-0
B. CREATE INDEX IX_Table1 OK Table1 (Column1)
INCLUDE (Column2)
C. CREATE INDEX IX_Table1 ON Table1 (Column2)
WHERE Column2 IS NULL
D. CREATE INDEX IX_Table1 ON Table1 (Column2)
WHERE Column2 IS NOT NULL


Answer Clike
What should you use?

You are creating a table named Orders.
You need to ensure that every time a new row is added to the Orders table, a table that is used for auditing is updated.
What should you use?
More than one answer choice may achieve the goal. Select the BEST answer.
A. A Data Definition Language (DDL) trigger
B. A DEFAULT constraint
C. A CHECK constraint
D. A FOREIGN KEY constraint
E. A data manipulation language (DML) trigger


Answer Clike
What should you set from the index options?

You have an index for a table in a SQL Azure database. The database is used for Online Transaction Processing (OLTP).
You discover that the index consumes more physical disk space than necessary.
You need to minimize the amount of disk space that the index consumes.
What should you set from the index options?
A. STATISTICS_NORECOMPUTE = OFF
B. FILLFACTOR = 80
C. FILLFACTOR = 0
D. STATISTICS_NORECOMPUTE = ON


Answer Clike
What should you implement?

You run the following code:

You need to ensure that the root node of the XML data stored in the Details column is <Order_Details>.
What should you implement?
More than one answer choice may achieve the goal. Select the BEST answer.
A. A user-defined data type
B. An XML index
C. A Data Definition Language (DDL) trigger
D. A data manipulation language (DML) trigger
E. An XML schema collection


Answer Clike
What should you use?

You are creating a table named Orders.
You need to ensure that every time a new row is added to the Orders table, a user-defined function is called to validate the row before the row is added to the table.
What should you use?
More than one answer choice may achieve the goal. Select the BEST answer.
A. A data manipulation language (DML) trigger
B. A DEFAULT constraint
C. A Data Definition Language (DDL) trigger
D. A CHECK constraint
E. A FOREIGN KEY constraint


Answer Clike
Which code segment should you execute?

You execute the following code:

You have a stored procedure that includes the following SELECT statement:

You need to create a covering index on UserInfo.
Which code segment should you execute?

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


Answer Clike
How many rows should you identify?

You plan to execute the following code:

You need to identify how many rows will be in dbo.Table1 after you execute the code.
How many rows should you identify?
A. 0
B. 1
C. 2
D. 3


Answer Clike
Which type of objects should you identify?

You plan to design an application that temporarily stores data in a SQL Azure database.
You need to identify which types of database objects can be used to store data for the application. The solution must ensure that the application can make changes to the schema of a temporary object during a session.
Which type of objects should you identify?
A. Common table expressions (CTEs)
B. Temporary stored procedures
C. Temporary tables
D. Table variables


Answer Clike
Which dynamic management view should you use?

You need to identify which long running transactions use an index.
Which dynamic management view should you use?
A. sys.dm_exec_query_optimizer_info
B. sys.dm_exec_connections
C. sys.dm_exec_query_stats
D. sys.dm_exec_sessions


Answer Clike
What should you do?

You create a table named Customers by using the following code segment:

You create a non-clustered index named IX_Name on the name column.
You write the following query to retrieve all of the customers that have a name that starts with the letters SMI:

You discover that the query performs a table scan.
You need to ensure that the query uses the index.
What should you do?
A. Replace LEFT(name,3) = ‘smi’ by using name like ‘smi%’
B. Replace LEFT(name,3) = ‘smi’ by using substring(name,l,3) = ‘smi’
C. Recreate IX_Name as a unique index
D. Recreate IX Name as a clustered index


Answer Clike
What should you do?

You have a SQL Server 2012 database named Database1.
Database1 has a table named Customers. Customers contains more than 1 million rows.
The database has a stored procedure that was created by using the following script:

You need to ensure that up_customers returns rows when the following statement is executed:
EXECUTE up_customers’1,2,3,4,5′;
What should you do?
A. Update @CustcmerTypelist to use the int data type.
B. Convert @CustomerTypeList to a table variable.
C. Convert @CustomerTypeList to an XML variable.
D. Update @CustomerTypeList to use the XML data type.


Answer Clike
What should you add to the query?

You have a database that contains a table named Department. Department contains the names and locations of each department.
You have a table-valued function named ProjectList() that returns a list of all the projects assigned to a department. The name of the department is passed as an argument to the ProjectList() function.
You need to create a query that returns a list of all the department names and the project names. The solution must return only departments that are associated to projects.
What should you add to the query?
A. OUTER APPLY
B. OUTER JOIN
C. CROSS JOIN
D. CROSS APPLY


Answer Clike
Which view should you identify?

You have a database that uses the following management views:
Sys.dm_os_volume_stats
Sys.dm_db_partition_stats
Sys.dm_db_file_space_usage
Sys.fulltext_indexes
You plan to migrate the database to Microsoft SQL Azure.
You need to identify which view can be used in SQL Azure.
Which view should you identify?
A. sys.fulltext_indexes
B. sys.dm_db_file_space_usage
C. sys.dm_os_volume_stats
D. sys.dm_db_partition_stats


Answer Clike
What should you add next to the beginning of each stored procedure?

You need to implement a solution that meets the data recovery requirements.
You update each stored procedure to accept a parameter named @transactionID.
What should you add next to the beginning of each stored procedure?
A. SAVE TRANSACTION WITH MARK @transactionID
B. ROLLBACK DISTRIBUTED TRANSACTION @transactionID
C. BEGIN TRANSACTION WITH MARK @transactionID
D. COMMIT TRANSACTION @transactionID


Answer Clike
What should you include in the recommendation?

You plan to create a database that has multiple tables. The tables will contain product information.
Each product has a stock-keeping unit (SKU).
You need to recommend a solution to ensure that each SKU starts with the letters "ADV" and is followed by 10 digits.
The solution must minimize the amount of development effort required.
What should you include in the recommendation?
A. A FOREIGN KEY constraint
B. A trigger
C. A user-defined data type
D. A CHECK constraint


Answer Clike
What should you change Proc1 to do?

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 Clike
Which query should you use?

You have a database that contains a user-defined function named Schema1.Udf1 and two tables named Schema1.Table1 and Schema1.Table2.
Schema1.Table1 has 1 million rows. The schema for Schema1.Table1 is configured as shown in the following table.

Schema1.Udf1 was defined by using the following code:

You need to write a query that will contain the following columns:
Country
CountryID
CustomerName
The solution must meet the following requirements:
Rows must be returned only if the function returns data.
The amount of time it takes the query to execute must be minimized.
Which query should you use?

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


Answer Clike
Which code segment should you add at line 06?

You have a database hosted on SQL Azure.
You are developing a script to create a view that will be used to update the data in a table.
The following is the relevant portion of the script. (Line numbers are included for reference only.)

You need to ensure that the view can update the data in the table, except for the data in Column1.
Which code segment should you add at line 06?
A. WITH CHECK OPTION
B. WITH VIEW_METADATA
C. WITH ENCRYPTION
D. WITH SCHEMABINDING


Answer Clike
What should you do?

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 Clike
What should you implement?

You have a database named database1.
Database developers report that there are many deadlocks.
You need to implement a solution to monitor the deadlocks. The solution must meet the following requirements:
Support real-time monitoring.
Be enabled and disabled easily.
Support querying of the monitored data.
What should you implement?
More than one answer choice may achieve the goal. Select the BEST answer.
A. Log errors by using trace flag 1222
B. Log errors by using trace flag 1204
C. A SQL Server Profiler template
D. An Extended Events session


Answer Clike
What should you modify?

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 Clike
Which advanced server option should you modify?

You have a database for a mission-critical web application. The database is stored on a SQL Server 2012 instance and is the only database on the instance.
The application generates all T-SQL statements dynamically and does not use stored procedures. You need to maximize the amount of memory available for data caching.
Which advanced server option should you modify?
A. Optimize for Ad hoc Workloads
B. Enable Contained Databases
C. Allow Triggers to Fire Others
D. Scan for Startup Procs


Answer Clike
What should you do?

You have an application that uses a view to access data from multiple tables.
You need to ensure that you can insert rows into the underlying tables by using the view.
What should you do?
A. Create an INSTEAD OF trigger on the view.
B. Define the view by using the SCHEMABINDING option.
C. Define the view by using the CHECK option.
D. Materialize the view.


Answer Clike
What should you do?

You have a table named Rooms that contains three columns.
You execute the following query:

You discover the execution plan shown in the exhibit. (Click the Exhibit button.)

You need to recommend a solution to reduce the amount of time it takes to execute the query.
What should you do?
More than one answer choice may achieve the goal. Select the BEST answer.
A. Include the RoomName column and the Position column in the Room_IX index.
B. Create a nonclustered index for RoomName, Id, and Position.
C. Create a clustered index for Id.
D. Use the WITH (INDEX(Room_IX),NOLOCK) query hint.


Answer Clike
What should you do?

You execute the following code:

You create a nonclustered index named IX_CustomerName on the CustomerName column.
You execute the following query:

You need to reduce the amount of time it takes to execute the query.
What should you do?
A. Partition the table and use the CustomerName column for the partition scheme.
B. Replace IX_CustomerName with a clustered index.
C. Replace LEFT(CustomerName ,1) = ‘a’ with CustomerName LIKE ‘a%’.
D. Replace LEFT(CustomerName ,1) = ‘a’ with SUBSTRING(CustomerName ,1,1) - ‘a’.


Answer Clike
What should you do?

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 Clike
Which code segment should you execute?

You review a query that runs slowly. The query accesses data in a table named Schema1.Table1.
The following is the relevant portion of the execution plan for the query:

You need to create the missing index.
Which code segment should you execute?
A. CREATE NONCLUSTERED INDEX IX1 on Schema1.Table1 (Column1) INCLUDE (Column4) WHERE Column2 <> Column3
B. CREATE NONCLUSTERED INDEX IX1 on Schema1.Table1 (Column1)
C. CREATE NONCLUSTERED INDEX IX1 on Schema1.Table1 (Column1, Column2, Column3) INCLUDE (Column4)
D. CREATE NONCLUSTERED INDEX IX1 on schema1.Table1 (Column1) INCLUDE (Column4)


Answer Clike
Which code segment should you add at line 03?

You use SQL Server 2012 to maintain the data used by the applications at your company.
You plan to create a table named Table1 by using the following statement. (Line numbers are included for reference only.)

You need to ensure that Table1 contains a column named UserName. The UserName column will:
Store string values in any language.
Accept a maximum of 200 characters.
Be case-insensitive and accent-insensitive.
Which code segment should you add at line 03?
A. UserName nvarchar(200) COLLATE Latin1_General_CS_AS NOT NULL,
B. UserName varchar(200) COLLATE Latin1_General_CI_AI NOT NULL,
C. UserName varchar(200) COLLATE Latin 1_General_CS_AS NOT NULL,
D. UserName nvarchar(200) COLLATE Latin1_General_CI_AI NOT NULL,


Answer Clike
Which code segment should you use?

You execute the following code:

You need to select the task that has an IsFinished value of true from the Project that has an Id value of 1.
Which code segment should you use?

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


Answer Clike
Which code segment should you use?

You are creating a table to support an application that will cache data outside of SQL Server.
The application will detect whether cached values were changed before it updates the values.
You need to create the table, and then verify that you can insert a row into the table.
Which code segment should you use?

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


Answer Clike
Which transaction isolation level should you use in sp1?

You use SQL Azure to store data used by an e-commerce application.
You develop a stored procedure named sp1. Sp1 is used to read and change the price of all the products sold on the e-commerce site.
You need to ensure that other transactions are blocked from updating product data while sp1 is executing.
Which transaction isolation level should you use in sp1?
A. Repeatable read
B. Read committed
C. Serializable
D. Snapshot


Answer Clike
Which isolation levels should you recommend?

You plan to deploy SQL Server 2012.
You are designing two stored procedures named USP_1 and USP_2 that have the following requirements:
Prevent data read by USP_1 from being modified by other active processes.
Prevent USP_2 from performing dirty reads.
You need to recommend the isolation level for each stored procedure. The solution must maximize concurrency.
Which isolation levels should you recommend?
To answer, drag the appropriate isolation level to the correct stored procedure in the answer area.
Select and Place:



Answer Clike
You use SQL Server 2014.

You use SQL Server 2014.
You need to create a single object that inserts a provided value into Table1, and then returns a count of the records in Table1.
Develop the solution by selecting and arranging the required code blocks in the correct order. You may not need all of the code blocks.
Select and Place:



Answer Clike
You are the senior database administrator at Contoso, Ltd. You manage a SQL Server 2014

You are the senior database administrator at Contoso, Ltd. You manage a SQL Server 2014
Instance, with multiple databases used for reporting.
You have recently hired a junior database administrator. You want this person to be able to view the database structures on the server, but you do not want him or her to be able to make changes or see the data in the tables.
The new hire’s login credentials are as follows:
Login name: JFree
Password: Jx672$qse
You want the new hire to be required to change his password on his next login.
The code that is produced should execute no matter the initial database context in which it is started.
You need to write the code required to give the new hire only the desired access, using the smallest number of steps. Develop the solution by selecting and arranging the required code blocks in the correct order. You may not need all of the code blocks.
Select and Place:



Answer Clike
How many servers should you identify?

You plan to install two SQL Server 2014 environments named Environment1 and Environment.
Your company identifies the following availability requirements for each environment:
Environment1 must have mirroring with automatic failover implemented.
Environment2 must have AlwaysOn with automatic failover implemented.
You need to identify the minimum number of SQL Server 2014 servers that must be deployed to each environment to ensure that all data remains available if a physical server fails.
How many servers should you identify?
To answer, drag the appropriate number to the correct environment in the answer area.
Select and Place:



Answer Clike
Which technologies should you identify?

You are designing a database for a university.
The database will contain two tables named Classes and Enrollment that have the following specifications:
Classes will store brochures in the XPS format. The brochures must be structured in folders and must be accessible by using UNC paths.
Enrollment will store information about students and their classes. Performance must be enhanced for queries of the current enrollments.
You need to identify which SQL Server technology meets the specifications of each table.
Which technologies should you identify?
To answer, drag the appropriate technology to the correct table in the answer area.
Select and Place:



Answer Clike
You use SQL Server 2014 Enterprise Edition.

You use SQL Server 2014 Enterprise Edition. Your database contains a partitioned table named AuditData. AuditData is partitioned by year. Partition 1 contains data from the year 2010 and prior.
Management has decided to archive all AUDITDATA records from 2010 and prior. Management wants the records to be removed from the database entirely and provided to the backup team as a zipped text file. The data must no longer reside in the database. There is very little tolerance for performance degradation in your environment.
You need to remove all 2010 and prior data from the AuditData table by using the least amount of system resources possible.
Develop the solution by selecting and arranging the required SQL actions in the correct order.
You may not need all of the actions.
Select and Place:



Answer Clike
What should you include in the recommendation?

You have a SQL Server 2014 environment That contains 20 servers.
The corporate security policy states that all SQL Server 2014 instances must meet specific security standards.
You need to recommend a management strategy for the SQL Server 2014 servers.
What should you include in the recommendation?
More than one answer choice may achieve the goal. Select the BEST answer.
A. Multi server jobs
B. Policy-Based Management
C. Common criteria compliance
D. Maintenance plans


Answer Clike
What should you include in your design?

You are planning to deploy a database to Windows Azure SQL Database.
You need to design a stored procedure to update rows. The stored procedure must meet the following requirements:
If the update fails, an error must be raised to the application and the update must be discarded.
The stored procedure must be designed to maximize concurrency.
What should you include in your design?
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:



Answer Clike
Which options should you recommend?

You plan to deploy a database to SQL Azure.
You must create two tables named Table 1 and Table 2 that will have the following specifications:
Table1 will contain a date column named Column1 that will contain a null value approximately 80 percent of the time.
Table2 will contain a column named Column2 which is the product of two other columns in Table2.
Both Table1 and Table2 will contain over one million rows.
You need to recommend which options must be defined for the columns. The solution must minimize the time it takes to retrieve data from the tables.
Which options should you recommend?
To answer, drag the appropriate options to the correct column in the answer area.
Select and Place:



Answer Clike
Which order should the four actions be performed?

You need to recommend the actions that are required to partition a table.
In which order should the four actions be performed?
To answer, move the actions from the list of actions to the answer area and arrange them in the correct order.
Select and Place:



Answer Clike
Which upgrade steps should you recommend?

You have two servers named SQL1 and SQL2 that have SQL Server 2012 installed. SQL1 contains a database that is mirrored asynchronously to SQL2. The database contents are updated once a month.
You need to upgrade the database to SQL Server 2014. The solution must minimize downtime.
Which upgrade steps should you recommend?
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:



Answer Clike
Which type of index storage should you recommend?

You have a server named SQL1 that has SQL Server 2014 installed. SQL1 hosts a database named Database! Database1 contains a table named Table1. Table1 is partitioned across five filegroups based on the TransactionType field.
The schema of Table1 is configured as shown in the following table.

Table1 contains the indexes shown in the following table.

You need to recommend an index strategy to maximize performance for the queries that consume the indexes available to Table1.
Which type of index storage should you recommend?
To answer, drag the appropriate index storage type to the correct index in the answer area.
Select and Place:



Answer Clike
Which features should you identify?

You plan to deploy three highly available SQL Server environments that will use SQL Server 2014.
You identify the following specifications for each environment as shown following table.

You need to recommend which high-availability feature is required for each environment.
Which features should you identify?
To answer, drag the appropriate feature to the correct requirement in the answer area.
Select and Place:



Answer Clike
Which features should you identify?

You plan to deploy a database by using SQL Server 2014.
Your company identifies the following requirements for the database:
The name of all stored procedures must start with "usp_"always.
All distribution statistics must be updated daily.
You need to identify which feature must be used to meet each database requirement.
Which features should you identify?
To answer, drag the appropriate feature to the correct database requirement in the answer area.
Select and Place:



Answer Clike
Which features should you identify?

You plan to deploy SQL Server 2014.
You identify the following security requirements for the deployment:
Users must be prevented from intercepting and reading the T-SQL statements sent from the clients to the database engine.
All database files and log files must be encrypted if the files are moved to another disk on another server.
You need to identify which feature meets each security requirement. The solution must minimize processor overhead.
Which features should you identify?
To answer, drag the appropriate feature to the correct requirement in the answer area.
Select and Place:



Answer Clike
Which technologies should you recommend?

You are designing an authentication strategy for a new server that has SQL Server 2014 installed.
The strategy must meet the following business requirements:
The account used to generate reports must be allowed to make a connection during certain hours only.
Failed authentication requests must be logged.
You need to recommend a technology that meets each business requirement. The solution must minimize the amount of events that are logged.
Which technologies should you recommend?
To answer, drag the appropriate solution to the correct business requirement in the answer area.
Select and Place:



Answer Clike
What should you include in the recommendation?

You need to recommend a backup process for data warehouse database.
The solution must meet the following requirements:
Ensure that if a hardware failure occurs, you can bring the database online without losing more than 24 hours of transactions.
Minimize the amount of administrative effort required to restore any lost data.
Minimize the space used by transaction logs.
What should you include in the recommendation?
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:



Answer Clike
Which features should you identify?

You plan to deploy SQL Server 2014.
Your company identifies the following monitoring requirements for the database;
An e-mail message must be sent if the SQL Server Authentication mode changes.
An e-mail message must be sent if CPU utilization exceeds 90 percent.
You need to identify which feature meets each monitoring requirement.
Which features should you identify?
To answer, drag the appropriate feature to the correct monitoring requirement in the answer area.
Select and Place:



Answer Clike
Which features should you identify?

You plan to deploy SQL Server 2014.
Your company identifies the following monitoring requirements:
Tempdb must be monitored for insufficient free space.
Deadlocks must be analyzed by using Deadlock graphs.
You need to identify which feature meets each monitoring requirement.
Which features should you identify?
To answer, drag the appropriate feature to the correct monitoring requirement in the answer area.
Select and Place:



Answer Clike
What should you include in the recommendation?

You have a SQL Server 2014 environment That contains 20 servers.
The corporate security policy states that all SQL Server 2014 instances must meet specific security standards.
You need to recommend a management strategy for the SQL Server 2014 servers.
What should you include in the recommendation?
More than one answer choice may achieve the goal. Select the BEST answer.
A. Multi server jobs
B. Policy-Based Management
C. Common criteria compliance
D. Maintenance plans


Answer Clike
Which columns should you designate as SPARSE?

You use SQL Server 2014. You create a table within a database by using the following DDL:

The following table illustrates a representative sample of data:

The system is expected to handle 50 million orders a month over the next five years.
You have been instructed by your Team Lead to follow best practices for storage and performance in the utilization of SPARSE columns.
Which columns should you designate as SPARSE? To answer, mark each column as SPARSE or
NOT SPARSE in the answer area.
Hot Area:



Answer Clike
Which isolation levels should you recommend?

You plan to deploy a database to SQL Azure.
You are designing two stored procedures named USP_1 and USP_2 that have the following requirements:
Prevent data read by USP_1 from being modified by other active processes.
Allow USP_2 to perform dirty reads.
You need to recommend the isolation level for the stored procedures.
The solution must maximize concurrency.
Which isolation levels should you recommend?
To answer, drag the appropriate isolation level to the correct stored procedure in the answer area.
Select and Place:



Answer Clike
Which data types should you recommend for each column?

You have a SQL Azure database named Database1.
You need to design the schema for a table named table1. Table1 will have less than one million rows. Table1 will contain the following information for each row:

The solution must minimize the amount of space used to store each row.
Which data types should you recommend for each column?
To answer, drag the appropriate data type to the correct column in the answer area.
Select and Place:



Answer Clike
What should you do?

You are the administrator for a SQL Server 2014 instance that stores the data for an online transaction processing sales system.
The company takes full backups every week; differential backups on the days with no full backups; and hourly transaction backups. These backups are stored on a backup server in the company’s data center.
Every week, the company places the full backup on a tape and sends it to a third-party backup storage system.
The company is worried that a disaster might occur that could destroy their computer center and cause them to lose orders.
You need to determine the best method for providing the smallest amount of data loss and downtime without leasing or purchasing additional physical locations.
What should you do? More than one answer choice may achieve the goal. Select the BEST answer.
A. Set up SQL Server Always On with a SQL Azure database as a replica.
B. Set up SQL Server Always On by using a SQL Server on a Windows Azure Virtual Machine.
C. Put the differential backup on tape and send it to the third-party backup storage system.
D. Use the Microsoft SQL Server Backup to Microsoft Windows Azure Tool to direct all backups to a different geographical location.


Answer Clike
Which server should you recommend?

You have a SQL Server 2014 environment That includes four servers. The servers are configured as shown in the following table.

You plan to configure Policy-Based Management to enforce the following rules:
On Server1, enable SQL Server password policies and enable the default trace.
On Server3, ensure that the names of user-defined stored procedures begin with the prefix "usp_" and ensure that all databases use a casesensitive collation.
You need to recommend which server you must configure as a Central Management Server.
Which server should you recommend? (Each correct answer presents a complete solution. Choose all that apply.)
A. Server1
B. Server2
C. Server3
D. Seiver4


Answer Clike
What should you recommend?

You deploy a database by using SQL Server 2014. The database contains a table named Table1.
You need to recommend a solution to track all of the deletions executed on Table1. The solution must minimize the amount of custom code required.
What should you recommend?
A. Change data capture
B. Statistics
C. A trigger
D. Master Data Services


Answer Clike
What should you do?

You are designing a SQL Server database for an order fulfillment system.
You create a table named Sales.Orders by using the following script:

Each order is tracked by using one of the following statuses:
Fulfilled
Shipped
Ordered
Received
You need to design the database to ensure that you can retrieve the status of an order on a given date. The solution must ensure that new statuses can be added in the future.
What should you do?
More than one answer choice may achieve the goal. Select the BEST answer.
A. Implement change data capture on the Sales.Orders table.
B. To the Sales.Orders table, add a column named Status that will store the order status. Update the Status column as the order status changes.
C. Create a new table named Sales.OrderStatus that contains three columns named OrderID, StatusDate, and Status. Insert new rows into the table as the order status changes.
D. To the Sales.Orders table, add three columns named FulfilledDate, ShippedDate, and ReceivedDate. Update the value of each column from null to the appropriate date as the order status changes.


Answer Clike
What should you include in the recommendation?

You have a server named Server1 that has 2 processors.
You plan to deploy multiple instances of SQL Server 2014 to Server1. Each instance will have multiple databases.
You need to recommend a method to allocate processor time to each database.
What should you include in the recommendation?
More than one answer choice may achieve the goal. Select the BEST answer.
A. Resource Governor
B. Max Degree of Parallelism
C. Windows System Resource Manager (WSRM)
D. Processor affinity


Answer Clike
What should you include in the recommendation?

You have a SQL Server 2014 database named DB1.
You plan to import a large number of records from a SQL Azure database to DB1.
You need to recommend a solution to minimize the amount of space used in the transaction log during the import operation.
What should you include in the recommendation?
A. The bulk-logged recovery model
B. The full recovery model
C. A new partitioned table
D. A new log file
E. A new file group


Answer Clike
What should you recommend?

You have a server that has SQL Server 2014 installed. The server contains 100 user databases.
You need to recommend a backup solution for the user databases.
The solution must meet the following requirements:
Perform a transaction log backup every hour.
Perform a full backup of each database every week.
Perform a differential backup of each database every day.
Ensure that new user databases are added automatically to the backup solution.
What should you recommend?
More than one answer choice may achieve the goal. Select the BEST answer.
A. Policy-Based Management
B. A Data Definition Language (DDL) trigger
C. SQL Server Agent jobs
D. A maintenance plan


Answer Clike
Which method should you recommend?

You have a database hosted on SQL Server 2012 R2. The database contains 5 million rows.
You need to recommend a repeatable method to migrate the database to SQL Azure.
Which method should you recommend? More than one answer choice may achieve the goal.
Select the BEST answer.
A. Create a SQL Server Integration Services (SSIS) package, and then run the package.
B. Back up the database, and then restore the database.
C. Extract a data-tier application, and then import the application.
D. Generate scripts to create all of the all database objects and all of the data, and then execute the scripts by using SQL Azure.


Answer Clike
What should you recommend?

You are designing a database named DB1.
Changes will be deployed to DB1 every Wednesday night.
You need to recommend a strategy to deploy the changes to DB1. The strategy must meet the following requirements:
The strategy must not disrupt backup operations.
DB1 must be online while the changes are deployed.
You must be able to undo quickly any changes made to objects.
What should you recommend?
More than one answer choice may achieve the goal. Select the BEST answer.
A. Perform a copy-only database backup before the changes are deployed. If the deployment fails, restore the database to another server and recover the original objects from the restored database.
B. Create a database snapshot. If the deployment fails, recover the objects from the database snapshot.
C. Create a database snapshot. If the deployment fails, revert the database to the database snapshot.
D. Perform a full database backup before the changes are deployed. If the deployment fails, restore the database to another server and recover the original objects from the restored database.


Answer Clike
What should you include in the recommendation?

You are designing a monitoring application for a new SQL Server 2014 instance.
You need to recommend a solution to generate a report that displays the 10 most frequent wait types that occur for the instance.
What should you include in the recommendation?
More than one answer choice may achieve the goal. Select the BEST answer.
A. The SQL Server error log
B. The sys.dm_os_wait_stats dynamic management view
C. The DBCC SQLPERF(WAITSTATS) command
D. SQL Server Profiler


Answer Clike
What should you include in the design?

You have four databases that are accessed by using an Online Transaction Processing (OLTP) application. The databases are stored on a server named SQL1 that has SQL Server 2014 installed.
You plan to deploy an additional server that has SQL Server 2014 installed.
You need to design a high-availability solution for the databases that meets the following requirements:
If SQL1 fails, the databases must be available.
Users must be able to run reports against a secondary copy of the databases.
What should you include in the design?
More than one answer choice may achieve the goal. Select the BEST answer.
A. AlwaysOn availability groups
B. Database mirroring
C. Log shipping
D. Failover Clustering


Answer Clike
What queries are being executed

You administer a SQL Server 2014 instance.
Users report that the SQL Server has seemed slow today. A large database was being restored for much of the day, which could be causing issues.
You want to write a query of the system views that will report the following:
Number of users that have a connection to the server
Whether a user’s connection is active
Whether any connections are blocked
What queries are being executed
Whether the database restore is still executing and, if it is, what percentage of the restore is complete.
Which system objects should you use in your query to best achieve this task?
A. sys.dm_exec_requests, sys.dm_exec_sessions, sys.objects
B. sys.dm_exec_sessions, sys.dm_exec_query_stats, sys.dm_exec_query_text,sys.objects
C. sys.sysprocesses, sys.dm_exec_query_text, sys.objects
D. sys.dm_exec_requests, sys.dm_exec_sessions, sys.dm_exec_query_text


Answer Clike
What should you do?

You are troubleshooting an application that runs a query. The application frequently causes deadlocks.
You need to identify the isolation level used by the query when a deadlock occurs.
What should you do?
More than one answer choice may achieve the goal. Select the BEST answer.
A. Query the sys.dm_exec_requests dynamic management view.
B. Create a trace in SQL Server Profiler that contains the Deadlock graph event.
C. Query the sys.dm_exec_sessions dynamic management view.
D. Enable trace flag 1222, and then view the SQL Server error log.


Answer Clike
What should you recommend?

You have a query that is used by a reporting dashboard.
Users report that the query sometimes takes a long time to run. You need to recommend a solution to identify what is causing the issue.
What should you recommend?
More than one answer choice may achieve the goal. Select the BEST answer.
A. Set the blocked process threshold, and then run SQL Server Profiler.
B. Set the blocked process threshold, and then create an alert.
C. Enable trace flag 1204, and then create an alert.
D. Create a job that queries the sys.dm_os_waiting_tasks dynamic management view.


Answer Clike
What should you use?

You have two SQL Server 2014 instances named SQLDev and SQLProd.
You plan to create a new database on SQLProd that will use SQL Server Authentication.
You need to ensure that when the new database is copied from SQLProd to SQLDev, users can connect to the database on SQLDev even if they do not have a login on the SQLDev instance.
What should you use?
More than one answer choice may achieve the goal. Select the BEST answer.
A. Triggers
B. Contained database
C. SQL Server Analysis Services (SSAS) scripts
D. Extended Events
E. SQL Server Integration Services (SSIS) scripts


Answer Clike
What should you recommend?

You plan to create a database.
The database will be used by a Microsoft .NET application for a special event that will last for two days.
During the event, data must be highly available.
After the event, the database will be deleted.
You need to recommend a solution to implement the database while minimizing costs. The solution must not affect any existing applications.
What should you recommend?
More than one answer choice may achieve the goal. Select the BEST answer.
A. SQL Server 2014 Enterprise
B. SQL Server 2014 Standard
C. SQL Azure
D. SQL Server 2014 Express with Advanced Services


Answer Clike
What should you include in the design?

You are building a stored procedure for a Windows Azure SQL Database. The procedure will add multiple rows to a table.
You need to design the stored procedure to meet the following requirements:
If any of the new rows violates a table constraint, then no further additions must be attempted and all changes made by the stored procedure must be discarded.
If any errors occur, a row must be added to an audit table, and the original error must be returned to the caller of the stored procedure.
What should you include in the design?
A. An explicit transaction that has XACT_ABORT disabled
B. An implicit transaction that has error handling enabled
C. An explicit transaction that has error handling enabled
D. An implicit transaction that has XACT.ABORT enabled


Answer Clike
What should you recommend?

You are creating a database that will store usernames and credit card numbers for an application.
You need to recommend a solution to store and reuse the credit card numbers in the database.
What should you recommend? More than one answer choice may achieve the goal. Select the BEST answer.
A. Data encryption
B. Transparent Data Encryption (TDE)
C. Encrypting File System (EPS)
D. Data hashing


Answer Clike
What should you recommend?

You are creating a database that will store usernames and passwords for an application.
You need to recommend a solution to store the passwords in the database.
What should you recommend?
More than one answer choice may achieve the goal. Select the BEST answer.
A. One-way encryption
B. Encrypting File System (EFS)
C. Transparent Data Encryption (TDE)
D. Reversible encryption


Answer Clike
What should you include in the recommendation?

You have a server named Server1 that has 16 processors.
You plan to deploy multiple instances of SQL Server 2014 to Server1.
You need to recommend a method to allocate processors to each instance.
What should you include in the recommendation?
More than one answer choice may achieve the goal. Select the BEST answer.
A. Processor affinity
B. Windows System Resource Manager (WSRM)
C. Max Degree of Parallelism
D. Resource Governor


Answer Clike
What should you include in the recommendation?

You have two databases named DB1 and DB2 that are located on the same server.
You plan to create a stored procedure named SProc1 in DB1.
SProc1 will query a table named Table2 in DB2.
You need to recommend a solution to ensure that SProc1 can access Table2 without granting users direct access to Table2.
What should you include in the recommendation?
More than one answer choice may achieve the goal. Select the BEST answer.
A. Contained databases
B. Application roles
C. Cross-database ownership chaining
D. Digital certificates


Answer Clike
What should you do?

You are troubleshooting an application that runs a query. The application frequently causes deadlocks.
You need to identify which transaction causes the deadlock.
What should you do?
More than one answer choice may achieve the goal. Select the BEST answer.
A. Query the sys.dm_exec_sessions dynamic management view.
B. Query the sys.dm_exec_requests dynamic management view.
C. Create a trace in SQL Server Profiler that contains the Deadlock graph event
D. Create an extended events session to capture deadlock information.


Answer Clike
What should you do?

You are the new database administrator for a SQL Server 2014 instance.
You conduct an assessment on the instance and determine that the auto create statistics setting on the database named DB1 has been turned off. You see no evidence that any maintenance has been occurring.
You want to set up monitoring to see if query performance is being affected.
You need to set up a monitoring process that will capture any cases where statistics could have been useful if they existed.
What should you do?
A. Create a SQL Server Agent job to execute DBCC SHOWSTATISTICS on each of the primary key columns in the database.
B. Use the missing_column_statistics extended event.
C. Query the sys.statistics system view to see all cases where the statistics were last needed.
D. Write a query using the sys.dm_db_missing_index_group_stats DMV Joining to sys.indexes, filtering on is_hypothetical.


Answer Clike
Which method should you use?

You manage a SQL Server 2014 instance that contains a database named DB1.
Users report that some queries to DB1 take longer than expected. Although most queries run in less than one second, some queries take up to 20 seconds to run.
You need to view all of the performance statistics for each database file.
Which method should you use?
A. Query the sys.dm_os_tasks dynamic management view.
B. Query the sys.dm_os_performance_counters dynamic management view.
C. Query the sys.dm_io_virtual_file_stats dynamic management function.
D. Examine the Data File I/O pane in Activity Monitor.


Answer Clike
What should you recommend?

You have two SQL Server instances named SQLDev and SQLProd that have access to various storage media.
You plan to synchronize SQLDev and SQLProd.
You need to recommend a solution that meets the following requirements:
The database schemas must be synchronized from SQLDev to SQLProd.
The database on SQLDev must be deployed to SQLProd by using a package.
The package must support being deployed to Windows Azure SQL Database.
What should you recommend?
More than one answer choice may achieve the goal. Select the BEST answer.
A. A database snapshot
B. SQL Server Integration Services (SSIS)
C. Change data capture
D. A data-tier application


Answer Clike
What should you recommend?

Your network contains an Active Directory domain that has two groups named Group1 and Group2.
The domain contains two SQL Server instances named SQLDev and SQLProd. Each SQL Server instance has access to various storage media.
The SQL Server instances have a database that contains a table named Table1.
Table1 contains a column named Column1. The value for Column1 can be either Value1 or Value2.
You need to recommend a solution to ensure that users in Group1 can retrieve only rows from Column1 that contain the value of Value1.
What should you recommend?
A. A dynamic management view
B. Filegroups
C. Snapshot isolation
D. User-defined views


Answer Clike
Which code should you use?

You use SQL Server 2014 to maintain the data used by applications at your company.
You want to execute two statements.
You need to guarantee that either both statements succeed, or both statements fail together as a batch.
Which code should you use?

A. Option A
B. Option B
C. Option C
D. Option D
E. Option E


Answer Clike
What should you recommend?

You deploy a SQL Server instance named SQLProd that uses SQL Server 2014.
You need to recommend a solution to monitor the transactions that are running currently against SQLProd. The solution must minimize the amount of custom code required.
What should you recommend?
A. Statistics
B. A dynamic management view
C. A trigger
D. User-defined views


Answer Clike
Which SQL elements should you include in the recommendation?

You have a SQL Server 2014 instance named SQL1.
SQL1 creates error events in the Windows Application event log.
You need to recommend a solution that will run an application when SQL1 logs a specific error in the Application log.
Which SQL elements should you include in the recommendation? (Each correct answer presents part of the solution. Choose all that apply.)
A. A policy
B. A maintenance plan
C. An alert
D. A job
E. A trigger


Answer Clike
What should you do?

You have a database named DB1.
You plan to create a stored procedure that will insert rows into three different tables. Each insert must use the same identifying value for each table, but the value must increase from one invocation of the stored procedure to the next.
Occasionally, the identifying value must be reset to its initial value.
You need to design a mechanism to hold the identifying values for the stored procedure to use.
What should you do?
More than one answer choice may achieve the goal. Select the BEST answer.
A. Create a sequence object that holds the next value in the sequence. Retrieve the next value by using the stored procedure. Reset the value by using an ALTER SEQUENCE statement as needed.
B. Create a sequence object that holds the next value in the sequence. Retrieve the next value by using the stored procedure. Increment the sequence object to the next value by using an ALTER SEQUENCE statement. Reset the value as needed by using a different ALTER SEQUENCE statement.
C. Create a fourth table that holds the next value in the sequence. At the end each transaction, update the value by using the stored procedure. Reset the value as needed by using an UPDATE statement.
D. Create an identity column in each of the three tables. Use the same seed and the same increment for each table. Insert new rows into the tables by using the stored procedure. Use the DBCC CHECKIDENT command to reset the columns as needed.


Answer Clike
What should you recommend?

You have a SQL Server instance on a server named Server1.
You need to recommend a solution to perform the following tasks every week:
Rebuild the indexes by using a new fill factor.
Run a custom T-SQL command.
Back up the databases.
What should you recommend?
More than one answer choice may achieve the goal. Select the BEST answer.
A. A trigger
B. An alert
C. A maintenance plan
D. Windows PowerShell
E. A system policy


Answer Clike
What should you recommend?

You need to recommend a solution for the error handling of USP_4. The solution must handle errors for nested stored procedures in the code for USP_4.
What should you recommend?
A. Use the @@ERROR variable in the nested stored procedures.
B. Use the @@ERROR variable in USP_4.
C. Use the RAISERROR command in the nested stored procedures.
D. Use the RAISERROR command in USP_4.


Answer Clike
What should you recommend replacing Table1?

You need to recommend a solution to minimize the amount of time it takes to execute USP_1.
With what should you recommend replacing Table1?
A. A view
B. A temporary table
C. A table variable
D. A function


Answer Clike
You need to design the database to ensure that that you can retrieve the following information:

You are designing a Windows Azure SQL Database for an order fulfillment system. You create a table named Sales.Orders with the following script.

Each order is tracked by using one of the following statuses:
Fulfilled
Shipped
Ordered
Received
You need to design the database to ensure that that you can retrieve the following information:
The current status of an order
The previous status of an order.
The date when the status changed.
The solution must minimize storage.
More than one answer choice may achieve the goal. Select the BEST answer.
A. To the Sales.Orders table, add three columns named Status, PreviousStatus and ChangeDate.
Update rows as the order status changes.
B. Create a new table named Sales.OrderStatus that contains three columns named OrderID, StatusDate, and Status. Insert new rows into the table as the order status changes.
C. Implement change data capture on the Sales.Orders table.
D. To the Sales.Orders table, add three columns named FulfilledDate, ShippedDate, and ReceivedDate. Update the value of each column from null to the appropriate date as the order status changes.


Answer Clike
What should you recommend?

You create a stored procedure that retrieves all of the rows from a table named Table1.
You need to recommend a solution to ensure that all of the statements in the stored procedure can be executed if another transaction is modifying rows in Table1 simultaneously.
What should you recommend?
A. Snapshot isolation
B. A database snapshot
C. Filegroups
D. Indexes


Answer Clike
Which change should you recommend?

You need to recommend a change to USP_3 to ensure that the procedure completes only if all of the UPDATE statements complete.
Which change should you recommend?
A. Set the XACT_ABORT option to off
B. Set the XACT_ABORT option to on.
C. Set the IMPLICIT_TRANSACTIONS option to off.
D. Set the IMPLICIT_TRANSACTIONS option to on.


Answer Clike
Which change should you recommend?

You need to recommend a change to USP_3 to ensure that the procedure continues to execute even if one of the UPDATE statements fails.
Which change should you recommend?
A. Set the XACT_ABORT option to off.
B. Set the XACT_ABORT option to on.
C. Set the IMPLICIT_TRANSACTIONS option to off.
D. Set the IMPLICIT_TRANSACTIONS option to on.


Answer Clike
What should you include in the recommendation?

You need to recommend a solution to minimize the amount of time it takes to execute USP_5.
What should you include in the recommendation?
A. Enable cross-database chaining.
B. Use a server role to group all logins.
C. Use the EXECUTE AS clause in USP_5.
D. Copy USP.5 to each database.


Answer Clike
What should you recommend?

You need to recommend a solution for the planned changes to the customer classifications.
What should you recommend? (Each correct answer presents part of the solution. Choose all that apply.)
A. Add a row to the Customers table each time a classification changes.
B. Add columns for each classification to the Customers table.
C. Add a table to track any changes made to the classification of each customer.
D. Add a column to the Classifications table to track the status of each classification.
E. Implement change data capture.


Answer Clike
What should you include in the recommendation?

You need to recommend a solution to ensure that USP_4 adheres to the security requirements.
What should you include in the recommendation?
A. Enable SQL Server Audit.
B. Enable trace flags.
C. Configure data manipulation language (DML) triggers.
D. Enable C2 audit tracing.


Answer Clike
What should you include in the recommendation?

You need to recommend a solution to meet the security requirements of the junior database administrators.
What should you include in the recommendation?
A. A server role
B. A database role
C. A credential
D. A shared login


Answer Clike
What should you include in the recommendation?

You need to recommend a disaster recovery strategy for the Inventory database.
What should you include in the recommendation?
A. Log shipping
B. SQL Server Failover Clustering
C. AlwaysOn availability groups
D. Peer-to-peer replication


Answer Clike
What should you recommend replacing Table1?

You need to recommend a solution to minimize the amount of time it takes to execute USP_1.
With what should you recommend replacing Table1?
A. An indexed view
B. A function
C. A table variable
D. A temporary table


Answer Clike
What should you include in the recommendation?

You need to recommend a disaster recovery solution for the Dev database.
What should you include in the recommendation?
A. The simple recovery model and full backups
B. The bulk-logged recovery model and full backups
C. The full recovery model, full backups, and differential backups
D. The full recovery model, full backups, and transaction log backups


Answer Clike
What should you recommend?

You need to recommend a solution for the error handling of USP_3. The solution must minimize the amount of custom code required.
What should you recommend?
A. Use the @@ERROR variable in the nested stored procedures.
B. Use a TRY CATCH block in the called stored procedures.
C. Use the @@ERROR variable in the called stored procedures.
D. Use the RAISERROR command in the nested stored procedures.


Answer Clike
What should you recommend?

You need to recommend a solution to minimize the amount of time it takes to execute USP_2.
What should you recommend?
A. A database snapshot
B. A table variable
C. A temporary table
D. Snapshot isolation


Answer Clike
You need to create the CustomerAccess table to support the reporting and performance requirements.

You need to create the CustomerAccess table to support the reporting and performance requirements.
Develop the solution by selecting and arranging the required code blocks in the correct order. You may not need all of the code blocks.
Select and Place:



Answer Clike
Which code fragment should you use in each location in the command to complete one of the commands you will need to include in the job?

You need to create a job to automate some database maintenance tasks.
Which code fragment should you use in each location in the command to complete one of the commands you will need to include in the job? To answer, drag the appropriate lines of code to the correct locations in the command. Each line of code may be used once, more than once, or not at all. You may need to drag the split bar between panes or scroll to view content.
Select and Place:



Answer Clike
Which function should you assign to each server?

You need to distribute functionality across the three servers.
Which function should you assign to each server? To answer, drag the appropriate functions to the correct servers. Each function may be used once, more than once, or not at all. You may need to drag the split bar between panes or scroll to view content.
Select and Place:



Answer Clike
The business requires a satellite office to have a local copy of the data to report against.

The business requires a satellite office to have a local copy of the data to report against.
You want to implement a solution to support the requirements. You need to establish a new
Availability Group between the two servers.
Develop the solution by selecting and arranging the required code blocks in the correct order. You may not need all of the code blocks.
Select and Place:



Answer Clike
Which two database options should you change to meet the requirements?

You need to change the ProdDB database.
Which two database options should you change to meet the requirements? Each correct answer presents part of the solution. Choose two.
A. CONTAINS FILESTREAM
B. Change recovery model to FULL
C. CONTAINMENT = PARTIAL
D. Change recovery model to BULK_LOGGED
E. COLLATE IN.MEMORY
F. CONTAINS MEMORY OPTIMIZED DATA


Answer Clike
Which two actions should you perform?

You need to configure security on the Product table for customer support representatives.
Which two actions should you perform? Each correct answer presents part of the solution. Choose two.
A. Create a view called CustProduct that includes columns ProductID. ProductName, Product Description, QuantityOnHand, ProductPrice, ProductCost, and ProductSupplierID.
B. GRANT ALL on CustProduct TO OurDomainCustomerSupport
C. Create a user-defined data type called CustProduct that includes columns ProductID, ProductName, Product Description, and ProductPrice.
D. Create a view called CustProduct that includes columns ProductID, ProductName, Product Description, QuantityOnHand, and ProductPrice.
E. GRANT SELECT on CustProduct TO OurDomainCustomerSupport.
F. GRANT SELECT on CustProduct TO public.


Answer Clike
Which command should you use only during the monthly maintenance window?

You are designing your maintenance plan.
Which command should you use only during the monthly maintenance window?
A. DBCC INDEXDEFRAG (ProdDB, SalesOrderDetail, SODIndex)
B. ALTER INDEX SODIndex ON SalesOrderDetail REORGANIZE
C. ALTER INDEX SODIndex ON SalesOrderDetail REBUILD
D. ALTER INDEX SODIndex ON SalesOrderDetail REBUILD WITH (ONLINE * ON)


Answer Clike
Which two actions should you perform?

You need to implement a backup strategy to support the requirements.
Which two actions should you perform? Each correct answer presents part of the solution. Choose two.
A. Create a credential called MyCredential on SQL Server by using a Windows domain account and password.
B. Schedule a full backup by using the command BACKUP DATABASE ProdDB TO DISK…
C. Create a share on your Windows Azure site by using your Windows Azure storage account information, and grant permission to the SQL Server service login.
D. Schedule a full backup by using the command BACKUP DATABASE ProdDB TO URL … WTTH CREDENTIAL=N’MyCredential’
E. Create a share on the hot standby site and grant permission to the SQL Server service login.
F. Create a credential called MyCredential on SQL Server, using MyStorageAccount for the storage account name and StorageAccountKey for the access key.
G. Schedule a full backup by using the command BACKUP DATABASE ProdDB TO SHARE …
WITH CREDENTIAL=N’ MyCredential’


Answer Clike
Which three actions should you perform?

You need to implement changes to the system to reduce contention and improve performance of the SalesOrderDetail table.
Which three actions should you perform? Each correct answer presents part of the solution.
Choose three.
A. Use (SNAPSHOT] hints in the report queries
B. ALTER DATABASE [ProdDB] SET READ_COMMITTED_SNAPSHOT ON
C. ALTER DATABASE [ProdDB] SET READ_COMMITTED_SNAPSHOT OFF
D. SET TRANSACTION ISOLATION LEVEL SNAPSHOT
E. Use (TABLOCK) hints in the report queries
F. SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
G. ALTER DATABASE [ProdDB] SET ALLOW.SNAPSHOT ISOLATION ON
H. Use (SNAPSHOT] hints in the update statements


Answer Clike
Which code should you use?

You need to write code that will allow the sales force to retrieve data for their reports with the least amount of effort.
Which code should you use?

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


Answer Clike
What should you recommend changing?

You need to recommend changes to the ERP application to resolve the search issue. The solution must minimize the impact on other queries generated from the ERP application.
What should you recommend changing?
A. The collation of the Products table
B. The index on the ProductName column
C. The collation of the ProductName column
D. The data type of the ProductName column


Answer Clike
What should you recommend?

You need to recommend a solution that addresses the concurrency requirement.
What should you recommend?
A. Break each stored procedure into two separate procedures, one that changes Sales.Table1 and one that changes Sales.Table2.
B. Make calls to Sales.Proc1 and Sales.Proc2 synchronously.
C. Call the stored procedures in a Distributed Transaction Coordinator (DTC) transaction.
D. Modify the stored procedures to update tables in the same order for all of the stored procedures.


Answer Clike
What should you include in the recommendation?

You need to recommend a solution that addresses the backup issue. The solution must minimize the amount of development effort.
What should you include in the recommendation?
A. Indexed views
B. Filegroups
C. Table partitioning
D. Indexes


Answer Clike
What should you recommend?

You need to recommend a solution that addresses the security requirement.
What should you recommend?
A. Revoke user permissions on the tables. Create stored procedures that manipulate data. Grant the users the EXECUTE permission on the stored procedures.
B. Grant the users the SELECT permission on the tables. Create views that retrieve data from the tables. Grant the users the SELECT permission on the views.
C. Deny the users SELECT permission on the tables. Create views that retrieve data from the tables. Grant the users the SELECT permission on the views.
D. Deny the users the SELECT permission on the tables. Create stored procedures that manipulate data. Grant the users the EXECUTE permission on the stored procedures.


Answer Clike
What should you include in the recommendation?

You need to recommend a solution that addresses the file storage requirements.
What should you include in the recommendation?
A. FileStream
B. FileTable
C. The varbinary data type
D. The image data type


Answer Clike
What should you include in the recommendation?

You need to recommend a solution that addresses the installation issues.
What should you include in the recommendation?
A. Windows logins
B. Server roles
C. Contained users
D. Database roles


Answer Clike
What should you include in the recommendation?

You need to recommend a solution that reduces the time it takes to import the supplier data.
What should you include in the recommendation?
A. Enable instant file initialization.
B. Reorganize the indexes.
C. Disable Resource Governor.
D. Enable Auto Update Statistics.


Answer Clike
What should you include in the recommendation?

You need to recommend a solution that meets the data recovery requirement.
What should you include in the recommendation?
A. A differential backup
B. A transaction log backup
C. Snapshot isolation
D. A database snapshot


Answer Clike
What should you include in the recommendation?

You need to recommend a solution that addresses the index fragmentation and index width issue.
What should you include in the recommendation? (Each correct answer presents part of the solution. Choose all that apply.)
A. Change the data type of the lastModified column to smalldatetime.
B. Remove the lastModified column from the clustered index.
C. Change the data type of the modifiedBy column to tinyint.
D. Change the data type of the id column to bigint.
E. Remove the modifiedBy column from the clustered index.
F. Remove the id column from the clustered index.


Answer Clike
What should you recommend?

You need to recommend a solution that resolves the missing data issue. The solution must minimize the amount of development effort.
What should you recommend?
A. Denormalize the Products table.
B. Denormalize the OrderDetails table.
C. Normalize the OrderDetails table.
D. Normalize the Products table.


Answer Clike
Which isolation level should recommend?

You need to recommend an isolation level for usp_UpdateOrderDetails.
Which isolation level should recommend?
A. Read committed
B. Repeatable read
C. Read uncommitted
D. Serializable


Answer Clike
What should you recommend?

You need to recommend a solution to synchronize Database2 to App1_Db1.
What should you recommend?
A. Change data capture
B. Snapshot replication
C. Master Data Services
D. Transactional replication


Answer Clike
What should you include in the recommendation?

You need to recommend a database reporting solution that meets the business requirements.
What should you include in the recommendation?
A. Data collection
B. Performance Monitor
C. A maintenance plan
D. A dynamic management view


Answer Clike
What should you include in the recommendation?

You need to recommend a solution for the deployment of SQL Server 2014. The solution must meet the business requirements.
What should you include in the recommendation?
A. Create a new instance of SQL Server 2014 on the server that hosts the SQL Server 2008 instance.
B. Upgrade the existing SQL Server 2008 instance to SQL Server 2014.
C. Deploy two servers that have SQL Server 2014 installed and implement Failover Clustering.
D. Deploy two servers that have SQL Server 2014 installed and implement database mirroring.


Answer Clike
What should you include in the recommendation?

You need to recommend a solution to improve the performance of usp.UpdateInventory. The solution must minimize the amount of development effort.
What should you include in the recommendation?
A. A table variable
B. A common table expression
C. A subquery
D. A cursor


Answer Clike
What should you include in the recommendation?

You need to recommend a disk monitoring solution that meets the business requirements.
What should you include in the recommendation?
A. An audit
B. A dynamic management view
C. A maintenance plan
D. A SQL Server Agent alert


Answer Clike
What should you include in the recommendation?

You need to recommend a solution for Application1 that meets the security requirements.
What should you include in the recommendation?
A. Signed stored procedures
B. Certificate Authentication
C. Encrypted columns
D. Secure Socket Layer (SSL)


Answer Clike
What should you recommend?

You need to recommend a solution to allow application users to perform tables. The solution must meet the business requirements.
What should you recommend?
A. Create a Policy-Based Management Policy.
B. Create a user-defined database role and add users to the role.
C. Create stored procedures that use EXECUTE AS clauses.
D. Create functions that use EXECUTE AS clauses.


Answer Clike
What should you include in the recommendation?

You need to recommend a feature to support your backup solution.
What should you include in the recommendation?
A. Transparent Data Encryption (TDE)
B. Column-level encryption
C. An NTFS file permission
D. A Secure Sockets Layer (SSL)


Answer Clike
You need to implement a new version of usp_AddMobileLocation.

You need to implement a new version of usp_AddMobileLocation. Develop the solution by selecting and arranging the required code blocks in the correct order. You may not need all of the code blocks.
Select and Place:



Answer Clike
You need to redesign the system to meet the scalability requirements of the application.

You need to redesign the system to meet the scalability requirements of the application.
Develop the solution by selecting and arranging the required code blocks in the correct order.
You may not need all of the code blocks.
Select and Place:



Answer Clike
Which task should you use with each maintenance step?

You need to optimize the index and table structures for POSTransaction.
Which task should you use with each maintenance step? To answer, drag the appropriate tasks to the correct maintenance steps. Each task may be used once, more than once, or not at all. You may need to drag the split bar between panes or scroll to view content.
Select and Place:



Answer Clike
What should you recommend?

During performance testing, you discover that database INSERT operations against the Inventory table are slow.
You need to recommend a solution to reduce the amount of time it takes to complete the INSERT operations.
What should you recommend?
A. Partition the nonclustered index.
B. Partition the Inventory table.snapshot replication
C. Create a column store index.Master Data Services
D. Drop the clustered index.change data capture


Answer Clike
Which three steps should you perform in sequence?

You need to design the UserActivity table.
Which three steps should you perform in sequence? To answer, move the appropriate three actions from the list of actions to the answer area and arrange them in the correct order.
Select and Place:



Answer Clike
You need to create the usp.AssignUser stored procedure.

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.
Select and Place:



Answer Clike
What should you do?

You need to modify the stored procedure usp_LookupConcurrentUsers.
What should you do?
A. Use the summary table as an in-memory optimized table with a non-hash clustered index.
B. Use the summary table as an in-memory optimized table with a non-hash nonclustered index.
C. Use a type variable instead of the summary table.
D. Add a clustered index to the summary table.


Answer Clike
What should you do?

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 nonclustered index to CreateDate.
B. Add a not hash nonclustered index to CreateDate.
C. Add a not hash clustered index on POSTransactionId and CreateDate.
D. Add a hashed clustered index on POSTransactionId and CreateDate.


Answer Clike
What should you do?

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 Clike
Which two actions should you perform?

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 Clike
What should you do?

You need to implement security for the restore and audit process. What should you do?
A. Grant the COFFECORPAuditors group ALTER ANY CONNECTION and SELECT ALL USER SECURABLES permissions. Grant the COFFECORP
StoreAgent group ALTER ANY CONNECTION and IMPERSONATE ANY LOGIN permissions.
B. Grant the COFFECORPAuditors group CONNECT ANY DATABASE and IMPERSONATE ANY LOGIN permissions. Grant the COFFECORP
StoreAgent group CONNECT ANY DATABASE and SELECT ALL USER SECURABLES permissions.
C. Grant the COFFECORPAuditors group ALTER ANY CONNECTION and IMPERSONATE ANY LOGIN permissions. Grant the COFFECORP
StoreAgent group ALTER ANY CONNECTION and SELECT ALL USER SECURABLES permissions.
D. Grant the COFFECORPAuditors group CONNECT ANY DATABASE and SELECT ALL USER SECURABLES permissions. Grant the COFFECORP
StoreAgent group CONNECT ANY DATABASE and IMPERSONATE ANY LOGIN permissions.


Answer Clike
What should you do?

You need to monitor the health of your tables and indexes in order to implement the required index maintenance strategy.
What should you do?
A. Query system DMVs to monitor avg_chain_length and max_chain_length. Create alerts to notify you when these values converge.
B. Create a SQL Agent alert when the File Table: Avg time per file I/O request value is increasing.
C. Query system DMVs to monitor total_bucket_count. Create alerts to notify you when this value increases.
D. Query system DMVs to monitor total_bucket_count. Create alerts to notify you when this value decreases.


Answer Clike
Which statements should you execute?

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 Clike
What should you add to usp.GetOrdersAndItems?

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 WITH (NOLOCK) to the end of line 47.
B. Add SET TRANSACTION ISOLATION LEVEL READ COMMITTED to line 44.
C. Add SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED to line 44.
D. Add WITH (READPAST) to the end of line 47.


Answer Clike
What should you do?

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. Add WITH (FORCESEEK) to line 69 in usp.GetOrdersByProduct.
C. Add WITH RECOMPILE to line 64 in usp.GetOrdersByProduct.
D. Execute sp_recompile usp.GetOrdersByProduct’.


Answer Clike
What should you do?

You need to implement a solution that addresses the index monitoring requirements.
What should you do?
A. Schedule a SQL Server Agent job that saves data from the dynamic management views to a table in the database.
B. Create a SQL Server Audit that saves data to a log file, and then create a SQL Server Audit Specification that gathers data from the DATABASE_OPERATION group.
C. Create a performance monitor Data Collector Set (DCS) that monitors the SQL Server counters.
D. Schedule a SQL Server Profiler trace, and then save the trace data to a table in the database.


Answer Clike
Which statement should you execute?

You need to implement a solution that addresses the page split issues.
Which statement should you execute?
A. ALTER INDEX IX_Orders_ShipDate ON Orders
REBUILD WITH (PAD_INDEX=OFF, DROP_EXISTING = ON);
B. ALTER INDEX IX_Orders_ShipDate ON Orders
REBUILD WITH (FILLFACTOR=50, DROP_EXISTING = ON);
C. ALTER INDEX IX_Orders_ShipDate ON Orders
REBUILD WITH (FILLFACTOR = 0, DROP_EXISTING = ON);
D. ALTER INDEX IX_Orders_ShipDate ON Orders
REBUILD WITH (PAD_INDEX=ON, DROP_EXISTING = ON);


Answer Clike
Which parameters should you add to usp_AddXMLOrder on line 04 and line 05?

You need to ensure that usp_AddXMLOrder can be used to validate the XML input from the retailers.
Which parameters should you add to usp_AddXMLOrder on line 04 and line 05? (Each correct answer presents part of the solution. Choose all that apply.)
A. @schema varbinary(100).
B. @items varchar(max).
C. @schema sysname.
D. @items varbinary(max).
E. @items xml.
F. @schema xml.


Answer Clike
Which code segment should you develop?

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+ ‘][email protected]’, int)
C. SET @productID = @items.value’/Root/Product[‘+ @itemNumber+ ‘]/productID’, int)
D. SET @productID = @items.value’[email protected]’, int)


Answer Clike
What should you do?

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 Clike
Which statement should you execute?

You need to modify the Orders table to store the XML data used by the retailers.
Which statement should you execute?
A. ALTER Orders
ADD originalOrder XML (ValidateOrder);
B. ALTER Orders
ADD originalOrder XML;
C. ALTER Orders
ADD originalOrder varchar(max);
D. ALTER Orders
ADD originalOrder varbinary(max);


Answer Clike
Which statement should you execute?

You need to implement a solution that addresses the performance issues of the usp_GetOrdersByProduct stored procedure.
Which statement should you execute?

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


Answer Clike
What should you add to line 08 in usp_ImportOrderDetails?

You need to implement a solution that addresses the bulk insert requirements.
What should you add to line 08 in usp_ImportOrderDetails?
A. LASTROW=0.
B. BATCHSIZE=0.
C. BATCHSIZE=1000.
D. LASTROW = 1000.


Answer Clike
What should you do?

You discover that the usp_GetOrdersAndItems stored procedure takes a long time to complete while usp_AddOrder or usp_AddXMLOrder run.
You need to ensure that usp_GetOrdersAndItems completes as quickly as possible.
What should you do? (Each correct answer presents part of the solution. Choose all that apply.)
A. Set the isolation level of the usp_GetOrdersAndItems stored procedure to SERIALIZABLE.
B. Execute the ALTER DATABASE Sales SET ALLOW_SNAPSHOT_ISOLATION ON statement.
C. Set the isolation level of the usp_AddOrder stored procedure to SERIALIZABLE.
D. Set the isolation level of the usp_GetOrdersAndItems stored procedure to SNAPSHOT.
E. Set the isolation level of the usp_AddOrder stored procedure to SNAPSHOT.
F. Execute the ALTER DATABASE Sales SET ALLOW_SNAPSHOT_ISOLATION OFF statement.


Answer Clike
What should you add next to the beginning of each stored procedure?

You need to implement a solution that meets the data recovery requirements.
You update each stored procedure to accept a parameter named @transactionID.
What should you add next to the beginning of each stored procedure?
A. SAVE TRANSACTION WITH MARK @transactionID
B. ROLLBACK DISTRIBUTED TRANSACTION @transactionID
C. BEGIN TRANSACTION WITH MARK @transactionID
D. COMMIT TRANSACTION @transactionID


Answer Clike
What should you implement?

You need to implement a solution that meets the site requirements.
What should you implement?
A. A non-indexed view on Server1
B. A non-indexed view on Server2
C. A distributed view on Server1
D. A distributed view on Server2


Answer Clike
What should you add to usp_GetOrdersAndItems?

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 Clike
What should you modify in usp_ExportOpenings?

You need to implement a change to usp_ExportOpenings that meets the integration requirements.
What should you modify in usp_ExportOpenings? (Each correct answer presents part of the solution. Choose all that apply?)
A. To the end of line 04, add [Opening].
B. To the end of line 05, add [Opening! title].
C. To line 10, add FOR XML RAW.
D. To line 10, add FOR XMLEXPLICIT.
E. To line 10, add FOR XML AUTO.
F. To the end of line 04, add [Opening!ELEMENT].
G. To the end of line 06, add [Opening!salary!ELEMENT].
H. To the end of line 05, add [Opening!title!ELEMENT].
I. To the end of line 06, add [Opening! salary].


Answer Clike
What should you include in the recommendation?

You need to recommend a solution that meets the concurrency problems.
What should you include in the recommendation?
A. Modify the stored procedures to use the SERIALIZABLE isolation level.
B. Modify the order in which usp_AcceptCandidate accesses the Applications table and the Candidates table.
C. Modify the order in which usp_UpdateCandidate accesses the Applications table and the Candidates table.
D. Modify the stored procedures to use the REPEATABLE READ isolation level.


Answer Clike
Which statement should you execute on DB1?

You need to resolve the performance issues of the usp_ExportOpenings stored procedure. The solution must minimize the amount of hard disk space used.
Which statement should you execute on DB1?
A. EXEC sp_dboption ‘DB1’, ‘auto create statistics’, ‘TRUE’;
B. CREATE INDEX IX_Exp_Openings ON Openings(PostDate, FilledDate) INCLUDE (Description, Title, Salary);
C. CREATE INDEX IX_Exp_Openings ON Openings(PostDate) INCLUDE (Description, Title, Salary) WHERE FilledDate IS NULL;
D. EXEC sp_recompile ‘usp_ExportOpenings’;


Answer Clike
Which line of code should you modify?

You need to implement a solution that meets the locking requirements.
Which line of code should you modify?
A. Change line 07 in usp_UpdateOpening to:
UPDATE Openings WITH (UPDLOCK)
B. Change line 09 in usp_GetOpenings to:
FROM Openings o (ROWLOCK)
C. Change line 07 in usp_UpdateOpening to:
UPDATE Openings WITH (READPAST)
D. Change line 09 in usp_GetOpenings to:
FROM Openings o (NOLOCK)


Answer Clike
Which statement should you execute on DB1?

You need to implement a solution that resolves the salary query issue.
Which statement should you execute on DB1?

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


Answer Clike
Which code segment should you use to implement the Conversions assembly?

You need to implement a solution that addresses the upload requirements.
Which code segment should you use to implement the Conversions assembly?

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


Answer Clike
Which type of object should you include in the solution?

You need to design a solution that meets the refactoring requirements.
Which type of object should you include in the solution?
A. An indexed view
B. An aggregate function
C. A distributed view
D. A table-valued function


Answer Clike
What should you include in the script?

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 SERVER ROLE command and the sp_reassign_proxy, sp_add_job, sp_add_jobstep, and sp_grant_login_to_proxy system stored procedures.
B. The CREATE CREDENTIAL command and the sp_add_proxy, sp_add_job, sp_add_jobstep, and sp_grant_proxy_to_subsystem system stored procedures.
C. The CREATE CREDENTIAL command and the sp_reassign_proxy, sp_add_job, sp_add_jobstep, and sp_grant_login_to_proxy 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.


Answer Clike
Which statement should you execute?

You need to implement a solution that meets the security requirements.
Which statement should you execute?

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


Answer Clike
Which data type should you use for ProductType?

Which data type should you use for ProductType?
A. varchar(11)
B. nvarchar(11)
C. char(11)
D. bigint


Answer Clike
Which SQL Server feature should you use?

You need to identify the cause of the page split issues.
Which SQL Server feature should you use?
A. DBCC REINDEX
B. SQL Server Profiler
C. Extended Events
D. DBCC TRACEOFF


Answer Clike
What should you do?

You need to implement a solution that meets the job application requirements.
What should you do?
A. Create a one-to-one relationship between the Openings table and the Applications table.
B. Create a one-to-one relationship between the Candidates table and the Applications table.
C. Add a UNIQUE constraint to the Applications table on the ApplicationID column and CandidateID column.
D. Add a UNIQUE constraint to the Applications table on the OpeningID column and the CandidateID column.


Answer Clike
Which code segments should you execute?

You need to prepare the database to use the .NET Framework ProcessProducts component.
Which code segments 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
F. Option F
G. Option G


Answer Clike
Which line of code should you modify in CategoryFromType.sql?

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. 05
C. 12
D. 14


Answer Clike
Which code segment should you execute on the other server?

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 Clike
Which code segment should you use to complete the function?

You need to create a function that will use a SELECT statement in ProductsByProductType.sql.
Which code segment should you use to complete the function?

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


Answer Clike
Which code segment should you use?

An administrator provides a digital certificate named ServerCert.
You need to implement Transparent Data Encryption (TDE) on ProductsDB.
Which code segment should you use?
A. USE PRODUCTSDB;
GO
CREATE DATABASE ENCRYPTION KEY WITH ALGORITHM = TRIPLE_DES_3KEY ENCRYPTION BY SERVER CERTIFICATE DBCERT;
GO
ALTER DATABASE PRODUCTSDB SET ENCRYPTION ON;
GO
B. USE PRODUCTSDB;
GO
CREATE DATABASE ENCRYPTION KEY WITH ALGORITHM = TRIPLE_DES_3KEY ENCRYPTION BY SERVER CERTIFICATE
PRODUCTSCERT;
GO
ALTER DATABASE PRODUCTSDB SET ENCRYPTION ON;
GO
C. USE PRODUCTSDB;
GO
CREATE DATABASE ENCRYPTION KEY WITH ALGORITHM = AES_256
ENCRYPTION BY SERVER CERTIFICATE PRODUCTSCERT;
GO
ALTER DATABASE PRODUCTSDB SET ENCRYPTION ON;
GO
D. USE PRODUCTSDB;
GO
CREATE DATABASE ENCRYPTION KEY WITH ALGORITHM = AES_256
ENCRYPTION BY SERVER CERTIFICATE DBCERT;
GO
ALTER DATABASE PRODUCTSDB SET ENCRYPTION ON;
GO


Answer Clike
Which WHILE statement should you use at line 18?

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 SUM(RowNumber) FROM @indextable)
C. WHILE COUNT(@RowNumber) < (SELECT @counter FROM @indextable)
D. WHILE @counter < (SELECT COUNT(RowNumber) FROM @indextable)


Answer Clike
Which code segment should you use?

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 Clike
What should you add to usp_SelectEmployeesByName?

You need to modify usp_SelectEmployeesByName to support server-side paging. The solution must minimize the amount of development effort required.
What should you add to usp_SelectEmployeesByName?
A. A table variable
B. The ROWNUMBER keyword
C. An OFFSET-FETCH clause
D. A recursive common table expression


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

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 Clike
Which code segment should you execute?

You need to modify Production.ProductDetails_Insert to comply with the application requirements.
Which code segment should you execute?

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


Answer Clike
Which code segment or segments should you add at line 27 of Tables.sql?

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 Clike
Which code segment should you use?

You need to add a new column named Confirmed to the Employees table. The solution must meet the following requirements:
Have a default value of TRUE.
Minimize the amount of disk space used.
Which code segment should you use?

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


Answer Clike
Which code segment should you use?

You need to create the object used by the parameter of usp_UpdateEmployeeName.
Which code segment should you use?
A. CREATE XML SCHEMA COLLECTION EmployeesInfo
B. CREATE TYPE EmployeesInfo AS Table
C. CREATE SCHEMA EmployeesInfo
D. CREATE TABLE EmployeesInfo


Answer Clike
What should you include in the recommendation?

You need to recommend a solution to ensure that SQL1 supports the auditing requirements of usp_UpdateEmployeeName.
What should you include in the recommendation?
A. Change data capture
B. Change tracking
C. Transactional replication
D. The Distributed Transaction Coordinator (DTC)


Answer Clike
What should you add at line 31 of StoredProcedures.sql?

You execute usp_SelectEmployeesByName multiple times, passing strings of varying lengths to @LastName. You discover that usp_SelectEmployeesByName uses inefficient execution plans.
You need to update usp_SelectEmployeesByName to ensure that the most efficient execution plan is used.
What should you add at line 31 of StoredProcedures.sql?
A. OPTION (ROBUST PLAN)
B. OPTION (OPTIMIZE FOR UNKNOWN)
C. OPTION (KEEP PLAN)
D. OPTION (KEEPFIXED PLAN)


Answer Clike
What should you do in Procedures.sql?

You need to ensure that if any of the statements in usp_UpdateSpeakerName return an error message, all of the changes executed by usp_UpdateSpeakerName are not committed to the database.
What should you do in Procedures.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
E. Option E


Answer Clike
Which statement should you use?

You need to create the object used by the parameter of usp_InsertSessions.
Which statement should you use?
A. CREATE SCHEMA SessionDataTable
B. CREATE TYPE SessionDataTable AS Table
C. CREATE TABLE SessionDataTable
D. CREATE XML SCHEMA COLLECTION SessionDataTable


Answer Clike
What should you update?

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 the NOLOCK query hint
B. Usp_UpdateSpeakerName to use snapshot isolation
C. Usp_SelectSpeakersByName to use the NOLOCK query hint
D. Usp_SelectSpeakersByName to use snapshot isolation


Answer Clike
What should you do?

BBatch 1
While testing usp.GetFutureSessions, you discover that IX_Sessions is accessed by a scan rather than a seek.
You need to minimize the amount of time it takes to execute usp_GetFutureSessions.
What should you do? (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
F. Option F


Answer Clike
What should you add at line 14 of Tables.sql?

You are evaluating the table design.
You need to recommend a change to Tables.sql that reduces the amount of time it takes for usp_AttendeesReport to execute.
What should you add at line 14 of Tables.sql?
A. FullName nvarchar(100) NOT NULL CONSTRAINT DF_FuIlName DEFAULT (dbo.CreateFullName (FirstName, LastName)),
B. FullName AS (FirstName +` ‘+ LastName),
C. FullName nvarchar(100) NOT NULL DEFAULT (dbo.CreateFullName (FirstName, LastName)).
D. FullName AS (FirstName +` ‘+ LastName) PERSISTED,


Answer Clike
What should you add to usp_SelectSpeakersByName?

You need to modify usp_SelectSpeakersByName to support server-side paging. The solution must minimize the amount of development effort required.
What should you add to usp_SelectSpeakersByName?
A. A table variable
B. An OFFSET-FETCH clause
C. The ROWNUMBER keyword
D. A recursive common table expression


Answer Clike
Which code block should you use?

You need to add a new column named Confirmed to the Attendees table.
The solution must meet the following requirements:
Have a default value of false.
Minimize the amount of disk space used.
Which code block should you use?
A. ALTER TABLE Attendees
ADD Confirmed bit DEFAULT 0;
B. ALTER TABLE Attendees
ADD Confirmed char(l) DEFAULT ‘1’;
C. ALTER TABLE Attendees
ADD Confirmed bit DEFAULT 1;
D. ALTER TABLE Attendees
ADD Confirmed char(l) DEFAULT `1′;


Answer Clike
Which statement should you use?

You need to create the object used by the parameter of usp_InsertSessions.
Which statement should you use?
A. CREATE XML SCHEMA COLLECTION SessionDataTable
B. CREATE TYPE SessionDataTable AS Table
C. CREATE SCHEMA SessionDataTable
D. CREATE TABLE SessionDataTable


Answer Clike
What should you configure in Procedures.sql?

Developers report that usp_UpdateSessionRoom periodically returns error 3960.
You need to prevent the error from occurring. The solution must ensure that the stored procedure returns the original values to all of the updated rows.
What should you configure in Procedures.sql?
A. Replace line 46 with the following code:
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
B. Replace line 46 with the following code:
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ
C. Move the SELECT statement at line 49 to line 57.
D. Move the SET statement at line 46 to line 53.


Answer Clike
Which code segment should the stored procedure contain?

You need to convert the functionality of Legacy.sql to use a stored procedure.
Which code segment should the stored procedure contain?

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


Answer Clike
Which code segment should you use to complete the function?

You need to create a function that filters invoices by CustomerID. The SELECT statement for the function is contained in InvoicesByCustomer.sql.
Which code segment should you use to complete the function?

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


Answer Clike
Which code segment should you use to create the stored procedure?

You need to build a stored procedure that amortizes the invoice amount. Which code segment should you use to create the stored procedure? To answer, move the appropriate code segments from the list of code segments to the answer area and arrange them in the correct order.
Select and Place:



Answer Clike
Which code segment should you add at line 47 of Tables.sql?

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 Clike
What should you add at line 30 of Procedures.sql?

You execute usp_TestSpeakers.
You discover that usp_SelectSpeakersByName uses inefficient execution plans.
You need to update usp_SelectSpeakersByName to ensure that the most efficient execution plan is used.
What should you add at line 30 of Procedures.sql?
A. OPTION (FORCESCAN)
B. OPTION (FORCESEEK)
C. OPTION (OPTIMIZE FOR UNKNOWN)
D. OPTION (OPTIMIZE FOR (@LastName= ‘Anderson’))


Answer Clike
What should you include in the recommendation?

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 Clike
Which WHILE statement should you use at line 18?

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 Clike
Which data type should you use for CustomerID?

Which data type should you use for CustomerID?
A. varchar(11)
B. bigint
C. nvarchar(11)
D. char(11)


Answer Clike
Which code segment should you execute?

You need to modify InsertInvoice to comply with the application requirements.
Which code segment should you execute?

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


Answer Clike
How should you modify the INSERT statement?

You attempt to process an invoice by using usp_InsertInvoice.sql and you receive the following error message: "Msg 515, Level 16, State 2, Procedure usp_InsertInvoice, Line 10 Cannot insert the value NULL into column ‘InvoiceDate’, table ‘DB1.Accounting.Invoices’; column does not allow nulls. INSERT fails."
You need to modify usp_InsertInvoice.sql to resolve the error.
How should you modify the INSERT statement?
A. InvoiceDate varchar(100) ‘InvoiceDate’,
B. InvoiceDate varchar(100) ‘Customer/InvoiceDate’,
C. InvoiceDate date ‘@InvoiceDate’,
D. InvoiceDate date ‘[email protected]’,


Answer Clike
Which line of code should you modify in CountryFromID.sql?

You need to modify the function in CountryFromID.sql to ensure that the country name is returned instead of the country ID.
Which line of code should you modify in CountryFromID.sql?
A. 04
B. 05
C. 06
D. 19


Answer Clike
Which code segment should you execute?

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 Clike
How should you define the InvoiceID column in the CREATE TABLE statement?

You need to create the InvoiceStatus table in DB1.
How should you define the InvoiceID column in the CREATE TABLE statement?

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


Answer Clike