SQL SERVER – INFORMATION_SCHEMA.COLUMNS and Value Character Maximum Length -1

I personally use the sys schema and DMV to retrieve most of the information. However, I am not surprised see usage of Information_Schema. It has been very popular and works in most of the time. Though, I do not use any feature it does not mean everybody else should stop using the same feature. The matter of the fact, when I receive questions about features which I have not used frequently I feel refreshed to come across new concepts.

Just a few days ago, I received a simple question about INFORMATION_SCHEMA.COLUMNS table. The question was as follows:

Question: I often see the value -1 in the CHARACTER_MAXIMUM_LENGTH column of INFORMATION_SCHEMA.COLUMNS table. I understand that the length of any column can be between 0 to large number but I do not get it when I see value in negative (i.e. -1). Any insight on this subject?

Answer: Of course, I love this kind of simple question which often know the answer or assume that we know the answer. Whenever we use data type VARCHAR(MAX) for any column it is represented by -1 in INFORMATION_SCHEMA.COLUMNS table. Let us see a quick demonstration of the same.

Let us create a table which has column which is of VARCHAR(MAX) and see the result returned by the same.

-- 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  

Let us check the resultset.

You will see that the column address which is of datatype VARCHAR(MAX) have Character Maximum Length value as -1. You will see the same behavior from nvarchar(max) and varbinary(max).

I personally believe in simple learning – if we learn a thing a day we will learn 365 new things every year!

Reference: Pinal Dave (https://blog.sqlauthority.com)