You are developing a database application by using Microsoft SQL Server 2012.
An application that uses a database begins to run slowly.
You discover that the root cause is a query against a frequently updated table that has a clustered index. The query returns four columns: three columns in its
WHERE clause contained in a non- clustered index and one additional column.
You need to optimize the statement.
What should you do?

Your application contains a stored procedure for each country. Each stored procedure accepts an employee identification number through the @EmplD parameter.
You need to build a single process for each employee that will execute the appropriate stored procedure based on the country of residence.
Which approach should you use?

You develop a database application. You create four tables. Each table stores different categories of products.
You create a Primary Key field on each table.
You need to ensure that the following requirements are met:
The fields must use the minimum amount of space.
The fields must be an incrementing series of values.
The values must be unique among the four tables.
What should you do?

You are developing a database that will contain price information.
You need to store the prices that include a fixed precision and a scale of six digits.
Which data type should you use?

Your database contains a table named Customer that has columns named CustomerID and Name.
You want to write a query that retrieves data from the Customer table sorted by Name listing 20 rows at a time.
You need to view rows 41 through 60.
Which Transact-SQL query should you create?

You develop a Microsoft SQL Server 2012 database. The database is used by two web applications that access a table named Products.
You want to create an object that will prevent the applications from accessing the table directly while still providing access to the required data.
You need to ensure that the following requirements are met:
Future modifications to the table definition will not affect the applications’ ability to access data.
The new object can accommodate data retrieval and data modification. You need to achieve this goal by using the minimum amount of changes to the existing
applications.
What should you create for each application?

You use Microsoft SQL Server 2012 database to develop a shopping cart application.
You need to invoke a table-valued function for each row returned by a query.
Which Transact-SQL operator should you use?

You are developing a database application by using Microsoft SQL Server 2012.
An application that uses a database begins to run slowly.
You discover that a large amount of memory is consumed by single-use dynamic queries.
You need to reduce procedure cache usage from these statements without creating any additional indexes.
What should you do?

You are developing a database application by using Microsoft SQL Server 2012.
You have a query that runs slower than expected.
You need to capture execution plans that will include detailed information on missing indexes recommended by the query optimizer.
What should you do?

You are developing a database application by using Microsoft SQL Server 2012.
An application that uses a database begins to run slowly. You discover that during reads, the transaction experiences blocking from concurrent updates.
You need to ensure that throughout the transaction the data maintains the original version.
What should you do?