Microsoft 70-461 Exam Dumps | Microsoft 70-461 Practice Test Questions

Question No 1 

A local bank uses a SQL Server database to manage accounts. You are developing a stored procedure that contains multiple Transact-SQL INSERT statements. The stored procedure must use transaction management to handle errors. You need to ensure that the stored procedure rolls back the entire transaction if a run-time occurs. Which Transact-SQL statement should you add to the stored procedure?

A. SET ARITHABORT ON
B. SET NOEXEC ON
C. SET TRANSACTION ISOLATION LEVEL ON
D. SET XACT_ABORT ON

Answer: D 

Explanation:
SET XACT_ABORT specifies whether SQL Server automatically rolls back the current transaction when a Transact-SQL statement raises a run-time error.When SET XACT_ABORT is ON, if a Transact-SQL statement raises a run-time error, the entire transaction is terminated and rolled back.

References:
https://docs.microsoft.com/en-us/sql/t-sql/statements/set-xact-abort-transactsql?view=sql-server-2017


Question No 2 

Note: This question is part of a series of questions that use the same set of answer choices. An answer choice may be correct for more than one question in the series. You develop a database for a travel application. You need to design tables and other database objects. You create the Historical_Events table. You need to store dates by using the smallest possible storage size. 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.

Answer: B 

Explanation: 
The size of a column with the DATE format is 3 bytes fixed.

Incorrect Answers: 
G: The DATETIME data type storage size is 8 bytes.
H: datetime2 can be considered as an extension of the existing datetime type that has a larger date range, a larger default fractional precision, and optional user-specified precision.
I: The 26 DATETIMEOFFSET data type storage size is 10 bytes.

References: 
https://docs.microsoft.com/en-us/sql/t-sql/data-types/date-transact-sql?view=sql-server2017



Question No 3 

You use a Microsoft Azure SQL DataBase instance. The instance contains a table named Customers that has columns named Id, Name, and IsPriority. You need to create a view named VwPriorityCustomers that: returns rows from Customer that have a value of True in the IsPriority column, and does not allow columns to be altered or dropped in the underlying table. Which Transact-SQL statement shoul you run?

A. CREATE VIEW VwPriorityCustomers AS SELECT Id, Name FROM dbo.Customers WHERE IsPriority=1 WITH CHECK OPTION
B. CREATE VIEW VwPriorityCustomers WITH VIEW_METADATA AS SELECT Id, Name FROM dbo.Customers WHERE IsPriority=1
C. CREATE VIEW VwPriorityCustomers WITH ENCRYPTION AS SELECT Id, Name FROM dbo.Customers WHERE IsPriority=1
D. CREATE VIEW VwPriorityCustomers WITH SCHEMABINDING AS SELECT Id, Name FROM dbo.Customers WHERE IsPriority=1

Answer: D 

Explanation: 
SCHEMABINDING binds the view to the schema of the underlying table or tables. When SCHEMABINDING is specified, the base table or tables cannot be modified in a way that would affect the view definition.

References: 
https://docs.microsoft.com/en-us/sql/t-sql/statements/create-view-transact-sql?view=sqlserver-2017

Moreover:
https://www.realexamdumps.us/microsoft/70-461-question-answers.html