Interview Questions Part-6 | What is @@ROWCOUNT in SQL | What is Difference between Commit and Rollback in SQL | What is Candidate key, alternate key and composite key in SQL | What are Isolation Levels in SQL | What is Lock Escalation in SQL Server

What is the Use of @@ROWCOUNT?

Returns the number of rows affected by the Query.

Ex:

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:

BEGIN TRANSACTION

Operations

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):

1. Read Uncommitted
2. Read Committed
3. Repeatable Read
4. Serializable

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.

If you enjoyed this post, please support the blog below. It's FREE! Get the latest Asp.net, C#.net, VB.NET, jQuery, Plugins & Code Snippets for FREE by subscribing to our Facebook, Twitter, RSS feed, or by email.

Subscribe by RSS Subscribe by Email