November 29, 2012 at 5:35 am
HOW MANY datapages in sql ?
November 29, 2012 at 5:52 am
Huh?
What are you asking?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
November 29, 2012 at 8:42 am
Everybody knows this is 42 😛
_______________________________________________________________
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/
November 29, 2012 at 3:14 pm
Depends on how many bytes in table, and how long a row is.
A SQL page is 8K, with 8060 total bytes available for data. Each row resides on a single page (excluding special column types).
So if every row takes, say, 4100 bytes, each would take a whole page, with the rest of that page unuseable except to expand the existing row.
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
November 30, 2012 at 4:34 am
ScottPletcher (11/29/2012)
Depends on how many bytes in table, and how long a row is.A SQL page is 8K, with 8060 total bytes available for data. Each row resides on a single page (excluding special column types).
you sounds like only one row per page ,right ?
http://msdn.microsoft.com/en-us/library/ms190969%28v=sql.105%29.aspx
So if every row takes, say, 4100 bytes, each would take a whole page, with the rest of that page unuseable except to expand the existing row.
I think remaining space can be used by other rows .
Please can you share any link related it ?
-----------------------------------------------------------------------------
संकेत कोकणे
November 30, 2012 at 4:45 am
ramyours2003 (11/29/2012)
HOW MANY datapages in sql ?
Sean Lange (11/29/2012)
Everybody knows this is 42 😛
No, you're wrong, they changed it! It's 321 since Thanksgiving!
:w00t:
They may cut it back to 238, there is still discussion in a senate. Republicans want it to be cut down! Let's pray for Obama 😀
That would be a great question for Question Of The Day...
November 30, 2012 at 4:45 am
sanket kokane (11/30/2012)
ScottPletcher (11/29/2012)
Depends on how many bytes in table, and how long a row is.A SQL page is 8K, with 8060 total bytes available for data. Each row resides on a single page (excluding special column types).
you sounds like only one row per page ,right ?
http://msdn.microsoft.com/en-us/library/ms190969%28v=sql.105%29.aspx
So if every row takes, say, 4100 bytes, each would take a whole page, with the rest of that page unuseable except to expand the existing row.
I think remaining space can be used by other rows .
Please can you share any link related it ?
As long as the second row is 3900 bytes or less.
The example was that if every row takes 4100 bytes, so that would mean that only 1 row can be stored on a page, the remaining 3900 bytes could only ever be used by the row occuping the page.
November 30, 2012 at 4:58 am
anthony.green (11/30/2012)
sanket kokane (11/30/2012)
ScottPletcher (11/29/2012)
Depends on how many bytes in table, and how long a row is.A SQL page is 8K, with 8060 total bytes available for data. Each row resides on a single page (excluding special column types).
you sounds like only one row per page ,right ?
http://msdn.microsoft.com/en-us/library/ms190969%28v=sql.105%29.aspx
So if every row takes, say, 4100 bytes, each would take a whole page, with the rest of that page unuseable except to expand the existing row.
I think remaining space can be used by other rows .
Please can you share any link related it ?
As long as the second row is 3900 bytes or less.
The example was that if every row takes 4100 bytes, so that would mean that only 1 row can be stored on a page, the remaining 3900 bytes could only ever be used by the row occuping the page.
what if second row size > 3900 int this case .
Row overflow can take place or not ?
or it will go directly to the another new page?
-----------------------------------------------------------------------------
संकेत कोकणे
November 30, 2012 at 5:02 am
No as it has to be the row which overflows, not the page. The row is not overflowing as its 4100 bytes, for row overflow to occur the whole row must be > 8060 bytes.
November 30, 2012 at 5:12 am
anthony.green (11/30/2012)
No as it has to be the row which overflows, not the page. The row is not overflowing as its 4100 bytes, for row overflow to occur the whole row must be > 8060 bytes.
sorry if I not ask my doubt clearly
my first row size = 4100
(Remaining page size = 3960)
my second row size = 5000
(extra byte 1040)
so cant my second row stored on same page as of first row ? and overflowing my second row remaining bytes to new page ?
-----------------------------------------------------------------------------
संकेत कोकणे
November 30, 2012 at 5:18 am
No as the row is not bigger than 8060 bytes to use row overflow
Rows cannot span pages,
This restriction is relaxed for tables that contain varchar, nvarchar, varbinary, or sql_variant columns. When the total row size of all fixed and variable columns in a table exceeds the 8,060 byte limitation, SQL Server dynamically moves one or more variable length columns to pages in the ROW_OVERFLOW_DATA allocation unit, starting with the column with the largest width. This is done whenever an insert or update operation increases the total size of the row beyond the 8060 byte limit.
November 30, 2012 at 5:44 am
sanket kokane (11/30/2012)
ScottPletcher (11/29/2012)
Depends on how many bytes in table, and how long a row is.A SQL page is 8K, with 8060 total bytes available for data. Each row resides on a single page (excluding special column types).
you sounds like only one row per page ,right ?
No, he said each row resides on a single page, not each page contains only one row.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
November 30, 2012 at 5:58 am
thanks for clearing out 🙂
any other good link/eBook related to architecture other than MSDN ?
-----------------------------------------------------------------------------
संकेत कोकणे
November 30, 2012 at 6:45 am
SQL Server 2008 Internals by Kalen Delaney
Paul Randal's blog.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
December 2, 2012 at 10:25 pm
GilaMonster (11/30/2012)
SQL Server 2008 Internals by Kalen DelaneyPaul Randal's blog.
Thanks Gail.
-----------------------------------------------------------------------------
संकेत कोकणे
Viewing 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply