70-762 무료 덤프문제 온라인 액세스

Note: This question is part of a series of questions that use the same scenario. For your convenience, the scenario is repeated in each question. Each question presents a different goal and answer choices, but the text of the scenario is exactly the same in each question in this series.
You have a database named DB1 that contains the following tables: Customer, CustomerToAccountBridge, and CustomerDetails. The three tables are part of the Sales schema. The database also contains a schema named Website. You create the Customer table by running the following Transact-SQL statement:

The value of the CustomerStatus column is equal to one for active customers. The value of the Account1Status and Account2Status columns are equal to one for active accounts. The following table displays selected columns and rows from the Customer table.

You plan to create a view named Website.Customer and a view named Sales.FemaleCustomers.
Website.Customer must meet the following requirements:
* Allow users access to the CustomerName and CustomerNumber columns for active customers.
* Allow changes to the columns that the view references. Modified data must be visible through the view.
* Prevent the view from being published as part of Microsoft SQL Server replication.
Sales.Female.Customers must meet the following requirements:
* Allow users access to the CustomerName, Address, City, State and PostalCode columns.
* Prevent changes to the columns that the view references.
* Only allow updates through the views that adhere to the view filter.
You have the following stored procedures: spDeleteCustAcctRelationship and spUpdateCustomerSummary.
The spUpdateCustomerSummary stored procedure was created by running the following Transact-SQL statement:

You run the spUpdateCustomerSummary stored procedure to make changes to customer account summaries.
Other stored procedures call the spDeleteCustAcctRelationship to delete records from the CustomerToAccountBridge table.
You need to create Sales.FemaleCustomers.
How should you complete the view definition? To answer, drag the appropriate Transact-SQL segments to the correct locations. Each Transact_SQL segment may be used once, more than once or not at all. You may need to drag the split bar between panes or scroll to view content.

정답:

Explanation

Box 1:WITH SCHEMABINDING:
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.
Box 2:Box 2: WITH CHECK OPTION
CHECK OPTION forces all data modification statements executed against the view to follow the criteria set within select_statement. When a row is modified through a view, the WITH CHECK OPTION makes sure the data remains visible through the view after the modification is committed.
Note: Sales.Female.Customers must meet the following requirements:
References: https://msdn.microsoft.com/en-us/library/ms187956.aspx

You are profiling a frequently used database table named UserEvents. The READ_COMMITED_SNAPSHOT database option is set to OFF.
In the trace results, you observe that lock escalation occurred for one stored procedure even though the number of locks in the database did not exceed memory or configuration thresholds. Events details are provided in the following table:

You need to modify the uspDeleteEvents stored procedure to avoid lock escalation.
How should you modify the stored procedure? To answer, select the appropriate Transact-SQL segments in the answer area.

정답:

Explanation

Delete up to 4000 rows at a time. Keep doing it until all rows have been deleted.
Note that @@ROWCOUNT returns the number of rows affected by the last statement.
References: https://msdn.microsoft.com/en-us/library/ms187316.aspx

Note: This question is part of a series of questions that present the same scenario. Each question in the series contains a unique solution. Determine whether the solution meets the stated goals.
You need to identify the indexes that are not being used so that you can remove them from the database.
Solution: You run the sys.dm_db_index_physical_stats dynamic management view.
Does the solution meet the goal?

• A.Yes
• B.No

You have an existing Microsoft SQL Trace script. You plan to convert the script to an Extended Events session.
You need to collect the trace ID and other required information.
Which system table should you use?

• A.dbo.syssessions
• B.dbo. syinotifications
• C.sysdbmaintplan.
• D.trace xenction_map

You manage a database that supports an internal of Things (IOT) solution. The database records metrics from over 100 million devices every minute. The database requires 99.995 uptime.
The database uses a table named checkins that is 100 gigabytes (GB) in size. The checkins table stores metrics from the devices. The database also has a table named Archive that stores four terabytes (TB) of data. You use store produce for all access to the tables.
You observe that the wait type PAGELATCH_ to causes large amount of blocking.
You need to resolve the blocking issues while minimizing downtime for the database.
Which two actions should you perform? Each correct answer present part of the solution.

• A.Convert all stored procedure that accesses the checkins table to natively compile procedures.
• B.Convert all tables to clustered columnstore indexes.
• C.Convert the checkins table to a cluster clounmstore indexes.
• D.Convert the checkins table to an in memory OLTP table.