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)
select max(len(Desc)) from table_name
I get an error
Msg 156, Level 15, State 1, Line 1 Incorrect syntax near the keyword 'Desc'.
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 (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
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