SQL Server 2016 Standart Edition supports table partitioning. Yeah I was amazed and pleased like everybody this feature come in Standart edition. It’s available after you install SQL Server 2016 SP1 package.
Table partitioning is frankly not a complete solution to get more query performance in SQL Server but it’s almost indispensable helper if considering we have lots of big data with size of 500GB, 1 TB, 5 TB etc. So without table partitioning it’s really a big issue to manage disk allocation, index maintenance, backup times, data replication in SQL Server unless you have additional 3rd solutions.
In this post I’m gonna show table partitioning in an existing database with SQL Server Management Studio. Table column with datetime or integer data type is appropriate for partitioning column.
Well, a table has large size in my database and I decided to part it. First, I added some filegroups and files for DB:
Files assigned to new filegroups in order:
And here is which files created in disk. As I mentioned before files can be moved to another disk drives. You can move files are rarely using to slow disks in your production, for instance.
I select the column with datetime type so I can separate data for 4 months, 6 months, weekly or yearly.
Also in this section there is storage-align index option with partitioning column. Storage-aling index means you can optimize particular index in the partition, leads to easily managing partition switching and generally provides better query performance.
Next, in Map Partitions section I set boundary as quarterly then wizard calculated rowcount and size:
Benefits of table partitioning is more like depends on your production and query structure. While some productions get pretty well results with partitioning but some not. So you should analyze and make more test what optimation you need in your SQL Server production and part the data by yourself.