英文:
Index SELECT MAX with WHERE
问题
我知道这个问题已经被问过很多次，但是我找不到一个明确的答案，特别是当有一个WHERE子句时，这对我来说非常重要，因为我将有一个包含数百万条记录的表。
如果我有以下查询：
SELECT MAX(SomeINTvalue)
FROM tbTest
WHERE Filter1 = SomeVARCHAR32value
最好创建哪个索引？
另外，如果我使用以下查询：
SELECT TOP 1
SomeINTvalue
FROM tbTest
WHERE Filter1 = SomeVARCHAR32value
ORDER BY SomeINTvalue DESC
这样做会更好吗？在这种情况下应该使用哪个索引？
我研究了很多帖子，但是我找不到一个考虑WHERE子句（和这些特定数据类型）的答案。
英文:
I know that this question was asked many times, but I could not find an explicit answer when there is a WHERE clause as well, and this is very important for me since I will have table with Millions of records.
If I have:
SELECT MAX (SomeINTvalue)
FROM tbTest
WHERE Filter1 = SomeVARCHAR32value
What index is best to create?
Also, if I use:
SELECT TOP 1
SomeINTvalue
FROM tbTest
WHERE Filter1 = SomeVARCHAR32value
ORDER BY SomeINTvalue DESC
will it be better and what index to use in this case?
I researched many posts but I could not find an answer that considers WHERE clause (and these specific data types)
答案1
得分: 1
你需要以下索引：
CREATE NONCLUSTERED INDEX tbTest (SomeVARCHAR32value, SomeINTvalue DESC)
根据你运行的其他查询，你可能还想添加INCLUDE列。
如果你已经创建了这个索引，那么两个查询可能最终会得到相同的执行计划。
英文:
You need the following index:
CREATE NONCLUSTERED INDEX tbTest (SomeVARCHAR32value, SomeINTvalue DESC)
You may want to add INCLUDE columns also, depending on what other queries you are running.
Both queries will probably end up with the same plan if you have that index in place.
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论