在 SQL Server 中存储季度和年度的最佳方式? [英] Best way to store quarter and year in SQL Server?

问题描述

在数据库中存储季度和年度的最佳方式是什么?我有付款表，我需要指定季度/年，以便很容易知道付款是在哪个季度进行的.

What would be the best way to store Quarter and Year in database? I have payments table and I need to assign quarter/year so that it's easy to tell for which quarter the payment was made.

我在想:

a) 为每笔付款添加两个 int 列
b) 添加另一个表并添加最多 5 年的可能值，然后使用 ID 连接该表和付款一次.

a) adding two int columns to each payment
b) adding another table and add possible values up to 5 years ahead and use the ID to join that table with payments one.

还有哪些选择?也许有些更好或更容易维护.该数据库将与 C# 程序一起使用.

What are other options? Maybe some is better or/and easier to maintain. This database will be used with C# program.

推荐答案

如果您必须使用单独的年份和季度而不是日期(因为您似乎有特定的报告要求)，我会选择 tinyint 表示季度，smallint 表示年份，并将它们存储在 PAYMENT 表本身中.

If you have to use separate year and quarter instead of a date (since you seem to have specific reporting requirements), I would go for a tinyint for quarter and smallint for year and store them in the PAYMENT table itself.

我不会将它存储在不同的表中.这很糟糕，因为:

I would not store it in a different table. This is bad since:

• 您必须确保您制作了足够多的年/季度
• 您必须加入并使用外键

如果您将数据与记录一起存储，这将有助于读取性能.您的表可能很小，但始终牢记性能.

If you store the data with the record, it will help performance on reads. Your table could be small but it is always good to keep in mind performance.

假设你需要得到

特定季度的所有付款付款已超过具体金额和客户是特定客户

all payments in specific quarter where payment has been more than specific amount and customer is a particular customer

在这种情况下，您需要对所有项目使用覆盖索引，但仍然无济于事，因为您的查询是针对特定季度而非季度年度的.但是，将数据放在表上将有助于制定更轻量的执行计划.

In this case, you would need a covering index on all items and still does not help since your query is for specific quarter and not quarter year. Having the data on the table, however, will help with lighter execution plan.

这篇关于在 SQL Server 中存储季度和年度的最佳方式?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！