TSQL - What is the Difference between LEN() AND DataLength() Functions

LEN( ) function returns us the number of characters or length of text. This function can be used in different scenarios, It is very common we want to know the Max number of characters available in our one of the columns.

For that we can use

SELECT MAX(LEN(colname)) FROM dbo.TABLE

DataLength( ) function returns us the space(memory) taken by value we provide to this function in Bytes.

Let's see couple of examples

Fig 1: Len vs DataLength Functions in TSQL

As we can see that 'Aamir' has five characters and Len( ) functions returned us 5. DataLength( ) Function also returned us 5 ( 5 Bytes) as 'Aamir' is considered varchar data type and each character takes one byte in varchar data type.

Let's change the data type of 'Aamir' to NVARCHAR by adding N'Aamir'. Now our DataLength( ) function should return us 10 ( 10 Bytes). As two bytes are required to save NVARCHAR character.

Fig 2: Difference between DataLength() and Len() Function in TSQL