How BETWEEN is inclusive

This is a old and simple one, but an important subtlety that TSQL developers must be aware of.

Using the BETWEEN syntax is easy enough in a WHERE clause, and it's pretty obvious for numbers.

use testing

go

create table decbetweentesting

(      id int identity(1,1) not null primary key

,      testdec decimal(19,5) not null

)

go

insert into decbetweentesting (testdec) values (.99), (1), (1.01), (2), (2.01)

go

select * from decbetweentesting where testdec between '1' and '2'

And the long form equivalent

select * from decbetweentesting where testdec >= '1' and testdec <= '2'

id testdec
2 1.00000
3 1.01000
4 2.00000


id testdec
2 1.00000
3 1.01000
4 2.00000


Easy, right?

So don't mistake that simple logic for dates.

create table datebetweentesting

(      id int identity(1,1) not null primary key

,      testdate datetime not null

)

go

insert into datebetweentesting (testdate) values ('12/29/2011 23:59:59'), ('12/30/2011 00:00:00'), ('12/30/2011 00:01:00'), ('12/31/2011 00:00:00'), ('12/31/2011 00:01:00'), ('1/1/2012 00:00:00'), ('1/1/2012 00:01:00')

go

select * from datebetweentesting where testdate between '12/30/2011' and '12/31/2011'

select * from datebetweentesting where testdate >= '12/30/2011' and testdate <= '12/31/2011'

id testdate
2 2011-12-30 00:00:00.000
3 2011-12-30 00:01:00.000
4 2011-12-31 00:00:00.000


id testdate
2 2011-12-30 00:00:00.000
3 2011-12-30 00:01:00.000
4 2011-12-31 00:00:00.000



This simple query to get everything before the end of the year will IGNORE things that happened during the date of 12/31/2011, after midnight.  Almost certainly not what you want.

But, don't fall for this:

select * from datebetweentesting where testdate between '12/30/2011' and '1/1/2012'

select * from datebetweentesting where testdate >= '12/30/2011' and testdate <= '1/1/2012'

 id testdate

2 2011-12-30 00:00:00.000

3 2011-12-30 00:01:00.000

4 2011-12-31 00:00:00.000

5 2011-12-31 00:01:00.000

6 2012-01-01 00:00:00.000

id testdate

2 2011-12-30 00:00:00.000

3 2011-12-30 00:01:00.000

4 2011-12-31 00:00:00.000

5 2011-12-31 00:01:00.000

6 2012-01-01 00:00:00.000

Which would return data from the new year.

In other words, to catch the end of 2011 and nothing in 2012, don't use BETWEEN.

select * from datebetweentesting where testdate >= '12/30/2011' and testdate < '1/1/2012'

id testdate
2 2011-12-30 00:00:00.000
3 2011-12-30 00:01:00.000
4 2011-12-31 00:00:00.000
5 2011-12-31 00:01:00.000


In fact, I rarely use it at all because of this common mis-perception about the border exclusivity in other developers.

SQL Server Best Practices for DMV's - Houston Tech Fest 2012

It is a pleasure meeting everyone today at Houston Tech Fest!  The new facility is miles away - literally and figuratively - from the previous Houston Tech Fest facility.

Here's a link below for downloading the presentation files and sample scripts from my presentation on "SQL Server Best Practices for DMV's" at Houston Tech Fest 2012 at the Reliant Center.

Download the .zip file here.

SQL Agent Alerts - Severity Condition is NOT "greater than or equal to"

I ran into some bad training that had convinced a client sys admin that the SQL Agent Alerts had the "greater than or equal to property."

As in, if I want to catch and notify an operator of all high severity errors 17 through 25, all I needed to do was set up an Alert for Severity 17.  This is false.

Here's a quick proof:

USE [msdb]

GO

--This query will not run in a batch for obvious reasons.  The high-severity error messages will kill the batch.

--Run each statement manually and observe the results.

EXEC msdb.dbo.sp_add_alert @name=N'020',

              @message_id=0,

              @severity=20,

              @enabled=1,

              @delay_between_responses=0,

              @include_event_description_in=1,

              @job_id=N'00000000-0000-0000-0000-000000000000'

GO

--This query will return date and time 0 | 0.  The new alert exists.

select name, last_occurrence_date, last_occurrence_time from msdb.dbo.sysalerts where name = '020'

GO

RAISERROR (N'testingonly.  This is message %s %d.', -- Message text.

           21, -- Severity,

           1, -- State,

           N'number', -- First argument.

           5) WITH LOG; -- Second argument.

GO

--This query will still return date and time 0 | 0.  Alert for Severity 20 has NOT been triggered by a Severity 21 error.

