Microsoft 70-461 Online Practice Questions and Exam Preparation

Microsoft 70-461 Online Questions & Answers

• Question 441:

  Which data type should be used in place of TIMESTAMP?

  A. VARBINARY
  B. ROWVERSION
  C. DATETIME2
  D. TIME
  B. ROWVERSION

  ---

  ROWVERSION is the replacement for the deprecated TIMESTAMP.

• Question 442:

  You have a SQL Server database named CUSTOMERS.

  You need to sign a stored procedure named SelectCustomers in the CUSTOMERS database.

  Which four statements should you execute in sequence? To answer, move the appropriate statements from the list of statements to the answer area and arrange them in the correct order.

  Select and Place:

  

  

  ---

  There are four steps involved in signing a module: References:https://msdn.microsoft.com/en-us/library/bb669102(v=vs.110).aspx

• Question 443:

  You have a Microsoft SQL Server database that contains two tables named SalesTax and SalesTerritory.

  You need to write a query that will return all possible combinations of SalesTax and SalesTerritory, regardless of whether two particular records are related.

  Which Transact-SQL statement should you run?

  

  A. Option A
  B. Option B
  C. Option C
  D. Option D
  C. Option C

  ---

  We want to do a CROSS JOIN, which returns the same rows as if no WHERE clause was specified in an old-style, non-SQL-92-style join.

  Reference: https://docs.microsoft.com/en-us/sql/t-sql/queries/from-transact-sql?

• Question 444:

  You work as a database developer at ABC.com. ABC.com has a SQL Server 2012 database named SalesDB as illustrated in the following database diagram:

  

  ABC.com has retail stores in a few major cities across the country. The company wants a list of Customers who live in a city that does not have a ABC.com store, along with the customer's City. The result set must be sorted alphabetically by City name.

  Which of the following Transact-SQL statements would return the required information?

  A. SELECT CustomerName, CustomerCity FROM Customers WHERE CustomerCity NOT EXISTS (SELECT StoreCity FROM Stores) ORDER BY CustomerCity
  B. SELECT CustomerName, CustomerCity FROM Customers WHERE CustomerCity < > ALL (SELECT StoreCity FROM Stores) ORDER BY StoreCity
  C. SELECT CustomerName, CustomerCity FROM Customers WHERE CustomerCity < > ANY (SELECT StoreCity FROM Stores) ORDER BY CustomerCity
  D. SELECT CustomerName, CustomerCity FROM Customers WHERE CustomerCity NOT IN (SELECT StoreCity FROM Stores) ORDER BY StoreCity
  A. SELECT CustomerName, CustomerCity FROM Customers WHERE CustomerCity NOT EXISTS (SELECT StoreCity FROM Stores) ORDER BY CustomerCity

• Question 445:

  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 non-clustered 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()))
  D. SELECT COUNT(*)FROM Purchases WHERE PurchaseTime >= CONVERT(DATE, GETDATE()) AND PurchaseTime < DATEADD(DAY, 1, CONVERT(DATE, GETDATE()))

  ---

  http://technet.microsoft.com/en-us/libr ary/ms181034.aspx

• Question 446:

  You work as a database administrator at ABC.com. ABC.com has a SQL Server 2012 database infrastructure that contains a database named SalesDB.

  The SalesDB database is used by an in-house application named ABCApp3. ABC.com users report that ABCApp3 is functioning sluggishly.

  You discover that application consumes considerable memory when it runs single-use dynamic queries against the SalesDB database. You suspect that these queries are making excessive use of procedure cache.

  How would you reduce procedure cache if you cannot create new indexes on the SalesDB database?

  A. You should replace the queries with recursive stored procedures.
  B. You should add make use of the INCLUDE clause in the index.
  C. You should make use of the READPAST hint in the queries.
  D. You should make use of the READCOMMITTED hint in the queries.
  E. You should make use of the optimize for ad hoc workloads option.
  E. You should make use of the optimize for ad hoc workloads option.