INFORMATION_SCHEMA.COLUMNS and Value Character Maximum Length -1

INFORMATION_SCHEMA.COLUMNS and Value Character Maximum Length -1

July 20, 2012 by pinaldave

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 TableCREATE TABLE t(id INT,name VARCHAR(200),address VARCHAR(MAX))GO-- select from columnsSELECT COLUMN_NAME,CHARACTER_MAXIMUM_LENGTHFROM INFORMATION_SCHEMA.COLUMNSWHERE TABLE_NAME=OBJECT_NAME(OBJECT_ID('t'))GO-- drop tableDROP 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 (http://blog.sqlauthority.com)