Рассчитать хэш или контрольную сумму для таблицы в SQL Server

Я пытаюсь вычислить контрольную сумму или хэш для всей таблицы в SQL Server 2008. Проблема, с которой я сталкиваюсь, состоит в том, что таблица содержит тип данных столбца XML, который не может использоваться контрольной суммой и должен быть преобразован в nvarchar. первый. Поэтому мне нужно разбить его на две проблемы:

  1. вычислить контрольную сумму для строки, схема неизвестна до времени выполнения.
  2. вычислите контрольную сумму для всех строк, чтобы получить полную контрольную сумму таблицы.
13.10.2009 13:35:44
3 ОТВЕТА
РЕШЕНИЕ

Вы можете использовать CHECKSUM_AGG . Это может занять только один аргумент, так что вы можете это сделать CHECKSUM_AGG(CHECKSUM(*))- но это не работает для вашего типа данных XML, поэтому вам придется прибегнуть к динамическому SQL.

Вы можете динамически сгенерировать список столбцов, INFORMATION_SCHEMA.COLUMNSа затем вставить int в шаблон:

SELECT @column_list = COALESCE(@column_list + ', ', '')
        + /* Put your casting here from XML, text, etc columns */ QUOTENAME(COLUMN_NAME)
FROM    INFORMATION_SCHEMA.COLUMNS
WHERE   TABLE_NAME = @table_name
    AND TABLE_SCHEMA = @schema_name

DECLARE @template AS varchar(MAX)
SET @template = 'SELECT CHECKSUM_AGG(CHECKSUM({@column_list})) FROM {@schema_name}.{@table_name}'

DECLARE @sql AS varchar(MAX)
SET @sql = REPLACE(REPLACE(REPLACE(@template
    '{@column_list}', @column_list),
    '{@schema_name}', @schema_name),
    '{@table_name}', @table_name)

EXEC ( @sql )
23
11.10.2019 15:54:26
Спасибо! Мне пришлось немного подправить его для особых случаев типов данных, но я смог придумать что-то быстрое и основанное на этом решении. Отлично!
Gabe Brown 20.10.2009 18:36:04
Просто будьте осторожны со столбцами идентификаторов, также я бы использовал BINARY_CHECKSUM, так как это чувствительно к регистру.
user170442 16.12.2014 09:21:11
ЗАМЕНА требует 3 аргумента. Вы sql SET @sql = REPLACE( REPLACE( REPLACE( @template, '{@column_list}', @column_list ), '{@schema_name}', @schema_name ), '{@table_name}', @table_name);
Paul-Sebastian Manole 10.10.2019 14:58:08
Почему это приводит к разной контрольной сумме в разных системах с одной и той же таблицей? Я даже скопировал таблицу в одну систему под другим именем, используя select в Linked Server, и на том же компьютере контрольные суммы совпадают, но не на другом компьютере, хотя таблица идентична.
Paul-Sebastian Manole 11.10.2019 07:45:48
@ Paul-SebastianManole: да, @templateв конце первой строки замен должен быть символ. Что касается другой контрольной суммы, у вас есть столбец идентификаторов или что-то в этом роде?
Cade Roux 11.10.2019 15:54:13

Я изменил скрипт, чтобы сгенерировать запрос для всех соответствующих таблиц в базе данных.

USE myDatabase
GO
DECLARE @table_name sysname
DECLARE @schema_name sysname
SET @schema_name = 'dbo'

DECLARE myCursor cursor
FOR SELECT TABLE_NAME
      FROM INFORMATION_SCHEMA.TABLES T
     WHERE T.TABLE_SCHEMA = @schema_name
       AND T.TABLE_TYPE = 'BASE TABLE'
       AND T.TABLE_NAME NOT LIKE 'MSmerge%'
       AND T.TABLE_NAME NOT LIKE 'sysmerge%'
       AND T.TABLE_NAME NOT LIKE 'tmp%'
     ORDER BY T.TABLE_NAME

OPEN myCursor

FETCH NEXT 
FROM myCursor
INTO @table_name 

WHILE @@FETCH_STATUS = 0
BEGIN
    DECLARE @column_list nvarchar(MAX)
    SET @column_list=''
