Microsoft 70-762 Exam Practice Test Instant Access

Question 1

Note: this question is part of a series of questions that use the same or similar answer choices. An answer choice may be correct for more than one question in the series. Each question is independent of the other questions in the series. Information and details provided in a question apply only to that question.

You are developing and application to track customer sales.

You need to create an object that meets the following requirements:

- Run managed code packaged in an assembly that was created in the Microsoft.NET Framework and uploaded in Microsoft SQL Server.

- Run written a transaction and roll back if a future occurs.

- Run when a table is created or modified.

What should you create?

Aextended procedure

BCLR procedure

Cuser-defined procedure

DDML trigger

Escalar-valued function

Ftable-valued function

GDDL trigger

Answer : B

The common language runtime (CLR) is the heart of the Microsoft .NET Framework and provides the execution environment for all .NET Framework code. Code that runs within the CLR is referred to as managed code.

With the CLR hosted in Microsoft SQL Server (called CLR integration), you can author stored procedures, triggers, user-defined functions, user-defined types, and user-defined aggregates in managed code. Because managed code compiles to native code prior to execution, you can achieve significant performance increases in some scenarios.

Question 2

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 that contains the following tables: BlogCategory, BlogEntry, ProductReview, Product, and SalesPerson. The tables were created using the following Transact SQL statements:

You must modify the ProductReview Table to meet the following requirements:

* The table must reference the ProductID column in the Product table

* Existing records in the ProductReview table must not be validated with the Product table.

* Deleting records in the Product table must not be allowed if records are referenced by the ProductReview table.

* Changes to records in the Product table must propagate to the ProductReview table.

You also have the following database tables: Order, ProductTypes, and SalesHistory, The transact-SQL statements for these tables are not available.

You must modify the Orders table to meet the following requirements:

* Create new rows in the table without granting INSERT permissions to the table.

* Notify the sales person who places an order whether or not the order was completed.

You must add the following constraints to the SalesHistory table:

* a constraint on the SaleID column that allows the field to be used as a record identifier

* a constant that uses the ProductID column to reference the Product column of the ProductTypes table

* a constraint on the CategoryID column that allows one row with a null value in the column

* a constraint that limits the SalePrice column to values greater than four

Finance department users must be able to retrieve data from the SalesHistory table for sales persons where the value of the SalesYTD column is above a certain threshold.

You plan to create a memory-optimized table named SalesOrder. The table must meet the following requirements:

* The table must hold 10 million unique sales orders.

* The table must use checkpoints to minimize I/O operations and must not use transaction logging.

* Data loss is acceptable.

Performance for queries against the SalesOrder table that use Where clauses with exact equality operations must be optimized.

You need to enable referential integrity for the ProductReview table.

How should you complete the relevant Transact-SQL statement? To answer? select the appropriate Transact-SQL segments in the answer area.

Select two alternatives.

AFor the first selection select: WITH CHECK

BFor the first selection select: WITH NOCHECK

CFor the second selection select: ON DELETE NO ACTION ON UPDATE CASCADE

DFor the second selection select: ON DELETE CASCADE ON UPDATE CASCADE

EFor the second selection select: ON DELETE NO ACTION ON UPDATE NO ACTION

FFor the second selection select: ON DELETE CASCADE ON UPDATE NO ACTION

Answer : B, C

B: We should use WITH NOCHECK as existing records in the ProductReview table must not be validated with the Product table.

C: Deletes should not be allowed, so we use ON DELETE NO ACTION.

Updates should be allowed, so we use ON DELETE NO CASCADE

NO ACTION: the Database Engine raises an error, and the update action on the row in the parent table is rolled back.

CASCADE: corresponding rows are updated in the referencing table when that row is updated in the parent table.

Note: ON DELETE { NO ACTION | CASCADE | SET NULL | SET DEFAULT }

Specifies what action happens to rows in the table that is altered, if those rows have a referential relationship and the referenced row is deleted from the parent table. The default is NO ACTION.

ON UPDATE { NO ACTION | CASCADE | SET NULL | SET DEFAULT }

Specifies what action happens to rows in the table altered when those rows have a referential relationship and the referenced row is updated in the parent table. The default is NO ACTION.

Note: You must modify the ProductReview Table to meet the following requirements:

1. The table must reference the ProductID column in the Product table

2. Existing records in the ProductReview table must not be validated with the Product table.

3. Deleting records in the Product table must not be allowed if records are referenced by the ProductReview table.

4. Changes to records in the Product table must propagate to the ProductReview table.

References: https://msdn.microsoft.com/en-us/library/ms190273.aspx

https://msdn.microsoft.com/en-us/library/ms188066.aspx

Question 3

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_usage_stats dynamic management view.

Does the solution meet the goal?

AYes

BNo

Answer : A

References:

https://www.mssqltips.com/sqlservertutorial/256/discovering-unused-indexes/

https://www.sqlshack.com/how-to-identify-and-monitor-unused-indexes-in-sql-server/

Question 4

