SQL exam 70-461 questions and answers part 4

MS-SQL exam 70-461 questions and answers dumps

QUESTION 31

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

Correct Answer: Below

QUESTION 32

You administer a Microsoft SQL Server 2012 database that contains a table named OrderDetail. You discover that the NCI_OrderDetail_CustomerID non-clustered index is fragmented. You need to reduce fragmentation. You need to achieve this goal without taking the index offline. Which Transact-SQL batch should you use?

A. CREATE INDEX NCI_OrderDetail_CustomerID ON OrderDetail.CustomerID WITH DROP

EXISTING

B. ALTER INDEX NCI_OrderDetail_CustomerID ON OrderDetail.CustomerID REORGANIZE

C. ALTER INDEX ALL ON OrderDetail REBUILD

D. ALTER INDEX NCI_OrderDetail_CustomerID ON OrderDetail.CustomerID REBUILD

Correct Answer: Below

QUESTION 33

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. Reference a user-defined function within the computed column.

B. Create a BEFORE trigger that maintains the state of 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.

Correct Answer: Below

QUESTION 34

You administer a Microsoft SQL Server 2012 database named ContosoDb. The database contains a table named Suppliers and a column named IsActive in the Purchases schema. You create a new user named ContosoUser in ContosoDb. ContosoUser has no permissions to the Suppliers table. You need to ensure that ContosoUser can delete rows that are not active from Suppliers. You also need to grant ContosoUser only the minimum required permissions. Which Transact-SQL statement should you use?

A. GRANT DELETE ON Purchases.Suppliers TO ContosoUser

B. CREATE PROCEDURE Purchases.PurgeInactiveSuppliers

WITH EXECUTE AS USER = 'dbo'

AS

DELETE FROM Purchases.Suppliers WHERE IsActive = 0

GO

GRANT EXECUTE ON Purchases.PurgelnactiveSuppliers TO ContosoUser

C. GRANT SELECT ON Purchases.Suppliers TO ContosoUser

D. CREATE PROCEDURE Purchases.PurgeInactiveSuppliers

AS

DELETE FROM Purchases.Suppliers WHERE IsActive = 0

GO

GRANT EXECUTE ON Purchases.PurgeInactiveSuppliers TO ContosoUser

Correct Answer: Below

QUESTION 35

You use a contained database named ContosoDB within a domain. You need to create a user who can log on to the ContosoDB database. You also need to ensure that you can port the database to different database servers within the domain without additional user account configurations. Which type of user should you create?

A. User mapped to a certificate

B. SQL user without login

C. Domain user

D. SQL user with login

Correct Answer: Below

QUESTION 36

You administer a Microsoft SQL Server 2012 database that has multiple tables in the Sales schema. Some users must be prevented from deleting records in any of the tables in the Sales schema. You need to manage users who are prevented from deleting records in the Sales schema. You need to achieve this goal by using the minimum amount of administrative effort. What should you do?

A. Create a custom database role that includes the users. Deny Delete permissions on the Sales

schema for the custom database role.

B. Include the Sales schema as an owned schema for the db_denydatawriter role. Add the users

to the db_denydatawriter role.

C. Deny Delete permissions on each table in the Sales schema for each user.

D. Create a custom database role that includes the users. Deny Delete permissions on each table

in the Sales schema for the custom database role.

Correct Answer: Below

QUESTION 37

You administer a Microsoft SQL Server 2012 database. The database contains a Product table created by using the following definition: You need to ensure that the minimum amount of disk space is used to store the data in the Product table. What should you do?

A. Convert all indexes to Column Store indexes.

B. Implement Unicode Compression.

C. Implement row-level compression.

D. Implement page-level compression.

Correct Answer: Below

QUESTION 38

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

 

QUESTION 39

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

What should you create for each application?

A. views

B. table partitions

C. table-valued functions

D. stored procedures

QUESTION 40

Your application contains a stored procedure for each country. Each stored procedure accepts an employee identification number through the @EmpID parameter. You plan to build a single process for each employee that will execute the stored procedure based on the country of residence. Which approach should you use?

A. A.a recursive stored procedure

B. Trigger

C. An UPDATE statement that includes CASE

D. Cursor

E. The foreach SQLCLR statement

Correct Answer:

31	32	33	34	35	36	37	38	39	40
C	B	A	D	C	A	D	C	A	D