SELECT @column_list = @column_list + CASE WHEN DATA_TYPE IN ('xml','text','ntext','image sql_variant') THEN 'CONVERT(nvarchar(MAX),'
                                          ELSE ''
                                     END
                                   + QUOTENAME(COLUMN_NAME)
                                   + CASE WHEN DATA_TYPE IN ('xml','text','ntext','image sql_variant') THEN ' /* ' + DATA_TYPE + ' */)'
                                          ELSE ''
                                     END + ', '
  FROM INFORMATION_SCHEMA.COLUMNS
     WHERE TABLE_NAME = @Table_name
     ORDER BY ORDINAL_POSITION

    SET @column_list = LEFT(@column_list, LEN(@column_list)-1) -- remove trailing comma

    DECLARE @sql AS nvarchar(MAX)
    SET @sql = 'SELECT ''' + QUOTENAME(@schema_name) + '.' + QUOTENAME(@table_name) + ''' table_name,
       CHECKSUM_AGG(CHECKSUM(' + @column_list + ')) CHECKSUM
  FROM ' + QUOTENAME(@schema_name) + '.' + QUOTENAME(@Table_name) + ' WITH (NOLOCK)'


    PRINT  @sql

    FETCH NEXT 
    FROM myCursor
    INTO @table_name 

    IF @@FETCH_STATUS = 0
        PRINT  'UNION ALL'

END

CLOSE myCursor
DEALLOCATE myCursor
GO
3
17.12.2014 15:54:37

// Быстрая хеш-сумма SQL и C # mirror Ukraine // HASH_ZKCRC64 /// -------------------------------- -------------------------------------------------- ---------------------------- частный Int64 HASH_ZKCRC64 (byte [] Data) {Int64 Result = 0x5555555555555555; if (Data == null || Data.Length <= 0) return 0; int SizeGlobalBufer = 8000; int Ost = Data.Length% SizeGlobalBufer; int LeftLimit = (Data.Length / SizeGlobalBufer) * SizeGlobalBufer;

        for (int i = 0; i < LeftLimit; i += 64)
        {
            Result = Result
            ^ BitConverter.ToInt64(Data, i)
            ^ BitConverter.ToInt64(Data, i + 8)
            ^ BitConverter.ToInt64(Data, i + 16)
            ^ BitConverter.ToInt64(Data, i + 24)
            ^ BitConverter.ToInt64(Data, i + 32)
            ^ BitConverter.ToInt64(Data, i + 40)
            ^ BitConverter.ToInt64(Data, i + 48)
            ^ BitConverter.ToInt64(Data, i + 56);
             if ((Result & 0x0000000000000080) != 0)
             Result = Result ^ BitConverter.ToInt64(Data, i + 28); 
        }

        if (Ost > 0)
        {
           byte[] Bufer = new byte[SizeGlobalBufer];
           Array.Copy(Data, LeftLimit, Bufer, 0, Ost);
           for (int i = 0; i < SizeGlobalBufer; i += 64)
           {
               Result = Result
               ^ BitConverter.ToInt64(Bufer, i)
               ^ BitConverter.ToInt64(Bufer, i + 8)
               ^ BitConverter.ToInt64(Bufer, i + 16)
               ^ BitConverter.ToInt64(Bufer, i + 24)
               ^ BitConverter.ToInt64(Bufer, i + 32)
               ^ BitConverter.ToInt64(Bufer, i + 40)
               ^ BitConverter.ToInt64(Bufer, i + 48)
               ^ BitConverter.ToInt64(Bufer, i + 56);
               if ((Result & 0x0000000000000080)!=0)
               Result = Result ^ BitConverter.ToInt64(Bufer, i + 28); 
           }
        }

        byte[] MiniBufer = BitConverter.GetBytes(Result);
        Array.Reverse(MiniBufer);
        return BitConverter.ToInt64(MiniBufer, 0);

        #region SQL_FUNCTION
        /*  CREATE FUNCTION [dbo].[HASH_ZKCRC64] (@data as varbinary(MAX)) Returns bigint
            AS
            BEGIN
            Declare @I64 as bigint Set @I64=0x5555555555555555
            Declare @Bufer as binary(8000)
            Declare @i as int Set @i=1
            Declare @j as int 
            Declare @Len as int Set @Len=Len(@data)     

            if ((@data is null) Or (@Len<=0)) Return 0

              While @i<=@Len
              Begin
               Set @Bufer=Substring(@data,@i,8000)
               Set @j=1
                   While @j<=8000
                   Begin
                    Set @I64=@I64 
                    ^ CAST(Substring(@Bufer,@j,   8) as bigint) 
                    ^ CAST(Substring(@Bufer,@j+8, 8) as bigint) 
                    ^ CAST(Substring(@Bufer,@j+16,8) as bigint)
                    ^ CAST(Substring(@Bufer,@j+24,8) as bigint)
                    ^ CAST(Substring(@Bufer,@j+32,8) as bigint)
                    ^ CAST(Substring(@Bufer,@j+40,8) as bigint)
                    ^ CAST(Substring(@Bufer,@j+48,8) as bigint)
                    ^ CAST(Substring(@Bufer,@j+56,8) as bigint)
                    if @I64<0 Set @I64=@I64 ^ CAST(Substring(@Bufer,@j+28,8) as bigint)      
                    Set @j=@j+64    
                   End;  
               Set @i=@i+8000
              End
            Return @I64
            END
         */
        #endregion

   }
-2
24.08.2016 11:48:38
Пожалуйста, отформатируйте ваш код и обновите ваш ответ с объяснением.
Nikolay Mihaylov 24.08.2016 12:05:25