Use transactions for select statements

Transactions are usually when you have insert, update or delete statements with the “atomic” behaviour. However, in a highly concurrent application, it could happen that data you’ve read is already modified.

In this situation, you need to use a transaction to wrap your selects with the correct isolation level.

It’s usually advisable to set the deadlock property to low to avoid impacting behaviours other than select.


...

CREATE procedure [Event].[ReadEvents]
...
  @startDate datetime = NULL,
  @endDate datetime = NULL,
...
  
  AS
begin

...

  set nocount on;
  set deadlock_priority low;
  set transaction isolation level serializable;

  begin transaction;
 
...
    
commit transaction;    
    
end

GO

Use transactions for select statements

Indexing CreatedDate

Indexing columns is a joy and adventure.
I have just indexed a datetime column to boost performance. The query had a date range, and the column didn’t have index yet.

exec Event.ReadEvents @startDate='2012-11-19 00:07:30', @endDate='2012-11-19 00:07:35' 

The query took longer than a minute without index. Surprisingly and very happily, the time reduced to 6 milliseconds. I love index.

CREATE NONCLUSTERED INDEX [IX_Events_CreatedDate] ON [dbo].[Events]
(
	[CreatedDate] ASC
) ON [PRIMARY]

Another surprise. It took 6 minutes to create the index.

This is the stats.

I’m not sure which sort order (ASC or DESC) is better for datetime column. Maybe it’s just order preference, if your query requires sorted result.

Indexing CreatedDate