Recommended Reading: NULL Vs NULL

Mike Coles has posted a follow-up article "NULL Vs NULL" to his previous article "Four Rules On Nulls"

I noticed that SET ANSI_NULLS is been deprecated and will be removed in future versions only when I read this article. Now I have started looking for other deprecated features in SQL 2005. How I see that MS has not documented alternatives for SET ANSI_NULLS and few opther deprectaed features. Check Deprecated Database Engine Features in SQL Server 2005 for more details.

Efficient paging of recordsets with T-SQL

One of the challenges the Developers face is implementing efficient paging mechanism in case of large data. Jeff gives an efficient solution to Efficient paging of recordsets with T-SQL.

Adding to this, Richard is giving a code-based solution here.... Both have their own advantages and disadvantages as listed by Richard in his blog.

SQL2K Record Concurrency Control

When many people attempt to modify data in a database at the same time, a system of controls must be implemented so that modifications made by one person do not adversely affect those of another person. This is called concurrency control.

Concurrency control is usually implemented in two ways:

Pessimistic concurrency control
A system of locks prevents users from modifying data in a way that affects other users. After a user performs an action that causes a lock to be applied, other users cannot perform actions that would conflict with the lock until the owner releases it. This is called pessimistic control because it is mainly used in environments where there is high contention for data, where the cost of protecting data with locks is less than the cost of rolling back transactions if concurrency conflicts occur.

Optimistic concurrency control
In optimistic concurrency control, users do not lock data when they read it. When an update is performed, the system checks to see if another user changed the data after it was read. If another user updated the data, an error is raised. Typically, the user receiving the error rolls back the transaction and starts over. This is called optimistic because it is mainly used in environments where there is low contention for data, and where the cost of occasionally rolling back a transaction outweighs the costs of locking data when read.

In real world application Optimistic Concurrency Control is preferred than Pessimistic Concurrency control, except for situations stated.

I happened to read a solution based on timestamps for optimistic concurrency control implementation at Vadivel's blog. Read it here...