在典型的日期范围之间，您希望有几行?您通常一次要看一个月吗?

How many rows do you expect to have between a typical date range? Are you typically looking at a month at a time?

我将从在[Date]列上的索引开始.如果对于典型查询而言，结果行数很少，则无需在索引中添加[OrderId]列.

I would start out with an index over the [Date] column. If, for a typical query, your resulting row count is small you shouldn't need to add the [OrderId] column to your index.

另一方面，如果通常一个月中有很多行，则可以将[OrderId]列添加到索引中，尽管由于它被视为布尔值，所以可能不会给您带来很多好处.它取决于NULL和NOT NULL的行数.如果给定月份有很多行，但是只有少数几个行具有有效的[OrderId]，那么索引可能会提高性能.

On the other hand, if you have a large amount of rows in a typical month, then you can add the [OrderId] column to the index, though since it's being treated as a boolean value, it may not buy you much. It depends on how many rows are NULL vs NOT NULL. If you have you a lot of rows for a given month, but only a few have a valid [OrderId] then the index would probably improve performance.

阅读此相关问题中可接受的答案，并确定是否值得在附加列上建立索引:

Read the accepted answer in this related question and determine if it's worth indexing over the additional column:

我应该在SQL Server中索引位字段吗? /a>

Should I index a bit field in SQL Server?

当然，还要测试使用和不使用索引的索引和生成的计划.

And of course, test the indexes and the plans generated with and without the index.

更新:其他一些答案指定了更具侵略性的索引，这将提高此查询的性能，但可能会对表上的其他操作产生不利影响.例如，建议的覆盖索引将允许SQL Server处理该查询，而对实际表的影响很小，但是当其他查询写入实际表时，可能会引起问题(因为SQL Server将需要同时更新表和覆盖索引)这种情况).

Update: Some of the other answers specify a more aggressive index, which should improve performance of this query, but may adversely affect other operations on the table. For example, the covering index suggested will allow SQL Server to process this query with little impact to the actual table, but may cause problems when other queries write to the actual table (since SQL Server will need to update both the table and covering index in that case).

由于这是一个报表查询，因此我将尽可能地对其进行优化.如果此查询运行时间过长，导致其他更关键的查询运行缓慢或超时，我将仅对该查询进行充分优化以减少其对其他查询的影响.

Because this is a reporting query, I would optimize it as little as possible. If this query is running long, causing other, more critical, queries to run slowly or timeout, I would only optimize this query enough to reduce it's affect on those other queries.

但是，如果您希望该表继续增长，我会考虑采用单独的报告模式并定期从该表中提取数据.

Though, if you expect this table to continue growing I would consider a separate reporting schema and periodically extract data from this table.