Maybe try this:

select k.charges,
sum(case when k.range>='101' then charges else 0 end) as '101charges',
sum(case when k.range>='201' then charges else 0 end) as '201charge'
from
(select charges,case when substring(daterange,1,3) like '0-%'...

SELECT CAST(SUBSTRING((getFileName, 5, 4) + 
    LEFT(getFileName, 4) AS date) AS FileNameDate 
FROM #t

If you can afford to have CASCADE in effect, drop and recreate the index with ON DELETE CASCADE specified.

Hmm, yeah, if not, we'll have some complex code to write.  We...

If you are going to do that -- and then keep modifying that index endlessly as the base query add columns and otherwise changes -- you might as...

If you are going to do that -- and then keep modifying that index endlessly as the base query add columns and otherwise changes -- you might as well do...

It depends, but, yes, if this query is vital for you, then change the clustering key to ( registration_date, UserID /*assuming a unique UserID*/ ) (the PK can still be...

SQL itself will delete from the FK-related tables if you "tell" it to: that is, you set the ON DELETE CASCADE option on for that FK.

Great, glad that resolved it.  Autoclose on or taking the db offline were the only things I could think of that would cause that to happen.

I believe heaps do show up, just under index_id 0 rather than 1.

OK, I finally found notes on the sys view I was trying to remember:

sys.dm_db_file_space_usage

This time, that should (actually) help you determine what you need to know (you would likely need...

So sorry, obviously I didn't look closely enough.

I can't imagine the db has autoclose on or that the db is taken offline, so, yeah, that is really weird.

Take a look at sys.dm_db_index_usage_stats, it should give you what you want.

With the exception of random but evenly distributed indexes, I wouldn't use logical fragmentation to determine if an index needs to be rebuilt.  In the case mentioned, you actually...

Wrap the value in a: FLOOR, CEILING or ROUND(,0) function, depending on how you want to handle the decimal part of the value.

There's not a lot of data left in the file, so I'm rather surprised it would take that long to shrink it, since SQL wouldn't need to move that much...