Vertical Partitioning Is Almost Never the Answer. Here’s Why.
10 Comments
You’re looking at a wide table with 100-200 columns.
Years ago, it started as a “normal” table with maybe 10-20, but over the years, people kept gradually adding one column after another. Now, this behemoth is causing you problems because:
- The table’s size on disk is huge
- Queries are slow, especially when they do table scans
- People are still asking for more columns, and you feel guilty saying yes
You’ve started to think about vertical partitioning: splitting the table up into one table with the commonly used columns, and another table with the rarely used columns. You figure you’ll only join to the rarely-used table when you need data from it.
However, you’re dreading the amount of code changes required. It’s gonna affect everywhere in your application that does inserts, updates, deletes, and selects. And at the end of the day, are you even sure it’s going to help performance? You don’t have the time to build a test version of the entire application with the new table structures.
Stop. Do index tuning first instead. A nonclustered index is literally a vertically partitioned copy of the table. You might even build an index with the commonly queried columns of the table – and find out that it’s being used for scans. Those scans will be faster than full table scans because the commonly-queried copy is simply physically smaller! The less columns you use for it, the faster those scans will go. Don’t do table changes that require app code changes before you’ve attended my Fundamentals of Index Tuning and Mastering Index Tuning classes.
Next, consider columnstore. SQL Server’s columnstore indexes store every column independently so that you can query just the columns you want. This can have spectacular payoffs for query performance, although not for all workloads or datatypes. Start by taking the free ColumnScore.com quiz to get a rough idea of whether your table is a good fit for columnstore indexes, and if so, check out my Fundamentals of Columnstore class.
Index changes don’t require code changes. Start there, and only after you’ve exhausted those easy wins, should you consider vertical partitioning.
Some of my posts are long and involve detailed code examples. Not this one, though, because the answer’s pretty straightforward. Vertical partitioning isn’t just hard work for you, it’s hard work for everyone around you. Do the easy stuff first, then do the stuff that might seem hard for you, but doesn’t require work from other people. Assigning yourself AND other people hard work should be your very last resort.
If your company is hiring, leave a comment. The rules:
#4: SSAS, SSIS, and SSRS – it’s wild to think back to 2000-2005 when Crystal Reports dominated the data visualization business. Everybody used it everywhere. It was such a de facto standard amongst small to midsize businesses that Microsoft had to bundle SQL Server Reporting Services for free with your purchase of SQL Server just to be competitive. Similarly, they had to give away Integration Services for free to compete with tools like Informatica. For about a decade, Microsoft kept pouring improvements into these products, and if you bet your career on ’em, you did pretty well for a long period of time. Today, they’re falling out of popularity, but I’d be remiss if I didn’t include them in this list because they did so well for so long.
![[Video] Office Hours and an AI-Generated Sweatshirt](https://i0.wp.com/www.brentozar.com/wp-content/uploads/2025/01/video-office-hours-and-an-ai-generated-sweatshirt-1737635480.png?fit=600%2C315&ssl=1)
![[Video] Office Hours in My Vegas Backyard](https://i0.wp.com/www.brentozar.com/wp-content/uploads/2025/01/video-office-hours-in-my-vegas-backyard-1737634325.png?fit=600%2C315&ssl=1)
