Developing SQL Databases - Exam 70-762 Certification

You are a database developer for companyX. Your company hosts a web application.

The database contains a table named Item that is defined by the following statements:

CREATE TABLE dbo.Item

(

            ItemID INT IDENTITIY(1,1) PRIMARY KEY CLUSTERED NOT NULL,

            ItemName NVARCHAR(100) NOT NULL,

            ItemPrice DECIMAL(10,2) NOT NULL,

            ISValid BIT DEFAULT(1) NOT NULL

)

You have a table named SaleOrder that contains information about items sold to clients. The lines of the table definition are as follows (Here, Linenumbers included for reference only):

01 CREATE TABLE dbo.SaleOrder

02 (

03        SaleOrderId INT IDENTITY(1,1) PRIMARY KEY NONCLUSTERED NOT NULL,

04        ItemID INT NOT NULL,

05        SaleDate DATETIME DEFAULT (GETDATE()),

06        Qty DECIMAL(10,2) NOT NULL

07 )

You have the following requirements:

  • Ensue that the ItemPrice column in the Item table always has a positive value.
  • Ensure that deleted items are retained in the table with the ISValid flag set to 0.
  • Extend the existing Item table by adding a field ItemISONum. That field will store numeric values that are smaller than 180 and this field will always have a value.

You have to create a function that returns all unsold items for a given month.

Solution: Prepare an AFTER DELETE trigger on the dbo.Item table to change the ISValid flag to 0.

Does this solution meet the goal?

Options are :

  • No (Correct)
  • Yes

Answer : No

Explanation This solution does not meet the goal. You should not prepare an AFTER DELETE trigger on the dbo.Item table to change the ISValid flag to 0. An AFTER trigger runs when the action has already taken place. You should use INSTEAD OF trigger to reach the goal.

You are a Database Administrator for a company. Company has a server that contains many physical disks and these disks are not part of Redundant Array of independent disk(RAID). The server hosts four Microsoft SQL server instances. Many SQL jobs runs during off-peak hours.

You have to monitor and optimize SQL server to increase throughput, response time and overall performance of SQL. You have to identify previous situations where a modification prevents a query to select data from a table.

You have to create a baseline set of metrics to report how the computer running SQL server operates under normal load. The baseline must include the resource usage associated with the server processes.

What should you do?

Options are :

  • Create a sys.dm_os_wait_stats query
  • Create a sp_configure 'max server memory' query
  • Create a sys.dm_os_waiting_tasks query.
  • Create a sys.dm_os_memory_objects query (Correct)

Answer : Create a sys.dm_os_memory_objects query

Explanation Sys.dm_os_memory_objects return the memory objects that are currently allocated by SQL server. We can use this stored-procedure to analyze memory uses and to identify possible memory leaks.

70-764 Administering a SQL Database Infrastructure Exam

Which query or command you use to find the most recent update for statistics on tables or indexed views?

Options are :

  • SELECT name, auto_created (object_id, stats_id) FROM sys.stats WHERE object_id IN(SELECT object_id FROM sys.objects WHERE type=’U’);
  • SELECT name, STATS_DATE (object_id, stats_id) FROM sys.stats WHERE object_id IN(SELECT object_id FROM sys.objects WHERE type=’U’); (Correct)
  • DBCC SHOW_STARISTICS(‘ConsolidatedMessengerDB’, All indexes);
  • Not any query use

Answer : SELECT name, STATS_DATE (object_id, stats_id) FROM sys.stats WHERE object_id IN(SELECT object_id FROM sys.objects WHERE type=’U’);

Explanation The sys.stats catalog view contains both stats_id, and object_id columns it is necessary to use the STATS_DATE system function t returns most recent update date for an object’s statistics.

You are a database developer for companyX. Your company hosts a web application.

The database contains a table named Item that is defined by the following statements:

CREATE TABLE dbo.Item

