SELECT LEFT(SCHID, x.Pos - 1) [Id],
       REPLACE(SUBSTRING(SCHID, x.Pos + 1, 8000),',','|') [AdditionalIds]
  FROM #X1
 CROSS APPLY(VALUES (CHARINDEX(',', SCHID))) x(Pos)

I think it actually increases fragmentation, because records are typically added in increasing date order rather than decreasing date order.  It improves efficiency, because the index is organised in...

I also notice that you...

I've created some test code, not realistic, but the CTE with SELECT TOP(1) is over ten times faster, though has more logical reads.

set statistics io, time off
set...

Hi Michael, The indexes recommended by SQL Server are not always the best, the index you would need to try is:

CREATE NONCLUSTERED INDEX [IX98_SampleData3]
ON [dbo].[SampleData2] (ACC,[Name],[Ref],[DATE]) INCLUDE...

It also requires two scans of the table and is approximately twice as expensive as a result.  It will always perform...

SELECT [id],
       Stuff((SELECT ',' + [VALUE]
               ...

You could be missing some curly brackets from column form_line1.

Try running this to see if there are any invalid values in column form_line1

select f.form_line1
from s1for_formula f
where f.form_line1...

If you are doing that many databases, wouldn't you be better off generating a script to create them? Then you'd just need one click to create them all.

If you press the left-arrow it goes back to the Databases node.

Here's a method that doesn't use ROW_NUMBER()

;WITH CTE AS
(
    SELECT DISTINCT SD2.Name,
           SD2.ref
 ...

In my experience, the size of the backup is reduced to about 1/6 that of a non-compressed backup.

It does, of course, depend on the contents of your database. If you...

Yes, that was one thought. I don't really understand why that query is taking 7 seconds to complete when it's just reading an index with two 14 character columns on...

It's only reading the index so it seems about as efficient as it could get.

Have you tried rebuilding the index?

Could be the statistics need updating and the plan it uses with the parameters is a better plan.

You should look at the execution plan of both queries.