SQL Server Performance Forum – Threads Archive

about limit of 8KB per row

In SQL2005 with the option ROW_OVERFLOW_DATA the restriction of 8KB by row relaxed for tables that contain varchar, nvarchar, varbinary, sql_variant, or CLR user-defined type columns. In this case SQL Server use as best the page size, however I wonder what happen when this option is turned off. For example, If a row size is of 3 KB and I have ROW_OVERFLOW_DATA OFF how SQL Server store my rows? there are about 2 KB of wasted space by page??

Hm, interesting question. Haven’t got my hands on the 2005 admin books of "Inside SQL Server", but i would say, when the option is turned off the overflow area isn’t used anyway and thus the space is not wasted. But that’s just an educated guess, which I hope someone will confirm or rectify. —
Frank Kalis
Moderator
Microsoft SQL Server MVP
Webmaster:http://www.insidesql.de

OK Frank, thank you a lot. If you are rigth and the space is not wasted, I would like know how SQL Server use this 2 KB. I hope someone confirm or rectify what you think. I’ll keep an eye on it.

Well that’s how SQL 2000 worked – so if you have this new option switched off then yes, you will waste 2KB of space. This is why when you are designing tables with large column widths you should try and make the width divide nicely into 8060. www.skyscanner.net – flight search

http://www.artima.com/forums/flat.jsp?forum=152&thread=142615 a refernce on the topic too, HTH. Satya SKJ
Microsoft SQL Server MVP
Contributing Editor & Forums Moderator
http://www.SQL-Server-Performance.Com
This posting is provided AS IS with no rights for the sake of knowledge sharing.

Interesting. Shows me, that I have to order Kalen’s 2005er books. [<img src=’/community/emoticons/emotion-1.gif’ alt=’

‘ />] – Frank Kalis Moderator Microsoft SQL Server MVP Webmaster:<a target="_blank" href=http://www.insidesql.de>http://www.insidesql.de</a>

Please don’t forget this question. It’s important for all. Whenever any of you finds out anything, please notify us all.

I’ve bookmarked this thread and anyone who finds new informations should post them here. [<img src=’/community/emoticons/emotion-1.gif’ alt=’

‘ />] – Frank Kalis Moderator Microsoft SQL Server MVP Webmaster:<a target="_blank" href=http://www.insidesql.de>http://www.insidesql.de</a>

Its not directly related, but useful.
http://sqlblog.com/blogs/kalen_delaney/archive/2006/10/13/301.aspx Roji. P. Thomas
http://toponewithties.blogspot.com

]]>