(

            ItemID INT IDENTITIY(1,1) PRIMARY KEY CLUSTERED NOT NULL,

            ItemName NVARCHAR(100) NOT NULL,

            ItemPrice DECIMAL(10,2) NOT NULL,

            ISValid BIT DEFAULT(1) NOT NULL

)

You have a table named SaleOrder that contains information about items sold to clients. The lines of the table definition are as follows (Here, Linenumbers included for reference only):

01 CREATE TABLE dbo.SaleOrder

02 (

03        SaleOrderId INT IDENTITY(1,1) PRIMARY KEY NONCLUSTERED NOT NULL,

04        ItemID INT NOT NULL,

05        SaleDate DATETIME DEFAULT (GETDATE()),

06        Qty DECIMAL(10,2) NOT NULL

07 )

You have the following requirements:

  • Ensue that the ItemPrice column in the Item table always has a positive value.
  • Ensure that deleted items are retained in the table with the ISValid flag set to 0.
  • Extend the existing Item table by adding a field ItemISONum. That field will store numeric values that are smaller than 180 and this field will always have a value.

You have to create a function that returns all unsold items for a given month.

Solution: Prepare a scalar valued function to return all unsold items for a given month.

Does this solution meet the goal?

Options are :

  • Yes
  • No (Correct)

Answer : No

Explanation This solution does not meet the goal. You should not prepare a scalar valued function to return all unsold items for a given month. Scalar valued functions return a single value and requirement is that the function returns information about all items that have not been sold.

Which monitoring tools can you use to get information about SQL Server memory usage without writing any code?

Options are :

  • Server-side SQL Trace
  • DMVs
  • None
  • Performance Monitor (Correct)

Answer : Performance Monitor

Explanation Performance Monitor provides graphical interfaces that you can use to monitor SQL Server’s memory usage.

70-764 Administering a SQL Database Infrastructure Mock Exam

You are a database developer at software company. You create a stored procedure that contains proprietary code.    

You need to protect the code from being viewed by your customers.

Which Stored Procedure option you use?

Options are :

  • ENCRYPT BY CERT
  • ENCRYPT BY KEY
  • ENCRYPTION (Correct)
  • ENCRYPT BY PASS PHRASE

Answer : ENCRYPTION

Explanation When we use encryption we cannot get the definition of stored procedure.

Which of the following system stored procedure must be called once for each filter on an event data column?

Options are :

  • sp_trace_create
  • sp_trace_setfilter (Correct)
  • sp_trace_setevent
  • sp_trace_setstatus

Answer : sp_trace_setfilter

Explanation sp_trace_setfilter procedure must be called once for each filter on an event data column.

You have a data warehouse that stored sales data. One fact table has 100 million rows.You must reduce storage needs for the data warehouse.You need to implement a solution that uses column-based storage and provides real-time analytics for the operational workload.Solution: You load the data in a heap table.Does this solution meet the goal?

Options are :

  • Yes
  • No (Correct)

Answer : No

Explanation A heap table means a table without any index. You should use an index.

70-764 Administering a SQL Database Infrastructure Question and Answers

You have a stored procedure name Sp_procedure. In your stored procedure you have the following statement:SET IMPLICIT_TRANSACTION ONYou need to identify which statements will end a transaction.Which two statements will end a transaction?

Options are :

  • SAVE TRANSACTION
  • ROLLBACK TRANSACTION (Correct)
  • BEGIN TRANSACTION
  • COMMIT TRANSACTION (Correct)

Answer : ROLLBACK TRANSACTION COMMIT TRANSACTION

Explanation The COMMIT TRANSACTION statement will end a transaction. This statement will end implicit and explicit transactions. This statement would be used to complete a transaction in the code. The ROLLBACK TRANSACTION statement will also end a transaction. This statement will undo implicit and explicit transactions. This statement would be used if an error is detected in the code.

You are developing a table named Items and 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 add a CHECK constraint.

Does the solution meet the goal?

Options are :

  • Yes
  • No (Correct)

Answer : No

Explanation This solution does not meet the goal because a CHECK constraint would have to be maintained in all the tables. A CHECK constraint would prevent storage of data that has an incorrect format, which is not a requirement in this case.

