how to get the max size used by a field in table

Question

I have a field which has been set to max size. How can i find the max size occupied by the field.

For example if the records are for table TableA

FieldA

123
abcd
1234567

I need to know which row occupied the most size and what the size is

Thanks

Prady

Answer 1

LEN tests for the length in characters, e.g. "a" = 1 char

select max(len(fieldA)) from tbl

DATALENGTH checks for the size in bytes, an NVarchar occupies 2 bytes per character

select max(datalength(fieldA)) from tbl

To get all the rows in the table that have the maximum length of data in FieldA,

select *
from tbl join (select MAX(LEN(fieldA)) maxlen from tbl) l
    on l.maxlen = LEN(tbl.fieldA)

Answer 2

SELECT TOP 1 WITH TIES * 
FROM tbl
ORDER BY len(tbl.fieldA) DESC