NEW QUESTION 1

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.

• A. Mastered
• B. Not Mastered

Answer: A

Explanation:
Box 1: Create a table named Table2 by using the same schema as Table1. Note: Table2 is the staging table.
Box 2: Partition Table1
Box 3: Import the data to Table2.
Box 4: Create a columnstore index on Table2 for Column1. Box 5: Switch Table2 to Table1
Note:
* An xVelocity memory optimized columnstore index, groups and stores data for each column and then joins all the columns to complete the whole index.
Columnstore indexes can transform the data warehousing experience for users by enabling faster performance for common data warehousing queries such as filtering, aggregating, grouping, and star-join queries.
* Tables that have a columnstore index cannot be updated. There are three ways to work around this problem.
A) To update a table with a columnstore index, drop the columnstore index, perform any required INSERT, DELETE, UPDATE, or MERGE operations, and then rebuild the columnstore index.
B) (applies in this scenario) Partition the table and switch partitions. For a bulk insert, insert data into a staging table, build a columnstore index on the staging table, and then switch the staging table into an empty partition. For other updates, switch a partition out of the main table into a staging table, disable or drop the columnstore index on the staging table, perform the update operations, rebuild or re-create the columnstore index on the staging table, and then switch the staging table back into the main table.
C) Place static data into a main table with a columnstore index, and put new data and recent data likely to change, into a separate table with the same schema that does not have a columnstore index.
Reference: Best Practices: Updating Data in a Columnstore Index

NEW QUESTION 2

Which data type should you use for ProductType?

• A. varchar(11)
• B. nvarchar(11)
• C. char(11)
• D. bigint

Answer: C

NEW QUESTION 3

You plan to create a new column in a disk-based 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. FileStream
• B. nvarchar
• C. image
• D. varbinary

Answer: A

NEW QUESTION 4

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

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

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

Answer: A

NEW QUESTION 5

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: A

NEW QUESTION 6

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: D

NEW QUESTION 7

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

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

Answer: B

NEW QUESTION 8

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: ACDE

Explanation:
http://msdn.microsoft.com/en-us/library/ms131048.aspx http://msdn.microsoft.com/en-us/library/ms131052.aspx http://msdn.microsoft.com/en-us/library/ms189524.aspx http://msdn.microsoft.com/en-us/library/ms345106.aspx http://msdn.microsoft.com/en-us/library/ms131107.aspx

NEW QUESTION 9

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

• A. Mastered
• B. Not Mastered

Answer: A

Explanation:
Note:
Locking the table during the process and not recomputing statistics would be the fastest.
* Online = OFF
Table locks are applied for the duration of the index operation. An offline index operation that creates, rebuilds, or drops a clustered, spatial, or XML index, or rebuilds or drops a nonclustered index, acquires a Schema modification (Sch-M) lock on the table. This prevents all user access to the underlying table for the duration of the operation. An offline index operation that creates a nonclustered index acquires a Shared (S) lock on the table. This prevents updates to the underlying table but allows read operations, such as SELECT statements.
* STATISTICS_NORECOMPUTE = ON
Out-of-date statistics are not automatically recomputed. Reference: ALTER INDEX (Transact-SQL)

NEW QUESTION 10

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.

• A. Mastered
• B. Not Mastered

Answer: A

Explanation:

NEW QUESTION 11

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: B

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

NEW QUESTION 12

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.

• A. Mastered
• B. Not Mastered

Answer: A

Explanation:
Box 1:

Box 2:

Box 3:

Box 4:

Box 5:

Box 6:

Box 7:

Note:
* MEMORY_OPTIMIZED_DATA
First create a memory-optimized data filegroup and add a container to the filegroup. Then create a memory-optimized table.
* You must specify a value for the BUCKET_COUNT parameter when you create the memory-optimized
table. In most cases the bucket count should be between 1 and 2 times the number of distinct values in the index key.
* Example:
-- create a durable (data will be persisted) memory-optimized table
-- two of the columns are indexed CREATE TABLE dbo.ShoppingCart (
ShoppingCartId INT IDENTITY(1,1) PRIMARY KEY NONCLUSTERED,
UserId INT NOT NULL INDEX ix_UserId NONCLUSTERED HASH WITH (BUCKET_COUNT=1000000),
CreatedDate DATETIME2 NOT NULL, TotalPrice MONEY
) WITH (MEMORY_OPTIMIZED=ON) GO

