Row Size Limitation

Question

Post reply

Row Size Limitation

Viewing 15 posts - 16 through 30 (of 35 total)

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

Gazareth One Orange Chip Points: 27737 More actions · Answer 1

Koen Verbeeck (9/29/2014)

arrjay (9/29/2014)

OK, thanks for the advice. Are we saying that without making significant structural changes to the table I won't be able tot increase the number of characters in the field in question? Thanks.

No. You went over the limit of bytes allowed. The only remedy is to reduce the number of bytes used and that is done by changing data types. Or removing columns altogether 🙂

You can look into vertical partitioning, but I'd imagine that's going to be significantly more work than changing data types.

arrjay Hall of Fame Points: 3455 More actions · Answer 2

arrjay

Hall of Fame

Points: 3455

September 29, 2014 at 7:18 am

#1748803

if I changed the out-of-row exception would be able to increase the number of characters; i.e

EXEC sp_tableoption 'demo.OneColVarcharMaxOut', 'large value types out of row', 1;

--------------------------------------------

Laughing in the face of contention...

arrjay Hall of Fame Points: 3455 More actions · Answer 3

Hi there - I am at a loose end with this one!

I am inserting a value which contains approx. 2000 characters and it fails however the current datalength for that row is 392. I have tried reducing the value to 6 characters and the insert completes successfully, but when I up it to 7 it fails.

The total data length defiantly does not add up to 8060. I have scanned each value separately and there's no spurious characters either.

I am also inserting a similar values for hundreds of thousand of rows and nothing and they complete, but for some reason this record keeps failing.

Has anyone come across this or something similar that you have a fix for.

Thanks!

--------------------------------------------

Laughing in the face of contention...

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 4

The "data length" of a row is more complex than simply counting the data characters. Columns have a minimum length, and many of the columns in your table have a minimum length which is excessive for the data which you are storing. For instance, you might use the integer values 1, 2, 3 to represent gender. You could store these values in an INT column which is 4 bytes - which you have. You would be better off storing them in a TINYINT column of 1 byte.

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

arrjay Hall of Fame Points: 3455 More actions · Answer 5

I appreciate what you're saying but there seems to be no discrepancy with the record that is failing versus the records that update successfully.

Literally, I am updating thousands of rows with similar values and similar data lengths. I can not see anything wrong with this record. They all have the same field criteria.

Is there anything else I can check.?

--------------------------------------------

Laughing in the face of contention...

ScottPletcher SSC Guru Points: 100262 More actions · Answer 6

While this will slow down processing of the value(s) somewhat, change the datatype of one (or more) of the longest columns -- [UserName_vc], [OfficeEmailAddress_vc], [HomeEmailAddress_vc] -- from nvarchar(320) to nvarchar(max). That will allow them to be stored off-row if necessary to reduce the row size to under the legal limit.

SQL DBA,SQL Server MVP(07, 08, 09) "Money can't buy you happiness." Maybe so, but it can make your unhappiness a LOT more comfortable!

arrjay Hall of Fame Points: 3455 More actions · Answer 7

arrjay

Hall of Fame

Points: 3455

September 29, 2014 at 12:46 pm

#1748908

Thanks for this, it works!!

Do you know the technical reason behind it working with MAX and not 320? Thanks again, much appreciated.

--------------------------------------------

Laughing in the face of contention...

ScottPletcher SSC Guru Points: 100262 More actions · Answer 8

Yes. Honestly, it's because that's just the way SQL works.

If a row doesn't fit, SQL will look for any (MAX) columns that are currently being stored in the row. If there are, if moving those out of row (off page) will allow the page to be written, it does so. SQL doesn't do this for nvarchar(<nnnn>), I think because this is no logic already built into SQL to handle moving a known-length column off page.

Btw, I forgot to mention before, naturally you want to move the least-used column(s) off page first. For example, say the first column is used in 90% of queries, the second in 20% and the third in 10%. You'd start with setting the second and third columns to (MAX), trying to leave the first column as nvarchar(320) since it is read so often.

SQL DBA,SQL Server MVP(07, 08, 09) "Money can't buy you happiness." Maybe so, but it can make your unhappiness a LOT more comfortable!

Sean Lange SSC Guru Points: 286562 More actions · Answer 9

I know that this is not neccesarily your design but starting all your table names with tbl_ and storing the datatype used in the column name is a seriously poor design choice. What happens when the datatype needs to change? Either your column name is no longer correct or you have to update all the code that references it. YUCK!!!

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

Koen Verbeeck SSC Guru Points: 259015 More actions · Answer 10

Sean Lange (9/29/2014)

I know that this is not neccesarily your design but starting all your table names with tbl_ and storing the datatype used in the column name is a seriously poor design choice. What happens when the datatype needs to change? Either your column name is no longer correct or you have to update all the code that references it. YUCK!!!

+1 This will only bite you in the end

Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP

arrjay Hall of Fame Points: 3455 More actions · Answer 11

arrjay

Hall of Fame

Points: 3455

September 29, 2014 at 3:07 pm

#1748948

I agree. The database isn't designed with best practice in mind but we have plans in place to rename the objects.

Thanks for all the feedback guys. Much appreciated.

--------------------------------------------

Laughing in the face of contention...

Sean Lange SSC Guru Points: 286562 More actions · Answer 12

Koen Verbeeck (9/29/2014)

Sean Lange (9/29/2014)

I know that this is not neccesarily your design but starting all your table names with tbl_ and storing the datatype used in the column name is a seriously poor design choice. What happens when the datatype needs to change? Either your column name is no longer correct or you have to update all the code that references it. YUCK!!!

+1 This will only bite you in the end

hehe "bite you in the end"...I see what you did there double entrende.

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

arrjay Hall of Fame Points: 3455 More actions · Answer 13

arrjay

Hall of Fame

Points: 3455

September 30, 2014 at 3:38 am

#1749079

Apologise to keep this going but I was wondering why SQL qould not more the MAX field (the field that i'm updating) onto a separate page? Thanks All!!

--------------------------------------------

Laughing in the face of contention...

Gazareth One Orange Chip Points: 27737 More actions · Answer 14

arrjay (9/30/2014)

Apologise to keep this going but I was wondering why SQL qould not more the MAX field (the field that i'm updating) onto a separate page? Thanks All!!

I'm wondering the same myself! Unless my arithmetic is out I calculated the maximum size of the row as just under 6,000 bytes excluding the MAX field. I though SQL would automatically move the field out if required, perhaps it isn't doing that.

Have you had chance to test changing that table option?

arrjay Hall of Fame Points: 3455 More actions · Answer 15

arrjay

Hall of Fame

Points: 3455

September 30, 2014 at 5:59 am

#1749102

V. strange. I have tried changing the field lengths from 320 to MAX (is this what you mean by changing the table options?), this fixed the problem but I'm still unsure why the original would not move onto a separate page. Thanks.

--------------------------------------------

Laughing in the face of contention...