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)
10 Comments. Leave new
Thanks Pinal..Even i dint know, the value of field varchar(MAX) repersents -1.
good to know. personally I avoid learning on weekends :)
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’ ?
Day by day learning something new, thanks
thanks pinal
Thanks Pinal.
Also if the datatype is xml the CHARACTER_MAXIMUM_LENGTH is -1.
Here’s another reason why data-types with a capacity of upto 2GB are represented as having a length of -1.
2 GB = 2147483648 bytes
However, as mentioned in my post ), this would result in a buffer overflow when represented as a 32-bit INT value, which has a range from -2^31 (-2147483648) to 2^31-1 (2147483647).
Thanks Pinal,i want to know about report tables in sql server.Plase help me.
i dont no column name .but I know value of columns then can i found value. from information_schema. plz inform me
I have a process that creates two files using the BCP process. The first step outputs the column names of a table into a file (tableName.hdr). The second one outputs the data into a second file (tableName.dat). I later concatenate the two files into a single export file (tableName.txt). Oddly, the first step started returning the column names in a different order than it had previously. Below is the command to get the column headers for the export file.
What would cause the following statement to start returning the columns in an order different that it had previously?
SET @command = ‘bcp “DECLARE @columns VARCHAR(MAX); SELECT @columns = COALESCE(@columns + CHAR(9), ””) + column_name FROM ‘ + @dbName + ‘.INFORMATION_SCHEMA.COLUMNS where TABLE_NAME = ”’ + @tableName + ”’; SELECT @columns;” queryout “‘ + @outputFileName + ‘.hdr” -c -r \n -T’;
EXEC master..xp_cmdshell @command, no_output;
To simplify; it seems like the order of the output of this query changed:
SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS WHRE TABLE_NAME = ‘tableName’
I realize that it does not include and ORDER BY clause which I have since added. I wouldn’t concern myself with this anomoly but our client wants to know why the column order changed from one day to the next. We have not modified the table structure.
Thanks in advance for any assistance you’re willing to offer.
Ken Sturgeon