Which of the following system stored procedures are used to manage the query store? (choose all possible answers)

Options are :

  • sp_query_store_remove_query (Correct)
  • sp_query_store_flush_db (Correct)
  • sp_query_store_remove_plan (Correct)
  • sp_query_store_force_plan (Correct)

Answer : sp_query_store_remove_query sp_query_store_flush_db sp_query_store_remove_plan sp_query_store_force_plan

Explanation sp_query_store_flush_db, sp_query_store_force_plan, sp_query_store_remove_plan, sp_query_store_remove_query, sp_query_store_reset_exec_stats, sp_query_store_unforce_plan etc. system stored procedures are used to manage the query store.

70-764 Administering a SQL Database Infrastructure Question and Answers

You administer a Microsoft SQL Server 2016 database that contains a table named Orders. You discover that the NCI_Orders_CustomerId non-clustered index is fragmented.

- You need to reduce fragmentation.

- You need to achieve this goal without taking the index offline.

Which T-SQL Statement should you use?

Options are :

  • Alter index NCI_Orders_CustomerId ON Orders REBUILD.
  • Alter index ALL ON Orders REBUILD.
  • Alter index NCI_Orders_CustomerId ON Orders REORGANIZE. (Correct)
  • Create index NCI_Orders_CustomerId ON Orders(CustomerId) WITH(DROP_EXISTING=ON).

Answer : Alter index NCI_Orders_CustomerId ON Orders REORGANIZE.

Explanation You Should Alter index NCI_Orders_CustomerId ON Orders REORGANIZE. The REORGANIZE works online without taking the index offline. You cannot use REBUILD option because it works offline.

Which of the following dynamic management views is used to view all current locks, the lock resources, lock modes and other related information?

Options are :

  • sys.dm_tran_locks (Correct)
  • sys.dm_os_waiting_tasks
  • sys.dm_os_wait_stats

Answer : sys.dm_tran_locks

Explanation sys.dm_tran_locks: It is used to view all current locks, the lock resources, lock modes and other related information. sys.dm_os_waiting_tasks: It is used to see which tasks are waiting for resource. sys.dm_os_wait_stats: It is used to see how often processes are waiting while locks are taken.

You are using the concept of partitioned view.

True or False: A partitioned view is based on a query that uses a UNION ALL set operator to treat multiple tables as one.

Options are :

  • False
  • True (Correct)

Answer : True

Explanation The given statement is true. A partitioned view is based on a query that uses a UNION ALL set operator to treat multiple tables as one.

70-764 Administering a SQL Database Infrastructure

True or False: By default, UNIQUE Constraint creates a Unique Clustered index.

Options are :

  • False (Correct)
  • True

Answer : False

Explanation The UNIQUE Constraint creates a Unique Nonclustered index by default. If you want to create UNQIUE Clustered index specify Clustered Keyword. Alter table table_name Add constraint constraint_name UNIQUE CLUSTERED(Column_name);

You are using SQL Server 2016 database. you need to optimize the performance of the database.

Query store is currently configured to record actual execution plans. You want to compare those plans with estimated query execution plans of queries you are trying to optimize.

You need to display estimated execution plan when you execute query by using T-SQL. Your solution must work in future versions of SQL Server

Solution: you use the following statements:

SET SHOWPLAN_XML ON;

GO

Does the solution meet the goal?

Options are :

  • Yes (Correct)
  • No

Answer : Yes

Explanation You should use the following code: SET SHOWPLAN_XML ON; GO You should execute SET SHOWPLAN_XML ON. This will show the estimated execution plan when you execute statements within the session. T-SQL statements executes after this statement are not executed by SQL server. Only the execution plan information will be returned. The solution also works in future versions of SQL Server. SET SHOWPLAN_TEXT ON and SET SHOWPLAN_ALL ON this SET option is marked as deprecated and should not be used for future versions of SQL.

