SQL Server Row Byte Limit

[Guest]

The documentation from M'soft for the limits on SQL Server DataBase is:

"SQL Server can have as many as two billion tables per database and 1,024
columns per table. The number of rows and total size of the table are limited
only by the available storage. The maximum number of bytes per row is 8,060.
If you create tables with varchar, nvarchar, or varbinary columns in which
the total defined width exceeds 8,060 bytes, the table is created, but a
warning message appears. Trying to insert more than 8,060 bytes into such a
row or to update a row so that its total row size exceeds 8,060 produces an
error message and the statement fails."

I have a database that has large binary fields representing images that far
exceeds the 8060 bytes per row but yet I don't get an error...why is this?

 

[David Browne]

The documentation from M'soft for the limits on SQL Server DataBase is:

"SQL Server can have as many as two billion tables per database and 1,024
columns per table. The number of rows and total size of the table are
limited
only by the available storage. The maximum number of bytes per row is
8,060.
If you create tables with varchar, nvarchar, or varbinary columns in which
the total defined width exceeds 8,060 bytes, the table is created, but a
warning message appears. Trying to insert more than 8,060 bytes into such
a
row or to update a row so that its total row size exceeds 8,060 produces
an
error message and the statement fails."

I have a database that has large binary fields representing images that
far
exceeds the 8060 bytes per row but yet I don't get an error...why is this?

BLOB types (Text and Image datatypes) don't count. For these types a
16-byte pointer is stored on the page, and the data is stored elsewehre in
the file. It's just varchar and varbinary which are stored on the page and
count toward the 8060 byte limit.

David

 

[Guest]

Hey thanks a lot for the explaination...wonder why M'soft couldn't have
expanded their's a bit. However, believe it or not, I'm not sure what I'm
using for the images. I have an access database and when I create the table,
I use OLEOBJECT for the field type then convert images to a byte array and
save it using parameters and OleDb.OleDbType.LongVarBinary as the param type.
It seems to work since I can store two pictures of 500k bytes each in one
record.