Determine size of record/row in a table

Question

Determine size of record/row in a table

Dan Fran

Old Hand

Points: 388
More actions
April 21, 2010 at 1:26 pm

#231970

Hello,
I am curious to know if there is a way to determine the size of a specific row/record in a given table. I know that sqlserver only allows 8kb as the maximum size. The reason that I am asking is that, from time to time, I have an application that tries to do and insert and/or an update and I get a 'String or binary data would be truncated' error which I am imagining is occurring due to the record being over 8kb.

Viewing 11 posts - 1 through 10 (of 10 total)

You must be logged in to reply to this topic. Login to reply

Steve Cullen SSCertifiable Points: 5598 More actions · Answer 1

Dan Fran (4/21/2010)
Hello,
I am curious to know if there is a way to determine the size of a specific row/record in a given table. I know that sqlserver only allows 8kb as the maximum size. The reason that I am asking is that, from time to time, I have an application that tries to do and insert and/or an update and I get a 'String or binary data would be truncated' error which I am imagining is occurring due to the record being over 8kb.

Varchar, nvarchar and varbinary can have a maximum size of 2^31 - 1 for SQL Server 2005 and beyond. A record is a row and is not limited to 8k.

You will get a truncation warning if you are trying to insert/update a value to a column where the size is less than the size of the source. For example, if you update a varchar(20) with a varchar(50), you may get a warning.

Converting oxygen into carbon dioxide, since 1955.

GregoryF SSCertifiable Points: 6296 More actions · Answer 2

Also, it needs to be remembered that a bitmap is created for NULL's

If memory serves me it 2 bytes + 1 additional byte for every eight columns

/* ----------------------------- */
Tochter aus Elysium, Wir betreten feuertrunken, Himmlische, dein Heiligtum!

Dan Fran Old Hand Points: 388 More actions · Answer 3

So based on your comment it's probably safe to say that one of the varchar fields (which in this table are all max) is being provided with a value beyond the max size. If that is the case what would be my alternative? ntext?

GregoryF SSCertifiable Points: 6296 More actions · Answer 4

If you had 10 varchar(8000) fields, you could certainly have a problem. The applications really should address this.

/* ----------------------------- */
Tochter aus Elysium, Wir betreten feuertrunken, Himmlische, dein Heiligtum!

Dan Fran Old Hand Points: 388 More actions · Answer 5

There are only two fields with varchar(max). I guess at the application level I could check to ensure that they do not enter no more than allotted amount of 8000.

GregoryF SSCertifiable Points: 6296 More actions · Answer 6

That's what needs to be done, the application needs to make the decision on what if any data can be truncated. It's very poor design that thry set up a DB with 16k records, but don't have any logic to deal with records over 8k

/* ----------------------------- */
Tochter aus Elysium, Wir betreten feuertrunken, Himmlische, dein Heiligtum!

Dan Fran Old Hand Points: 388 More actions · Answer 7

Thanks to all. I'll handle at the application level.

Jeff Moden SSC Guru Points: 1004363 More actions · Answer 8

Dan Fran (4/21/2010)
Hello,
I am curious to know if there is a way to determine the size of a specific row/record in a given table. I know that sqlserver only allows 8kb as the maximum size. The reason that I am asking is that, from time to time, I have an application that tries to do and insert and/or an update and I get a 'String or binary data would be truncated' error which I am imagining is occurring due to the record being over 8kb.

No... that error is caused by someone trying to put too much data into a column. It has nothing to do with total row size.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Dan Fran Old Hand Points: 388 More actions · Answer 9

Just to understand a little more, doesn't varchar(max) allow for 2^31-1 bytes (or characters)? If that is the case, is it such that even though it allows for appx 2 gigs worth of storage, that once the 8 kb limit is reach, that's when the error is thrown. Or better yet, if the error pertains to the field, I really doubt that the user could've enter almost 2gigs of text. I do apologize for my ignorance with this, but I like to always be clear.

GregoryF SSCertifiable Points: 6296 More actions · Answer 10

Dan Fran (4/21/2010)
Just to understand a little more, doesn't varchar(max) allow for 2^31-1 bytes (or characters)? If that is the case, is it such that even though it allows for appx 2 gigs worth of storage, that once the 8 kb limit is reach, that's when the error is thrown. Or better yet, if the error pertains to the field, I really doubt that the user could've enter almost 2gigs of text. I do apologize for my ignorance with this, but I like to always be clear.

That's true, I believe that data over 8k is put on it's own page. But, it's my understanding (I'm still largely in a 2000 world) that these should only be used where blob's would have been esed in the past.

/* ----------------------------- */
Tochter aus Elysium, Wir betreten feuertrunken, Himmlische, dein Heiligtum!