When I work with clients, nearly every single one has this burning question about partitioning.
“We’ve got this huge table, should we partition it?”
“Do you need to insert or delete data in big chunks?”
“No, it’s all transactional.”
“Do you have last page contention problems?”
“No, but won’t it help performance?”
“No, not unless you’re using clustered column store.”
“…”
Read on to unpack Erik’s argument. I do wish that there were more good cases for partitioning in SQL Server, but they’re almost all in the analytics space—which is part of why partitioning is a lot more useful in Azure Synapse Analytics dedicated SQL pools.