If you're truncating your table every day then you're starting with an empty table. I can't think of any reason, therefore, why you'd want to rebuild your indexes at...

When do you get those messages - is it when you try to disable the job schedule from within a jobstep in the job? Why not just set the...

I'm going to have to disagree there, I'm afraid. Suppose the query had originally been written with an outer join, with the filter (quite correctly) in the join predicate,...

There's no difference. But don't take my word for it - compare the execution plans and see for yourself.

Now, if the join were an outer (instead of inner) join,...

If you don't have a backup, and you didn't have the job scripted out, then I'm afraid you've lost it. The sysjobhistory entries will only show that the job...

Jeff

Does your job highlight just jobs that failed, or does it include those that didn't run, perhaps because they were accidentally disabled, or because SQL Server Agent wasn't running?

I think...

Since included columns don't form part of the actual index key, it's unlikely they'll be useful in WHERE clauses, JOINs and so on. What they are useful for is...

I still can't get it to work on my data set - I've clearly done something wrong, and I don't really have time to investigate. Looks like I've been...

WITH -- Itzik Ben-Gaan's virtual tally runs fastest and I don't know why.

L0 AS (SELECT con vert(int, 1) AS C UN ION ALL SELECT con vert(int,1)),--2 rows

L1 AS (SELECT 1...

Ah yes - good spot! It still gives totally different results from the other queries, though - possibly because of those pesky time portions that you mentioned.

John

The Dixie Flatline (8/11/2016)

---

IF you can afford a nonclustered index on check_date, this will drastically reduce the i/o.

;with Eomonths (Eom) as (select top(500) EoMonth(DateAdd(MONTH,(N-1),'1/1/2016')) from tally)

select Eom as Check_date, Total

from...

Chris, yes, that's very similar to mine now.CPU Elapsed

--- -------

4303 1185

4260 1184

4273 1255

John

SQL Server 2014, four processors, 16GB RAM. Test harness:USE tempdb

CREATE TABLE Dates (MyDate datetime);

WITH N10(n) AS (

SELECT CAST(n AS bigint) FROM (VALUES (0),(1),(2),(3),(4),(5),(6),(7),(8),(9))m(n)

)

, N100 AS (

SELECT m1.n FROM...

Edvard

If you add one day to the last day of the month, you get the first day of the next month. You could therefore try something like this:

WHERE DATEPART(day,DATEADD(day,1,Check_date))...

I agree with Jeff - no need for PowerShell. Write a view or stored procedure, and create a job to e-mail the results.

I don't share Jeff's doubt over whether...