By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
443,534 Members | 865 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 443,534 IT Pros & Developers. It's quick & easy.

SQL Server Row Byte Limit

P: n/a
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?
--
Dennis in Houston
Nov 21 '05 #1
Share this Question
Share on Google+
2 Replies


P: n/a

"Dennis" <De****@discussions.microsoft.com> wrote in message
news:89**********************************@microsof t.com...
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
Nov 21 '05 #2

P: n/a
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.

--
Dennis in Houston
"David Browne" wrote:

"Dennis" <De****@discussions.microsoft.com> wrote in message
news:89**********************************@microsof t.com...
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

Nov 21 '05 #3

This discussion thread is closed

Replies have been disabled for this discussion.