asked
page size in sql
Hi all, can anyone clarify my doubt, thanks in advance. why the page size in sql is 8 kb only,Is there any specific reason for that?
The antithesis of the DBA stock answer 'it depends' but with the same vague tone - **it just is**!
The page size is always 8192 bytes, out of which 8060 are available to us lesser mortals and the rest is used by the engine to store data such as volatile rowid, 2 byte overhead per each varchar column etc. This means that create table dbo.dont_do_it(col1 varchar(100), col2 varchar(8000)); is silly but table is created with warning and works until one tries to modify data such that one of the records is wider than 8060 bytes. The good news is that at least the stupidity like this is not allowed with create table dbo.cant_do_it(col1 char(100), col2 char(8000)); because the engine will not allow such table to be created. Unfortunately, SQL Server 2008 actually allows the insanity described above because it has newly added overflow pages (not to be mixed up with LOB pages used to store CLOBs such as varchar(max) and BLOBs such as varbinary(max)).
Yes & no. You can create a table that defines a row that goes beyond 8060 and you'll get warnings when createing the table. As long as data inserted stays below 8060 you won't have any trouble, but if you insert data beyone 8060, you'll get an error. Physically, SQL Server cannot store beyond 8060. So while you can define a structure beyond that, it is a hard stop.
I'm sure it has something to do with memory architecture. 8 pages of 8k each to make up a 64k extent... I agree with your answer.
...unless you are using the new (MAX) datatypes, and overflow-pages, of course... :)
I found this from MichaelJasson : Why This is 8k? 1 MB = 128 pages. Now 1 MB = 1024 so we needed something that should divide this completely and that should now be cumbersome as well. You know that one row, if this doesn't fit into one page, is written on another page. One row can't be written on two pages. Keeping this in mind 8K was choosen as 4K would be too short and every row would have to be < 4K. [from
http://www.sqlservercentral.com/Forums/Topic747798-146-1.aspx ]
I have this vague memory that the maximum rowsize wasn't always just shy of 8k...
Actually the more I think of this, the more interesting it becomes since I used to think that if you needed to add a lot of text, say, over 100 characters wide, then you should use a text application like Word. A database just wasn't the place for lots of text! Databases were primarily thought of as tools for numbers and a little text (think GL and totals). So I can hear a developer from long ago thinking that we would never need that much space anyway. I also ran across this comment from Rick Strahl from 2004 too, who is one brilliant guy, I think: This is yet another instance where I'm surprised by some of the inflexibility that a SQL Backend provides. This limit seems pretty superficial based on some engine behavior that probably dates back to the Sybase days... [
http://www.west-wind.com/weblog/posts/207.aspx]
The simple answer is: History. 8K sucks. It makes no sense. Bigger would be better. Unfortunately making the change would be an expensive opportunity cost for Microsoft and it would be disruptive and probably unpopular with customers when they upgraded. So there is little incentive to change it.
Can you imagine all the databases that would contain one wide table though? :)
