How to save current checksum in SQL?

Question

I am trying to save the current checksum for the account for the order, so take the previous "checksum" and xor it with the current one and create a new checksum.

Name      Checksum     Rolling Checksum
------    -----------  -----------------
foo       11829231     11829231
bar       27380135     checksum(27380135 ^ 11829231) = 93291803
baz       96326587     checksum(96326587 ^ 93291803) = 67361090

How could I do something like this?

(Please note that the calculations are fully compiled and are for illustration purposes only)

Answer 1

This is basically a running total problem .

Edit:

, , . , , , .

Corina "quirky update". , , , 3 , 26 . . , . , , .

( CLR), . , , .

CREATE TABLE TestTable
(
PK int identity(1,1) primary key clustered,
[Name] varchar(50),
[CheckSum] AS CHECKSUM([Name]),
RollingCheckSum1 int NULL,
RollingCheckSum2 int NULL
)


/*Insert some random records (753,571 on my machine)*/
INSERT INTO TestTable ([Name])
SELECT newid() FROM sys.objects s1, sys.objects s2, sys.objects s3

: Jeff Moden

DECLARE @RCS int

 UPDATE TestTable
    SET @RCS = RollingCheckSum1 = 
                                 CASE WHEN @RCS IS NULL THEN 
                                                        [CheckSum] 
                                 ELSE 
                                             CHECKSUM([CheckSum] ^ @RCS) 
                                 END
   FROM TestTable WITH (TABLOCKX)
 OPTION (MAXDOP 1)

- , , .

SET NOCOUNT ON
BEGIN TRAN

DECLARE @RCS2 INT
DECLARE @PK INT, @CheckSum INT

DECLARE curRollingCheckSum CURSOR LOCAL STATIC READ_ONLY
    FOR
    SELECT PK, [CheckSum]
    FROM         TestTable
    ORDER BY PK

   OPEN curRollingCheckSum

  FETCH NEXT FROM curRollingCheckSum
   INTO @PK, @CheckSum

  WHILE @@FETCH_STATUS = 0
  BEGIN

  SET @RCS2 = CASE WHEN @RCS2 IS NULL THEN @CheckSum ELSE CHECKSUM(@CheckSum ^ @RCS2) END


 UPDATE dbo.TestTable 
    SET RollingCheckSum2 = @RCS2
  WHERE @PK = PK

  FETCH NEXT FROM curRollingCheckSum
   INTO @PK, @CheckSum

    END

COMMIT

,

SELECT * FROM TestTable
WHERE RollingCheckSum1<> RollingCheckSum2

Answer 2

Select Name, Checksum
    , (Select T1.Checksum_Agg(Checksum)
        From Table As T1
        Where T1.Name < T.Name) As RollingChecksum
From Table As T
Order By T.Name

-, . , , - . ( ). , Checksum_Agg SQL.

, , . , Where T1.PK < T.PK . , Name , .

Answer 3

I'm not sure about the moving checksum, but, for example, for a rolling checksum, you can do this with the UPDATE command:

declare @a table (name varchar(2), value int, rollingvalue int)
insert into @a
    select 'a', 1, 0 union all select 'b', 2, 0 union all select 'c', 3, 0 

select * from @a

declare @sum int
set @sum = 0

update @a
set @sum =  rollingvalue = value + @sum 

select * from @a