在SQL Server中，将字符型数据转换为唯一标识符时出现转换失败错误。

Question

我一直遇到“将字符转换为唯一标识符时转换失败”的错误，现在已经束手无策了。我已经尽可能地缩小了问题范围，同时保持错误不变。如果您想要重现，请先从此处安装CSV分割器： http://www.sqlservercentral.com/articles/Tally+Table/72993/ 以下是测试代码。我使用的是SQL 2008R2版本，但数据库兼容SQL 2005：

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[ZZZTESTTABLE]') AND type in (N'U'))
DROP TABLE [dbo].[ZZZTESTTABLE]
GO

CREATE TABLE [dbo].[ZZZTESTTABLE](
    [Col1] [uniqueidentifier] NOT NULL,
 CONSTRAINT [PK_ZZZTESTTABLE] PRIMARY KEY CLUSTERED 
(
    [Col1] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

-- Test table that I would like to check my values against
insert dbo.ZZZTESTTABLE(Col1) values('85B049B7-CDD0-4995-B582-5A74523039C0')

-- Test string that will be split into table in the DelimitedSplit8k function
declare @temp varchar(max) = '918E809E-EA7A-44B5-B230-776C42594D91,6F8DBB54-5159-4C22-9B0A-7842464360A5'

-- I'm trying to delete all data in the ZZZTESTTABLE that is not in my string but I get the error 
delete dbo.ZZZTESTTABLE
where Col1 not in 
(
-- ERROR OCCURS HERE
    select cast(Item as uniqueidentifier) from dbo.DelimitedSplit8K(@temp, ',')
)

这里是DelimitedSplit8K函数的源代码，这样你就不用去找它了：

CREATE FUNCTION dbo.DelimitedSplit8K
--===== Define I/O parameters
        (@pString VARCHAR(8000), @pDelimiter CHAR(1))
RETURNS TABLE WITH SCHEMABINDING AS
 RETURN
--===== "Inline" CTE Driven "Tally Table" produces values from 0 up to 10,000...
     -- enough to cover VARCHAR(8000)
  WITH E1(N) AS (
                 SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL 
                 SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL 
                 SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1
                ),                          --10E+1 or 10 rows
       E2(N) AS (SELECT 1 FROM E1 a, E1 b), --10E+2 or 100 rows
       E4(N) AS (SELECT 1 FROM E2 a, E2 b), --10E+4 or 10,000 rows max
 cteTally(N) AS (--==== This provides the "zero base" and limits the number of rows right up front
                     -- for both a performance gain and prevention of accidental "overruns"
                 SELECT 0 UNION ALL
                 SELECT TOP (DATALENGTH(ISNULL(@pString,1))) ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) FROM E4
                ),
cteStart(N1) AS (--==== This returns N+1 (starting position of each "element" just once for each delimiter)
                 SELECT t.N+1
                   FROM cteTally t
                  WHERE (SUBSTRING(@pString,t.N,1) = @pDelimiter OR t.N = 0) 
                )
--===== Do the actual split. The ISNULL/NULLIF combo handles the length for the final element when no delimiter is found.
 SELECT ItemNumber = ROW_NUMBER() OVER(ORDER BY s.N1),
        Item       = SUBSTRING(@pString,s.N1,ISNULL(NULLIF(CHARINDEX(@pDelimiter,@pString,s.N1),0)-s.N1,8000))
   FROM cteStart s
;

- creativejourney.com

---

我会怀疑split函数，因为它是复杂的部分。如果在拆分选择中添加WHERE Item不为空且LEN（Item）> 1，是否仍会出现错误？ - hatchet - done with SOverflow

我刚在网上找到了一些随机的脚本，我不明白它是如何工作的，但我计划使用它，有人能帮我调试一下吗？ - Remus Rusanu

分割函数看起来没问题。我也想知道为什么你的删除操作不能按照写的方式工作。如果改写为EXISTS，它会以同样的方式失败。 - hatchet - done with SOverflow

嘿，雷姆斯，这不是一个随意的脚本，我已经和一位优秀的数据库管理员进行了调试。我创建了一个最小的示例来重现问题。也许你应该仔细看一看，似乎是 SQL 服务器的一个错误。 - creativejourney.com

我同意，需要道歉。你确实做了功课，这确实不是一个简单的编码问题，所以我必须为我说的话道歉。我正在跟进并调查UDF是否具有某些内在的过程性期望（QO可以打破），或者它是真正纯粹的声明性代码，生成的执行计划很差。 - Remus Rusanu

Answer 1

使用此UDF确实对执行顺序做出了过程性假设。它假定UDF内部的WHERE子句将在cast(item as uniqueidentifier)之前被评估。这种假设是错误的，因为优化器可以自由地更改计划以将WHERE子句移动到cast之上，其净效应是要求cast将部分标记转换为guid（即像18E809E-EA7A-44B5-B230-776C42594D91这样的字符串）。

有关更详细的答案，请阅读T-SQL函数不意味着一定的执行顺序。

作为解决方法，您可以强制将NULL投影到未满足WHERE子句的行的值中：

CREATE FUNCTION dbo.DelimitedSplit8K
...
cteStart(N1, nullify) AS (--==== This returns N+1 (starting position of each "element" just once for each delimiter)
                 SELECT t.N+1, 
                    case when (SUBSTRING(@pString,t.N,1) = @pDelimiter OR t.N = 0) then 1 else 0 end
                   FROM cteTally t
                  WHERE (SUBSTRING(@pString,t.N,1) = @pDelimiter OR t.N = 0) 
                )
--===== Do the actual split. The ISNULL/NULLIF combo handles the length for the final element when no delimiter is found.
 SELECT ItemNumber = ROW_NUMBER() OVER(ORDER BY s.N1),
        Item       = case s.nullify
            when 1 then SUBSTRING(@pString,s.N1,ISNULL(NULLIF(CHARINDEX(@pDelimiter,@pString,s.N1),0)-s.N1,8000))
            else null
            end
   FROM cteStart s;
go

由于CASE表达式在CAST之前被保证被评估（因为CAST的输入是CASE的输出），WHERE子句的重新排序是安全的。

- Remus Rusanu

---

我因为误读了你的博客，做出了错误的假设，并在 LinkedIn 上给你发送了一条信息。请原谅我的错误，并忽略这条信息。 - Jeff Moden

Answer 2

不确定这里发生了什么，但问题似乎不在于guid的格式或函数的输出。执行以下内容可以解决问题：

declare @temp varchar(max) = '918E809E-EA7A-44B5-B230-776C42594D91,6F8DBB54-5159-4C22-9B0A-7842464360A5'    
select cast(Item as uniqueidentifier) from dbo.DelimitedSplit8K(@temp, ',')

也许查询处理器正在查看函数的返回模式，并表示它无法转换为 uniqueidentifier 类型？希望其他人能提供具体的答案。

将拆分函数的输出选择到临时表中将起作用：

select cast(Item as uniqueidentifier) as Item into #temp from dbo.DelimitedSplit8K(@temp, ',')

-- I'm trying to delete all data in the ZZZTESTTABLE that is not in my string but I get the error 
delete dbo.ZZZTESTTABLE
where Col1 not in 
(
-- ERROR OCCURS HERE
    --select cast(Item as uniqueidentifier) from dbo.DelimitedSplit8K(@temp, ',')
    select Item from #temp
)

- Jeff Ogata

---

谢谢，这是我们想出的一个解决方案，也可能是我们将要使用的方案。我注意到一些可能相关的事情是：我可以将Select语句单独从where子句中取出来，它可以正常工作。我也可以将NOT IN更改为简单的IN，就不会出现错误（尽管我得不到想要的结果）。它似乎与UDF（用户定义函数）如何返回或使用CTE（公共表达式）有关。如果我将UDF更改为在表中返回硬编码字符串，我就不会出现错误。我认为这是一个SQL的bug或特性。非常感谢您的关注！ - creativejourney.com

Answer 3

为什么要将Item转换为uniqueidentifier，而不是反过来呢？

与其这样做，不如...

where Col1 not in 
(
-- ERROR OCCURS HERE
    select cast(Item as uniqueidentifier) from dbo.DelimitedSplit8K(@temp, ',')
)

你可以尝试这个：

where cast(Col1 as varchar(64)) not in 
(
    select Item 
    from dbo.DelimitedSplit8K(@temp, ',')
)

- Skorpioh

---

如果split函数输出的字符串无法转换为唯一标识符，则说明它在某种程度上搞乱了这些字符串。避免使用CAST可以消除错误，但结果仍然不正确（即会删除不应该删除的内容或相反）。 - hatchet - done with SOverflow

作为一个乐观主义者，我只是假设函数DelimitedSplit8K能够很好地完成它的工作。测试应该非常简单，即运行SELECT * FROM dbo.DelimitedSplit8K('918E809E-EA7A-44B5-B230-776C42594D91,6F8DBB54-5159-4C22-9B0A-7842464360A5', ',')。 - Skorpioh

split函数可以很好地分割字符串，而你的解决方案可以解决原始代码中导致转换失败的任何奇怪问题。 - hatchet - done with SOverflow

抱歉，如果我不将其转换为GUID而使用指定的另一种方式，则它可以工作。当然，我没有考虑到这一点，因为从理论上讲，我想尽可能具体并且它应该可以工作。感谢您的帮助！ - creativejourney.com

Answer 4

看起来我第一次误读了问题。很好，你制作了一个能够重现错误的测试脚本。以下是我的解决方案：

delete dbo.ZZZTESTTABLE
WHERE Col1 in
(
    select Z.Col1
    from dbo.ZZZTESTTABLE Z
    LEFT JOIN dbo.DelimitedSplit8K(@temp, ',') S on S.Item = Z.Col1
    where S.Item is null
)
OPTION (force order)

- RichardTheKiwi

---

它们都是有效的。我可以将Select语句从where子句中单独取出，它可以正常工作。我也可以将NOT IN更改为简单的IN，这样就不会出现错误（尽管我没有得到想要的结果）。似乎与UDF（用户定义函数）如何返回或使用CTE（公共表达式）有关。如果我将UDF更改为返回硬编码字符串，我就不会出现错误。看起来像是SQL的一个bug或特性。 - creativejourney.com