按照年份分区，分别命名为Data_2011，Data_2010等 [英] Partitioning by Year vs. separate tables named Data_2011, Data_2010, etc
问题描述
我们正在设计一个大容量的SQL Server应用程序，涉及对指定年份内限制的数据进行处理和报告。
按照年份使用分区。另一个建议是以编程方式创建单独的物理表，其中名称的后缀是年份，并且在多年来需要报告时，提供一个视图，即联盟的物理表。
我的直觉告诉我，这种情况是分区设计要处理的。使用其他方法有什么好处吗？
从内部角度看，方法基本相同。
在幕后，当您创建基于日期的分区时，SQL引擎为每个分区创建单独的物理表，然后基本上是一个 UNION 当您查询表本身时。
如果您在查询中使用与您的分区字段（ DateField 让我们说），那么引擎可以直接转到数据所需的分区。如果没有，那么它根据需要搜索逻辑表中的每个物理表，以完成查询。
如果您的查询涉及日期过滤器（这听起来像他们会从你的问题），那么我可以想到你的自定义方法没有优势。
本质上，你需要做的选择是你想成为负责所有涉及分区的逻辑和角色案例，还是信任微软开发人员几十年来为此做的事情？
为了我自己的目的，如果有一个内置的框架，我想做的事情，我总是尝试使用它。 roll-your-own解决方案总是更快，更稳定，更容易出错。
We are designing a high volume SQL Server application that involves processing and reporting on data that is restricted within a specified year.
Using Partitioning by year comes to mind.
Another suggestion is to programmatically create separate physical table where the suffix of the name is the year and, when reporting is needed across years, to provide a view which is the union of the physical tables.
My gut tells me that this situation is what partitioning is design to handle. Are there any advantages to using the other approach?
From an internals perspective, the methods are essentially the same.
Behind the scenes, when you create a date-based partition the SQL engine creates separate physical tables for each partition, then does what is basically a UNION when you query the table itself.
If you use a filter in your query on the partitioned table that corresponds to your partitioning field (DateField let's say), then the engine can go directly to the partition that you need for the data. If not, then it searches each physical table in the logical table as needed to complete the query.
If your queries will involve a date filter (which it sounds like they will from your question) then I can think of no advantage to your "custom" method.
Essentially, the choice you need to make is do you want to be responsible for all the logic and corner cases involved in partitioning, or trust the developers at Microsoft who have been doing this for decades to do it for you?
For my own purposes, if there is a built-in framework for something I want to do then I always try to use it. It is invariably faster, more stable, and less error-prone than a "roll-your-own" solution.
这篇关于按照年份分区，分别命名为Data_2011，Data_2010等的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
