Developing SQL Databases - Exam 70-762 Certification Test

You have a database named db_test. There is no memory-optimized file group in the database.

You run the below query:

DECLARE @lname varchar(20)=?Patel?;

SELECTT p.fname, Addr.* From People as p

JOIN Bussiness_address

ON p.bussiness_entityId= Bussiness_address.bussiness_entityId

JOIN address as Addr

ON Bussiness_address.address_id= Addr.address_id

Where p.lname=@lname;

Users frequently run the same query with different values for the local variable @lname.

The table named People is persisted on disk.

You have to create an index on the People table that must meet the following requirements:

  • All users must be able to benefit from the index
  • Fname must be added to the index as an included column

What should you do?

Options are :

  • Create a nonclustered Columnstore index on the table
  • Create a clustered index on the table
  • Create a nonclustered index on the table (Correct)
  • Create a clustered Columnstore index on the table

Answer : Create a nonclustered index on the table

Explanation We include a non-key column to create a nonclustered indexes it cover more queries. Because non-key column are data types not allowed as index key columns and it is not consider by the Database engine when calculating the number of index key column or index key size.

In SQL server 2016 which of the following resource pool supports R services?

Options are :

  • External (Correct)
  • User-defined resource pool
  • Internal
  • Default

Answer : External

Explanation External resource pool support R service because the execution of R scripts can be resource intensive.

In which of following transaction isolation level SQL server ignores all the existing locks?

Options are :

  • Read Committed
  • Serializable
  • Read Uncommitted (Correct)
  • Snapshot

Answer : Read Uncommitted

Explanation SQL server ignore existing locks and reads both committed and uncommitted data when the isolation level is Read Uncommitted.

Which one of the following system stored-procedure is not used to monitor SQL database Performance?

Options are :

  • sys.dm_exec_query_stats
  • sys.dm_os_waiting_tasks (Correct)
  • sys.event_log
  • sys.dm_db_resource_stats

Answer : sys.dm_os_waiting_tasks

Explanation Sys.dm_os_waiting_tasks dynamic management view(DMV) is use for finding blocked sessions and also use to find the requests currently waiting for a resource.

You are developing a new application that uses a stored procedure. And this Stored Procedure insert thousands of records as a single batch in to the Persons table.

Users reports that application response time has worsened since the Stored procedure updated. You examine disk-related performance counters for the Microsoft SQL server instance and observe several high values that include a disk performance issue.

You examine wait statistics and observe a high WRITELOG value.

You need to improve the application response time.

Solution: You add a unique clustered index to the Persons table. Does the solution meet the goal?

Options are :

  • Yes (Correct)
  • No

Answer : Yes

Explanation We have to create a unique clustered index on the Persons table.

You need to alter the properties of the Test column of the Demo table in the Info schema. So that, it is of data type nvarchar 20 rather than nvarchar 40.

Which of the following statements can you use to complete this task?

Options are :

  • ALTER COLUMN Test nvarchar(20)
  • ALTER TABLE Test nvarchar(20) ALTER COLUMN [Info].[Demo]
  • ALTER COLUMN [Info].[Demo] Test nvarchar(20)
  • ALTER TABLE [Info].[Demo] ALTER COLUMN Test nvarchar(20) (Correct)

Answer : ALTER TABLE [Info].[Demo] ALTER COLUMN Test nvarchar(20)

Explanation You should use the ALTER TABLE [Info].[Demo] ALTER COLUMN Test nvarchar(20) statement. You should not use ALTER COLUMN [Info].[Demo] Test nvarchar(20) And ALTER COLUMN Test nvarchar(20) statements because this statements must be used as part of the ALTER TABLE statement. You should not use ALTER TABLE Test nvarchar(20) ALTER COLUMN [Info].[Demo] statement because this statement incorrectly has the name of the column placed where the name of the table should be.

You need to remove the unique constraint named UNQ_Test from the Demo table. This table is associated with the Info schema.

Which of the following T-SQL statements can you use to achieve this goal?

