Archive - 70-762 - Developing SQL Databases

Log-in Register

Archive - 70-762: Developing SQL Databases

Sample Questions

Question: 173
Measured Skill: Optimize database objects and SQL infrastructure (20–25%)

You are tuning a database named MyDatabase.

You need to create an Extended Events session to capture execution plans for queries that run for at least 10 minutes.

The following requirements must be met:

The target must write complete buffers to disk asynchronously.
The system must retain a maximum of 10 files.
Each session must allocate no more than 10 megabytes (MB) of memory for event buffering.

Which four Transact-SQL segments should you use to develop the solution?

(To answer, move the appropriate Transact-SQL segments from the list of Transact-SQL segments to the answer area and arrange them in the correct order.)

www.cert2brain.com

A	Sequence: 2, 1, 6, 7
B	Sequence: 2, 1, 3, 4
C	Sequence: 2, 5, 6, 7
D	Sequence: 2, 5, 3, 4

Correct answer: B

Explanation:

The following example shows the syntax of the CREATE EVENT SESSION statement.


CREATE EVENT SESSION [YourSession]

    ON SERVER 

    ADD EVENT sqlserver.sql_statement_completed

    (

        ACTION(sqlserver.sql_text)

        WHERE

        ( [sqlserver].[like_i_sql_unicode_string]([sqlserver].[sql_text], N'%SELECT%HAVING%')

        )

    )

    ADD TARGET package0.event_file

    (SET

        filename = N'C:\Junk\YourSession_Target.xel',

        max_file_size = (2),

        max_rollover_files = (2)

    )

    WITH (

        MAX_MEMORY = 2048 KB,

        EVENT_RETENTION_MODE = ALLOW_MULTIPLE_EVENT_LOSS,

        MAX_DISPATCH_LATENCY = 3 SECONDS,

        MAX_EVENT_SIZE = 0 KB,

        MEMORY_PARTITION_MODE = NONE,

        TRACK_CAUSALITY = OFF,

        STARTUP_STATE = OFF

    );

GO

We need to capture the actual execution plan (post), not the estimated (pre) execution plan.

MAX_MEMORY =size [ KB | MB ] Specifies the maximum amount of memory to allocate to the session for event buffering. The default is 4 MB. size is a whole number and can be a kilobyte (KB) or a megabyte (MB) value. The maximum amount cannot exceed 2 GB (less than 2048 MB). However, using memory values in GB range is not recommended.

Reference: CREATE EVENT SESSION (Transact-SQL)

Question: 174
Measured Skill: Implement programmability objects (20–25%)

You have a SQL Server database named DB1. DB1 contains tables with the following requirements:

Some records in the table named OrderLines do not exist in the table named Order.
The column named OrderNumber must be a unique value in the Order table.
The OrderNumber column on the OrderLines table allows null values.
The OrderNumber column on the OrderLines table must be validated with the Order table.
Must not allow a new record in the OrderLines table that does not match a record in the Order table.

How should you complete the statements?

(To answer, select the appropriate transact-SQL segments from the drop-down menus in the answer area. NOTE: Each correct selection is worth one point.)

www.cert2brain.com

A	ALTER TABLE Orders ADD CONSTRAINT Order_Key UNIQUE(OrderNumber) ALTER TABLE OrderLines WITH NOCHECK ADD CONSTRAINT Orders_Check CHECK(OrderNumber IN (SELECT OrderNumber FROM Orders))
B	ALTER TABLE Orders ADD CONSTRAINT Order_Key UNIQUE(OrderNumber) ALTER TABLE OrderLines WITH CHECK ADD CONSTRAINT Orders_Check FOREIGN KEY(OrderNumber) REFERENCES OrderLines(OrderNumber)
C	ALTER TABLE Orders ADD CONSTRAINT Order_Key UNIQUE(OrderNumber) ALTER TABLE OrderLines WITH NOCHECK ADD CONSTRAINT Orders_Check CHECK(OrderNumber IN (SELECT OrderNumber FROM Orders))
D	ALTER TABLE Orders ADD CONSTRAINT Order_Key PRIMARY KEY(OrderNumber) ALTER TABLE OrderLines WITH CHECK ADD CONSTRAINT Orders_Check FOREIGN KEY(OrderNumber) REFERENCES OrderLines(OrderNumber)
E	ALTER TABLE Orders ADD CONSTRAINT Order_Key PRIMARY KEY(OrderNumber) ALTER TABLE OrderLines WITH NOCHECK ADD CONSTRAINT Orders_Check FOREIGN KEY(OrderNumber) REFERENCES OrderLines(OrderNumber)
F	ALTER TABLE Orders ADD CONSTRAINT Order_Key PRIMARY KEY(OrderNumber) ALTER TABLE OrderLines WITH CHECK ADD CONSTRAINT Orders_Check CHECK(OrderNumber IN (SELECT OrderNumber FROM Orders))

Correct answer: E

Explanation:

We need to add a primary key constraint to the Orders tabel and a foreign key to the OrderLines table. Since some records in the OrderLines table do not exist in the Order table, we need to specify the NOCHECK option.

Note: A FOREIGN KEY allows inserting NULL values if there is no NOT NULL constraint defined on this key, but the PRIMARY KEY does not accept NULLs.

Question: 175
Measured Skill: Implement programmability objects (20–25%)

You have a memory-optimized table named Customer. The table is accessed by a stored procedure named ManageCustomer.

The database was created in Microsoft SQL Server 2014. A backup and restore operation was used to move the database to SQL Server 2016.

You have performance issues with the stored procedure.

You need to resolve the performance issues and ensure the table statistics are updated automatically.

Which three Transact-SQL segments should you use to develop the solution?

(To answer, move the appropriate Transact-SQL segments from the list of Transact-SQL segments to the answer area and arrange them in the correct order.)

