CODEWITHSUNDEEP
sql-server
ThomasAJ
ThomasAJ

Reputation: 721

What exactly does 'Bytes per row' mean in SQL Server max. capacity specifications

See Max Capacity Specs

So next to 'Bytes per row' it is 8060 but then later next to 'Characters per ntext or nvarchar(max) column' there is 2^31-1.

Max columns is 30,000.

I do not understand how there can only be 8060 bytes in 1 row when eg the other 2 numbers (2^31-1 and 30,000) are so huge.

Well I'll have a go at what I think may be the explanation. Sure I can define 30,000 nvarchar columns but say 29,999 must be null if one of them contains a string of length roughly 7500.

And if I want to have some data in the other 29,999 columns I need to use row-overflow storage which would cripple the possibility of fast searches.

(Pleeease let's not get into a discussion of the type "why would you need 30,000 columns, you need to normalize etc". My numbers are just to illustrate my possible complete misunderstanding of the 8,060 Bytes per row limit.)

Upvotes: 3

Views: 2620

Answers (1)

IVNSTN
IVNSTN

Reputation: 9318

Rows are stored in Pages, page size is 8KB after deduction of space required to store system information you have 8060 bytes for data. Row size restriction means row cannot be split over several pages. Text and obsolete Image types are stored in pages of special type outside of "normal" 8KB pages. Long strings that overflow regular page's size are moved to pages of another special type row overflow storage as you mentioned. Original row which is still stored in "normal" data page retains only keys to make it possible to access those outer pages with long data.

Check out this calculation algorithm for table size estimation:
https://msdn.microsoft.com/en-us/library/ms178085.aspx

Upvotes: 1

Related Questions