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 (http://blog.sqlauthority.com)

About these ads

9 thoughts on “SQL SERVER – INFORMATION_SCHEMA.COLUMNS and Value Character Maximum Length -1

  1. Thanks, Pinal, that is another great post.
    May I ask, why you write
    … WHERE TABLE_NAME=OBJECT_NAME(OBJECT_ID(‘t’))
    instead of
    … WHERE TABLE_NAME=’t’ ?

    Like

  2. Pingback: SQL SERVER – Weekly Series – Memory Lane – #038 | Journey to SQL Authority with Pinal Dave

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s