What is the Use of @@ROWCOUNT?
Returns the number of rows affected by the Query.
UPDATE authors SET au_lname = ‘Jones’
WHERE au_id = ‘999-888-7777’
IF @@ROWCOUNT = 0
print ‘Warning: No rows were updated’
What is Difference between Commit and Rollback when Used in Transactions?
The usual structure of the TRANSACTION is as follows:
COMMIT TRANSACTION or ROLLBACK TRANSACTION
When Commit is executed, every statement between BEGIN and COMMIT becomes persistent to database. When Rollback is executed, every statement between BEGIN and ROLLBACK are reverted to the state when BEGIN was executed.
What is Candidate, alternate and composite keys in SQL?
A candidate key is one that can identify each row of a table uniquely. Generally a candidate key becomes the primary key of the table. If the table has more than one candidate key, one of them will become the primary key, and the rest are called alternate keys.
A key formed by combining at least two or more columns is called composite key.
What are Isolation Levels?
An isolation level determines the degree of isolation of data between concurrent transactions. The default SQL Server isolation level is Read Committed.
Following are the other isolation levels (in the ascending order of isolation):
- Read Uncommitted
- Read Committed
- Repeatable Read
One can set isolation levels at connection level using the following sql
SET TRANSACTION ISOLATION LEVEL
What is Lock Escalation?
Lock escalation is the process of converting a lot of low level locks (like row locks, page locks) into higher level locks (like table locks).
Every lock is a memory structure too many locks would mean, more memory being occupied by locks. To prevent this from happening, SQL Server escalates the many fine-grain locks to fewer coarse-grain locks.
Lock escalation threshold was definable in SQL Server 6.5, but from SQL Server 7.0 onwards it’s dynamically managed by SQL Server.