Determine top 25 rows in a table that includes a ntext data type.

We have a table that has ~900,000 rows in the table. The table previously had
~1.5 million rows of data when the ~600,000 rows where deleted the table size
shrunk ~1 G. The table is currently useing ~27 GB of storage.
I would like to write a query to determine my largest 25 rows. This is
being done to determine if the storage is truely being used.
C,
Perhaps:
SELECT TOP 25 RowKey, LEN(NTextColumn)
FROM YourTable
ORDER BY LEN(NTextColumn) DESC
RLF
"C RAMSEY" <CRAMSEY@.discussions.microsoft.com> wrote in message
news:B84581A5-1BB6-4495-B68C-FB65E250E917@.microsoft.com...
> We have a table that has ~900,000 rows in the table. The table previously
> had
> ~1.5 million rows of data when the ~600,000 rows where deleted the table
> size
> shrunk ~1 G. The table is currently useing ~27 GB of storage.
> I would like to write a query to determine my largest 25 rows. This is
> being done to determine if the storage is truely being used.
|||I'm attemping to run it now. I had to use DATALENGTH instead of LEN for the
NTEXT field.
I was hoping to find a was to gather the information using system tables ie
number of pages used by row.
"Russell Fields" wrote:

> C,
> Perhaps:
> SELECT TOP 25 RowKey, LEN(NTextColumn)
> FROM YourTable
> ORDER BY LEN(NTextColumn) DESC
> RLF
> "C RAMSEY" <CRAMSEY@.discussions.microsoft.com> wrote in message
> news:B84581A5-1BB6-4495-B68C-FB65E250E917@.microsoft.com...
>
>
|||> I was hoping to find a was to gather the information using system tables
> ie
> number of pages used by row.
No system table stores this information... there might be some undocumented
DBCC command that does it, but even that would probably be coming from the
page angle, not the row angle.
|||That's what I was affraid of. I used DBCC IND to get the page information.
The other technique was taking to long to run so I stopped it.
"Aaron Bertrand [SQL Server MVP]" wrote:

> No system table stores this information... there might be some undocumented
> DBCC command that does it, but even that would probably be coming from the
> page angle, not the row angle.
>
>
|||> That's what I was affraid of. I used DBCC IND to get the page
> information.
Did you figure out the top 25 rows already? If not...

> The other technique was taking to long to run so I stopped it.
...maybe you could try doing this in two steps, e.g.
SELECT pk, tcl = DATALENGTH(text_col) INTO #foo FROM main_table;
SELECT TOP 25 pk, tcl FROM #foo ORDER BY tcl DESC;
This might be a lot cheaper then including the datalength() calculation and
the ordering in the same step.
|||C RAMSEY wrote:
> We have a table that has ~900,000 rows in the table. The table previously had
> ~1.5 million rows of data when the ~600,000 rows where deleted the table size
> shrunk ~1 G. The table is currently useing ~27 GB of storage.
> I would like to write a query to determine my largest 25 rows. This is
> being done to determine if the storage is truely being used.
When you delete rows, the space that was occupied might not be released.
It might be retained as "unused space", and reused for future rows.
If it is your goal to lower the used storage, then you could first run
sp_spaceused to determine the amount of unused space and/or DBCC
SHOWCONTIG to determine the average page density. If there is a lot of
unused space, then make sure you have a clustered index (or add one) and
run DBCC INDEXDEFRAG or DBCC DBREINDEX / ALTER TABLE to remove the
unused space.
HTH,
Gert-Jan