As a SQL Server user, you may have come across the INFORMATION_SCHEMA.COLUMNS table while working with database schemas. This table provides valuable information about the columns in your database tables, such as their names, data types, and maximum lengths.

In a recent question I received, someone asked about the presence of negative values in the CHARACTER_MAXIMUM_LENGTH column of the INFORMATION_SCHEMA.COLUMNS table. They were puzzled by the appearance of -1 in this column and wanted to understand its significance.

The answer to this question lies in the usage of the VARCHAR(MAX) data type. When a column is defined as VARCHAR(MAX), it can store variable-length character data of any length, up to a maximum of 2^31-1 bytes. However, the INFORMATION_SCHEMA.COLUMNS table represents this maximum length as -1.

To illustrate this behavior, let’s create a sample table with a column of type VARCHAR(MAX) and examine the result returned by the INFORMATION_SCHEMA.COLUMNS table:

-- Create Sample Table
CREATE TABLE t (
  id INT,
  name VARCHAR(200),
  address VARCHAR(MAX)
)
GO

-- Select from columns
SELECT COLUMN_NAME, CHARACTER_MAXIMUM_LENGTH
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = OBJECT_NAME(OBJECT_ID('t'))
GO

-- Drop table
DROP TABLE t
GO

Upon executing the above code, you will notice that the column “address” which is of type VARCHAR(MAX) has a Character Maximum Length value of -1. This behavior is also observed with the NVARCHAR(MAX) and VARBINARY(MAX) data types.

It’s important to note that while I personally prefer using the sys schema and DMVs (Dynamic Management Views) to retrieve information about database objects, the INFORMATION_SCHEMA.COLUMNS table remains a popular choice among SQL Server users. It provides a simple and straightforward way to access column metadata.

So, the next time you encounter a negative value in the CHARACTER_MAXIMUM_LENGTH column of the INFORMATION_SCHEMA.COLUMNS table, you can confidently attribute it to the usage of the VARCHAR(MAX), NVARCHAR(MAX), or VARBINARY(MAX) data types.

Remember, learning something new every day adds up to a wealth of knowledge over time. By exploring different concepts and features in SQL Server, you can enhance your understanding and become a more proficient database professional.