NEW QUESTION 13

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.

• A. Mastered
• B. Not Mastered

Answer: A

Explanation:
Box 1:

Box 2:

Box 3:

Box 4:

Box 5:

Box 6:

Note:
* From scenario:
The mobile application will need to meet the following requirements:
•Update the location of the user by using a stored procedure named usp_AddMobileLocation.
* DELAYED_DURABILITY
SQL Server transaction commits can be either fully durable, the SQL Server default, or delayed durable (also known as lazy commit).
Fully durable transaction commits are synchronous and report a commit as successful and return control to the client only after the log records for the transaction are written to disk. Delayed durable transaction commits are asynchronous and report a commit as successful before the log records for the transaction are written to disk. Writing the transaction log entries to disk is required for a transaction to be durable. Delayed durable transactions become durable when the transaction log entries are flushed to disk.

NEW QUESTION 14

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: A

NEW QUESTION 15

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

• A. Mastered
• B. Not Mastered

Answer: A

Explanation:
Box 1: sys.dm_db_index_usage_stats
sys.dm_db_index_usage_stats shows you how many times the index was used for user queries. It returns
counts of different types of index operations and the time each type of operation was last performed in SQL Server.
Box 2: sys.dm_db_missing_index_details
sys.dm_db_missing_index_details returns detailed information about a missing index; for example, it returns the name and identifier of the table where the index is missing, and the columns and column types that should make up the missing index.
References:
https://docs.microsoft.com/en-us/sql/relational-databases/system-dynamic-management-views/sys-dm-db-index-
https://docs.microsoft.com/en-us/sql/relational-databases/system-catalog-views/sys-indexes-transact-sql https://technet.microsoft.com/en-us/library/ms345524(v=sql.105).aspx

NEW QUESTION 16

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

• A. Mastered
• B. Not Mastered

Answer: A

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

NEW QUESTION 17

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

• A. Mastered
• B. Not Mastered

Answer: A

Explanation:
http://msdn.microsoft.com/en-us/library/ms131102.aspx http://msdn.microsoft.com/en-us/library/bb522446.aspx http://msdn.microsoft.com/en-us/library/bb510489.aspx

NEW QUESTION 18

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

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

Answer: B

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

NEW QUESTION 19

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:
You need to identify which isolation level you must set for each stored procedure. The solution must minimize the number of locks.
Which isolation level should you identify?
To answer, drag the appropriate isolation level to the correct stored procedure in the answer area. (Answer choices may be used once, more than once, or not at all.)

• A. Mastered
• B. Not Mastered

Answer: A

Explanation:
Box 1: read uncommitted
READ UNCOMMITTED is the least restrictive isolation level because it ignores locks placed by other transactions. Transactions executing under READ UNCOMMITTED can read modified data values that have not yet been committed by other transactions; these are called "dirty" reads.
Box 2: SERIALIZABLE
Places a range lock on the data set, preventing other users from updating or inserting rows into the data set until the transaction is complete. This is the most restrictive of the four isolation levels. Because concurrency is lower, use this option only when necessary. This option has the same effect as setting HOLDLOCK on all tables in all SELECT statements in a transaction.
References: https://msdn.microsoft.com/en-us/library/tcbchxcb(v=vs.110).aspx

NEW QUESTION 20

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

• A. Mastered
• B. Not Mastered

Answer: A

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

NEW QUESTION 21

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: B

NEW QUESTION 22

Which data type should you use for CustomerID?

• A. varchar(11)
• B. bigint
• C. nvarchar(11)
• D. char(11)

Answer: D

Explanation:
Invoices.xml
All customer IDs are 11 digits. The first three digits of a customer ID represent the customer's country. The remaining eight digits are the customer's account number.
int: -2^31 (-2,147,483,648) to 2^31-1 (2,147,483,647) (just 10 digits max)
bigint: -2^63 (-9,223,372,036,854,775,808) to 2^63-1 (9,223,372,036,854,775,807)
http://msdn.microsoft.com/en-us/library/ms176089.aspx http://msdn.microsoft.com/en-us/library/ms187745.aspx

NEW QUESTION 23

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: C

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

NEW QUESTION 24

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

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

Answer: A

NEW QUESTION 25
......