Microsoft SQL Server (70-461)
Certification Exam Sample Questions
1. You use Microsoft SQL Server 2012 to write code for a transaction that contains several
statements. There is high contention between readers and writers on several tables used by your
transaction.
You need to minimize the use of the tempDB space. You also need to prevent reading queries
from blocking writing queries.
Which isolation level should you use?
a) SERIALIZABLE
b) SNAPSHOT
c) READ COMMITTED SNAPSHOT
d) REPEATABLE READ
Answer:
c) READ COMMITTED SNAPSHOT
2. How many clustered indexes can you create on a table?
a) 999
b) 1
c) 16
d) 900
Answer:
b) 1
3. What is the limit for the number of steps in statistic histograms?
a) 10 steps per histogram
b) 200 histograms per column
c) 200 pages per histogram
d) 200 steps per histogram
Answer:
d) 200 steps per histogram
4. Your database contains a table named Purchases. The table includes a DATETIME column
named PurchaseTime that stores the date and time each purchase is made. There is a
nonclustered index on the PurchaseTime column.
The business team wants a report that displays the total number of purchases made on the current
day. You need to write a query that will return the correct results in the most efficient manner.
Which Transact-SQL query should you use?
a) SELECT COUNT(*) FROM Purchases WHERE PurchaseTime = CONVERT(DATE,
GETDATE())
b) SELECT COUNT(*) FROM Purchases WHERE PurchaseTime = GETDATE()
c) SELECT COUNT(*) FROM Purchases WHERE CONVERT(VARCHAR, PurchaseTime,
112) = CONVERT(VARCHAR, GETDATE(), 112)