You have a data warehouse that stored sales data. One fact table has 100 million rows.You must reduce storage needs for the data warehouse.You need to implement a solution that uses column-based storage and provides real-time analytics for the operational workload.Solution: Create a Non-clustered columnstore index.Does this solution meet the goal?

Options are :

  • No
  • Yes (Correct)

Answer : Yes

Explanation You should use a columnstore index for column-based storage. For real-time analytics you should use nonclustered columnstore index. Columnstore indexes works well on data warehouse table.

70-764 Administering a SQL Database Infrastructure Exam

You should use Columnstore index when you only need a small percentage of the columns form the table.

Which of the following data types are not supported by Columnstore index? (choose all possible options)

Options are :

  • text (Correct)
  • xml (Correct)
  • sql_variant (Correct)
  • varchar(max) (Correct)

Answer : text xml sql_variant varchar(max)

Explanation The Columnstore index not support sql_variant, xml, text, varchar(max), rowversion, CLR based types, ntext, image, nvarchar(max).

You want to prevent rows being deleted from a table with referring data, or inserting or updating data into the referring table that does not match the referenced table.

Which of the following constraint should you use?

Options are :

  • Foreign Key Constraint (Correct)
  • UNIQUE Constraint
  • Check Constraint
  • Primary Key Constraint

Answer : Foreign Key Constraint

Explanation You should use the Foreign Key Constraint to prevent rows being deleted from a table with referring data, or inserting or updating data into the referring table that does not match the referenced table.

70-764 Administering a SQL Database Infrastructure

You are creating a memory-optimized table and you have to ensure that SQL server guarantees the durability of table schema and data.

Which of the following Durability option should you use?

Options are :

  • SCHEMA_ONLY
  • SCHEMA_AND_DATA (Correct)

Answer : SCHEMA_AND_DATA

Explanation You should use the SCHEMA_AND_DATA durability option to ensure that SQL Server guarantees the durability of table schema and data. Use SCHEMA_ONLY durability option to ensure only durability of table schema.

You are using views to provide an interface for an application.

Which of the following option should you use so an application that accesses the view sees the metadata?

Options are :

  • WITH ENCRYPTION
  • WITH CHECK OPTION
  • WITH SCHEMABINDING
  • WITH VIEW_METADATA (Correct)

Answer : WITH VIEW_METADATA

Explanation You should use the WITH VIEW_METADATA option for views. So an application that accesses the view sees the metadata.

True or False: No locks are acquired for snapshot isolation level.

Options are :

  • False
  • True (Correct)

Answer : True

Explanation The given statement is true. No locks are acquired for snapshot isolation level.

Developing SQL Databases - Exam 70-762 Certification Exam

The following statement is true or false?

You can reference a view in a natively compiled stored procedure.

Options are :

  • False (Correct)
  • True

Answer : False

Explanation The given statement is false because you cannot reference a view in a natively compiled stored procedure. You have to define your desired SELECT statement explicitly in the store procedure.

You use SQL Server 2016 to maintain the data used by applications at your company.You want to execute two statements.You need to guarantee that either both statements succeed, or both statements fail together as a batch.Which code should you use?

Options are :

  • BEGIN TRY BEGIN TRANSACTION INSERT table1(field1) VALUES (‘One’) INSERT table2(field1) VALUES (‘Two’) COMMIT TRANSACTION END TRY BEGIN CATCH ROLLBACK TRANSACTION END CATCH (Correct)
  • BEGIN TRANSACTION INSERT table1(field1) VALUES (‘One’) INSERT table2(field1) VALUES (‘Two’) IF @@ERROR =0 COMMIT TRANSACTION ELSE ROLLBACK TRANSACTION
  • BEGIN TRY INSERT table1(field1) VALUES (‘One’) INSERT table2(field1) VALUES (‘Two’) END TRY BEGIN CATCH THROW END CATCH
  • BEGIN TRY INSERT table1(field1) VALUES (‘One’) INSERT table2(field1) VALUES (‘Two’) END TRY BEGIN CATCH THROW ROLLBACK TRANSACTION END CATCH

