Home
MS SQL
Interview FAQs

FAQs of level

The HAVING clause specifies a search condition for a GROUP BY or an aggregate. The difference is that HAVING can be used only with the SELECT statement whereas the WHERE can be used during update and delete operations. HAVING is typically used with a GROUP BY clause. The HAVING clause is used in an aggregate function or a GROUP BY clause in a query, whereas a WHERE Clause is applied to each row before they are part of the GROUP BY clause or aggregate function in a query.

The database server uses a B-tree structure to organize index information. B-tree generally has the following types of index pages or nodes:

Root node: A root node contains node pointers to only one branch node.
Branch nodes: A branch node contains pointers to two or more leaf nodes or other branch nodes.
Leaf nodes: A leaf node contains index items and horizontal pointers to two or more leaf nodes.

A filtered index is used to index a portion of the rows in a table. This means it applies a filter on an INDEX which improves query performance, reduces index maintenance costs, and reduces index storage costs when compared with full-table indices. When we see an index created with a WHERE clause, then that is actually a Filtered Index.

A table can have one of the following index configurations.

  • No indexes
  • A clustered index only
  • A clustered index with one non-clustered indexes
  • A non-clustered index with no clustered index
  • A clustered index with many non-clustered indices
  • Many non-clustered indices with no clustered index

The EXCEPT operator is similar to the MINUS operation in Oracle. The EXCEPT query and MINUS query return all rows in the first query that are not found in the second query. 

There are two types of subquery-

  • Single-row sub-query, where the sub-query returns only one row.
  • Multiple-row sub-query, where the sub-query returns multiple rows.

The four multi-query operators are UNION, UNION ALL INTERSECT, and EXCEPT. The INTERSECT operator introduced in SQL Server 2005 and later versions is used to retrieve the common records from both the left and the right query of the INTERCECT operator. The INTERSECT operator returns almost the same results as an INNER JOIN clause for all of the fields listed in the query. When using the INTERSECT operator the number and the order of the columns must be the same in all queries and the data type must be compatible.

Exclusive Lock
An exclusive lock allows only one user/connection to update a particular piece of data (insert, update, and delete). When one user has an exclusive lock on a row or table, no other lock of any type may be placed on it.The locked data can be read or processed by one user only. A request for another exclusive lock or for a shared lock is rejected.When exclusive lock is on any process, no other lock can be placed on that row or table. Every other process has to wait until the exclusive lock completes its tasks.  
Update Lock
Update locks are always row-level locks. When a user accesses a row with the SELECT... FOR UPDATE statement, the row is locked with an update mode lock. This means that no other user can read or update the row and ensures the current user can later update the row. Also, update locks are asymmetric with respect to shared locks. You can acquire an update lock on a record that already has a shared lock, but you cannot acquire a shared lock on a record that already has an update lock. Because an update lock prevents subsequent read locks, it is easier to convert the update lock to an exclusive lock.
Difference between an update lock and exclusive lock-

  • Update locks are similar to exclusive locks. The main difference between the two is that you can acquire an update lock when another user already has a shared lock on the same record. This lets the holder of the update lock read data without excluding other users. However, once the holder of the update lock changes the data, the update lock is converted into an exclusive lock.
  • Update lock reads the data of the row which has the shared lock. As soon as the update lock is ready to change the data it converts itself to the exclusive lock.
  • An update lock is a type of exclusive lock, except that it can be placed on the row which already has shared lock on it. 

Lock escalation is one of the lesser known phenomena inside SQL Server. Often times locking 1 row in a table cost less than locking the entire table. If you are updating thousands of records at once then SQL might find that is less costly to lock the table once rather than locking thousands of individual rows.

SQL Server uses this to minimize the overhead of locking too many structures by escalating the locks from just row locks to page locks to table locks. There is now a lock escalation option in the alter table of SQL Server 2008 which allows the disabling of lock escalation on that table.

PreviousDisplaying 15 of 15
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15
Need Help? Contact Us.

Log in

*
*

Forgot password?

*

New User

*
*
*
*