www.cert2brain.com

A	Sequence: 1, 5, 4
B	Sequence: 1, 3, 2
C	Sequence: 4, 5, 2
D	Sequence: 3, 5, 4

Correct answer: A

Explanation:

In the first step, we should upgrade the database compatibility level to SQL Server 2016 (version 13.x). At compatibility level setting 130, the logic that automatically updates statistics is more aggressive on large tables. In practice, this is intended to reduce cases in which customers experience performance problems with queries, where newly inserted rows are frequently queried, but where the statistics have not been updated accordingly and these values are not yet included.

In the second step we should delete the existing interpreted stored procedure.

In the third step, we recreate the stored procedure with native compilation. A stored procedure with native compilation retrieves data from memory-optimized tables directly from memory. An interpreted stored procedure retrieves the data through a disk-based buffer.

Reference: ALTER DATABASE (Transact-SQL) Compatibility Level

Question: 176
Measured Skill: Implement programmability objects (20–25%)

You create tables by using the following Transact-SQL statements:

CREATE TABLE Customer ( CustomerId INT IDENTITY(1,1) PRIMARY KEY, Name VARCHAR(200) NOT NULL, CreditLimit DECIMAL(16,2) NOT NULL ) CREATE TABLE Address ( AddressId INT IDENTITY(1,1) PRIMARY KEY, CustomerId INT NOT NULL REFERENCES Customer(CustomerId), Address1 VARCHAR(200) NOT NULL, Address2 VARCHAR(200) NULL, City VARCHAR(20) NOT NULL, CountryCode CHAR(2) NOT NULL, IsActive BIT NOT NULL )

Each customer may have multiple addresses but only one is the primary address.

You must plan a solution that meets the following requirements:

Return both customers and address information.
Return only the primary address of the customer.
Allow updates of all customer information and address details with the exception of the identity columns and the IsActive column.

Which three actions should you perform is sequence?

(To answer, move the appropriate actions from the list of actions to the answer area and arrange them in the correct order.)

www.cert2brain.com

A	Sequence: 5, 6, 4
B	Sequence: 1, 6, 4
C	Sequence: 1, 3, 2
D	Sequence: 5, 3, 2

Correct answer: B

Explanation:

We should create a view that combines data from the two tables and includes only the primary address of each customer.

The WITH CHECK OPTION restriction 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.

INSTEAD OF triggers can be created on a view to make a view updatable. The INSTEAD OF trigger is executed instead of the data modification statement on which the trigger is defined. This trigger lets the user specify the set of actions that must happen to process the data modification statement. Therefore, if an INSTEAD OF trigger exists for a view on a specific data modification statement (INSERT, UPDATE, or DELETE), the corresponding view is updatable through that statement.

Reference: CREATE VIEW (Transact-SQL)

Question: 177
Measured Skill: Implement programmability objects (20–25%)

You have a database that contains three encrypted stored procedures named dbo.Proc1, dbo.Proc2 and dbo.Proc3. The stored procedures include INSERT, UPDATE, DELETE and BACKUP DATABASE statements.

You have the following requirements:

You must run all the stored procedures within the same transaction.
You must automatically start a transaction when stored procedures include DML statements.
You must not automatically start a transaction when stored procedures include DDL statements.

You need to run all three stored procedures.

Which four Transact-SQL segments should you use to develop the solution?

(To answer, move the appropriate Transact-SQL segments to the answer area and arrange them in the correct order.)

www.cert2brain.com

A	Sequence: 4, 6, 5
B	Sequence: 4, 1, 3
C	Sequence: 4, 1, 2
D	Sequence: 4, 5, 6

Correct answer: A

Explanation:

SET IMPLICIT_TRANSACTIONS sets implicit transaction mode for the connection.

When a connection is in implicit transaction mode and the connection is not currently in a transaction, executing any of the following statements starts a transaction:

ALTER TABLE
FETCH
REVOKE
BEGIN TRANSACTION
GRANT
SELECT (See exception below.)
CREATE
INSERT
TRUNCATE TABLE
DELETE
OPEN
UPDATE
DROP

If the connection is already in an open transaction, the statements do not start a new transaction.

Transactions that are automatically opened as the result of this setting being ON must be explicitly committed or rolled back by the user at the end of the transaction. Otherwise, the transaction and all of the data changes it contains are rolled back when the user disconnects. After a transaction is committed, executing one of the statements above starts a new transaction.

Implicit transaction mode remains in effect until the connection executes a SET IMPLICIT_TRANSACTIONS OFF statement, which returns the connection to autocommit mode. In autocommit mode, all individual statements are committed if they complete successfully.

The SQL Server Native Client OLE DB Provider for SQL Server and the SQL Server Native Client ODBC driver automatically set IMPLICIT_TRANSACTIONS to OFF when connecting. SET IMPLICIT_TRANSACTIONS defaults to OFF for connections with the SQLClientmanaged provider, and for SOAP requests received through HTTP endpoints.

When SET ANSI_DEFAULTS is ON, SET IMPLICIT_TRANSACTIONS is ON.

Executing a BEGIN TRANSACTION statement when SET IMPLICIT_TRANSACTIONS is ON causes two nested transactions to open. BEGIN_TRANSACTION increments @@trancount whenever a transaction is active.

The setting of SET IMPLICIT_TRANSACTIONS is set at execute or run time and not at parse time.

SELECT statements that do not select from a table do not start implicit transactions. For example SELECT GETDATE(); or SELECT 1, 'ABC'; do not require transactions.

See the following article for an example:

SET IMPLICIT_TRANSACTIONS

Tags: exam, examcollection, exam simulation, exam questions, questions & answers, training course, study guide, vce, braindumps, practice test