azawan (1/5/2016)

---

Dear Scot

First thanks for your reply, i am getting following error message

Msg 8116, Level 16, State 1, Line 8

Argument data type date is invalid for argument 2 of dateadd...

azawan (1/5/2016)

---

Dear Scot

First thanks for your reply, i am getting following error message

Msg 8116, Level 16, State 1, Line 8

Argument data type date is invalid for argument 2 of dateadd...

I can't use an inline tally table because a filter at work blocks it, so I'm using a physical tally table. The table is named "tally" and its column...

I just noticed the OR condition on only status. That will force a table scan regardless. But I'd still like to see the actual query plan.

Btw, are you...

You should cluster this table on ( ProcessDate ) or ( ProcessDate, SOID ) if you want to insure the index is unique. You can create a separate nonclustered...

I guess I wasn't clear enough. The idea is shrinking other databases on the same drive set(s) to free disk space, not on the db that needs the index...

You can create a non-temp table in tempdb.

CREATE TABLE tempdb.dbo.table_name ( ... )

That table will go away only when you explicitly drop it.

SELECT DISTINCT females_only.*

From dbo.vw_csys_NurserySchool

Cross Apply (

SELECT Parent1_id, Parent1_FullName, Parent1_Email, Parent1_STATUS, Parent1_Gender, YEAR, FULL_ADDRESS

WHERE Parent1_Gender = 'F' AND CATEGORY <> 'STF'

UNION

SELECT Parent2_id, Parent2_FullName, Parent2_Email, Parent2_STATUS, Parent2_Gender,YEAR, FULL_ADDRESS

WHERE Parent2_Gender = 'F' AND...

14GB is almost certainly too much. Keep in mind that the 'max memory' limit for SQL Server is for buffer space only. SQL will take additional RAM for...

Change the UNION ALL to just UNION. But the only way you'd have dups is if parent1 and parent2 both contained the same data.

Select females_only.*

From dbo.vw_csys_NurserySchool

Cross Apply (

SELECT Parent1_id, Parent1_FullName, Parent1_Email, Parent1_STATUS, Parent1_Gender, FULL_ADDRESS

WHERE Parent1_Gender = 'F'

UNION ALL

...

Very odd. Any conversion of the column should be vastly more likely...

You can use tempdb database instead of the db containing the index to hold most of the data for the rebuild if you prefer. But you will need enough...

If you mean all times for today's date, and not all times for the immediately preceding 24 hours, then you should do this:

WHERE (a.DateAppReceived >= DATEADD(DAY, DATEDIFF(DAY, 0, GETDATE()), 0)...

You need a WHERE condition on the SELECT from the CTE:

...

SELECT log1.ID,

log1.STime StartTime,

log2.STime EndTime,

DATEDIFF(second, log1.STime,...