I am dealing with database tables with tens of millions of rows (with the potential to converge with hundreds of millions over time), and I am looking at implementing database partitioning to try to maintain performance stability as the number of rows increases. This is what I would like to do:
Say I have a table in which animals are stored. One of the fields is AnimalType (i.e. Bird / Fish / Cat / Dog). I would like each AnimalType to be a separate section, because 99% of queries are related to only one type of AnimalType, and the table has approximately the same number of AnimalTypes (1000 fish, 1000 birds, 1000 dogs), so this means that the sections should be nice and evenly distributed. However, there are many types of animals, and I don’t want to go and manually create hundreds of sections for each AnimalType, and then every time a new AnimalType is introduced, to create a new section.
Therefore, what I would like is to somehow say SQL Server for AnimalType-based partitioning. If a section exists for AnimalType, use this section; otherwise, SQL Server will automatically create a new section.
It sounds simple enough, but I can't find a way to do it. Is it possible?
Alternatively , what other methods allow you to quickly and quickly maintain table access speed? I would like to avoid everything that only manually moves the material to other tables, for example, moving old records to the history style table, since there is a possibility that the queries will require data from a complete set of data, and therefore this will not help. I already have some basic indexes that help greatly.