Options are :

  • ALTER TABLE Demo. Info DROP CONSTRAINT UNQ_Test
  • ALTER SCHEMA Info.Demo DROP CONSTRAINT UNQ_Test
  • ALTER SCHEMA Demo.Info DROP CONSTRAINT UNQ_Test
  • ALTER TABLE Info.Demo DROP CONSTRAINT UNQ_Test (Correct)

Answer : ALTER TABLE Info.Demo DROP CONSTRAINT UNQ_Test

Explanation You should use the ALTER TABLE Info.Demo DROP CONSTRAINT UNQ_Test statement because this statement drops the UNQ_Test unique constraint from the Demo table in the Info schema.

Your company has workers in different regions around the world. You have to create a database table that stores following workers attendance information:

  • Worker_id
  • Date and time worker checked into work
  • Date and time worker checked out of work

Date and time information must be time zone aware and not store fractional seconds.

Solution: you run the following T-SQL statement:

CREATE TABLE Worker (

            Worker_id int NOT NULL.

            dateCheckedIn datetime2 NOT NULL,

            dateCheckedOut datetime2 NOT NULL);

Does the given solution meet the goal?

Options are :

  • Yes
  • No (Correct)

Answer : No

Explanation Datetime2 data type defines a date that is combined with a time of day that is in 24-hour clock. It is not time-zone aware and it store fractional seconds.

Which of the following keywords can you include when creating a view?

Options are :

  • ENCRYPTION (Correct)
  • COMPUTE
  • COMPUTE BY
  • INTO

Answer : ENCRYPTION

Explanation You should use the ENCRYPTION keyword when creating a view. This stops other users from being able to see the view definition.

You are configuring an appropriate transaction isolation level for sessions. You need to ensure the following:

- Statements in a transaction cannot read data that has been altered but not committed.

- The other transactions cannot modify data read by the current transaction until the transaction commits and that other transactions cannot insert rows with key value that would fall into the range read by statement in the current transaction until that transaction completes. 

Which of the following T-SQL statements can you use to complete this task?

Options are :

  • SET TRANSACTION ISOLATION LEVEL SERIALIZABLE (Correct)
  • SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
  • SET TRANSACTION ISOLATION LEVEL SNAPSHOT
  • SET TRANSACTION ISOLATION LEVEL READ COMMITTED

Answer : SET TRANSACTION ISOLATION LEVEL SERIALIZABLE

Explanation You should use the SET TRANSACTION ISOLATION LEVEL SERIALIZABLE statement when you need to ensure that T-SQL statements cannot read data that has been altered but not committed, that other transactions cannot modify data read by the current transaction until the transaction commits, and that other transactions cannot insert rows with key value that would fall into the range read by statement in the current transaction until that transaction completes.

Which datatype is used for optimistic locking to version stamp in row?

Options are :

  • money
  • rowversion (Correct)
  • date
  • char

Answer : rowversion

Explanation rowversion is used for optimistic locking to version stamp in row. Its synonym is timestamp.

You need to determine the specific line in a block of T-SQL code that caused an error. You must configure an appropriate TRY . . . CATCH construct, using the code so that you can further diagnose the error.

Which of the following functions can you use to complete this task?

Options are :

  • ERROR_MESSAGE()
  • ERROR_NUMBER()
  • ERROR_PROCEDURE()
  • ERROR_LINE() (Correct)

Answer : ERROR_LINE()

Explanation You can use the ERROR_LINE() function to determine the line number at which the error occurred.

The ABC and XYZ views are associated with the Demo schema. You need to move the XYZ view to the Test schema and delete the ABC view.

Solution: You run the following T-SQL statement:

 DROP VIEW [Demo].[ABC]

ALTER SCHEMA [Test] TRANSFER [Demo].[XYZ]  

Does the solution meet the goal?

Options are :

  • Yes (Correct)
  • No

Answer : Yes

Explanation ALTER SCHEMA [Test] TRANSFER [Demo].[XYZ] statement moves the XYZ view from the Demo schema to the Test schema. DROP VIEW [Demo].[ABC] statement removes the ABC view from the Demo schema.

In which of the following system stored procedure list of logical and comparison operators for the trace filter are available?

Options are :

  • sp_trace_create
  • sp_trace_setfilter (Correct)
  • sp_trace_setevent
  • sp_trace_setstatus

