Sql Server Interview Questions

Transactions

Transactions are all about atomicity or the concept that something should act as unit. Execution of one or more than one T-SQL statements in a single unit or in a single time is called a transaction. Means executes all or nothing.
Example:
BEGIN TRANS

Update saving set balance=balance-10

Select balance from saving
COMMIT TRANS

Transactions may have four parts:
-BEGIN TRANS: This is a starting point of a Transaction.

-COMMIT TRANS: Make the transaction a permanent or transaction compete successfully.

-ROLLBACK: Means you want to forget that it ever happened.

-SAVE TRANS: We give these mainly for saving data in a transaction. When you have two save point before rollback and third save point after rollback then when rollback it clear only that two save point not third.

-Locks

When multi user access database simultaneously or we can say that when two or more user wants to change a piece of information at the same time. Locks are a mechanism for preventing a process from performing an action on an object those conflicts with something already being done on that object. For example user one tries to read date while other user making changing information on the same data. Then guess which data user one see, Changed or unchanged? Data may be logically Incorrect. So dealing with data concurrency in database is a process called locking. SQL Server lock manager do this locking.
By lock we can prevent four major problems:
1) Dirty Reads: It occurs when a transaction reads a record that is a part of another transaction which is not complete yet. If the first transaction complete normally than it is unlikely or which we don’t want come problem. But what is when the first transaction rolled back. To prevent this use the Sql Server Default for transaction isolation level (called Read Committed).
2) Non – Repeatable Read: Non – Repeatable Read is caused when you read the record twice in a transaction and another transaction alter that data in the interim (or in mid). We prevent this problem by two ways :
i) Create a check constraints and monitor for 547 error.
ii) Set our isolation level to be Repeatable read or serializable.
3) Phantoms: Someone performed a insert at the same time your update was running since it was an entirely new row. Means when you update a column in a table that every salary = 100 where salary less than 100 and at the same time any other user insert a statement where salary =50 but we don’t know about this and when we draw constraint on that column that is salary not less than 100 than error come that because a row exist which have salary 50(less than 100).
4) Lost Updates: Happens when one update is successfully written in the database but it accidentally overwritten by another transaction.

Consistency
Means a user should never see any data changes in mid of transaction
Isolation: Isolation is term or you can say that it is at the heart of multi user transactions. It means one transaction should not disrupt another transaction.

How the SQL Server Log Works?
Most activity you perform is logged to the “transaction log” rather than save directly to the database in normal operation of your database. A Checkpoint is a periodic operation that forces all dirty pages (are the log or data pages that have been modified after they were read into cache) for the database which is currently in use to be written on disk, but modification has not yet to be written on disk. If the checkpoint is not there then log would be fills up and use all the disk space.




Implicit Transaction: You have no need to write Begin Trans when any Trans comes it automatically Begin Trans and not committed or roll backed until not come commit Trans or Rollback Trans. By default it off but if you want to on then

SET IMPLICIT_TRANSACTION ON

Lockable Resources

• Database
• Table
• Page
• Key
• Row

Lock Escalation and lock effects on performance
Escalation is all about recognizing that maintaining a finer level of gratuity (means a row lock instead of page lock), when the number of item being locked is small. Escalation is based on the number of locks rather then the number of user in a single time.
Lock Modes: means what level of resources you are locking or what lock mode your query is going to acquire.
1) Shared Lock: is used when you only need to read the data that is you don’t want to change. It prevents user from dirty reads.
2) Exclusive Locks: these are not compatible with any other lock or we can say that we can’t implement this if any other lock exists or vice versa. This is used to preventing from updating, deleting or doing whatever at the same time.
3) Update Lock: are hybrid between shared locks and exclusive locks. It is a special kind of placeholder. This means that until you go to make a physical updates you need a shared lock. At the time of physical update, you will need an exclusive lock.
4) Intents Locks: is a true placeholder and is meant to deal with the object hierarchies. For example when you have a lock established on a row. But some one wants to establish a lock on a page, or extent or modify a table. You would not want another transaction to go around yours by going higher up the hierarchy. Without indent lock the higher level object would not even know that you had the lock at the lower level.
5) Schema Locks: This lock have two main forms:

• Schema Modification Lock (Sch-M):When you have this lock then no query or other create, alter or drop statements can be run against this object for the duration of Sch-M locks.

• Schema stability lock (Sch-s): This is very similar to a shared lock. The purpose of this lock is to prevent a Sch-M since there are already locks for other queries (create, alter, drop statements) active on the object. This is compatible with all other lock types.

6) Bulk Update Lock: is used to allow parallel loading of data that is locked from
any other normal T-Sql statement activities. But multiple bulk insert or BCP operations can be performed at the same time.

7) Serializable/HOLDLOCK: Once a lock is held by a statement in a transaction that lock is not released until the transaction is ended (via rollback or commit). Any insert are also prevented if the inserted record would match the criteria in the where clause in the query that maintain the lock (No Phantoms). This is the highest isolation level and guarantees absolute consistency of data.
8) READUNCOMMITTED/NOLOCK: means no lock or does not honor any other lock. But while a very fast option. It can generate dirty read.


Deadlock
It is not a type of lock in itself where it is a situation when a paradox comes or we can say that when a transaction want a resources and that resources hold by other transaction and both are locked in own side. Means no any transection works.

• By using transaction and lock we can minimize deadlocks and improve the overall performance of our system.