Get Full Version of the Exam
You are developing an ETL process to cleanse and consolidate incoming data. The ETL process will use a reference table to identify which data must be cleansed in the target table. The server that hosts the tables restarts daily.
You need to minimize the amount of time it takes to execute the query and the amount of time it takes to populate the reference table.
What should you do?
Convert the target table to a memory-optimized table. Create a natively compiled stored procedure to cleanse and consolidate the data.
Convert the reference table to a memory-optimized table. Set the durability option to SCHEMA_AND_DATAgt;
Create a native compiled stored procedure to implement the ETL process for both tables.
Convert the reference table to a memory-optimized table. Set the durability option to SCHEMA_ONLY.
Correct Answer: D
Note: This question is part of a series of questions that present the same scenario. Each question in the series contains a unique solution. Determine whether the solution meets the stated goals.
You have a database that contains a table named Employees. The table stores information about the employees of your company.
You need to implement and enforce the following business rules:
Limit the values that are accepted by the Salary column.
Prevent salaries less than $15,000 and greater than $300,000 from being entered. Determine valid values by using logical expressions.
Do not validate data integrity when running DELETE statements. Solution: You implement a foreign key on the table.
Does the solution meet the goal?
Correct Answer: B
A check constraint is needed.
You have a database with tables. You must insert data into the table by using views. Which of the following two statements best describes an updatable view?
NOTE: Each correct selection is worth on point.
The view must not include the primary key of the table.
The view must include GROUP By or HAVING statements.
Each view must reference columns for one table.
The view may include a composite filed.
The view must not include subqueries.
Correct Answer: CD
You have a Microsoft SQL Server database that has a table named Sales. The table is used for retrieving data and is updated during non business hours.
You run the following Transact-SQL statement:
You analyze the execution plan for the statement. (Click the Exhibit button).
You need to add an index that optimizes performance. How should you complete the Transact-SQL statement?
You are designing a solution for a company that operates retail stores. Each store has a database that tracks sales transactions. You create a summary table in the database at the corporate office. You plan to use the table to record the quantity of each product sold at each store on each day. Managers will use this data to identify reorder levels for products.
Every evening, stores must transmit s5les data to the corporate office. The data must be inserted into the summary table that includes the StorelD, ProductID, Qtysold, Totprodsales, and Datesold Columns.
You need to prevent duplicate rows in the summary table. Each row must uniquely identify the store that sold the product and the total amount sold for that store on a specific date.
What should you include in your solution?
Create a unique constraint.
Create a foreign key constraint to the storeID column in each of the store tables.
Create a rule and bind it to the storeID column.
Create a check constraint.
Correct Answer: A
You can use UNIQUE constraints to make sure that no duplicate values are entered in specific columns that do not participate in a primary key. Although both a UNIQUE constraint and a PRIMARY KEY constraint enforce uniqueness, use a UNIQUE constraint instead of a PRIMARY KEY constraint when you want to enforce the uniqueness of a column, or combination of columns, that is not the primary key.
You are performing a code review for Transact-SQL statements.
What action does the constraint in each statement perform? To answer, select the appropriate options in the answer area.
NOTE: Each correct selection is worth one point.
Database users report that SELECT statements take a long time to return results. You run the following Transact-SQL statement:
You need to create one nonclustered covering index that contains all of the columns in the above table. You must minimize index key size.
Which Transact-SQL statement should you run?
CREATE NONCLUSTERED INDEX IX_User ON Users (CountryCode, UserStatus, UserName);
CREATE NONCLUSTERED INDEX IX_User ON Users (CountryCode, UserStatus) INCLUDE (UserName);
CREATE NONCLUSTERED INDEX IX_User ON Users (CountryCode, UserName);
CREATE NONCLUSTERED INDEX IX_User ON Users (UserStatus, CountryCode) INCLUDE (UserName);
Correct Answer: D
Use the UserStatus as the first column in the index, as it is an in_equality column.
You are a database developer for a company that delivers produce and other refrigerated goods to grocery stores. You capture the food storage temperature for delivery vehicles by using Internet of Things (IoT) devices. You store the temperature data in a database table named VehicleTemperatures. The ChillerSensorNumber column stores the identifier for the IoT devices.
You need to create an indexed view that meets the following requirements:
Persists the data on disk to reduce the amount of I/O Provides the number of ChillerSensorNumber items Creates only a set of summary rows
How should you complete the view definition? To answer, drag the appropriate Transact-SQL segments to the correct locations. Each Transact-SQL segment may be used once, more than once, or not at all. You may need to drag the split bat between panes or scroll to view content.
You have a table stores transactions partitions by year.
Users occasionally experience performance issues when they access the table. The table is located exclusively when the records are updated.
You need to prevent exclusive locks on the table and maintain data integrity. What should you do?
Set the LOCK_EXCALARTION property to AUTO.
Set the LOCK_EXCALATION property to TABLE.
Set the DATA COMPRESSION property to RW at the partition level.
Set the DATA_COMPRESSION property to PAGE at the partition level.
Set the LOCK_EXCALATION property to DISABLE.
Correct Answer: D
Note: This question is part of a series of questions that use the same or similar answer choices. As answer choice may be correct for more than one question in the series. Each question is independent of the other questions in this series.
Information and details provided in a question apply only to that question.
You have a Microsoft SQL Server database named DB1 that contains the following tables:
Users frequently run the following query:
Users report that the query takes a long time to return results.
You need to minimize the amount of time requires for the query to return data. What should you do?
Create clustered indexes on TBL1 and TBL2.
Create a clustered index on TBL1. Create a nonclustered index on TBL2 and add the most
frequently queried columns as included columns.
Create a nonclustered index on TBL2 only.
Create unique constraints on both TBL1 and TBL2. Create a partitioned view that combines columns from TBL1 and TBL2.
Drop existing indexes on TBL1 and then create a clustered columnstore index. Create a nonclustered columnstore index on TBL1. Create a nonclustered index on TBL2.
Drop existing indexes on tbli and then create a clustered columnstore index. Create a nonclustered columnstore index on tbli. Make no changes to TBL2.
Create CHECK constraints on both TBL1 and TBL2.. Create a partitioned view that combines columns fromTBL1 and TBL2.
Create an indexed view that combines columns from TBL1 and TBL2.