select name, last_occurrence_date, last_occurrence_time from msdb.dbo.sysalerts where name = '020'

GO

RAISERROR (N'testingonly.  This is message %s %d.', -- Message text.

           20, -- Severity,

           1, -- State,

           N'number', -- First argument.

           5) WITH LOG; -- Second argument.

GO

--This query will return a valid date and time.  Alert for Severity 20 has been triggered by a Severity 20 error.

select name, last_occurrence_date, last_occurrence_time from msdb.dbo.sysalerts where name = '020'

GO

As you run the above script statement-by-statement (NOT in a batch), you'll see the occurrence data trigger only for an error with the severity EQUALS the defined alert state.  In this case, the Severity 21 error did NOT trigger the Severity 20 alert.

Here's a basic script from my toolbox to set up Alerts for all significant error severities, to notify your operator via email with details.  In this script, the operator is called "DBAs", and ideally would be a distribution group for all active members of the Database Administrator role in your IT department.

Make sure that database mail is setup, that SQL Agent is configured to use the appropriate Database Mail profile, and that the failsafe operator is enabled to email your DBA team.  

USE [msdb]

GO

EXEC msdb.dbo.sp_add_alert @name=N'errorseverity 17',

              @message_id=0,

              @severity=17,

              @enabled=1,

              @delay_between_responses=600,

              @include_event_description_in=1

GO

EXEC msdb.dbo.sp_add_notification @alert_name=N'errorseverity 17', @operator_name=N'DBAs', @notification_method = 1

GO

EXEC msdb.dbo.sp_add_alert @name=N'errorseverity 18',

              @message_id=0,

              @severity=18,

              @enabled=1,

              @delay_between_responses=600,

              @include_event_description_in=1

GO

EXEC msdb.dbo.sp_add_notification @alert_name=N'errorseverity 18', @operator_name=N'DBAs', @notification_method = 1

GO

EXEC msdb.dbo.sp_add_alert @name=N'errorseverity 19',

              @message_id=0,

              @severity=19,

              @enabled=1,

              @delay_between_responses=600,

              @include_event_description_in=1

GO

EXEC msdb.dbo.sp_add_notification @alert_name=N'errorseverity 19', @operator_name=N'DBAs', @notification_method = 1

GO

USE [msdb]

GO

EXEC msdb.dbo.sp_add_alert @name=N'errorseverity 20',

              @message_id=0,

              @severity=20,

              @enabled=1,

              @delay_between_responses=600,

              @include_event_description_in=1

GO

EXEC msdb.dbo.sp_add_notification @alert_name=N'errorseverity 20', @operator_name=N'DBAs', @notification_method = 1

GO

USE [msdb]

GO

EXEC msdb.dbo.sp_add_alert @name=N'errorseverity 21',

              @message_id=0,

              @severity=21,

              @enabled=1,

              @delay_between_responses=600,

              @include_event_description_in=1

GO

EXEC msdb.dbo.sp_add_notification @alert_name=N'errorseverity 21', @operator_name=N'DBAs', @notification_method = 1

GO

USE [msdb]

GO

EXEC msdb.dbo.sp_add_alert @name=N'errorseverity 22',

              @message_id=0,

              @severity=22,

              @enabled=1,

              @delay_between_responses=600,

              @include_event_description_in=1

GO

EXEC msdb.dbo.sp_add_notification @alert_name=N'errorseverity 22', @operator_name=N'DBAs', @notification_method = 1

GO

USE [msdb]

GO

EXEC msdb.dbo.sp_add_alert @name=N'errorseverity 23',

              @message_id=0,

              @severity=23,

              @enabled=1,

              @delay_between_responses=600,

              @include_event_description_in=1

GO

EXEC msdb.dbo.sp_add_notification @alert_name=N'errorseverity 23', @operator_name=N'DBAs', @notification_method = 1

GO

USE [msdb]

GO

EXEC msdb.dbo.sp_add_alert @name=N'errorseverity 24',

              @message_id=0,

              @severity=24,

              @enabled=1,

              @delay_between_responses=600,

              @include_event_description_in=1

GO

EXEC msdb.dbo.sp_add_notification @alert_name=N'errorseverity 24', @operator_name=N'DBAs', @notification_method = 1

GO

USE [msdb]

GO

EXEC msdb.dbo.sp_add_alert @name=N'errorseverity 25',

              @message_id=0,

              @severity=25,

              @enabled=1,

              @delay_between_responses=600,

              @include_event_description_in=1

GO

EXEC msdb.dbo.sp_add_notification @alert_name=N'errorseverity 25', @operator_name=N'DBAs', @notification_method = 1

GO

More info:
Error severity info: http://msdn.microsoft.com/en-us/library/ms164086.aspx