Here are the sample questions which will help you be familiar with Querying Microsoft SQL Server 2012/2014 (70-461) exam style and structure. We encourage you to try our Demo Querying Microsoft SQL Server Certification Practice Exam to measure your understanding of exam structure in an environment which simulates the MCSA - SQL Server 2012/2014 Certification test environment.
To make your preparation more easy for MCSA - SQL Server 2012/2014 (70-461) exam, we strongly recommend you to use our Premium Querying Microsoft SQL Server Certification Practice Exam. According to our survey with certified candidates, you can easily score 85% in your actual Microsoft Certification exam if you can score 100% in our premium Querying Microsoft SQL Server Certification practice exams.
Microsoft 70-461 Sample Questions:
01. 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
02. How many clustered indexes can you create on a table?
a) 999
b) 1
c) 16
d) 900
03. 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
04. 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)
d) SELECT COUNT(*) FROM Purchases WHERE PurchaseTime >= CONVERT(DATE, GETDATE()) AND PurchaseTime < DATEADD(DAY, 1, CONVERT(DATE, GETDATE()))
05. You use Microsoft SQL Server 2012 to develop a database application. You need to create an object that meets the following requirements:
- Takes an input parameter
- Returns a table of values
- Can be referenced within a view
Which object should you use?
a) inline table-valued function
b) user-defined data type
c) stored procedure
d) scalar-valued function
06. What happens when a scalar subquery returns more than one value?
a) The query fails at run time.
b) The first value is returned.
c) The last value is returned.
d) The result is converted to a NULL.
07. You use Microsoft SQL Server 2012 to develop a database application. You need to implement a computed column that references a lookup table by using an INNER JOIN against another table.
What should you do?
a) Create a BEFORE trigger that maintains the state of the computed column.
b) Reference a user-defined function within the computed column.
c) Add a default constraint to the computed column that implements hard-coded values.
d) Add a default constraint to the computed column that implements hard-coded CASE statements.
08. How can you express that the column categoryname allow NULLs?
a) categoryname PERMIT NULL NVARCHAR(15)
b) categoryname NVARCHAR(15) ALLOW NULL
c) categoryname NVARCHAR(15) PERMIT NULL
d) categoryname NVARCHAR(15) NULL
09. Your database contains a table named SalesOrders. The table includes a DATETIME column named OrderTime that stores the date and time each order is placed. There is a nonclustered index on the OrderTime column.
The business team wants a report that displays the total number of orders placed 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 SalesOrders WHERE OrderTime = CONVERT(DATE, GETDATE())
b) SELECT COUNT(*) FROM SalesOrders WHERE OrderTime = GETDATE()
c) SELECT COUNT(*) FROM SalesOrders WHERE OrderTime >= CONVERT(DATE, GETDATE()) AND OrderTime < DATEADD(DAY, 1, CONVERT(DATE, GETDATE()))
d) SELECT COUNT(*) FROM SalesOrders WHERE CONVERT(VARCHAR, OrderTime, 112) = CONVERT(VARCHAR, GETDATE(), 112))
10. You use Microsoft SQL Server 2012 to develop a database application. Your application sends data to an NVARCHAR(MAX) variable named @var. You need to write a Transact-SQL statement that will find out the success of a cast to a decimal (36,9).
Which code segment should you use?
a) BEGIN TRY SELECT convert (decimal(36,9), @var) as Value, 'True' As BadCast
END TRY BEGIN CATCH SELECT convert (decimal(36,9), @var) as Value, 'False' As BadCast END CATCH
b) TRY( SELECT convert (decimal(36,9), @var) SELECT 'True' As BadCast ) CATCH( SELECT 'False' As BadCast )
c) SELECT CASE WHEN convert (decimal(36,9), @var) IS NULL THEN 'True' ELSE 'False' END AS BadCast
d) SELECT IF(TRY_PARSE(@var AS decimal(36,9)) IS NULL, 'True', 'False' ) AS BadCast
Answers:
| Question: 1 | Answer: c | Question: 2 | Answer: b |
| Question: 3 | Answer: d | Question: 4 | Answer: d |
| Question: 5 | Answer: a | Question: 6 | Answer: a |
| Question: 7 | Answer: b | Question: 8 | Answer: d |
| Question: 9 | Answer: c | Question: 10 | Answer: d |
Note: Please write us on feedback@analyticsexam.com if you find any data entry error in these MCSA - SQL Server 2012/2014 (70-461) sample questions.