|
The following
Microsoft SQL Server T-SQL code samples demonstrate the usage for the DATALENGTH function and the difference between LEN() and DATALENGTH():
-- The LEN function counts characters(ASCII/UNICODE) without trailing spaces (RTRIM)
-- The DATALENGTH function counts bytes allocated including trailing spaces
DECLARE @string char(5) = 'ABC'
SELECT [LEN]=LEN(@string), [DATALENGTH]=DATALENGTH(@string)
/* LEN DATALENGTH
3 5 */
------------
DECLARE @nstring nchar(5) = N'ABC'
SELECT [LEN]=LEN(@nstring), [DATALENGTH]=DATALENGTH(@nstring)
/* LEN DATALENGTH
3 10 */
------------
DECLARE @string varchar(5) = 'ABC '
SELECT [LEN]=LEN(@string), [DATALENGTH]=DATALENGTH(@string)
/* LEN DATALENGTH
3 4 */
------------
DECLARE @nstring nvarchar(5) = N'ABC '
SELECT [LEN]=LEN(@nstring), [DATALENGTH]=DATALENGTH(@nstring)
/* LEN DATALENGTH
3 8 */
------------
------------
-- LEN/DATALENGTH Application to non-string data types
------------
DECLARE @money money = 123
SELECT [LEN]=LEN(@money), [DATALENGTH]=DATALENGTH(@money)
/* LEN DATALENGTH
6 8 */
------------
DECLARE @varbinary varbinary(max) = 0xFFEEDDCCBBAA2020
SELECT [LEN]=LEN(@varbinary), [DATALENGTH]=DATALENGTH(@varbinary)
/* LEN DATALENGTH
6 8 */
------------
DECLARE @binary binary(12) = 0xFFEEDDCCBBAA2020
SELECT [LEN]=LEN(@binary), [DATALENGTH]=DATALENGTH(@binary)
/* LEN DATALENGTH
12 12 */
------------
USE Northwind;
SELECT ' OrderID,' AS [Column],
(SELECT MAX(DATALENGTH(OrderID))
FROM Orders) AS [DATALENGTH]
UNION ALL
SELECT ' ShipName,',
(SELECT MAX(DATALENGTH(ShipName))
FROM Orders)
UNION ALL
SELECT ' ShipAddress,',
(SELECT MAX(DATALENGTH(ShipAddress))
FROM Orders)
UNION ALL
SELECT ' ShipCity,',
(SELECT MAX(DATALENGTH(ShipCity))
FROM Orders)
UNION ALL
SELECT ' ShipRegion,',
(SELECT MAX(DATALENGTH(ShipRegion))
FROM Orders)
/*
Column DATALENGTH
OrderID, 4
ShipName, 68
ShipAddress, 92
ShipCity, 30
ShipRegion, 26
*/
|
