sql 优化 ·· 持续更新··

转载于 2014-01-08 12:14:51 发布 · 638 阅读

本文介绍了SQL Server中提高查询效率的方法，包括如何避免在WHERE子句中使用函数，以及推荐的替代写法，如使用日期范围代替年份函数等。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

oracle的：：

http://www.cnblogs.com/wxj1020/archive/2008/04/27/1173638.html

sql server ：

The most common thing that will make a query non-sargable is to include a field inside a function in the where clause:

SELECT ... FROM ...
WHERE Year(myDate) = 2008

The SQL optimizer can't use an index on myDate, even if one exists. It will literally have to evaluate this function for every row of the table. Much better to use:

WHERE myDate >= '01-01-2008' AND myDate < '01-01-2009'

Some other examples:

Bad: Select ... WHERE isNull(FullName,'') = 'Ed Jones'
Fixed: Select ... WHERE ((FullName = 'Ed Jones') OR (FullName IS NULL))

Bad: Select ... WHERE SUBSTRING(DealerName,4) = 'Ford'
Fixed: Select ... WHERE DealerName Like 'Ford%'

Bad: Select ... WHERE DateDiff(mm,OrderDate,GetDate()) >= 30
Fixed: Select ... WHERE OrderDate < DateAdd(mm,-30,GetDate())