At a first glance, your SQL looks OK to me. Can you provide a script that

1. Creates the tables involved

2. Populates the tables with a few sample data

3. Executes the...

Nice solution, but I don't quite understand why you subtract 3 ms (and why you refer to month as m and mm in the same query). The following might be...

You could also try the following (which doesn't use a numbers table). You should probably have a (preferably clustered) index on your id column.

set nocount on

go

create table testtable (id int)

go

insert...

Try this:

create table test(id int identity(1,1), a int)

go

create proc insert_test

(

 @id int,

 @a int

)

as

begin

 set identity_insert test on

 insert into test (id, a) select @id, @a

 set identity_insert test off

end

go

grant exec on insert_test to public

go

exec...

Try

use [name.db]

A minor improvement on Sergiy's method... I don't know if it's faster than PW's algorithm, but the number of SQL statements seems to be the same (if you count each...

Exactly my conclusion. I think that the contents of your initial paragraph ought to be included in BOL (at least, I cannot find it) 

Good point, I didn't know that (but I guess I shouldn't be too surprised by now).

To answer this, I need to know whether ansi_nulls is on or off

Well, this is actually the case if you write col = null. Or (more likely) col = @var where @var can be null...

Good point on the joins. Although you could write

select foo from bar a join foobar b on a.col1 = b.col1 and a.col1 is not null

this is quite cumbersome, especially if...

I fully agree - if ansi_nulls is set to on. The point is that I have set ansi_nulls to off, which allows me to use null as a value in...

"where a = null", isn't that asking for an exact value match as well...?

Kenneth, thanks for the reply. I am aware of the correct "is null" syntax, I was only trying to stress the equality between examples 1 and 2.

The way I have...

426/852.0 I guess