Using READPAST with Order By clause in SQL Server with and without Index

Folks, I recently ran into this and decided to share this with the larger community...

If you wanted to do a READPAST on the table but have on 'Order By' in your SQL query, it somehow does not return any row if there is an existing lock on a row (although it still prevents you from doing dirty reads) but if you index the table on the column where you need to order by, it does the job. Let me articulate this in English by a simple example :-)

Lets create a simple table and insert some rows in it:

CREATE TABLE [dbo].[Customers](
 [CustID] [numeric](18, 0) NOT NULL,
 [CustName] [varchar](100) NOT NULL,
 [CustAddress] [varchar](400) NOT NULL,
 [CustPhone] [varchar](50) NOT NULL,
 [LastUpdated] [datetime] NOT NULL
) ON [PRIMARY]

Insert into Customers VALUES (1001,'MSFT', '1 Epping Road', '99999999', GetDate())
Insert into Customers VALUES (1002,'ORCL', 'North Ryde', '88888888', GetDate())
Insert into Customers VALUES (1003,'VITR', 'North Sydney', '77777777', GetDate())

Let me run this query on 2 separate query windows:

begin tran SELECT top 1 * FROM Customers WITH (updlock, readpast)

The first window will return MSFT, the other will return ORCL (as your second query will skip any rows that are currently locked i.e. MSFT).

Now make sure you do a commit tran on both the windows to make sure all the existing locks are released.

Run this query on 2 separate query windows:

begin tran SELECT top 1 * FROM Customers WITH (updlock, readpast) order by CustName;

You will see that the first one returns MSFT but the other does not return anything?

Again, make sure you do a commit tran on both the windows to make sure all the locks are released.

Go back to you designer and index your table on CustName. Save the table and re-run the above query on 2 separate windows again. There is a difference, after indexing, the SQL Server returns MSFT on the first window & ORCL on the second!