SQL 检查 length = 0 是否比将其与空字符串进行比较更快？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/3350959/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Is it faster to check if length = 0 than to compare it to an empty string?
提问by Espo
I've heard that in some programming languages it is faster to check if the length of a string is 0, than to check if the content is "". Is this also true for T-SQL?
我听说在某些编程语言中，检查字符串的长度是否为0比检查内容是否为 更快""。这也适用于 T-SQL 吗？
Sample:
样本：
SELECT user_id FROM users WHERE LEN(user_email) = 0
vs.
对比
SELECT user_id FROM users WHERE user_email = ''
采纳答案by Martin Smith
EditYou've updated your question since I first looked at it. In that example I would say that you should definitely always use
编辑自从我第一次看到它以来，您已经更新了您的问题。在那个例子中，我会说你绝对应该总是使用
SELECT user_id FROM users WHERE user_email = ''
Not
不是
SELECT user_id FROM users WHERE LEN(user_email) = 0
The first one will allow an index to be used. As a performance optimisation this will trump some string micro optimisation every time! To see this
第一个将允许使用索引。作为性能优化，这将每次都胜过一些字符串微优化！看到这个
SELECT * into #temp FROM [master].[dbo].[spt_values]
CREATE CLUSTERED INDEX ix ON #temp([name],[number])
SELECT [number] FROM #temp WHERE [name] = ''
SELECT [number] FROM #temp WHERE LEN([name]) = 0
Execution Plans
执行计划
Original Answer
原答案
In the code below (SQL Server 2008 - I "borrowed" the timing framework from @8kb's answer here) I got a slight edge for testing the length rather than the contents below when @stringToTestcontained a string. They were equal timings when NULL. I probably didn't test enough to draw any firm conclusions though.
在下面的代码中（SQL Server 2008 - 我在这里从@8kb 的回答中“借用”了计时框架），当@stringToTest包含字符串时，我在测试长度而不是下面的内容方面有一点优势。当 NULL 时它们是相等的时间。不过，我可能没有进行足够的测试来得出任何确切的结论。
In a typical execution plan I would imagine the difference would be negligible and if you're doing that much string comparison in TSQL that it will be likely to make any significant difference you should probably be using a different language for it.
在典型的执行计划中，我认为差异可以忽略不计，如果您在 TSQL 中进行如此多的字符串比较，它可能会产生任何重大差异，您可能应该为此使用不同的语言。
DECLARE @date DATETIME2
DECLARE @testContents INT
DECLARE @testLength INT
SET @testContents = 0
SET @testLength = 0
DECLARE
@count INT,
@value INT,
@stringToTest varchar(100)
set @stringToTest = 'jasdsdjkfhjskdhdfkjshdfkjsdehdjfk'
SET @count = 1
WHILE @count < 10000000
BEGIN
SET @date = GETDATE()
SELECT @value = CASE WHEN @stringToTest = '' then 1 else 0 end
SET @testContents = @testContents + DATEDIFF(MICROSECOND, @date, GETDATE())
SET @date = GETDATE()
SELECT @value = CASE WHEN len(@stringToTest) = 0 then 1 else 0 end
SET @testLength = @testLength + DATEDIFF(MICROSECOND, @date, GETDATE())
SET @count = @count + 1
END
SELECT
@testContents / 1000000. AS Seconds_TestingContents,
@testLength / 1000000. AS Seconds_TestingLength
回答by StuartLC
I would be careful about using LENin a WHEREclause as it could lead to table or index scans.
我会小心LEN在WHERE子句中使用，因为它可能导致表或索引扫描。
Also note that if the field is NULLable that LEN(NULL) = NULL, so you would need to define the behaviour, e.g.:
另请注意，如果该字段NULL能够LEN(NULL) = NULL，那么您需要定义行为，例如：
-- Cost .33
select * from [table]
where itemid = ''
-- Cost .53
select * from [table]
where len(itemid) = 0
-- `NULL`able source field (and assuming we treat NULL and '' as the same)
select * from [table]
where len(itemid) = 0 or itemid is NULL
回答by Evil Pigeon
I just tested it in a very limited scenario and execution plan ever so slightly favours comparing it to an empty string. (49% to 51%). This is working with stuff in memory though so it would probably be different if comparing against data from a table.
我只是在一个非常有限的场景中对其进行了测试，并且执行计划非常倾向于将其与空字符串进行比较。（49% 到 51%）。不过，这适用于内存中的内容，因此如果与表中的数据进行比较，它可能会有所不同。
DECLARE @testString nvarchar(max)
SET @testString = ''
SELECT
1
WHERE
@testString = ''
SELECT
1
WHERE
LEN(@testString) = 0
Edit: This is with SQL Server 2005.
编辑：这是 SQL Server 2005。