Answer : sp_trace_setfilter

Explanation sp_trace_setfilter system stored procedure contains a list of logical and comparison operators for the trace filter.

You want to create a stored procedure that generates a list of the standard deviation of prices listed for ItemID stored in the Demo.Items table.

Which of the following T-SQL statements can you use to complete this task?

Options are :

  • CREATE PROCEDURE Demo.uspItemData AS SET NOCOUNT ON; SELECT ItemID, STDEV(price) FROM Demo.Items WHERE ItemID IS NOT NULL GROUP BY ItemID; (Correct)
  • CREATE PROCEDURE Demo.uspItemData AS SET NOCOUNT ON; SELECT ItemID, AVG(price) FROM Demo.Items WHERE ItemID IS NOT NULL GROUP BY ItemID;
  • CREATE PROCEDURE Demo.uspItemData AS SET NOCOUNT ON; SELECT ItemID, MIN(price) FROM Demo.Items WHERE ItemID IS NOT NULL GROUP BY ItemID;
  • CREATE PROCEDURE Demo.uspItemData AS SET NOCOUNT ON; SELECT ItemID, MAX(price) FROM Demo.Items WHERE ItemID IS NOT NULL GROUP BY ItemID;

Answer : CREATE PROCEDURE Demo.uspItemData AS SET NOCOUNT ON; SELECT ItemID, STDEV(price) FROM Demo.Items WHERE ItemID IS NOT NULL GROUP BY ItemID;

Explanation You should use this statement because this statement creates a stored procedure that generates a list of the standard deviation of prices listed for ItemID stored in the Demo.Items table.

You need to re-enable the existing trigger named reminder that applies to the Demo table. You might need to disable this trigger in the future.

Which of the following T-SQL statements can you use to achieve this goal?

Options are :

  • ENABLE TRIGGER reminder ON Demo (Correct)
  • DROP TRIGGER reminder
  • DISABLE TRIGGER reminder ON Demo
  • ALTER TRIGGER reminder ON Demo

Answer : ENABLE TRIGGER reminder ON Demo

Explanation You should use the ENABLE TRIGGER reminder ON Demo statement because this statement will enable the disabled trigger.

You are developing a table named Items that has a column named ItemNumber.

The ItemNumber column requires a special format. The contents of the ItemNumber column must always start with the letters “IT_? followed by 4 digits.

The ItemNumber column is also used in other tables. The special formatting requirement for the ItemNumber column applies to all tables in which the ItemNumber column is used.

You have to ensure that the special formatting is implemented on the ItemNumber column. Your solution must minimize the effort required.

Solution: You implement an INSTEAD OF trigger.

Does this solution meet the goal?

Options are :

  • Yes
  • No (Correct)

Answer : No

Explanation This solution does not meet the goal because an INSTEAD OF trigger would have to be created on every table that uses the ItemNumber column.

The following statement is true or false:

The maximum key size for non-clustered index is 1700 bytes and 900 for a clustered index.

Options are :

  • true (Correct)
  • false

Answer : true

Explanation The maximum key size for non-clustered index is 1700 bytes and 900 for a clustered index.

In which of the following situation dirty reads occur in SQL server 2016?

Options are :

  • When data is read more than once within the same transaction while another transaction updates the same data between read operations.
  • When two processes read the same row and then update that data with different values
  • When one transaction reads the same data multiple times while another transaction inserts or updates a row between read operations.
  • When uncommitted transaction updates a row at the same time that another transaction reads that row with its new value. (Correct)

Answer : When uncommitted transaction updates a row at the same time that another transaction reads that row with its new value.

Explanation Dirty reads occur when uncommitted transaction updates a row at the same time that another transaction reads that row with its new value.

Which one of the following data mask function allow us to defined front and back value to be not-masked and replacing the center with a fixed string value?

Options are :

  • Random ()
  • Partial () (Correct)
  • Default ()
  • Email ()

Answer : Partial ()

Explanation Partial () data masking function allow us defined values from the front and back, and to replace the center with a fixed string value.

