Biggest/Longest records in varchar table

Question

We recently noticed that in one of our biggest tables some of the rows are a few times bigger than others. By "bigger" I mean longer and taking more storage space.

How to display top 1000 biggest rows in the table?

Almost all columns are varchar so it would be great if the query could sum up the size of the data in each row and show the biggest rows.

I tried to modify this:

select MyVarcharColumnName
from MyTableName
where len(MyVarcharColumnName) =
(select max(len(MyVarcharColumnName)) from MyTableName)

and this:

select max(len(Desc)) from table_name

but

I get an error

Msg 156, Level 15, State 1, Line 1 Incorrect syntax near the keyword 'Desc'.

Answer 1

How about this?

select top 1000 t.*
from MyTableName t
order by len(MyVarcharColumnName) desc;

If you have multiple columns, you can add them to the order by:

order by (len(col1) + len(col2) + . . . ) desc

I have used len() for this calculation, because that is what you are using in your question. You might also be interested in [DATALENGTH()][1].

Answer 2

It thinks that Desc is a keyword rather than an identifier. Try to enclose it in angle brackets ([]), like this:

select max(len([Desc])) from table_name