CODEWITHSUNDEEP
sqlsql-serversql-server-2008
Barış Velioğlu
Barış Velioğlu

Reputation: 5817

Getting max length of a varchar(max) from syscolumns in sql server

select  c.name, t.name, c.length   
from syscolumns c

c.length gives me -1 for any column that has max e.g varchar(max)

What should I do to get length ?

Upvotes: 3

Views: 4091

Answers (4)

Remus Rusanu
Remus Rusanu

Reputation: 294237

-1 means that the column is of type max. The max length is then the max type, as per documentation. MAX types have a maximum length of 2GB if the FILESTREAM attribute is not specified, or a max size limited only by the disk size available:

The sizes of the BLOBs are limited only by the volume size of the file system. The standard varbinary(max) limitation of 2-GB file sizes does not apply to BLOBs that are stored in the file system.

Therefore your question really doesn't have an answer. You can ask what is the actual size of any actual in the table value, using DATALENGTH.

Upvotes: 5

Ɖiamond ǤeezeƦ
Ɖiamond ǤeezeƦ

Reputation: 3331

The length of the column in each row could vary. Hence the result of -1

Upvotes: 0

aF.
aF.

Reputation: 66697

As seen HERE:

Variable-length, non-Unicode character data. n can be a value from 1 through 8,000. max indicates that the maximum storage size is 2^31-1 bytes. The storage size is the actual length of data entered + 2 bytes. The data entered can be 0 characters in length. The ISO synonyms for varchar are char varying or character varying.

In other words, max = 2147483647 bytes if all the possible space is occupied..

Upvotes: 0

Andrew
Andrew

Reputation: 27294

The data type of length on sys.columns is a smallint, whilst the max length of the varchar(max) is 2.1 billion, so it has a problem holding the real length. The -1 is in the documentation for denoting a varchar(max), varbinary(max), nvarchar(max) and xml.

http://msdn.microsoft.com/en-us/library/ms176106(v=sql.100).aspx

If you really need the number, then you would need a case statement to replace -1 with (2^31)-1

If you want to get the length of physical data, then you need to max / min / avg the appropriate lengths on the tables with the data on it based on what you need that information for. When querying the length of the field, DATALENGTH returns the bytes used, LEN returns the characters count.

Upvotes: 7

Related Questions