SQL exam 70-461 questions and answers part 3

Free Phone

MS-SQL exam 70-461 questions and answers dumps

QUESTION 21

You develop a database for a travel application. You need to design tables and other database objects. You need to store media files in several tables. Each media file is less than 1 MB in size.

The media files will require fast access and will be retrieved frequently. What should you do?

A. Use the CAST function.

B. Use the DATE data type.

C. Use the FORMAT function.

D. Use an appropriate collation.

E. Use a user-defined table type.

F. Use the VARBINARY data type.

G. Use the DATETIME data type.

H. Use the DATETIME2 data type.

I. Use the DATETIMEOFFSET data type.

J. Use the TODATETIMEOFFSET function.

Correct Answer: Below

QUESTION 22

You develop a database for a travel application. You need to design tables and other database objects. You create a view that displays the dates and times of the airline schedules on a report.

You need to display dates and times in several international formats. What should you do?

A. Use the CAST function.

B. Use the DATE data type.

C. Use the FORMAT function.

D. Use an appropriate collation.

E. Use a user-defined table type.

F. Use the VARBINARY data type.

G. Use the DATETIME data type.

H. Use the DATETIME2 data type.

I. Use the DATETIMEOFFSET data type.

J. Use the TODATETIMEOFFSET function.

Correct Answer: Below
QUESTION 23

You develop a database for a travel application. You need to design tables and other database objects. You create the Airline Schedules table. You need to store the departure and arrival dates and times of flights along with time zone information. What should you do?

A. Use the CAST function.

B. Use the DATE data type.

C. Use the FORMAT function.

D. Use an appropriate collation.

E. Use a user-defined table type.

F. Use the VARBINARY data type.

G. Use the DATETIME data type.

H. Use the DATETIME2 data type.

I. Use the DATETIMEOFFSET data type.

J. Use the TODATETIMEOFFSET function.

Correct Answer: I

QUESTION 24

You develop a database for a travel application. You need to design tables and other database objects. You create a stored procedure. You need to supply the stored procedure with multiple event names and their dates as parameters. What should you do?

A. Use the CAST function.

B. Use the DATE data type.

C. Use the FORMAT function.

D. Use an appropriate collation.

E. Use a user-defined table type.

F. Use the VARBINARY data type.

G. Use the DATETIME data type.

H. Use the DATETIME2 data type.

I. Use the DATETIMEOFFSET data type.

J. Use the TODATETIMEOFFSET function.

Correct Answer: Below

QUESTION 25

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 applications.

What should you create for each application?

A. Synonyms

B. Common table expressions

C. Views

D. Temporary tables

Correct Answer: Below

QUESTION 26

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?

A. Add a HASH hint to the query.

B. Add a LOOP hint to the query.

C. Add a FORCESEEK hint to the query.

D. Add an INCLUDE clause to the index.

E. Add a FORCESCAN hint to the Attach query.

F. Add a columnstore index to cover the query.

G. Enable the optimize for ad hoc workloads option.

H. Cover the unique clustered index with a columnstore index.

I. Include a SET FORCEPLAN ON statement before you run the query.

J. Include a SET STATISTICS PROFILE ON statement before you run the query.

K. Include a SET STATISTICS SHOWPLAN_XML ON statement before you run the query.

L. Include a SET TRANSACTION ISOLATION LEVEL REPEATABLE READ statement before you run the query.

M. Include a SET TRANSACTION ISOLATION LEVEL SNAPSHOT statement before you run the query.

N. Include a SET TRANSACTION ISOLATION LEVEL SERIALIZABLE statement before you run the query.

Correct Answer: Below

QUESTION 27

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?

A. Add a HASH hint to the query.

B. Add a LOOP hint to the query.

C. Add a FORCESEEK hint to the query.

D. Add an INCLUDE clause to the index.

E. Add a FORCESCAN hint to the Attach query.

F. Add a columnstore index to cover the query.

G. Enable the optimize for ad hoc workloads option.

H. Cover the unique clustered index with a columnstore index.

I. Include a SET FORCEPLAN ON statement before you run the query.

J. Include a SET STATISTICS PROFILE ON statement before you run the query.

K. Include a SET STATISTICS SHOWPLAN_XML ON statement before you run the query.

L. Include a SET TRANSACTION ISOLATION LEVEL REPEATABLE READ statement before you run the query.

M. Include a SET TRANSACTION ISOLATION LEVEL SNAPSHOT statement before you run the query.

N. Include a SET TRANSACTION ISOLATION LEVEL SERIALIZABLE statement before you run the query.

Correct Answer: Below

QUESTION 28

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?

A. Add a HASH hint to the query.

B. Add a LOOP hint to the query.

C. Add a FORCESEEK hint to the query.

D. Add an INCLUDE clause to the index.

E. Add a FORCESCAN hint to the Attach query.

F. Add a columnstore index to cover the query.

G. Enable the optimize for ad hoc workloads option.

H. Cover the unique clustered index with a columnstore index.

I. Include a SET FORCEPLAN ON statement before you run the query.

