TechBrothersIT is the blog spot and a video (Youtube) Channel to learn and share Information, scenarios, real time examples about SQL Server, Transact-SQL (TSQL), SQL Server Database Administration (SQL DBA), Business Intelligence (BI), SQL Server Integration Services (SSIS), SQL Server Reporting Services (SSRS), Data Warehouse (DWH) Concepts, Microsoft Dynamics AX, Microsoft Dynamics Lifecycle Services and all other different Microsoft Technologies.
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