Answer : BEGIN TRY BEGIN TRANSACTION INSERT table1(field1) VALUES (‘One’) INSERT table2(field1) VALUES (‘Two’) COMMIT TRANSACTION END TRY BEGIN CATCH ROLLBACK TRANSACTION END CATCH

Explanation You should use BEGIN TRY BEGIN TRANSACTION INSERT table1(field1) VALUES (‘One’) INSERT table2(field1) VALUES (‘Two’) COMMIT TRANSACTION END TRY BEGIN CATCH ROLLBACK TRANSACTION END CATCH Structure because if any error occurs in any statements then all are rollback otherwise it is save to database.

You have to create a view name Customer that must meet the following requirements:

- 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.

Which of the following two options you use at the time of creating a view?

Options are :

  • WITH VIEW_METADATA
  • WITH CHECK OPTION (Correct)
  • WITH ENCRYPTION (Correct)
  • VIEW SCHEMABINDING

Answer : WITH CHECK OPTION WITH ENCRYPTION

Explanation You should use the WITH ENCRYPTION Option to prevent the view from being published as part of SQL Server Replication. You should use the WITH CHECK OPTION makes sure the data remains visible through the view after the modification is committed.

70-764 Administering a SQL Database Infrastructure

True or False: A Primary Key constraint creates a unique clustered index.

Options are :

  • False
  • True (Correct)

Answer : True

Explanation The PRIMARY KEY constraint creates a unique clustered index by default. Only one clustered index is allowed per table.

Can we use the DBCC SHOW_STATISTICS command to check the histogram and index densities for an index?

Options are :

  • No
  • Yes (Correct)

Answer : Yes

Explanation Yes, we can use the DBCC SHOW_STATISTICS command to check the histogram and index densities for an index.

You have a database that contains the following tables.

Create table dbo.Product

(

            ProductId int NOT NULL IDENTITY (1,1) PRIMARY KEY,

            Name varchar (1000) NOT NULL

)

Create table dbo.ProductReview

(

            ProductReviewId int NOT NULL IDENTITY (1,1) PRIMARY KEY,

            Review varchar (1000) NOT NUL,

            ProductId int NOT NULL

)

You must modify the dbo.ProductReview table to meet the following requirements:

- The table must reference the ProductId column in the dbo.Product table.  

- Existing records in the ProductReview table must not be validated with Product table and foreign key must be disable.

- Deleting records in the Product table must Propagate to the ProductReview table.

You need to enable referential integrity for the ProductReview table.

How should you complete the T-SQL statement?

 Alter table dbo.ProductReview Option-1

Add constraint fk_ProductReview_ProductId FOREIGN KEY(ProductId) Option-2;

Select two alternatives.

Options are :

  • Option-1: NOCHECK (Correct)
  • Option-2: ON DELETE CASCADE (Correct)
  • Option-1: WITH NOCHECK
  • Option-2: ON DELETE SET NULL
  • Option-2: ON DELETE NO ACTION
  • Option-1: WITH CHECK

Answer : Option-1: NOCHECK Option-2: ON DELETE CASCADE

Explanation The NOCHECK disable the foreign key or check constraint. The ON DELETE CASCADE specifies that the column will be deleted when the referenced column is deleted.

70-764 Administering a SQL Database Infrastructure

You are administer Microsoft SQL Server 2016.You need to restrict a login name Login_User into SQL Server.Which trigger should you use?

Options are :

  • DML Trigger
  • Logon Trigger (Correct)
  • Server-Level DDL Trigger
  • Database-Level DDL Trigger

Answer : Logon Trigger

Explanation You should use Logon Trigger to restrict logins to SQL Server, Track login activity, Limit the number of sessions for specific login

Which of the following Transaction isolation level not solve problems like: Dirty Reads, Non-repeatable Reads, Phantom Reads, Lost Updates?

Options are :

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

Answer : Read Uncommitted

Explanation Read Uncommitted isolation level not solve any problems like: Dirty Reads, Non-repeatable Reads, Phantom Reads, Lost Updates.

