[RESOLVED] preposterous 8000 limit in SQLServer fields

[Phill64]

Hi all,

Now first of all, I know there's lots of forum posts on this topic, however, so far all I seem to find are workarounds to the 8000 varchar limit problem. That's not what i'm looking for, I would like to do this properly and so I would like to know some information about this to which I can't seem to find on any of Microsofts resources.

Ok so actually it's not varchar that I'm using, but the 8000 limit seems to apply to any field type, I'm using a binary field but I need to know it can store more than 8000. Now Microsoft itself says the size limit of a field is something like 2.1 billion bytes (same as every other database I've used).. yet when I try to create a field, it limits me to 8000, now I'm using express edition... and what I would like to know is..

does this same limit apply to Standard and Enterprise editions?

(I know there is a TOTAL 4gb limit on express, but there doesn't seem to be mention of field size)
So if anyone has Standard or Enterprise and could be kind enough to test making a field larger than 8000(or have done it before), please could you let me know?

If the paid for editions support the proper 2.1billion or so size, then I'll simply get hold of a copy of those. If not, I'll either mess around with multiple records in SQLServer or just ditch it for another engine.

Thanks for your time

[jmcilhinney]

If you're using varchar, varbinary or the like then you can specify the width of the field up to 8000 bytes (so 4000 characters for nvarchar) or else you can specify 'max'. Data that goes beyond the 8000 byte limit is stored external to the record itself for efficiency, hence the differentiation.

[Phill64]

If you're using varchar, varbinary or the like then you can specify the width of the field up to 8000 bytes (so 4000 characters for nvarchar) or else you can specify 'max'. Data that goes beyond the 8000 byte limit is stored external to the record itself for efficiency, hence the differentiation.

Thanks jm,

you're always full of useful knowledge!

[jmcilhinney]

Thanks jm,

you're always full of useful knowledge!

Full of something, that's for sure.

[leinad31]

http://msdn.microsoft.com/en-us/libr...1(SQL.90).aspx

[szlamany]

Don't ignore leinad31's link - it's got good info.

The 8000 byte limit is related to physical disk i/o of the sql engine - basically that's the max page size.

It's probably best to put a field with your varbinary(max) datatype into a table with very few columns - even if it's related to the PK of another table - resist putting a field like this into that table. Put it in a related sister table with the same PK.

We store photos of kids in a school district in a StuPhoto_T table that has just the PK of the student id and the single other field of the photo's binary data (actually we store the original filename and the size in bytes of the binary field as well)