SQL Interview Questions and Answers - Part 20
Are you preparing for the SQL Interview? If yes, then you are at the right place. This is the SQL Interview Questions and Answers article series. Here we will see the Top 100+ SQL Interview Questions with Answers.
Please visit our YouTube Channel for Interviews and other videos by below link:
Please read the complete Design Pattern, C#, MVC, WebAPI, and .Net Framework Interview Questions and Answers article series here.
Introduction
This is the 20th part of the SQL Interview Questions and Answers article series. Each part contains eight to ten SQL Interview Questions with Answers. Please read all the SQL Interview Questions list here.
I will highly recommend to please read the previous parts over here before continuing the current part:
SQL Interview Questions and Answers - Part 20
Q181. What is SQL Lock Compatibility? Can a resource have multiple locks at the same time?
Not all lock modes can be applied at all levels. For example :
Row Level Allowed Lock Modes:
- Exclusive (X)
- Shared (S)
- Update (U)
Table Level Allowed Lock Modes:
- Exclusive (X)
- Shared (S)
- Intent exclusive (IX)
- Intent shared (IS)
- Shared with intent exclusive (SIX)
- A Schema lock (Sch) is also a table-level lock as well, but it is not a data-related lock.
Lock compatibility controls whether multiple transactions can acquire locks on the same resource at the same time.
If a resource is already locked by another transaction, a new lock request can be granted only if the mode of the requested lock is compatible with the mode of the existing lock.
If the mode of the requested lock is not compatible with the existing lock, the transaction requesting the new lock waits for the existing lock to be released or for the lock timeout interval to expire.
For example, no lock modes are compatible with exclusive locks. While an exclusive (X) lock is held, no other transaction can acquire a lock of any kind (shared, update, or exclusive) on that resource until the exclusive (X) lock is released.
Alternatively, if a shared (S) lock has been applied to a resource, other transactions can also acquire a shared lock or an update (U) lock on that item even if the first transaction has not been completed. However, other transactions cannot acquire an exclusive lock until the shared lock has been released.
The following table shows the compatibility of the most commonly encountered lock modes in SQL:
An intent exclusive (IX) lock is compatible with an IX lock mode because IX means the intention is to update only some of the rows rather than all of them. Other transactions that attempt to read or update some of the rows are also permitted as long as they are not the same rows being updated by other transactions.
Further, if two transactions attempt to update the same row, both transactions will be granted an IX lock at the table and page level. However, one transaction will be granted an X lock at row level. The other transaction must wait until the row-level lock is removed.
Q182. What is Lock Escalation in SQL?
In order to prevent a situation where locking is using too many resources, SQL Server has introduced the lock escalation feature. Without escalation, locks could require a significant amount of memory resources.
For example, if there is a situation where more than 5,000 locks are acquired on a single level (row), SQL Server will escalate those locks to a single table-level lock.
By default, SQL Server will always escalate to the table level directly, which means that escalation to the page level never occurs.
Instead of acquiring numerous rows and page lock, SQL Server will escalate to the exclusive lock (X) on a table level. An exclusive lock (X) in a table means that no other transaction will be able to access the locked table and all queries trying to access that table will be blocked. Therefore, this will reduce system overhead but will increase the probability of concurrency contention.
In order to provide control over the escalation, starting with SQL Server 2008 R2, the LOCK_EXCALATION option is introduced as part of the ALTER TABLE statement:
ALTER TABLE Table_name SET LOCK_ESCALATION = TABLE
There are three set options i.e TABLE, AUTO, and DISABLE, which you can use.
Q183. What is Dynamic Locking in SQL?
Using low-level locks, such as row locks, increases concurrency by decreasing the probability that two transactions will request locks on the same piece of data at the same time.
Using low-level locks also increases the number of locks and the resources needed to manage them. Using a high-level table or page locks lowers overhead but at the expense of lowering concurrency.
So Microsoft SQL Server Database Engine uses a dynamic locking strategy to determine the most cost-effective locks. The Database Engine automatically determines what locks are most appropriate when the query is executed, based on the characteristics of the schema and query.
In SQL Server 2008 and later versions, the behavior of lock escalation has changed with the introduction of the LOCK_ESCALATION option.
Q184. What is SQL Table Hint?
SQL table hints are a special type of explicit command that is used to override the default behavior of the SQL Server query optimizer during the T-SQL query execution.
This is accomplished by enforcing a specific locking method, a specific index, or query processing operation, such as index seeks or table scan, to be used by the SQL Server query optimizer to build the query execution plan.
Table hints are usually specified in the FROM clause of the DML statement and affect only the table or view referenced in that clause. Table Hint is applied on DELETE, INSERT, SELECT, UPDATE, and MERGE statements.
Common Table Hints:
INDEX, NOLOCK, HOLDLOCK, NOWAIT, PAGLOCK, READCOMMITTED, READCOMMITTEDLOCK, READUNCOMMITTED, REPEATABLEREAD, ROWLOCK, SERIALIZABLE, SNAPSHOT, TABLOCK, TABLOCKX, UPDLOCK, XLOCK, FORCESEEK, FORCESCAN, IGNORE_CONSTRAINTS, IGNORE_TRIGGERS, FORCESEEK, FORCESCAN
Usually, table hints are supported in the FROM clause only when the hints are specified with the WITH keyword. But Table hints also must be specified with parentheses.
WITH ( <table_hint> )
-- OR
FROM table (<table_hint>)
When the hint is specified with another option, the hint must be specified with the WITH keyword:
FROM table WITH (TABLOCK, INDEX(index_name))
However, the following table hints are allowed with and without the WITH keyword: NOLOCK, READUNCOMMITTED, UPDLOCK, REPEATABLEREAD, SERIALIZABLE, READCOMMITTED, TABLOCK, TABLOCKX, PAGLOCK, ROWLOCK, NOWAIT, READPAST, XLOCK, SNAPSHOT, and NOEXPAND. When these table hints are specified without the WITH keyword, the hints should be specified alone.
Table Hint Fundamental Points:
- The table hints are ignored if the table is not accessed by the query plan.
- All lock hints are propagated to all the tables and views that are accessed by the query plan, including tables and views referenced in a view.
- Lock hints ROWLOCK, UPDLOCK, AND XLOCK that acquire row-level locks may place locks on index keys rather than the actual data rows.
- If a table contains computed columns that are computed by expressions or functions accessing columns in other tables, the table hints are not used on those tables and are not propagated.
- SQL Server does not allow for more than one table hint from Granularity hints (PAGLOCK, NOLOCK, TABLOCK, ROWLOCK) and Isolation level hints (READCOMMITTED, SERIALIZABLE, REPEATABLEREAD, HOLDLOCK, NOLOCK) in the FROM clause.
Q185. What is the difference between NOLOCK, and NOWAIT Table Hint in SQL?
WITH NOLOCK Table Hint:
NOLOCK is one of the most heavily used table hints in the SELECT statements. We know that the default transaction isolation level in SQL Server is the READ COMMITTED isolation level, in which retrieving the changing data will be blocked until these changes are committed.
So the NOLOCK table hint is used to override the default transaction isolation level of the table or the tables within the view in a specific query, by allowing the user to retrieve the data without being affected by the locks, on the requested data, due to another process that is changing it.
So the WITH (NOLOCK) table hint retrieves the rows without waiting for the other queries, that are reading or modifying the same data, to finish their processing.
Thus query will consume less memory in holding locks against that data. In addition to that, no deadlock will occur against the queries, that are requesting the same data from that table, allowing a higher level of concurrency due to a lower footprint.
The NOLOCK hint is equivalent to READUNCOMMITTED transaction isolation level. The transaction isolation level can be set globally at the connection level using the SET TRANSACTION ISOLATION LEVEL T-SQL command as well, but that is not required in most cases.
SELECT * FROM tblTransaction WITH (NOLOCK);
The NOLOCK hint leads to Dirty reads, Nonrepeatable reads, Phantom reads, and Loss of read problems as well, So the WITH (NOLOCK) table hint is only used when working with systems that accept out-of-sync data, such as the reporting systems.
Sometimes the NOLOCK query also gets blocked and can block other transactions as well. So the NOLOCK hint is not always 100% accurate. Actually, the NOLOCK table hint acquires schema stability (Sch-S) lock type. So the SELECT statement that is using the WITH (NOLOCK) table hint will be blocked if a schema modification is performed on that table.
WITH NOWAIT Table Hint:
The NOLOCK hint instructs the Database Engine to return a message as soon as a lock is encountered on the table. NOWAIT is equivalent to specifying SET LOCK_TIMEOUT 0 for a specific table.
The NOWAIT hint does not work when the TABLOCK hint is also included.
To terminate a query without waiting when using the TABLOCK hint, preface the query with SET LOCK_TIMEOUT 0; instead.
NOWAIT hint could be a better solution in the situation that the application does not want to read dirty data and also does not want to wait for other transactions to be finished.
Q186. What is the difference between READPAST and READUNCOMMITED Table Hint?
WITH READPAST Table Hint:
The READPAST specifies that the Database Engine will not read rows that are locked by other transactions. When READPAST is specified, row-level locks are skipped but page-level locks are not skipped.
So the Database Engine skips past the rows instead of blocking the current transaction until the locks are released. READPAST is primarily used to reduce locking contention when implementing a work queue that uses a SQL Server table.
For example, if table T1 contains a single integer column with the values of 1, 2, 3, 4, 5. If transaction A changes the value of 3 to 8 but has not yet committed, a SELECT * FROM T1 (READPAST) yields values 1, 2, 4, and 5.
READPAST can be specified for any table referenced in an UPDATE or DELETE statement, and any table referenced in a FROM clause. READPAST cannot be specified for tables in the INTO clause of an INSERT statement.
Update or delete operations that use READPAST may block when reading foreign keys or indexed views, or when modifying secondary indexes.
READPAST can only be specified in transactions operating at the READ COMMITTED or REPEATABLE READ isolation levels. When specified in transactions operating at the SNAPSHOT isolation level, READPAST must be combined with other table hints that require locks, such as UPDLOCK and HOLDLOCK.
The READPAST table hint cannot be specified when the READ_COMMITTED_SNAPSHOT database option is set to ON and either of the following conditions is true:
- The transaction isolation level of the session is READ COMMITTED.
- The READCOMMITTED table hint is also specified in the query.
WITH READUNCOMMITTED Table Hint:
The READUNCOMMITTED hint specifies that dirty reads are allowed. READUNCOMMITTED and NOLOCK hints apply only to data locks.
SELECT * FROM tblTransaction WITH (READUNCOMMITTED);
A READUNCOMMITTED hint tells that no shared locks are issued to prevent other transactions from modifying data read by the current transaction, and exclusive locks set by other transactions do not block the current transaction from reading the locked data.
All queries, including those with READUNCOMMITTED and NOLOCK hints, acquire Sch-S (schema stability) locks during compilation and execution. Because of this, queries are blocked when a concurrent transaction holds a Sch-M (schema modification) lock on the table.
So any concurrent queries, including those running with READUNCOMMITTED or NOLOCK hints, are blocked when attempting to acquire a Sch-S lock. Conversely, a query holding a Sch-S lock blocks a concurrent transaction that attempts to acquire a Sch-M lock.
READUNCOMMITTED and NOLOCK cannot be specified for tables modified by insert, update, or delete operations. The SQL Server query optimizer ignores the READUNCOMMITTED and NOLOCK hints in the FROM clause that apply to the target table of an UPDATE or DELETE statement.
Q187. What is the difference between IGNORE_CONSTRAINTS and IGNORE_TRIGGERS table hint?
Both IGNORE_CONSTRAINTS and IGNORE_TRIGGERS are limited table hints.
IGNORE_CONSTRAINTS Table Hint:
IGNORE_CONSTRAINTS is applicable only in an INSERT statement when the BULK option is used with OPENROWSET. It specifies that any constraints on the table are ignored by the bulk-import operation.
When IGNORE_CONSTRAINTS is specified for a bulk-import operation, INSERT must ignore these constraints on a target table.
By default, INSERT checks Unique Constraints and Check Constraints and Primary and Foreign Key Constraints. Note that you cannot disable UNIQUE, PRIMARY KEY, or NOT NULL constraints.
However, you might want to disable CHECK and FOREIGN KEY constraints if the input data contains rows that violate constraints. By disabling the CHECK and FOREIGN KEY constraints, you can import the data and then use Transact-SQL statements to clean up the data.
But when CHECK and FOREIGN KEY constraints are ignored, each ignored constraint on the table is marked as is_not_trusted in the sys.check_constraints or sys.foreign_keys catalog view after the operation.
At some point, you should check the constraints on the whole table. If the table was not empty before the bulk import operation, the cost of revalidating the constraint may exceed the cost of applying CHECK and FOREIGN KEY constraints to the incremental data.
IGNORE_TRIGGERS Table Hint:
It is also applicable only in an INSERT statement when the BULK option is used with OPENROWSET. It specifies that any triggers defined on the table are ignored by the bulk-import operation. By default, INSERT applies triggers.
Use IGNORE_TRIGGERS only if your application does not depend on any triggers and maximizing performance is important.
Q188. What is the use of INDEX Table Hint? What is the difference between INDEX(0) and INDEX(1) hint?
The INDEX() table hint specifies the names or IDs of one or more indexes to be used by the query optimizer when it processes the statement. The alternative INDEX = syntax specifies a single index value. Only one index hint per table can be specified.
If a clustered index exists, INDEX(0) forces a clustered index scan and INDEX(1) forces a clustered index scan or seek. If no clustered index exists, INDEX(0) forces a table scan and INDEX(1) is interpreted as an error.
If multiple indexes are used in a single hint list, the duplicates are ignored and the rest of the listed indexes are used to retrieve the rows of the table. The order of the indexes in the index hint is significant. The maximum number of indexes in the table hint is 250 nonclustered indexes.
If the collection of hinted indexes does not include all columns referenced by the query, a fetch is performed to retrieve the remaining columns after the SQL Server Database Engine retrieves all the indexed columns.
Q189. What is the difference between FORCESEEK and FORCESCAN Table Hint?
WITH FORCESEEK Table Hint:
The FORCESEEK hint specifies that the query optimizer use only an index seek operation as the access path to the data in the table or view.
Specifying FORCESEEK with index parameters is similar to using FORCESEEK with an INDEX hint. However, you can achieve greater control over the access path used by the query optimizer by specifying both the index to seek on and the index columns to consider in the seek operation.
Starting with SQL Server 2008 R2 SP1, index parameters can also be specified. In that case, the query optimizer considers only index seek operations through the specified index using at least the specified index columns.
FORCESEEK [ (<index_value>(<index_column_name> [ ,... n ] )) ]
Here index_value is the index name or index ID value. The index ID 0 (heap) cannot be specified. To get the index name or ID, you can query the sys.indexes catalog view. And index_column_name is the name of the index column to include in the seek operation.
SEELCT * FROM tblTransaction WITH (FORCESEEK);
SEELCT * FROM tblTransaction WITH (FORCESEEK, INDEX (PK_Index));
SEELCT * FROM tblTransaction WITH (FORCESEEK, INDEX (NC_Index(col1, col2)));
WITH FORCESCAN Table Hint:
The FORCESCAN hint only applies to SQL Server 2008 R2 SP1 and later. It specifies that the query optimizer uses only an index scan operation as the access path to the referenced table or view.
The FORCESCAN hint can be useful for queries in which the optimizer underestimates the number of affected rows and chooses a seek operation rather than a scan operation.
When this occurs, the amount of memory granted for the operation is too small and query performance is impacted.
FORCESCAN can be specified with or without an INDEX hint. When combined with an index hint, (INDEX = index_name, FORCESCAN), the query optimizer considers only scan access paths through the specified index when accessing the referenced table.
FORCESCAN can also be specified with the index hint INDEX(0) to force a table scan operation on the base table.
For partitioned tables and indexes, FORCESCAN is applied after partitions have been eliminated through query predicate evaluation. This means that the scan is applied only to the remaining partitions and not to the entire table.
Limitations of FORCESCAN Hint:
- FORCESCAN hint cannot be specified for a table that is the target of an INSERT, UPDATE, or DELETE statement.
- FORCESCAN hint cannot be used with more than one index hint.
- FORCESCAN hint cannot be specified in combination with the FORCESEEK hint.
- FORCESCAN hint prevents the Query Optimizer from considering any spatial or XML indexes on the table.
- FORCESCAN hint cannot be specified for a remote data source.
Q190. How can you check the occupied locks on a table or database?
SQL Server provides the Dynamics Management View (DMV) sys.dm_tran_locks that returns information about lock manager resources that are currently in use. It will display all “live” locks acquired by transactions.
SELECT * FROM sys.dm_tran_locks WHERE request_session_id = 90;
You can get the session ID by using the @@SPID for the current session.
Similarly, you can get the resource-specific search as well:
SELECT * FROM sys.dm_tran_locks WHERE resource_type <> ‘DATABASE’;
SELECT * FROM sys.dm_tran_locks WHERE resource_type = 'OBJECT';
The sys.dm_tran_locks returns a lot of information but some of them are as follows:
resource_type: It displays a database resource where the locks are being acquired. The values may contain one from ALLOCATION_UNIT, APPLICATION, DATABASE, EXTENT, FILE, HOBT, METADATA, OBJECT, PAGE, KEY, RID.
request_mode: It displays the lock mode that is acquired on the resource.
resource_description: It displays a short resource description and is not populated for all lock modes.
You can check the blocking query by using the sp_who2 command by the session number.
SELECT sp_who2 53;
To Be Continued Part-21...
Recommended Articles
Thanks for visiting this page. Please follow and join us on LinkedIn, Facebook, Telegram, Quora, YouTube, Twitter, Pinterest, Tumbler, and VK for regular updates.
Posts
No comments:
Post a Comment
Please do not enter any HTML. JavaScript or spam link in the comment box.