pages

  • HOW MANY datapages in sql ?

  • 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

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • 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/

  • 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) A socialist is someone who will give you the shirt off *someone else's* back.

  • 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 ?

    -----------------------------------------------------------------------------
    संकेत कोकणे

  • 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...

    _____________________________________________
    "The only true wisdom is in knowing you know nothing"
    "O skol'ko nam otkrytiy chudnyh prevnosit microsofta duh!":-D
    (So many miracle inventions provided by MS to us...)

    How to post your question to get the best and quick help[/url]

  • 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.

  • 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?

    -----------------------------------------------------------------------------
    संकेत कोकणे

  • 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.

  • 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 ?

    -----------------------------------------------------------------------------
    संकेत कोकणे

  • 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.

  • 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

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • thanks for clearing out 🙂

    any other good link/eBook related to architecture other than MSDN ?

    -----------------------------------------------------------------------------
    संकेत कोकणे

  • 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

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • GilaMonster (11/30/2012)


    SQL Server 2008 Internals by Kalen Delaney

    Paul 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