Which type of view you create if you want to federate data from multiple table?

Options are :

  • Schema-bind view
  • Partitioned View (Correct)
  • Indexed view
  • Encrypted View

Answer : Partitioned View

Explanation You should create a Partitioned view if you want to federate data from multiple table. The Partitioned view use UNION ALL Set operator to treat multiple tables as one.

70-764 Administering a SQL Database Infrastructure Question and Answers

If You not specify Deadlock Priority Explicitly using the SET Deadlock_Priority statement.

What is the default Deadlock Priority?

Options are :

  • Normal (0) (Correct)
  • Low (-5)
  • High (+5)

Answer : Normal (0)

Explanation If you not specify deadlock priority the default is Normal.

You use Microsoft SQL Server 2016 to write code for a transaction that contains several statements.There is high contention between readers and writers on several tables used by your transaction. You need to minimize the use of the tempdb space.You also need to prevent reading queries from bloking writing queries.Which isolation level should you use?

Options are :

  • SERIALIZABLE
  • READ COMMITTED SNAPSHOT (Correct)
  • SNAPSHOT
  • REPEATABLE READ

Answer : READ COMMITTED SNAPSHOT

Explanation For most applications, read committed isolation using row versioning is recommended over snapshot isolation because it consume less tempdb space than snapshot isolation level.

Which of the following system stored procedure reset the runtime execution statistics for a specified plan?

Options are :

  • sp_query_store_force_plan
  • sp_query_store_remove_plan
  • sp_query_store_reset_exec_stats (Correct)
  • sp_query_store_flush_db

Answer : sp_query_store_reset_exec_stats

Explanation sp_query_store_reset_exec_stats: Reset the runtime execution statistics for a specified plan. sp_query_store_flush_db: Flush the portion of the query store currently in memory to disk. sp_query_store_force_plan: Force SQL Server to use a specified query plan for a specified query. sp_query_store_remove_plan: Remove a specified query plan from the query store.

Which of the following Transaction isolation provides the highest consistency?

Options are :

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

Answer : Serializable

Explanation Serializable Transaction isolation level provides the Full data consistency including phantom protection.

You have a database name Sales that contains the table name Orders. You create table by using the below T-SQL statement.

Create table dbo.Orders

(

            OrderId int Not Null Primary Key,

            OrderDate Date Not Null,

            OrderItems varchar(100) Not Null,

            Price money Not Null

)

You have to determine which of the following index gets automatically created?

Options are :

  • Clustered Columnstore index
  • Unique clustered index (Correct)
  • Nonclustered Columnstore index
  • Nonclustered index

Answer : Unique clustered index

Explanation The Primary Key creates a Unique Clustered index automatically with table creation.

You are using elastic database client library.

Which of the following feature the database engine is used to process queries in parallel across separate shards and combine the results into a single result set?

Options are :

  • Shard map management
  • Multishard querying (Correct)
  • Data dependent routing
  • Shard elasticity

Answer : Multishard querying

Explanation The database engine uses multishard querying feature to process queries in parallel across separate shards and combine the results into a single result set.

True or False: Foreign key Constraint are not automatically indexed.

Options are :

  • True (Correct)
  • False

Answer : True

Explanation The Foreign Key Constraint are not automatically index. It not creates any index. The Primary Key constraint creates a Clustered index automatically.

Which of the following Transaction isolation level provides the Maximum Concurrency?

Options are :

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

Answer : Read Uncommitted

Explanation Read Uncommitted provides the maximum concurrency because it not required any locks. No read and write blocking.

Which of the following DMVs is used to retrieve metrics on a group of missing indexes?

Options are :

  • sys.dm_db_missing_index_ groups
  • sys.dm_db_missing_index_group_stats (Correct)
  • sys.dm_db_missing_index_details

Answer : sys.dm_db_missing_index_group_stats

