Yes - every time you use dm_db_index_physical_stats, SQL Server has to go away and examine the indexes to find out their fragmentation level. Even though you've filtered on those...

No, do you have plenty of space on the disk(s) on which the msdb data and log files are located?

John

In that case, it's even simpler.

SELECT

id

,product

,MIN(start_date) AS StartDate

,NULL AS EndDate

FROM @t

GROUP BY

id

,product

HAVING COUNT(*) > COUNT(end_date)

But what happened to the DATEDIFF requirement?

John

That any integer is just a placeholder. As you've already noticed, you'll get the same result whatever argument you put in there. What you do have to be...

Do you have plenty of free space on the disk(s) on which the msdb data and log files are located?

John

Gosh, that's the first time you've mentioned that requirement. But not to worry - all you need is a UNION ALL and a further statement selecting all rows where...

Is it not as simple as this?

SELECT

id

,product

,MIN(start_date) AS StartDate

,MAX(end_date) AS EndDate

,DATEDIFF(DAY,MIN(start_date),MAX(end_date)) AS Diff

FROM @t

WHERE end_date IS NOT NULL

GROUP BY

id

,product

John

It is if you don't want to lose any data. Yes, if you were doing this at all then perhaps...

Pedro

It's all explained here.

John

Ooo, how interesting. I thought it was sys.master_files that doesn't get updated immediately, not sys.database_files.

John

That's curious. What does this return, please?

SELECT name, physical_name

FROM Widgets_Copy.sys.database_files

John

Are you sure you're looking at the physical name of the files and not the logical name? Please will you post the RESTORE DATABASE script you ran? (If...

https://msdn.microsoft.com/en-GB/library/ms187928(v=sql.100).aspx

John

No, system accounts can't be used for interactive logins. Did you give your own Windows account access when you installed SQL Server? If not, you'll need to ask...

There are circumstances in which option 4 is the correct answer. The question does not state that the table is empty, nor does it say anything about the data...