Which of the following system stored procedure must be called once for each data column of the events to capture in trace?

Options are :

  • sp_trace_setevent (Correct)
  • sp_trace_setfilter
  • sp_trace_create
  • sp_trace_setstatus

Answer : sp_trace_setevent

Explanation sp_trace_setevent procedure must be called once for each data column of the events to capture in trace.

Which of the following system stored procedure is used to enable statistics collection for SQL server instance at the Procedure level?

Options are :

  • sys.dm_db_index_usage_stats
  • Sys.sp_query_store_flush_db
  • Sys.sp_xtp_control_proc_exec_stats (Correct)
  • Sys.sp_xtp_control_query_exec_stats

Answer : Sys.sp_xtp_control_proc_exec_stats

Explanation Sys.sp_xtp_control_proc_exec_stats system stored-procedure is used to enable statistics collection for SQL server instance at the procedure level and for query level use sys.sp_xtp_control_exec_stats.

You administer at Microsoft SQL server 2012 database that contains a table name Order_detail.

You discover that the NCI_orderdetails_customerid non-clustered index is fragmented. You need to reduce fragmentation.

You have to achieve this goal without taking the index offline.

Which of the following T-SQL statement you use to achieve this goal?

Options are :

  • ALTER INDEX ALL ON OrderDetail REBUILD
  • ALTER INDEX NCI_OrderDetail_CustomerID ON OrderDetail.CustomerID REBUILD
  • CREATE INDEX NCI_OrderDetail_CustomerID ON OrderDetail.CustomerID WITH DROP EXISTING
  • ALTER INDEX NCI_OrderDetail_CustomerID ON OrderDetail.CustomerID REORGANIZE (Correct)

Answer : ALTER INDEX NCI_OrderDetail_CustomerID ON OrderDetail.CustomerID REORGANIZE

Explanation You can modify an existing table or view index by disabling, reorganizing, rebuilding the index or setting the options on the index. The REORGANIZE operation is always performing online and queries or updates to the underlying table can continue during the ALTER INDEX REORGANIZE transaction.

You have two tables in database. Table 1 is a portioned table and Table 2 is non-portioned table. Users report that queries take long time to complete.

You analyze queries by using the Microsoft SQL server profiler. You also observe lock escalation for Table 1 and Table 2.

You need to allow escalation of Table1 locks to the partition level and prevent all lock escalation for Table 2.

Which of the following transaction isolation level is most pessimistic and use range locks on the data to prevent changes and insertions?

Options are :

  • Read uncommitted
  • Read Committed Snapshot
  • Serializable (Correct)
  • Snapshot

Answer : Serializable

Explanation The Serializable transaction isolation level is one the most pessimistic and it locks data prevent changes.

You have database that have following tables:

PostCategory, PostEntry, ItemReview, Item, and SalesPeople. The tables are created using following script:

CREATE TABLE PostCategory

(

            Cat_id int NOT NULL PRIMARY KEY,

            Cat_name varchar (20)

);

CREATE TABLE PostEntry

(

            Entry int NOT NULL PRIMARY KEY,

            Entry_title varchar (50),

            Category int NOT NULL FOREIGN KEY REFERENCES PostCategory(Cat_id)

);

CREATE TABLE ItemReview

(

            Itemreview_id int IDENTITY(1, 1) PRIMARY KEY,

            Item int NOT NULL,

            Review nvarchar (1000) NOT NULL

);

CREATE TABLE Item

(

            Item_id int IDENTITY(1, 1) PRIMARY KEY,

            Name varchar (1000) NOT NULL

);

CREATE TABLE SalesPeople

(

            Salepeople_id int IDENTITY(1, 1) PRIMARY KEY,

            Name varchar (1000) NOT NULL,

            Sales_id int NOT NULL

);

You must modify ItemReview table to meet the following requirements:

The table must reference Item_id column in the Item table.

Existing records in the ItemReview  table must not be validated with the Item table.

Deleting records in the Item table must not be allowed if records are referenced by the ItemReview table.

Changes to records in the Item table must propagate to the ItemReview table.

You have to enable referential integrity for the ItemReview table.

How should you complete the T-SQL statement? You have to select appropriate option?

