What is the length of my data with MSSQL

Microsoft SQL Server has rich string functionality.
datalength will provide you with the actual length of the data.

Data used

USE sql911;
SELECT deptname AS 'Department',
       location AS 'Location',
       admrdept AS 'Reports to'
FROM department;
go
Department                           Location         Reports to
------------------------------------ ---------------- ----------
SPIFFY COMPUTER SERVICE DIV.         NULL             A00
PLANNING                             NULL             A00
INFORMATION CENTER                   NULL             A00
DEVELOPMENT CENTER                   NULL             A  
MANUFACTURING SYSTEMS                NULL             D  
ADMINISTRATION SYSTEMS               NULL             D01
SUPPORT SERVICES                     NULL             A00
OPERATIONS                           NULL             E01
SOFTWARE SUPPORT                     NULL             E01

(9 row(s) affected)

Datalength

SELECT deptname AS 'Department',
       datalength(deptname) AS 'Dept data length',
       location AS 'Location',
       datalength(location) AS 'Location data length',
       admrdept,
       datalength(admrdept) AS 'Location data length'
FROM department;
go
Department                           Dept data length Location         Location data length admrdept Location data length
------------------------------------ ---------------- ---------------- -------------------- -------- --------------------
SPIFFY COMPUTER SERVICE DIV.         28               NULL             NULL                 A00      3
PLANNING                             8                NULL             NULL                 A00      3
INFORMATION CENTER                   18               NULL             NULL                 A00      3
DEVELOPMENT CENTER                   18               NULL             NULL                 A        3
MANUFACTURING SYSTEMS                21               NULL             NULL                 D        3
ADMINISTRATION SYSTEMS               22               NULL             NULL                 D01      3
SUPPORT SERVICES                     16               NULL             NULL                 A00      3
OPERATIONS                           10               NULL             NULL                 E01      3
SOFTWARE SUPPORT                     16               NULL             NULL                 E01      3

(9 row(s) affected)
  • deptname is defined as varchar(36).
  • admrdept is defined as char(3).
  • datalength is only useful with: varchar, varbinary, text, image, nvarchar, and ntext data types.
  • datalength reports the length of the data stored in that column.
  • datalength reports the length of the data stored including the leading and trailing spaces in that column, so it's useless for char().
  • The datalength of a NULL value is NULL.