SQL Server Automatic Partitioning of Large Database Tables

Question

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 Server 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.

Answer 1

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.