Microsoft Developing Microsoft SQL Server Databases - 070-464 FREE EXAM DUMPS QUESTIONS & ANSWERS
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?
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?
Correct Answer: D
Vote an answer
Explanation: Only visible for FreeCram members. You can sign-up / login (it's free).
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.
Correct Answer:
Explanation:
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.
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?
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?
Correct Answer: A
Vote an answer
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.
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.
Correct Answer: A
Vote an answer
Explanation: Only visible for FreeCram members. You can sign-up / login (it's free).
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.
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.
Correct Answer:
Explanation:
Note:
* sys.dm_exec_query_plan
Returns the Showplan in XML format for the batch specified by the plan handle. The plan specified by the plan handle can either be cached or currently executing.
* sys.dm_exec_cached_plans
Returns a row for each query plan that is cached by SQL Server for faster query execution. You can use this dynamic management view to find cached query plans, cached query text, the amount of memory taken by cached plans, and the reuse count of the cached plans.
You need to implement a solution that resolves the salary query issue.
Which statement should you execute on DB1?
Which statement should you execute on DB1?
Correct Answer: B
Vote an answer
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.
Develop the solution by selecting and arranging the required code blocks in the correct order.
You may not need all of the code blocks.
Correct Answer:
Explanation:
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
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.)
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.)
Correct Answer:
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
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.)
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.)
Correct Answer:
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-usage-stats-transact-sql
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