Total length off all characters in all columns of each row

Question

I'm new to SQL Server so I apologize if my question seems too easy. I tried finding and answer on my own, but I'm failing miserably. I am trying to create a query which will return total size on the drive of each row in the table.

i thought about using dbcc showconting but it doesn't work for varchar(max) which appears in my table. Also, it doesn't return size for each row, but rather the average, max and min size. My reading so far suggests that it is not possible to get query that could show the size of each individual row in the table so I decided to settle for the total length of all characters in each column in each row. Indirectly it will give me idea about the size of each row.

I have a table with some varchar(500) and varchar(max) columns. I noticed that some of the rows are a lot bigger than others.

What I need is top 1000 longest rows in the table, preferably in an output showing two columns: Column 1 showing EntryID Column 2 showing total length of the characters in all columns together for that record (eg total length of the characters in the column 1 + total length of the characters in the column 2 + column3 + column4 etc...) It would be great if this could be aliased RowLength.

What I tried so far is:

SELECT TOP 1000 
(LEN(columnname1) + LEN(columnname2) + LEN(columnname3) + LEN(columnname4)) as RowLength,
 FROM dbo.tablename
 ORDER BY Length Desc

It works, but it doesn't show entry ID corresponding to the total length of all characters in the row. How do I add it?

It also doesn't show the alias for the column showing number of characters in the row.

Could you please suggest how I can change the query to get the expected outcome? I'll be very grateful for any suggestions.

Answer 1

it doesn't show EntryID corresponding to the total length of all characters in the row. It also doesn't show the alias for the column showing number of characters in the row.

You have not specified an alias, so what should it show? You also haven't selected EntryID. If you want the longest 1000 rows you have to order by the length:

SELECT TOP 1000 
    EntryID,  
    Length = LEN(columnname1) + LEN(columnname2) + LEN(columnname3) + LEN(columnname4)
 FROM dbo.tablename
 ORDER BY Length DESC

Answer 2

SELECT TOP 1000 EntryID,
(LEN(columnname1) + LEN(columnname2) + LEN(columnname3) + LEN(columnname4)) AS   RowLength,
 FROM dbo.tablename
 ORDER BY EntryID