ALTER TABLE ItemReview

OPTION 1

ADD CONSTRAINT pk_Itemreview FOREIGN KEY(Item_id)

REFERENCE Item(Item_id) OPTION 2

Options are :

  • Option 1: WITH NOCHECK Option 2: ON DELETE NO ACTION ON UPDATE CASCADE (Correct)
  • Option 1: WITH CHECK Option 2: ON DELETE CASCADE ON UPDATE NO ACTION
  • Option 1: WITH NOCHECK Option 2: ON DELETECASCADE ON UPDATE CASCADE
  • Option 1: WITH CHECK Option 2: ON DELETE CASCADE ON UPDATE NO

Answer : Option 1: WITH NOCHECK Option 2: ON DELETE NO ACTION ON UPDATE CASCADE

Explanation Option 1: we use WITH NOCHECK as existing records in the ItemReview table must not validated with the Item table. Option 2: Delete and update is not allowed, so we use ON DELETE NO ACTION.

You are configuring an appropriate transaction isolation level for sessions. You need to ensure that statements in the transaction cannot read data altered by other transactions if those transactions have not yet committed and data selected during the transaction should be able to be modified during the current transaction by update statements issued later in the transaction and the other transactions should be able to insert data into rows with key value that would fall into the range read by statement in the current transaction.

Which of the following T-SQL statements can you use to complete this task?

Options are :

  • None
  • SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
  • SET TRANSACTION ISOLATION LEVEL SNAPSHOT
  • SET TRANSACTION ISOLATION LEVEL READ COMMITTED (Correct)

Answer : SET TRANSACTION ISOLATION LEVEL READ COMMITTED

Explanation You should use the SET TRANSACTION ISOLATION LEVEL READ COMMITTED statement when you need to ensure that statements cannot read data altered by other transactions when those transactions have not yet been committed. When you set the READ COMMITTED transaction level, data can be altered by other transactions between individual statements within the current transaction.

You are analyzing the performance of a database environment.

You need to find all unused indexes in the current database.

How you complete the T-SQL statement? For answer select the appropriate Options.

SELECT a.name as table_name, b.name ass index_name

FROM OPTION1 as b

INNER JOIN OPTION2 as s ON s.object_id=b.object_id AND s.index_id=b.index_id

INNER JOIN OPTION3 as a ON b.object_id=a.object_id

WHERE ((user_seeks= 0 AND user_scans = 0 AND user_lookups = 0) OR s.object_id is null)

Options are :

  • Option 1: sys.views Option 2: sys.dm_exec_requests Option 3: sys.datbases
  • Option 1: sys.indexes Option 2: sys.dm_db_index_usage_stats Option 3: sys.indexes (Correct)
  • Option 1: sys.database Option 2: sys.dm_exec_query_stats Option 3: sys.tables
  • Option 1: sys.tables Option 2: sys.dm_db_missing_index_stats Option 3: sys.views

Answer : Option 1: sys.indexes Option 2: sys.dm_db_index_usage_stats Option 3: sys.indexes

Explanation The sys.indexes contains one row for each index and table in the current database. When we use SQL sys.dm_db_index_usage_stats system stored procedure then SQL server cache is flushed whenever we restart SQL server instance.

sp_trace_setstatus procedure starts, stops or removes a trace. Is it true or false?

Options are :

  • true (Correct)
  • false

Answer : true

Explanation sp_trace_setstatus system stored procedure starts, stops or removes a trace.

You develop a Demo database. You create a view that performs the following tasks:

- Joins 8 tables that contain up to 400,000 records each.

- Performs aggregations on 4 fields.

- View is frequently used in several reports.

You want to improve the performance of the reports.

What should you do?

Options are :

  • Convert the view into a table-valued function.
  • Convert the view into an indexed view. (Correct)
  • Convert the view into a Common Table Expression (CTE).
  • Convert the view into a stored procedure and retrieve the result from the stored procedure into a temporary table.

Answer : Convert the view into an indexed view.

Explanation Creating a unique clustered index on a view improves query performance.

Comment / Suggestion Section
Point our Mistakes and Post Your Suggestions