Note: This question is part of a series of questions that use the same answer choices. An answer choice may be correct for more than one question in the series. Each question is independent of the other questions in this series. Information and details provided in a question apply only to that question.

You are developing an application to track customer sales. You create tables to support the application. You need to create a database object that meets the following data entry requirements:

What should you create?

Aextended procedure

BCLR procedure

Cuser-defined procedure

DDML trigger

EDDL trigger

Fscalar-valued function

Gtable-valued function

Answer : C

References: https://msdn.microsoft.com/en-us/library/ms345075.aspx

Question 5

You are optimizing the performance of a batch update process. You have tables and indexes that were created by running the following Transact-SQL statements:

The following query runs nightly to update the isCreditValidated field:

You review the database and make the following observations:

* Most of the IsCreditValidated values in the Invoices table are set to a value of 1.

* There are many unique InvoiceDate values.

* The CreditValidation table does not have an index.

* Statistics for the index IX_invoices_CustomerID_Filter_IsCreditValidated indicate there are no individual seeks but multiple individual updates.

You need to ensure that any indexes added can be used by the update query. If the IX_invoices_CustomerId_Filter_IsCreditValidated index cannot be used by the query, it must be removed. Otherwise, the query must be modified to use with the index.

Which three actions should you perform? Each correct answer presents part of the solution.

NOTE: Each correct selection is worth one point.

AAdd a filtered nonclustered index to Invoices on InvoiceDate that selects where IsCreditNote= 1 and
IsCreditValidated = 0.

BRewrite the update query so that the condition for IsCreditValidated = 0 precedes the condition for
IsCreditNote = 1.

CCreate a nonclustered index for invoices in IsCreditValidated, InvoiceDate with an include statement using
IsCreditNote and CustomerID.

DAdd a nonclustered index for CreditValidation on CustomerID.

EDrop the IX_invoices_CustomerId_Filter_IsCreditValidatedIndex.

Answer : A, B, E

A filtered index is an optimized nonclustered index especially suited to cover queries that select from a welldefined subset of data. It uses a filter predicate to index a portion of rows in the table. A well-designed filtered index can improve query performance as well as reduce index maintenance and storage costs compared with full-table indexes.

References:

https://docs.microsoft.com/en-us/sql/relational-databases/indexes/create-filtered-indexes

Question 6

Note: This question is part of a series of questions that use the same or similar answer choices. An Answer choice may be correct for more than one question in the series. Each question independent of the other questions in this series. Information and details provided in a question apply only to that question.

You are a database developer for a company. The company has a server that has multiple physical disks. The disks are not part of a RAID array. The server hosts three Microsoft SQL Server instances. There are many SQL jobs that run during off-peak hours.

You must monitor the SQL Server instances in real time and optimize the server to maximize throughput, response time, and overall SQL performance.

What should you do?

AA. Create asys.dm_os_waiting_tasks query.

BCreate a sys.dm_exec_sessions query.

CCreate aPerformance Monitor Data Collector Set.

DCreate a sys.dm_os_memory_objects query.

ECreate a sp_configure 'max server memory' query.

FCreate a SQL Profiler trace.

GCreate a sys.dm_os_wait_stats query.

HCreate an Extended Event.

Answer : B

sys.dm_exec_sessions returns one row per authenticated session on SQL Server. sys.dm_exec_sessions is a server-scope view that shows information about all active user connections and internal tasks. This information includes client version, client program name, client login time, login user, current session setting, and more. Use sys.dm_exec_sessions to first view the current system load and to identify a session of interest, and then learn more information about that session by using other dynamic management views or dynamic management functions.

Examples of use include finding long-running cursors, and finding idle sessions that have open transactions.

Question 7

Note: This question is part of a series of questions that use the same or similar answer choices. An answer choice may be correct for more than one question in the series. Each question is independent of the other questions in this series. Information and details provided in a question apply only to that question.

You have a Microsoft SQL Server database named DB1 that contains the following tables:

You frequently run the following queries:

There are no foreign key relationships between TBL1 and TBL2.

You need to minimize the amount of time required for the two queries to return records from the tables.

What should you do?

ACreate clustered indexes on TBL1 and TBL2.

BCreate a clustered index on TBL1 Create a nonclustered index on tbl2 and add the most frequently queried columns as included columns.

CCreate a nonclustered index on tbl2 only.

DCreate unique constraints on both TBL1 and TBL2. Create a partitioned view that combines columns from TBL1 and TBL2.

EDrop existing indexes on TBL1 and then create a clustered columnstore index. Create a nonclustered columnstore index on TBL1. Create a nonclustered index on TBL2.

FDrop existing indexes on TBL1 and then create a cluwered columnstore index. Create a nonclustered columnstore index on TBL1. Make no changes to TBL2.

GCreate check constraints on both TBL1 and tbl2. Create a partitioned view that combines columns from TBL1 and tbl2.

HCreate an indexed view that combines columns from TBL1 and TBL2.

Answer : F

Microsoft 70-762 Developing SQL Databases Exam Practice Test