Explanation sys.dm_db_missing_index_group_stats: It is used to retrieve metrics on a group of missing indexes. sys.dm_db_missing_index_details: It is used identify the columns used for inequality and equality predicates.

Which of the following data masking functions contains no configuration for masking? (choose all possible options)

Options are :

  • default() (Correct)
  • random()
  • email() (Correct)
  • partial()

Answer : default() email()

Explanation The email() and default() data masking function contains no configuration for masking. The default() takes the default mask of the data type. email() used fixed formatting to show the first letter of an email address, always ending in the extension .com. the partial(no of characters to display from front, value to be displayed between front and back character , no of characters to display from back). random() function is applied to a column of numeric type. it returns a random value between the specified ranges.

You work as a Database Administrator (DBA) for a company named Riya.comThe company uses a Microsoft SQL Server 2012 infrastructure.You have a database named Orders.You plan to create a stored procedure to access data in Orders.You need to ensure that the stored procedure supports dirty reads.What should you do?

Options are :

  • You should configure the stored procedure to use the SNAPSHOT isolation level.
  • You should configure the stored procedure to use the REPEATABLE READ isolation level.
  • You should configure the stored procedure to use the READ COMMITTED isolation level.
  • You should configure the stored procedure to use the READ UNCOMMITTED isolation level. (Correct)

Answer : You should configure the stored procedure to use the READ UNCOMMITTED isolation level.

Explanation You should configure the stored procedure to use the READ UNCOMMITTED isolation level to ensure that the stored procedure supports dirty reads.

You create a stored procedure that retrieves all of the rows from a table named Orders.You need to recommend a solution to ensure that all of the statements in the stored procedure can be executed if another transaction is modifying rows in Orders simultaneously.What should you recommend?

Options are :

  • Snapshot isolation (Correct)
  • Read Committed isolation
  • Indexes

Answer : Snapshot isolation

Explanation Once snapshot isolation level is enabled, updated row versions for each transaction are maintained in tempdb. A unique transaction sequence number identifies each transaction, and these unique numbers are recorded for each row version.

For optimization of query performance, you use natively complied stored procedures.

True or False: A natively compiled stored procedure can have only one atomic block.

Options are :

  • True (Correct)
  • False

Answer : True

Explanation A natively compiled stored procedure can have only one atomic block. An atomic block must include WITH (TRANSACTION ISOLATION LEVEL = SNAPSHOT, LANGUAGE = N’ENGLISH’).

You develop a stored procedure named sp1.sp1 is used to read and change the price of all the products sold on the e-commerce site.You need to ensure that other transactions are blocked from updating product data while sp1 is executing Which transaction isolation level should you use in sp1?

Options are :

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

Answer : Repeatable Read

Explanation You should use Repeatable Read isolation level because it ensures that any data read by one transaction is not changed by another transaction. It only prevents changes but another transaction can insert a new record. To prevent insert and update use Serializable isolation level.

You have a database named DB1 that stores more than 700 gigabytes (GB) of data and serves millions of requests per hour.Queries on DB1 are taking longer than normal to complete.You run the following T-SQL Statement:

SELECT * FROM sys.database_query_store_options

You determine that the query store is in Read-Only mode.You need to maximize the time that the Query Store is in Read-Write mode.Which T-SQL statement should you run?

Options are :

  • ALTER Database DB1 SET QUERY_STORE (MAX_STORAGE_SIZE_MB = 50)
  • ALTER Database DB1 SET QUERY_STORE (QUERY_CAPTURE_MODE = ALL)
  • ALTER Database DB1 SET QUERY_STORE (QUERY_CAPTURE_MODE = NONE)
  • ALTER Database DB1 SET QUERY_STORE (CLEANUP_POLICY = (STALE_QUERY_THRESHOLD_DAYS=14)) (Correct)

Answer : ALTER Database DB1 SET QUERY_STORE (CLEANUP_POLICY = (STALE_QUERY_THRESHOLD_DAYS=14))

Explanation Stale Query Threshold (days): time based cleanup policy that controls the retention period of persisted runtime statistics and inactive queries. By default, query store is configured to keep the data for 30 days which may be unnecessarily long for your scenario.

