Page: 1
/ 13
Total 184 questions
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.
Your company has employees in different regions around the world.
You need to create a database table that stores the following employee attendance information:
-> Employee ID
-> date and time employee checked in to work
-> date and time employee checked out of work
Date and time information must be time zone aware and must not store fractional seconds.
Solution: You run the following Transact-SQL statement:
Does the solution meet the goal?
- A. Yes
- B. No
Answer : A
Explanation:
Datetimeoffset defines a date that is combined with a time of a day that has time zone awareness and is based on a 24-hour clock.
Syntaxis: datetimeoffset [ (fractional seconds precision) ]
Forthe use "datetimeoffset(0)", the Fractional seconds precision is 0, which is required here.
References:
https://msdn.microsoft.com/en-us/library/bb630289.aspx
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.
The Account table was created using the following Transact-SQL statement:
There are more than 1 billion records in the Account table. The Account Number column uniquely identifies each account. The ProductCode column has 100 different values. The values are evenly distributed in the table. Table statistics are refreshed and up to date.
You frequently run the following Transact-SQL SELECT statements:
You must avoid table scans when you run the queries.
You need to create one or more indexes for the table.
Solution: You run the following Transact-SQL statement:
Does the solution meet the goal?
- A. Yes
- B. No
Answer : B
Explanation:
Create a clustered index on the AccountNumber column as it is unique, not a non nonclustered one.
References:
https://msdn.microsoft.com/en-us/library/ms190457.aspx
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.
The Account table was created using the following Transact-SQL statement:
There are more than 1 billion records in the Account table. The Account Number column uniquely identifies each account. The ProductCode column has 100 different values. The values are evenly distributed in the table. Table statistics are refreshed and up to date.
You frequently run the following Transact-SQL SELECT statements:
You must avoid table scans when you run the queries.
You need to create one or more indexes for the table.
Solution: You run the following Transact-SQL statement:
CREATE CLUSTERED INDEX PK_Account ON Account(ProductCode);
Does the solution meet the goal?
- A. Yes
- B. No
Answer : B
Explanation:
We need an index on the productCode column as well.
References:
https://msdn.microsoft.com/en-us/library/ms190457.aspx
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.
The Account table was created using the following Transact-SQL statement:
There are more than 1 billion records in the Account table. The Account Number column uniquely identifies each account. The ProductCode column has 100 different values. The values are evenly distributed in the table. Table statistics are refreshed and up to date.
You frequently run the following Transact-SQL SELECT statements:
You must avoid table scans when you run the queries.
You need to create one or more indexes for the table.
Solution: You run the following Transact-SQL statements:
Does the solution meet the goal?
- A. Yes
- B. No
Answer : A
Explanation:
Create a clustered index on theAccountNumber column as it is unique.
Create a nonclustered index that includes the ProductCode column.
References:
https://msdn.microsoft.com/en-us/library/ms190457.aspx
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 need to create a stored procedure that updates the Customer, CustomerInfo, OrderHeader, and OrderDetails tables in order.
You need to ensure that the stored procedure:
-> Runs within a single transaction.
-> Commits updates to the Customer and CustomerInfo tables regardless of the status of updates to the OrderHeader and OrderDetail tables.
-> Commits changes to all four tables when updates to all four tables are successful.
Solution: You create a stored procedure that includes the following Transact-SQL segment:
Does the solution meet the goal?
- A. Yes
- B. No
Answer : B
Explanation:
All four tables are updated in a single transaction.
Need to handle the case where the first two updates (OrderHeader, OrderDetail) are successful, but either the 3rd or the 4th (OrderHeader, OrderDetail) fail. Can add a variable in the BEGIN TRY block, and test the variable in the BEGIN CATCH block.
References:
https://docs.microsoft.com/en-us/sql/t-sql/language-elements/begin-transaction-transact-sql
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 need to create a stored procedure that updates the Customer, CustomerInfo, OrderHeader, and OrderDetails tables in order.
You need to ensure that the stored procedure:
-> Runs within a single transaction.
-> Commits updates to the Customer and CustomerInfo tables regardless of the status of updates to the OrderHeader and OrderDetail tables.
-> Commits changes to all four tables when updates to all four tables are successful.
Solution: You create a stored procedure that includes the following Transact-SQL segment:
Does the solution meet the goal?
- A. Yes
- B. No
Answer : A
Explanation:
Need to handle the case where the first two updates (OrderHeader, OrderDetail) are successful, but either the 3rd or the 4th (OrderHeader, OrderDetail) fail. We add the @CustomerComplete variable in the BEGIN TRY block, and test it in the BEGIN CATCH block.
Note: XACT_STATE indicates whether the request has an active user transaction, and whether the transaction is capable of being committed.
XACT_STATE =1: the current request has an active user transaction. The request can perform any actions, including writing data and committing the transaction.
References:
https://docs.microsoft.com/en-us/sql/t-sql/functions/xact-state-transact-sql
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 check constraint on the table.
Does the solution meet the goal?
- A. Yes
- B. No
Answer : A
Explanation:
References:
https://en.wikipedia.org/wiki/Check_constraint
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 FOR UPDATE trigger on the table.
Does the solution meet the goal?
- A. Yes
- B. No
Answer : B
Explanation:
References:
http://stackoverflow.com/questions/16081582/difference-between-for-update-of-and-for-update
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 cascading referential integrity constraints on the table.
Does the solution meet the goal?
- A. Yes
- B. No
Answer : A
Explanation:
References:
https://technet.microsoft.com/en-us/library/ms186973(v=sql.105).aspx
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.
The Account table was created by using the following Transact-SQL statement:
There are more than 1 billion records in the Account table. The Account Number column uniquely identifies each account. The ProductCode column has 100 different values. The values are evenly distributed in the table. Table statistics are refreshed and up to date.
You frequently run the following Transact-SQL SELECT statements:
You must avoid table scans when you run the queries.
You need to create one or more indexes for the table.
Solution: You run the following Transact-SQL statement:
CREATE NONCLUSTERED INDEX IX_Account_ProductCode ON Account(ProductCode);
Does the solution meet the goal?
- A. Yes
- B. No
Answer : A
Explanation:
References:
https://msdn.microsoft.com/en-za/library/ms189280.aspx
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 are developing a new application that uses a stored procedure. The stored procedure inserts thousands of records as a single batch into the Employees table.
Users report that the application response time has worsened since the stored procedure was updated. You examine disk-related performance counters for the
Microsoft SQL Server instance and observe several high values that include a disk performance issue. You examine wait statistics and observe an unusually high
WRITELOG value.
You need to improve the application response time.
Solution: You update the application to use implicit transactions when connecting to the database.
Does the solution meet the goal?
- A. Yes
- B. No
Answer : B
Explanation:
References:
http://sqltouch.blogspot.co.za/2013/05/writelog-waittype-implicit-vs-explicit.html
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 are developing a new application that uses a stored procedure. The stored procedure inserts thousands of records as a single batch into the Employees table.
Users report that the application response time has worsened since the stored procedure was updated. You examine disk-related performance counters for the
Microsoft SQL Server instance and observe several high values that include a disk performance issue. You examine wait statistics and observe an unusually high
WRITELOG value.
You need to improve the application response time.
Solution: You add a unique clustered index to the Employees table.
Does the solution meet the goal?
- A. Yes
- B. No
Answer : A
Explanation:
References:
https://msdn.microsoft.com/en-us/library/ms190457.aspx
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 are developing a new application that uses a stored procedure. The stored procedure inserts thousands of records as a single batch into the Employees table.
Users report that the application response time has worsened since the stored procedure was updated. You examine disk-related performance counters for the
Microsoft SQL Server instance and observe several high values that include a disk performance issue. You examine wait statistics and observe an unusually high
WRITELOG value.
You need to improve the application response time.
Solution: You replace the stored procedure with a user-defined function.
Does the solution meet the goal?
- A. Yes
- B. No
Answer : B
Explanation:
References:
https://msdn.microsoft.com/en-us/library/ms345075.aspx
Note: This question is part of a series of questions that use the same answer choices. An 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 are developing an application to track customer sales. You create tables to support the application. You need to create a database object that meets the following data entry requirements:
What should you create?
- A. extended procedure
- B. CLR procedure
- C. user-defined procedure
- D. DML trigger
- E. DDL trigger
- F. scalar-valued function
- G. table-valued function
Answer : C
Explanation:
References:
https://msdn.microsoft.com/en-us/library/ms345075.aspx
DRAG DROP -
You have a Microsoft Azure SQL Database named MyDb that uses server version V12.
You plan to use Query Performance Insight to troubleshoot performance problems. The database query store is not enabled.
You need to enable the database query store to meet the following requirements for the database:
-> Statistics must be aggregated every 15 minutes.
-> Query stores must use no more than 1,024 megabytes (MB) of storage.
-> Query information must be retained for at least 15 days.
-> Queries must be captured based on resource consumption.
You connect to the database by using SQL Server Managements Studio.
How should you complete the Transact-SQL statements? 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 bar between panes or scroll to view content.
NOTE: More than one combination of answer choices is correct. You will receive credit for any of the correct combinations you select. Each correct selection is worth one point.
Select and Place:
Answer :
Explanation:
Both answers are correct.
References:
https://msdn.microsoft.com/en-us/library/mt604821.aspx
30 days access 