Question 1.
You work as a database developer at ABC.com. ABC.com has a SQL Server 2012 database named SalesDB that has a table named WeeklySales. The WeeklySales table records the sales amount for each of ABC.com's 20 sales representatives. You need to write a Transact-SQL query that ranks the sales representatives by the average sales amount for the past year. You want the sales representatives with the same average sales amount to be ranked in the same sequence as they are being processed with no rank being skipped.

Which ranking function should you use?

A. The RANK( ) OVER function.
B. The NTILE( ) OVER function
C. The DENSE_RANK( ) OVER function
D. The ROW_NUMBER( ) OVER function
E. The FORMAT function.

Answer: C

Explanation:

Question 2.
You work as a database developer at ABC.com. ABC.com has a SQL Server 2012 database named SalesDB that has a table named WeeklySales. The WeeklySales table records the sales amount for each of ABC.com's 20 sales representitives. You need to write a Transact-SQL query that ranks the sales representatives by the average sales amount for the past year. You want the sales representatives with the same average sales amount to have the same rank with the subsequent rank being skipped.

Which ranking function should you use?

A. The RANK( ) OVER function.
B. The NTILE( ) OVER function
C. The DENSE_RANK( ) OVER function
D. The ROW_NUMBER( ) OVER function
E. The FORMAT function.

Answer: C

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

Question 3. CORRECT TEXT
You work as a SQL Server 2012 database developer at ABC.com. You are developing a query for a database driven Web application that allows visitors to vote for the cricket player of the week. The number of votes is stored in a table named WeeklyVotes that has columns named Week, PlayerName, Votes. You need to write a Transact-SQL query that ranks the top 30 cricket players by the average votes over the last 12 months. You want the top 10 cricket players to have a rank of 1, the next 10 to have a rank of 2, and the last 10 to have a rank of 3.

Which of the following SELECT statement would you use? To answer, type the correct code in the answer area.

Answer: SELECT TOP 50 PlayerName,
NTILE (3) OVER (ORDER BY AVG (Votes) DESC) AS AveVotes
FROM WeeklyVotes
GROUP BY PlayerName

Question 4.
You work as a database developer at ABC.com. ABC has an in-house application named ABCApp3 that runs a Transact-SQL query against a SQL Server 2012 database. You want to run an execution plan against the query that will provide detailed information on missing indexes.

How would you accomplish this task?

A. You should make use of the READPAST hint in the queries.
B. You should make use of the READCOMMITTED hint in the queries.
C. You should make use of the SET SHOWPLAN_XML ON statement in the query.
D. You should make use of the SET STATISTICS XML ON statement in the query.
E. You should make use of the SET XACT_ABORT OFF statement in the query.
F. You should make use of the SET CONTEXT_INFO statement in the query.

Answer: C

Explanation:

Question 5.
You work as a database administrator at ABC.com. ABC.com has a SQL Server 2012 database infrastructure that contains a database named ABCDB. The ABCDB database is used by an in-house application named ABCApp3 that queries a read only table with a clustered index. ABC.com users report that ABCApp3 is functioning sluggishly. You suspect query the application uses is causing the problem. You analyze the query and discover that column referenced in the WHERE clause is not part of the clustered index. You also notice that the query returns five columns, as well as a COUNT (*) clause grouped on the five columns.

How would you improve the efficiency of this query?

A. You should replace the query with recursive stored procedure.
B. You should replace the COUNT (*) clause with a persisted computed column.
C. You should create nonclustered indexes on all columns used in the query.
D. You should create a filtered index on the column used in the WHERE clause.
E. You should add an INCLUDE clause to the clustered index.
F. You should create a columnstore index on all columns used in the query.
G. You should create a unique clustered index on the column used in the WHERE clause.

Answer: F

Explanation:

