这个sql 如何优化，比如做索引

[山尖美色]

SELECT isnull(max(convert(numeric,right( substring(BillCode,3,4),4))),0) FROM [Bill] where BillType=99 AND left(BillCode,2)='RZ'


目的取一个单据的编号，单据越来越多，速度变慢

[不穿内裤的超人]

;with cte(BillCode) as (
select BillCode FROM [Bill] where BillType=99 AND left(BillCode,2)='RZ' 
)select isnull(max(convert(numeric,right( substring(BillCode,3,4),4))),0) from cte

不确定会不会快，你可以试试

[吉普赛的歌]

--添加一个计算列
ALTER TABLE [Bill] ADD BillCodeCal AS ISNULL(CONVERT(NUMERIC, RIGHT(SUBSTRING(BillCode, 3, 4), 4)),0) PERSISTED
--添加索引
CREATE INDEX IX_Bill_BillType_BillCode_BillCodeCal ON [Bill](BillType,BillCode,BillCodeCal DESC)

SELECT MAX(BillCodeCal)
FROM   [Bill]
WHERE  BillType = 99
       AND LEFT(BillCode, 2) = 'RZ'

[RINK_1]

BillType和BillCode上加索引。 left(BillCode,2)='RZ'改成BillCode like ‘RZ%’试试。 right( substring(BillCode,3,4),4)这里用right是不是有些多此一举。