Usage of DATALENGTH

Use DATALENGTH api is sqlserver to find the number of bytes taken by a value/data.It is useful with the variable length datatypes like : varchar,varbinary,image,text,nvarchar,ntext...

Examples:
1]
DECLARE @test nvarchar(12)
SELECT @test =' 1235 '
SELECT @test as STRING ,LEN(@test) LENGTH,DATALENGTH(@test) DATALNGTH

LENGTH[will trim the trailing spaces] gives the length of the data.DATALENGTH[[Will not trim]] will give the no of bytes occupied.
Output:
STRING LENGTH DATALNGTH
------------ ----------- -----------
1235 5 14

2]
DECLARE @i int
SELECT @i =' 123456789 '
SELECT @i AS I,LEN(@i) AS LENGTH,DATALENGTH(@i) AS DATALNGTH

If the variable is of int type, then all spaces will be trimmed in case of LENGTH.DATALENGTH will give number of bytes taken by the value
Output:
I LENGTH DATALNGTH
----------- ----------- -----------
123456789 9 4

3] Suppose you wanna find the number of bytes taken by the biggest attachment.
SELECT MAX(DATALENGTH(GDTXFT)) FROM F00165

More about DATALENGTH