It was suggested to partition the main table of my system to improve the performance of some queries.
The dev that made the suggestion suggested using the ano registry information to partition, because as the system has not been running for more than 10 years there would be few partitions (which second it would be ideal.)
But from what I understood of the partition's operation, it would be more useful to partition using the company / body identifier to which the registry belongs ( are approximately 300 different ). I thought of doing so because in most queries this key is used. But I do not know if I can or should do this.
What care, and what should I worry about partitioning a table in SQL Server ?
About the table, this is a very large table with more than one million records that has relationships at different levels with most of the other tables in the system.
UPDATE :
After reading the links posted in the comments, I saw that it would be impractical in this situation to partition using the company / organ identifier (due to the amount of partitions generated) and that would not bring partition results per year since I do not use this parameter in queries.
Even though it does not apply to this specific situation, I'd be happy to get an enlightening answer on what should I be concerned about when creating a partitioned table?