Which of the following system stored procedure remove a specified query plan from the query store?

Options are :

  • sp_query_store_force_plan
  • sp_query_store_remove_plan (Correct)
  • sp_query_store_flush_db
  • sp_query_store_remove_query

Answer : sp_query_store_remove_plan

Explanation sp_query_store_remove_plan: Remove a specified query plan from the query store. sp_query_store_flush_db: Flush the portion of the query store currently in memory to disk. sp_query_store_force_plan: Force SQL Server to use a specified query plan for a specified query. sp_query_store_remove_query: Remove a specified query from the query store.

Which of the following index should you use for creating an indexed view?

Options are :

  • Columnstore index
  • Filtered index
  • Non-clustered index
  • Clustered index (Correct)

Answer : Clustered index

Explanation You should use clustered index for creating an indexed view.

You have a database that contains the below tables:

Create table dbo.Customer

(

            CustomerId int IDENTITY(1,1) Primary key Not Null,

            Name varchar(100) Not NULL

)

Create table dbo.Orders

(

            CustomerId int,

            Name varchar(100) Not Null constraint Default_Name DEFAULT(‘No Name’)

)

Create table dbo.Employee

(

            EmployeeId int Not Null UNIQUE,

            Name varchar(100)

)

Create table dbo.GadgetItem

(

            ItemCode smallint not null constraint chk_ItemCode CHECK(ItemCode>0 AND ItemCode<1000)

)

The Tables are created using Constraint on the specific column.

You need to determine which constraint used which rule? (select all possible options)

Options are :

  • UNIQUE Constraint on dbo.Employee table enforce unique value for EmployeeId. (Correct)
  • CHECK Constraint on dbo.GadgetItem enforce expression predicate to data as it is inserted or updated. (Correct)
  • Primary Key Constraint on dbo.Customer table ensure that column contains unique values. (Correct)
  • Default Constraint on dbo.Orders table enforce that a column always have a value if user not provide value. (Correct)
  • Not Any Option is applicable

Answer : UNIQUE Constraint on dbo.Employee table enforce unique value for EmployeeId. CHECK Constraint on dbo.GadgetItem enforce expression predicate to data as it is inserted or updated. Primary Key Constraint on dbo.Customer table ensure that column contains unique values. Default Constraint on dbo.Orders table enforce that a column always have a value if user not provide value.

Explanation The Primary Key Constraint ensure that column contains unique values. The Default Constraint enforce that a column always has a value if user not provide value. The UNIQUE constraint enforce unique value for a column. CHECK Constraint enforce expression predicate to data as it is inserted or updated every time.

You have a OrderDetails View in SQL Server 2016.

You need to redesign the definition of the OrderDetails view.

The Requirements are:

- Retain Existing Permissions

- Remove without returning an error if the view is missing.

What should you do?

Options are :

  • Retain Existing Permissions – Alter view OrderDetails Remove without returning an error if the view is missing – Drop View If Exists OrderDetails. (Correct)
  • Retain Existing Permissions – Alter view OrderDetails Remove without returning an error if the view is missing – Drop View OrderDetails.
  • Retain Existing Permissions – Drop view OrderDetails Remove without returning an error if the view is missing – Drop View OrderDetails.

Answer : Retain Existing Permissions – Alter view OrderDetails Remove without returning an error if the view is missing – Drop View If Exists OrderDetails.

Explanation For Retaining existing permission on view use Alter View command. For removing view and make sure error not occur view is missing use Drop View If Exists command.

Which of the following Durability option is default when you do not specify option at the time you created a memory-optimized table?

Options are :

  • SCHEMA_ONLY
  • SCHEMA_AND_DATA (Correct)

Answer : SCHEMA_AND_DATA

Explanation If you do not specify durability option at the time of memory-optimized table creation it is durable by default, means SCHEMA_AND_DATA.

Comment / Suggestion Section
Point our Mistakes and Post Your Suggestions