Question 6.
You work as a database developer at ABC.com. ABC has an in-house application named ABCApp3 that runs a Transact-SQL query against a SQL Server 2012 database named SalesDB. ABC.com users report that ABCApp3 is functioning sluggishly. You discover that concurrent updates are causing blockages on the SalesDB database. 

How would you ensure that the query to use the original data when updates occur?

A. You should have the query run in the REPEATABLE READ ISOLATION LEVEL.
B. You should have the query run in the SERIALIZABLE ISOLATION LEVEL.
C. You should have the query run in the READCOMMITTED ISOLATION LEVEL.
D. You should have the query run in the SNAPSHOT ISOLATION LEVEL.
E. You should have the query run in the READPAST ISOLATION LEVEL.

Answer: D

Explanation:

Question 7.
You work as a database administrator at ABC.com. ABC.com has a SQL Server 2012 database infrastructure that contains a database named ABCDB. The ABCDB database is used by an in-house application named ABCApp3 that queries a table with a clustered index. ABC.com users report that ABCApp3 is functioning sluggishly. You suspect query the application uses is causing the problem. You analyze the query and discover that the query has four columns in the WHERE clause of which three are part of a nonclustered index.

How would you improve the efficiency of this query?

A. You should replace the query with recursive stored procedure.
B. You should replace the COUNT (*) clause with a persisted computed column.
C. You should create nonclustered indexes on all columns used in the query.
D. You should create a filtered index on the column used in the WHERE clause.
E. You should add an INCLUDE clause to the clustered index.
F. You should create a columnstore index on all columns used in the query.
G. You should create a unique clustered index on the column used in the WHERE clause.

Answer: E

Explanation:

Question 8.
You work as a database administrator at ABC.com. ABC.com has several stores that maintain their own Sales database. The Sales databases all have a Customers table with columns named CustomerID, FistName, LastName, and Tel. The main office has a database named Operations that has a table named Stores. The Stores table has a column named StoreID. You need to combine data from the various Customers tables into a single table to be located at the main office.

Which of the following would you use when creating this table?

A. A Unique Clustered Constraint on the StoreID and CustommerID columns.
B. A non-Clustered Constraint on the StoreID and CustommerID columns.
C. An Identity column on the StoreID and CustommerID columns.
D. A Columnstore index on the StoreID and CustommerID columns.

Answer: C

Question 9.
You are the database administrator of a SQL Server 2012 database infrastructure at ABC.com. You need to optimize a very large database table that contains several million rows of data by designing a view based on the table. The view must allow users to perform aggregations on several columns.

How should you configure the view to ensure optimal performance?

A. You should create the view as an indexed view.
B. You should create a clustered index on the view.
C. You should make use of a stored procedure to return that data.
D. You should make use of a table-valued function.

Answer: A

Explanation:

Question 10.
You work as a database administrator at ABC.com. ABC.com has a SQL Server 2012 database named SalesDB. The SalesDB database is shown in the following database diagram: 
You create a view on the SalesDB using the following Transact-SQL code:
CREATE VIEW SalesV
WITH SCHEMABINDINGS
AS
SELECT Products.ProductID, Invoices.InvoiceDate, SUM (Products.RetailPrice *
OrderDetail.Quantity * OrderDetail.SalesDiscount) AS Price
FROM OrderDetail INNER JOIN Products ON
OrderDetail.ProductID = Products.ProducID
INNER JOIN Invoices ON
OrderDetail.InvoiceID = Invoices.InvoiceID
GROUP BY Products.ProductID, Invoices.InvoiceDate
GO

How should you alter this view to allow users to update data through the SalesV?

A. You should add a CHECK constraint to the SalesV view.
B. You should add an INSTEAD OF trigger to the SalesV view.
C. You should add a clustered index to the SalesV view.
D. You should add an AFTER UPDATE trigger to the SalesV view.
E. Create a columnstore index on all columns used in the SalesV view.

Answer: B

Explanation:

	Web www.certsbraindumps.com