J. Include a SET STATISTICS PROFILE ON statement before you run the query.

K. Include a SET STATISTICS SHOWPLAN_XML ON statement before you run the query.

L. Include a SET TRANSACTION ISOLATION LEVEL REPEATABLE READ statement before you run the query.

M. Include a SET TRANSACTION ISOLATION LEVEL SNAPSHOT statement before you run the query.

N. Include a SET TRANSACTION ISOLATION LEVEL SERIALIZABLE statement before you run the query.

Correct Answer: Below

QUESTION 29

You are developing a database application by using Microsoft SQL Server 2012. An application that uses a database begins to run slowly. Your investigation shows the root cause is a query against a read-only table that has a clustered index. The query returns the following six columns: One column in its WHERE clause contained in a non-clustered index. Four additional columns One COUNT (*) column based on a grouping of the four additional columns. You need to optimize the statement.

A. Add a HASH hint to the query.

B. Add a LOOP hint to the query.

C. Add a FORCESEEK hint to the query.

D. Add an INCLUDE clause to the index.

E. Add a FORCESCAN hint to the Attach query.

F. Add a columnstore index to cover the query.

G. Enable the optimize for ad hoc workloads option.

H. Cover the unique clustered index with a columnstore index.

I. Include a SET FORCEPLAN ON statement before you run the query.

J. Include a SET STATISTICS PROFILE ON statement before you run the query.

K. Include a SET STATISTICS SHOWPLAN_XML ON statement before you run the query.

L. Include a SET TRANSACTION ISOLATION LEVEL REPEATABLE READ statement before you run the query.

M. Include a SET TRANSACTION ISOLATION LEVEL SNAPSHOT statement before you run the query.

N. Include a SET TRANSACTION ISOLATION LEVEL SERIALIZABLE statement before you run the query.

Correct Answer: Below

QUESTION 30

You are developing a database application by using Microsoft SQL Server 2012. An application that uses a database begins to run slowly. Your investigation shows the root cause is a query against a read-only table that has a non-clustered index. The query returns the following six columns: One column in its WHERE clause contained in a non-clustered index. Four additional columns One COUNT (*) column based on a grouping of the four additional columns. You need to optimize the statement.

A. Add a HASH hint to the query.

B. Add a LOOP hint to the query.

C. Add a FORCESEEK hint to the query.

D. Add an INCLUDE clause to the index.

E. Add a FORCESCAN hint to the Attach query.

F. Add a columnstore index to cover the query.

G. Enable the optimize for ad hoc workloads option.

H. Cover the unique clustered index with a columnstore index.

I. Include a SET FORCEPLAN ON statement before you run the query.

J. Include a SET STATISTICS PROFILE ON statement before you run the query.

K. Include a SET STATISTICS SHOWPLAN_XML ON statement before you run the query.

L. Include a SET TRANSACTION ISOLATION LEVEL REPEATABLE READ statement before you run the query.

M. Include a SET TRANSACTION ISOLATION LEVEL SNAPSHOT statement before you run the query.

N. Include a SET TRANSACTION ISOLATION LEVEL SERIALIZABLE statement before you run the query.

Correct Answer: Below

Free Phone

Correct Answer:

21	22	23	24	25	26	27	28	29	30
F	C	I	E	C	M	K	G	F	C

SQL exam 70-461 questions and answers part 2

MS - SQL exam 70-461 questions and answers dumps

QUESTION 11

You create a stored procedure that will update multiple tables within a transaction. You need to ensure that if the stored procedure raises a run-time error, the entire transaction is terminated and rolled back.

Which Transact-SQL statement should you include at the beginning of the stored procedure?

A. SET XACT_ABORT ON

B. SET ARITHABORT ON

C. TRY

D. BEGIN

E. SET ARITHABORT OFF

F. SET XACT_ABORT OFF

 

QUESTION 12

You are writing a set of queries against a FILESTREAM-enabled database. You create a stored procedure that will update multiple tables within a transaction. You need to ensure that if the stored procedure raises a run-time error, the entire transaction is terminated and rolled back.

Which Transact-SQL statement should you include at the beginning of the stored procedure?

A. SET TRANSACTION ISOLATION LEVEL SERIALIZABLE

B. SET XACT_ABORT OFF

C. SET TRANSACTION ISOLATION LEVEL SNAPSHOT

D. SET IMPLICIT_TRANSACTIONS ON

E. SET XACT_ABORT ON

F. SET IMPLICIT TRANSACTIONS OFF

QUESTION 13

A table named Profits stores the total profit made each year within a territory. The Profits table has columns named Territory, Year, and Profit. You need to create a report that displays the profits made by each territory for each year and its preceding year. Which Transact-SQL query should you use?

A. SELECT Territory, Year, Profit,

LAG(Profit, 1, 0) OVER(PARTITION BY Year ORDER BY Territory) AS NextProfit

FROM Profits

B. SELECT Territory, Year, Profit,

