It really is fascinating sometimes. Often do a double-take at what the optimizer is actually doing to satisfy requests.

IIRC, you should also always be careful to screen out NULL values when using IN / NOT IN (really generally you should avoid IN / NOT IN, and instead use...

Lol, nah.

I love your technique, it's great. I like that general method -- every 7 days is always the same weekday -- rather than any method...

Another minor point. I deeply disagree with the use of:

FROM master..spt_values

...

Or maybe this, since PARTITION BY generally seems to be pretty fast in SQL Server:

;WITH cte1 AS (

SELECT ROW_NUMBER() OVER (PARTITION BY vSurname, cPostcode, cCoverType ORDER BY vSurname)

...

That query will only include rows where the condition is true, so assuming 5 such rows exist, I think it will always return 5.

I assume by "last 5" you mean...

For INNER joins only, I don't think the sequence really matters. SQL will re-arrange the joins as it needs to.

For any type of OUTER join, as you've already seen,...

That's an interesting idea. I guess it depends on one's point of view. Let's consider a more...

Replace "4" with:

4 + SUBSTRING('0012221', DATEDIFF(DAY, '19000101',

DATEADD(month, DATEDIFF(month, 0, '20100301'), 0)) % 7 + 1, 1)

SQL's base date, 19000101, is a Monday. So the...

Typically table-based UDFs are not in themselves a performance issue.

So the issue is more likely what you and others have mentioned:

indexes, stats, or the actual code in the...

Correction, I forgot one thing:

EXEC master.dbo.xp_cmdshell @bcp_cmd

That way you can run it from any db.

To run bcp -- or any other .exe from SQL -- you need to have an environment that the .exe can run in.

For example, you could do something like this:

EXEC...

Yeah, INs can have odd results, and are horrible for performance.

Try NOT EXISTS(), viz:

INSERT INTO contacts (email, business)

SELECT email, business

FROM scrapes s

WHERE (dbo.vaValidEmail(email) = 1)

AND (NOT EXISTS(SELECT...

You don't really even need the CASE then because if...

If you're using dynamic SQL anyway, why not remove all references to B3 when "B3.NameSearch" is NULL, since in that situation B3 isn't needed/used anyway?