SQL Server 跟踪文件为 NTEXT 类型的字段 TextData 创建唯一标识符 [英] SQL Server Trace file create unique identifier for field TextData of type NTEXT

问题描述

要分析我导入的跟踪文件，它需要为 Select Distinct TextData from myImportedTraceFile 提供唯一值

To analyse my imported trace file it would like to have a unique value for Select Distinct TextData from myImportedTraceFile

我尝试使用 hashbyte 虽然我不确定 MD5 是否是创建唯一标识符的正确工具.即使是这种情况(请告诉我是不是这样)，那么我仍然有

I tried using hashbyte although i am not sure if MD5 is the right tool to create a unique identifier. Even if this were the case (please tell me if it is so) then i still have the problem that

• 使用 HASHBYTES('MD5', CAST(TextData AS varchar(7999))) As TextData_HashBytes 将 (查看此回复)

如何为 TextData 列中的每个唯一值(Select Distinct TextData from ..)创建唯一标识符?

What can i do to create a unique identifier for every unique value (Select Distinct TextData from ..) in the column TextData?

基于 Dan 的帖子，我创建了这个测试用例

Based on the post from Dan i created this testcase

Drop Table #Temp

Create Table #Temp
(    
   A int, 
   B NText
)

Insert Into #Temp ( A, B) 
    Select 1, 'some space' UNION ALL      
    Select 2, ' some space' UNION ALL              
    Select 3, ' some space '  UNION ALL                 
    Select 4, 'some space   '  UNION ALL                 
    Select 5, '  some space '  UNION ALL                 
    Select 6, '  some space  '

 -- this returns 6 rows
 Select 
      HASHBYTES('MD5', CAST(B AS nvarchar(MAX)))
      , CAST(B AS nvarchar(MAX)) as B from #Temp;

 -- this returns 3 rows
SELECT NEWID() AS UniqueID, B FROM 
( Select DISTINCT CAST(B AS nvarchar(MAX)) AS B 
    FROM #Temp 
) sq 

这三行是​​结果

'  some space ' -- 2sp B + 1sp E --> row 5
' some space'   -- 1sp B + 0sp E --> row 2
'some space   ' -- 0sp B + 3sp E --> row 4

尚不清楚如何处理第 1 (0sp)、3 (1sp B+E) 和第 6 (2sp B+E) 行.所以一些空白被删除，其他没有.

It is unclear how row 1 (0sp), 3 (1sp B+E) and 6 (2sp B+E) are handled. So some whitespace is removed other not.

推荐答案

您可以使用带有 SELECT DISTINCT 的派生表:

You could use a derived table with SELECT DISTINCT:

SELECT NEWID() AS UniqueID, TextData
FROM (
    SELECT DISTINCT CAST(TextData AS nvarchar(MAX)) AS TextData
    FROM myImportedTraceFile
    ) AS UniqueQueries;

这篇关于SQL Server 跟踪文件为 NTEXT 类型的字段 TextData 创建唯一标识符的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！