LAG(Profit, 1, 0) OVER(PARTITION BY Territory ORDER BY Year) AS NextProfit

FROM Profits

C. SELECT Territory, Year, Profit,

LEAD(Profit, 1, 0) OVER(PARTITION BY Territory ORDER BY Year) AS NextProfit

FROM Profits

D. SELECT Territory, Year, Profit,

LEAD(Profit, 1, 0) OVER(PARTITION BY Year ORDER BY Territory) AS NextProfit

FROM Profits

QUESTION 14

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? select

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

IIF(TRY_PARSE(@var AS decimal(36,9)) IS NULL, 'True', 'False')

AS BadCast

QUESTION 15

Your database contains two tables named DomesticSalesOrders and InternationalSalesOrders. Both

tables contain more than 100 million rows. Each table has a Primary Key column named SalesOrderId. The data in the two tables is distinct from one another. Business users want a report that includes aggregate information about the total number of global sales and total sales amounts.

You need to ensure that your query executes in the minimum possible time. Which query should you use?

A. SELECT COUNT(*) AS NumberOfSales, SUM(SalesAmount) AS TotalSalesAmount

FROM (

SELECT SalesOrderId, SalesAmount

FROM DomesticSalesOrders

UNION ALL

SELECT SalesOrderId, SalesAmount

FROM InternationalSalesOrders

) AS p

B. SELECT COUNT(*) AS NumberOfSales, SUM(SalesAmount) AS TotalSalesAmount

FROM (

SELECT SalesOrderId, SalesAmount

FROM DomesticSalesOrders

UNION

SELECT SalesOrderId, SalesAmount

FROM InternationalSalesOrders

) AS p

C. SELECT COUNT(*) AS NumberOfSales, SUM(SalesAmount) AS TotalSalesAmount

FROM DomesticSalesOrders

UNION

SELECT COUNT(*) AS NumberOfSales, SUM(SalesAmount) AS TotalSalesAmount

FROM InternationalSalesOrders

D. SELECT COUNT(*) AS NumberOfSales, SUM(SalesAmount) AS TotalSalesAmount

FROM DomesticSalesOrders

UNION ALL

SELECT COUNT(*) AS NumberOfSales, SUM(SalesAmount) AS TotalSalesAmount

FROM InternationalSalesOrders

QUESTION 16

You are a database developer at an independent software vendor. You create stored procedures

that contain proprietary code. You need to protect the code from being viewed by your customers.

Which stored procedure option should you use?

A. ENCRYPTBYKEY

B. ENCRYPTION

C. ENCRYPTBYPASSPHRASE

D. ENCRYPTBYCERT

 

QUESTION 17

You use a Microsoft SQL Server 2012 database. You want to create a table to store Microsoft Word documents. You need to ensure that the documents must only be accessible via Transact-SQL queries. Which Transact-SQL statement should you use?

A. CREATE TABLE DocumentStore

(

[Id] INT NOT NULL PRIMARY KEY,

[Document] VARBINARY(MAX) NULL

)

GO

B. CREATE TABLE DocumentStore

(

[Id] hierarchyid,

[Document] NVARCHAR NOT NULL

)

GO

C. CREATE TABLE DocumentStore AS FileTable

D. CREATE TABLE DocumentStore

(

[Id] [uniqueidentifier] ROWGUIDCOL NOT NULL UNIQUE,

[Document] VARBINARY(MAX) FILESTREAM NULL

)

GO

 

QUESTION 18

You develop a Microsoft SQL Server 2012 database that contains a heap named OrdersHistoncal. You write the following Transact-SQL query:

INSERT INTO OrdersHistorical

SELECT * FROM CompletedOrders

You need to optimize transaction logging and locking for the statement. Which table hint should you use?

A. HOLDLOCK

B. ROWLOCK

C. XLOCK

D. UPDLOCK

E. TABLOCK

QUESTION 19

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()))

QUESTION 20

You develop a Microsoft SQL Server 2012 database that contains a table named Products. The

Products table has the following definition:

You need to create an audit record only when either the RetailPrice or WholeSalePrice column is

updated. Which Transact-SQL query should you use?

A. CREATE TRIGGER TrgPriceChange ON Products FOR UPDATE AS

IF CCLUMNS_CHANGED(RetailPrice, WholesalePrice)

- - Create Audit Records

B. CREATE TRIGGER TrgPriceChange ON Products FOR UPDATE AS

IF EXISTS(SELECT RetailPrice from inserted) OR

EXISTS (SELECT WholeSalePnce FROM inserted)

- - Create Audit Records

C. CREATE TRIGGER TrgPriceChange ON Products FOR UPDATE AS

IF COLUMNS_UPDATED(RetailPrice, WholesalePrice)

- - Create Audit Records

D. CREATE TRIGGER TrgPriceChange ON Products FOR UPDATE AS

IF UPDATE(RetailPrice) OR UPDATE(WholeSalePrice)

- - Create Audit Records

Correct Answer:

11	12	13	14	15	16	17	18	19	10
A	E	B	D	A	B	A	E	D	D