超長字段的索引

SQL Server 不允許對長度超過900字節的varchar字段或超過450字節的nvarchar字段作成索引。遇上對這類字段做查詢時，就隻能做全表查詢，速度可想而知地慢。

網上看一篇文章介紹了一個好思路。就是為這個超長字段(F1)追加一個專門用來加速檢索的hash字段F2，根據F1的值生成Hash值，保存到F2中，然後對F2做成索引。當需要查詢F1時，隻需要對F2做查詢即可。圖例如下：
1）Table做成時
 ++++  F1  ++++ +++  F2 ++++
 ++++  Str                      Hash(Str)
 ++++  (例4000byte)    (16byte)
Insert into xxx (..,F1,F2,..) Values(...,@Str, Hash([@Str),...)

2)查詢時
原select ... From ... WHERE F1 = @xx        (無法使用INDEX)
現select ... From ... WHERE F2 = Hash(@xx)  (使用INDEX)

關鍵的這個Hash函數如下：（參照http://sqlservernation.com/blogs/howtos/archive/2009/03/15/hashing-large-data-strings.aspx）

使用例 Hash (@xx)= fn_hashbytesMAX(@xx, 'MD5')

create function dbo.fn_hashbytesMAX
     (@string nvarchar(max)
     ,@Algo    varchar(10)
    )
    returns binary(16)
as
/************************************************************
*
*    Author:        Brandon Galderisi
*    Last modified: 03-MAR-2009
* Purpose: uses the system function hashbytes as well
*                   as sys.fn_varbintohexstr to split an
* nvarchar(max) string and hash in 8000 byte
*                   chunks hashing each 8000 byte chunk,,
*                   getting the 32 byte output, streaming each
*                   32 byte output into a string then hashing
*                   that string.
*
*************************************************************/
begin
     declare    @concat       varchar(max)
               ,@concatX      XML
               ,@NumHash      int
               ,@HASH         binary(16)
     set @NumHash = ceiling((datalength(@string)/2)/(4000.0))
    /* HashBytes only supports 8000 bytes so split the string if it is larger */
    if @NumHash>1
    begin
                                                        -- # * 4000 character strings
          ;with a as (select 1 as n union all select 1) -- 2
               ,b as (select 1 as n from a ,a a1)       -- 4
               ,c as (select 1 as n from b ,b b1)       -- 16
               ,d as (select 1 as n from c ,c c1)       -- 256
               ,e as (select 1 as n from d ,d d1)       -- 65,536
               ,f as (select 1 as n from e ,e e1)       -- 4,294,967,296 = 17+ TRILLION characters
               ,factored as (select row_number() over (order by n) rn from f)
               ,factors as (select rn,(rn*4000)+1 factor from factored)

          select @concat = cast((
          select right(sys.fn_varbintohexstr
                         (
                         hashbytes(@Algo, substring(@string, factor - 4000, 4000))
                         )
                      , 32) + ''
          from Factors
          where rn <= @NumHash
          for xml path('')
          ) as nvarchar(max))


          set @HASH = dbo.fn_hashbytesMAX(@concat ,@Algo)
    end
     else
     begin
          set @HASH = convert(binary(16), hashbytes(@Algo, @string))
     end

return @HASH
end

真是個巧妙的想法！