优化特定表的查询

Question

我有一个表，我正在寻找提高其性能的方法。下面是要执行的查询列表。

dbo.CustomersSmallOrders
(
    ID INT NOT NULL IDENTITY(1,1) PRIMARY KEY CLUSTERED,
    CustomerName VARCHAR(MAX) NOT NULL,
    OrderDate DATETIME NOT NULL,
    Quantity INT NOT NULL,
    Amount DECIMAL(15,2) NOT NULL,
    AvailableAmount DECIMAL(15,2) NOT NULL,
    Type TINYINT NOT NULL,
    Status TINYINT NOT NULL
)

为了提高表的性能，你能帮我重写下面的查询并向表中添加索引吗？

SELECT *
FROM CustomersSmallOrders
WHERE Left(CustomerName, 4) = 'Levis'
SELECT *
FROM CustomersSmallOrders
WHERE DateDiff(month, OrderDate, GetDate()) >= 30
SELECT *
FROM CustomersSmallOrders
WHERE Quantity * 2 < 3400
SELECT 
    SUM(CASE 
            WHEN Type = 2 AND STATUS IN (0, 1, 2) THEN Amount 
            ELSE 0 
        END)
    ,SUM(CASE 
             WHEN Type = 1 AND STATUS IN (0, 1) THEN Amount 
             ELSE 0 
         END)
    ,SUM(CASE 
             WHEN Type = 2 AND STATUS IN (0, 1) THEN Amount - AvailableAmount 
             ELSE 0 
         END)
FROM CustomersSmallOrders
WHERE STATUS IN (0, 1, 2)

Answer 1

查询改进：

正如Martin Smith所说，第一个查询可以转换为

SELECT TOP 0 * FROM CustomersSmallOrders

因为矛盾。

如果是

WHERE Left(CustomerName, 5) = 'Levis'

然后将条件更改为

WHERE CustomerName LIKE 'Levis%'

将保持查询的可搜索性并允许使用索引。

第二个查询可以通过更改条件并在OrderDate:上添加索引来改进

SELECT *
FROM CustomersSmallOrders
WHERE OrderDate <= DATEADD(Mounth, -30, GetDate()) 

第三个（添加数量索引）：

SELECT *
FROM CustomersSmallOrders
WHERE Quantity < 1700

第4个-在STATUS上添加一个索引，其中还包含Type、Amount和AvailableAmount（尽管我不明白他为什么要两次Amount。我认为这可能是一个错误，他只想要1列输出）：

SELECT 
SUM(CASE 
        WHEN Type = 2 THEN Amount 
        ELSE 0 
    END)
,SUM(CASE 
         WHEN Type = 1 AND STATUS IN (0, 1) THEN Amount 
         ELSE 0 
     END)
,SUM(CASE 
         WHEN Type = 2 AND STATUS IN (0, 1) THEN Amount - AvailableAmount 
         ELSE 0 
     END)
FROM CustomersSmallOrders
WHERE STATUS IN (0, 1, 2)