0

I would like to md5 all the columns in the sql table no matter what the data type is. I have a query below but it wont work for any column that has more than 8000 character. I have many table that I have to work on. What would be the appropriate solution?

SELECT A.Account_id,   
hashbytes('MD5',                    
(SELECT A.[accountid]
  ,[description]
  ,[pagenumber]
  ,[Permission]---this exceeds over 8000 characters
            FROM (
                  VALUES(NULL))foo(bar)
            FOR xml auto)) AS [Hash]
FROM dbo.table AS A;
Dijal
  • 47
  • 5
  • 3
    What are you trying to do in the first place? No column can have more than 8000 *bytes*, that's the limit for an entire row. The only exceptions are the blob columns. – Panagiotis Kanavos Jan 17 '19 at 14:27
  • Possible duplicate of [SQL Server 2014 Hashbytes of a nvarchar(max) result is nvarchar(max)](https://stackoverflow.com/questions/38670262/sql-server-2014-hashbytes-of-a-nvarcharmax-result-is-nvarcharmax) – EzLo Jan 17 '19 at 14:29
  • 1
    In any case MD5 is too weak and actually *deprecated*. From the docs, `all algorithms other than SHA2_256, and SHA2_512 are deprecated. Older algorithms (not recommended) will continue working, but they will raise a deprecation event`. It's not only the deprecation event, the OS and CPUs have built-in acceleration for newer algorithms – Panagiotis Kanavos Jan 17 '19 at 14:30
  • 1
    From the same docs : `For SQL Server 2014 (12.x) and earlier, allowed input values are limited to 8000 bytes.`. Using `HASHBYTES` isnt' an efficient way to audit columns, detect and track changes or sign results. Change tracking, available in all SQL Server editions, is a far lighter way to track changes. SQL Server's also offers built-in auditing. `rowversion` columsn can track changes for concurrency purposes – Panagiotis Kanavos Jan 17 '19 at 14:31

0 Answers0