Partitioning Limits in SQL Server 2019

  • MilesC

    Ten Centuries

    Points: 1078

    Comments posted to this topic are about the item Partitioning Limits in SQL Server 2019

  • BTylerWhite

    Right there with Babe

    Points: 770

    Great question to start off the week, Miles!

  • Jeff Moden

    SSC Guru

    Points: 996046

    It's a little spooky that about half the people that have answered so far have gotten this one wrong.  Not because it's partitioning but because people won't take the time to look it up or don't know how to look it up.

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column.
    "If you think its expensive to hire a professional to do the job, wait until you hire an amateur."--Red Adair
    "Change is inevitable... change for the better is not."

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • MilesC

    Ten Centuries

    Points: 1078

    Jeff Moden wrote:

    It's a little spooky that about half the people that have answered so far have gotten this one wrong.  Not because it's partitioning but because people won't take the time to look it up or don't know how to look it up.

    LOL, I was thinking the same thing.

  • JeremyE

    SSCoach

    Points: 15398

    The explanation is not quite accurate since 15K partitions were supported in SQL Server 2008 SP2 and SQL Server 2008 R2 SP1.

    Explanation

    The partitioning limit for SQL Server 2019 for tables and indexes is 15,000.  This is true for all versions starting with SQL Server 2012.  Before SQL Server 2012 the partitioning limit was 1,000.

    http://download.microsoft.com/download/b/e/1/be1aabb3-6ed8-4c3c-af91-448ab733b1af/support_for_15000_partitions.docx

     

  • Jeff Moden

    SSC Guru

    Points: 996046

    Understood but, unless specifically stated (and I found this out the hard way a long time ago), the QOTD questions are based on the current version of SQL and do not include versions that are no longer supported.

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column.
    "If you think its expensive to hire a professional to do the job, wait until you hire an amateur."--Red Adair
    "Change is inevitable... change for the better is not."

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • Jacob Wilkins

    One Orange Chip

    Points: 27880

    Seems like the explanation paraphrase just left out a key phrase from the official documentation:

    SQL Server 2019 (15.x) supports up to 15,000 partitions by default. In versions earlier than SQL Server 2012 (11.x), the number of partitions was limited to 1,000 by default. On x86-based systems, creating a table or index with more than 1,000 partitions is possible, but is not supported.

    (my emphasis)

    Cheers!

  • Jeff Moden

    SSC Guru

    Points: 996046

    I'm not sure why such an omission matters.  The title of the question specifically cited "Partitioning Limits in SQL Server 2019" and the link is to the official documentaion.

     

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column.
    "If you think its expensive to hire a professional to do the job, wait until you hire an amateur."--Red Adair
    "Change is inevitable... change for the better is not."

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • Jacob Wilkins

    One Orange Chip

    Points: 27880

    I know. I'm not saying it makes the question or answer incorrect.

    I was just providing a possible reason for the explanation's being inaccurate in the way JeremyE pointed out.

    That the explanation is inaccurate is clear; whether it matters to anyone is a different question entirely, and one I'm not qualified to answer 🙂

  • Jeff Moden

    SSC Guru

    Points: 996046

    Jacob Wilkins wrote:

    I know. I'm not saying it makes the question or answer incorrect.

    I was just providing a possible reason for the explanation's being inaccurate in the way JeremyE pointed out.

    That the explanation is inaccurate is clear; whether it matters to anyone is a different question entirely, and one I'm not qualified to answer 🙂

    Ah... got it.  Thanks, Jacob and Jeremy.

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column.
    "If you think its expensive to hire a professional to do the job, wait until you hire an amateur."--Red Adair
    "Change is inevitable... change for the better is not."

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • Stewart "Arturius" Campbell

    SSC Guru

    Points: 71680

    remeber doing a presentation on this topic at a SQL user Group meeting in 2009, when only 1000 partitions were supported.

    a lot has changed since then, it seems...

    Nice question, thanksMiles

    ____________________________________________
    Space, the final frontier? not any more...
    All limits henceforth are self-imposed.
    “libera tute vulgaris ex”

Viewing 11 posts - 1 through 11 (of 11 total)

You must be logged in to reply to this topic. Login to reply