Good point. As it happens, BETWEEN was first so, if anything, would have been disadvantaged. But, I admit, this wasn't a controlled test and could be affected by...

For a SELECT, BETWEEN seems the most efficient :

SET DATEFORMAT dmy

DECLARE @DayStart DateTime

DECLARE @DayEnd DateTime

SET @DayStart = CAST('2/2/2004' AS DateTime)

SET @DayEnd = CAST('3/2/2004' AS DateTime)

SELECT * FROM MyTable WHERE CreatedDate...

CAST(CAST(GETDATE() AS Char(11)) AS DateTime) took 4 times as long as datediff or numeric on my server!

I have tried both methods as below. The DateDiff method turns out 5.5 percent faster on my loval installation of SS7 :

I don't think you can secure it completely but you can detect changes. A trigger as suggested will work except with outright sabotage where someone disables the trigger! ...

Create a temporary table prepopulated with twelve rows, one for each month, and then do an outer join on this table to provide the missing month numbers.

TOP works but I did come across a curious behaviour the other day. A fairly simple query without TOP ran in a few seconds. With TOP, I killed...

Thanks to you both. gljjr, looks a robust solution but I would prefer to avoid polling if possible. Greg, I will need to study this and try it...

What are the data types? Is it possible that there is a some subtle mismatch?

You can't pass parameters with OPENQUERY. You have to build the string first and then send it.