Microsoft 70-462 Exam Questions Instant Access

Question 1

You manage a server that has SQL Server installed and hosts databases for five applications. Each application has a different login to the SQL Server instance.

You need to create a TCP endpoint to be used only by a login named Application3. The solution must ensure that Application only uses the new endpoint.

Which three statements should you execute? Each correct answer presents part of the solution.

AGRANT CONNECT ON ENDPOINT::[TSQL Default TCP] TO Application3

BDENY CONNECT ON ENDPOINT::[TSQL Default TCP] TO Application3

CCREATE ENDPOINT Application3_EndPoint STATE=STARTED
AS TCP (LISTENER_PORT=1388)

DGRANT .CONNECT ON ENDPOINT:: Application3_EndPoint TO Public

EGRANT CONNECT ON ENDPOINT::[TSQL Default TCP] TO PUBLIC

FREVOKE CONNECT ON ENDPOINT::[TSQL Default TCP] TO Application3

GREVOKE CONNECT ON ENDPOINT::[TSQL Default TCP] TO PUBLIC

Answer : A, C, G

Question 2

Which of the following statements will set the maximum amount of system memory that is managed by the SQL Server Memory Manager to 4GB?

Asp_configure 'max memory', 4;

BALTER SYSTEM SET 'max server memory'=4096;

Csp_configure 'max server memory', 4;

Dsp_configure 'max server memory', 4096;

Answer : D

References:

https://msdn.microsoft.com/en-us/library/ms178067%28v=sql.110%29.aspx

Question 3

You administer a Microsoft SQL Server 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?

ACreate a custom database role that includes the users. Deny Delete permissions on the Sales schema for the custom database role.

BInclude the Sales schema as an owned schema for the db_denydatawriter role. Add the users to the db_denydatawriter role.

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

DCreate a custom database role that includes the users. Deny Delete permissions on each table in the Sales schema for the custom database role.

Answer : A

Question 4

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 non-clustered 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?

ASELECT COUNT(*) FROM SalesOrdersWHERE OrderTime = CONVERT(DATE, GETDATE())

BSELECT COUNT(*) FROM SalesOrdersWHERE OrderTime = GETDATE()

CSELECT COUNT(*) FROM SalesOrdersWHERE CONVERT(VARCHAR, OrderTime, 112) = CONVERT(VARCHAR, GETDATE(I, 112))

DSELECT COUNT(*) FROM SalesOrdersWHERE OrderTime >= CONVERT(DATE, GETDATE())AND OrderTime < DATEADD(DAY, CONVERT(DATE, GETDATE()))

Answer : D

Question 5

You are designing a data warehouse with two fact tables.

The first table contains sales per month and the second table contains orders per day.

Referential integrity must be enforced declaratively.

You need to design a solution that can join a single time dimension to both fact tables.

What should you do?

AJoin the two fact tables.

BMerge the fact tables.

CCreate a time dimension that can join to both fact tables at their respective granularity.

DCreate a surrogate key for the time dimension.

Answer : D

Question 6

You have three tables that contain data for vendors, customers, and agents. You create a view that is used to look up telephone numbers for these companies.

The view has the following definition:

You need to ensure that users can update only the phone numbers by using this view. What should you do?

AAlter the view. Use the EXPAND VIEWS query hint along with each SELECT statement.

BDrop the view. Re-create the view by using the SCHEMABINDING clause, and then create an index on the view.

CCreate an AFTER UPDATE trigger on the view.

DCreate an INSTEAD OF UPDATE trigger on the view.

Answer : D

Question 7

You administer a Microsoft SQL Server environment. You purchase a new server and plan to migrate your database from SQL Server 2008 to SQL Server 2012.

You want to evaluate to prepare for possible conflicts and issues that may arise during or after the migration.

Which SQL Server tool should you use?

ADistributed Replay

BMigration Assistant

CData Tools

DUpgrade Advisor

Answer : D

Microsoft Administering Microsoft SQL Server 2012 Databases 70-462 Exam Questions