SQL Server stores metadata about your database and its contents. These are typically accessed via sys. tables and information_schema. views.

The sys tables are somewhat specific to SQL Server, and tend to be fairly normalised. In contrast, the information_schema views (like other many reporting views you may create) provide data in a more readable format – they often join various sys tables together to get their results. See Difference between Information_schema vs sys tables in SQL Server for some more info.

COL_LENGTH() is a function that operates on the database, and doesn’t need to ‘read data’ as such.

However, for all practical purposes, you will find zero difference between these. If you’re just after the column length of a specific column, use COL_LENGTH, as it will probably be marginally faster. Otherwise feel free to use the information_schema views as they provide more easy-to-read information (or a custom set of sys tables joined together) as the number of reads to get the metadata is very small.

For example, I have a table I use for testing called ‘test’ with 5 columns (ID, and col2, col3, col4, col5). It has almost 2 million rows, but none of the data in that table actually needed to be read – just the metadata.

I ran the commands to get the column lengths/info from each. Each took 0.000s to complete (e.g., less than 1 millisecond). Here are the commands and results (first 10 columns only) to demonstrate some of the differences.

SELECT col_length('dbo.test', 'col2') AS Col2_info
  /*
  Col2_info
  100
  */

SELECT * FROM sys.columns where object_id = (SELECT TOP 1 object_id FROM sys.objects WHERE name = 'test')
  /*
  object_id   name  column_id  system_type_id  user_type_id  max_length  precision  scale  collation_name        is_nullable  is_ansi_padded
  2094630505  ID    1          56              56            4           10         0      NULL                  0            0
  2094630505  col2  2          167             167           100         0          0      Latin1_General_CI_AS  0            1
  2094630505  col3  3          167             167           100         0          0      Latin1_General_CI_AS  1            1
  2094630505  col4  4          167             167           100         0          0      Latin1_General_CI_AS  1            1
  2094630505  col5  5          167             167           100         0          0      Latin1_General_CI_AS  1            1
  */

SELECT * from information_schema.COLUMNS where table_name = 'test'
  /*
  TABLE_CATALOG  TABLE_SCHEMA  TABLE_NAME  COLUMN_NAME  ORDINAL_POSITION  COLUMN_DEFAULT  IS_NULLABLE  DATA_TYPE  CHARACTER_MAXIMUM_LENGTH
  Testdb         dbo           test        ID           1                 NULL            NO           int        NULL
  Testdb         dbo           test        col2         2                 NULL            NO           varchar    100
  Testdb         dbo           test        col3         3                 NULL            YES          varchar    100
  Testdb         dbo           test        col4         4                 NULL            YES          varchar    100
  Testdb         dbo           test        col5         5                 NULL            YES          varchar    100
  */

Note in the version above, the sys.columns version was a) harder to construct, as it was only related to the object_id of my test_table; also it provides data that is a lot less easily readable than the information_schema version.