在 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屋！