You work as a Database Administrator (DBA) for a company named Riya.com
The 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 READ UNCOMMITTED isolation level.
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?
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.
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?
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?
ALTER Database DB1 SET QUERY_STORE (CLEANUP_POLICY = (STALE_QUERY_THRESHOLD_DAYS=14))
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?
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?
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
Name varchar(100) Not Null constraint Default_Name DEFAULT(‘No Name’)
Create table dbo.Employee
EmployeeId int Not Null UNIQUE,
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)
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.
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?
Retain Existing Permissions – Alter view OrderDetails
Remove without returning an error if the view is missing – Drop View If Exists OrderDetails.
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?
If you do not specify durability option at the time of memory-optimized table creation it is durable by default, means SCHEMA_AND_DATA.
Subscribe to my Youtube channel for new videos : Subscribe Now