SQL Server stores metadata about your database and its contents. These are typically accessed via
sys. tables and
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.
CLICK HERE to find out more related problems solutions.