Partitioning Limits in SQL Server 2019

Question

Post reply

Partitioning Limits in SQL Server 2019

MilesC

Ten Centuries

Points: 1078
More actions
March 23, 2020 at 12:00 am

#3732066

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

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

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

BTylerWhite SSCommitted Points: 1569 More actions · Answer 1

BTylerWhite

SSCommitted

Points: 1569

March 23, 2020 at 12:46 pm

#3736207

Great question to start off the week, Miles!

Jeff Moden SSC Guru Points: 1004290 More actions · Answer 2

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.

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 More actions · Answer 3

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: 16194 More actions · Answer 4

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: 1004290 More actions · Answer 5

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.

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: 27976 More actions · Answer 6

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!

This reply was modified 4 years, 7 months ago by Jacob Wilkins.
This reply was modified 4 years, 7 months ago by Jacob Wilkins.

Jeff Moden SSC Guru Points: 1004290 More actions · Answer 7

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.

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: 27976 More actions · Answer 8

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: 1004290 More actions · Answer 9

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.

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: 72618 More actions · Answer 10

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”