SQL Server Automatic Partitioning of Large Database Tables

I am dealing with database tables with tens of millions of rows (with the potential to go to the hundreds of millions over time), and am looking at implementing database partitioning to try to keep performance stable as the row count increases. This is what I'd like to do:

Say I have a table that stores animals. One of the fields is the AnimalType (i.e. Bird/Fish/Cat/Dog). I'd like each AnimalType to be a separate partition, because 99% of queries only ever relate to one AnimalType & there is roughly an equal amount of AnimalTypes in the table (i.e. 1000 fish, 1000 birds, 1000 dogs) so it means the partitions should be nice and evenly spread. However, there are a lot of animal types, and I don't want to go and manually create the hundreds of partitions for each AnimalType, and then every time a new AnimalType is entered have to create a new partition.

Therefore, what I would like, is some way to tell SQL Se开发者_JAVA百科rver to partition based on AnimalType. If there is a partition already for the AnimalType, use that partition, otherwise SQL Server will automatically create a new partition.

It sounds simple enough, but I cannot seem to find a way to do this. Is it possible?

Alternatively, what are some other methods to keep table access speeds nice and fast? I'd like to avoid anything that is just manually moving stuff into more tables, such as moving older records into a History style table, as there is the potential for queries to need data from the full data set and hence this won't actually help. I already have some basic Indexes which help significantly.

---

Partitioning is a solution for storage problems, ie. determine on what filegroup data is located based on some field value. On its own, it gives no real performance benefit, in fact it actually slows down queries most times because new partition location operators need to be added. The only way to enforce queries to consider only one partition is the $PARTITION syntax, and this cannot be used in real world applications scenarios. Queries that opt to look up only one partition do so solely based on the index ranges, and would scan exactly the same number of records with or without partitioning.

the only time when partitioning has a performance benefit is for administration activities like partition switch in and switch out from a table or bulk import operations.

Performance benefits can come only from proper indexes and carefully designed queries.

---

This is a very old question so some updated information is probably in order. First of all, to answer the original question, yes, dynamic partitioning is possible by way of scheduled jobs:

Marlon Ribunal article, see section on dynamic paritioning

How to automate Table Partitioning in SQL Server by Jignesh Raiyani at SQL Shack

I also want to add that partitions can improve query performance in certain scenarios. For me, with clustered columnstore indexes, I was able to leverage partitioning to boost segment elimination. See the SO post about it from 2018:

Partitioning columnstore tables for performance

One must not enter into a partitioning scheme lightly. It is extra complexity and overhead that should be proven to enhance your design before implementing.