Sometimes I use the macro @GetVaultHash(), my definition is different from the AnalyticsCreator default.
Today I learned that CONCAT doesn't work as documented (Or maybe I am to stupid to understand the documentation).
From the documentation CONCAT (Transact-SQL) it was and is not clear to me, that this happens.
CONCAT implicitly converts all arguments to string types before concatenation. CONCAT implicitly converts null values to empty strings. If CONCAT receives arguments with all NULL values, it will return an empty string of type varchar(1). The implicit conversion to strings follows the existing rules for data type conversions. See CAST and CONVERT (Transact-SQL) for more information about data type conversions.
Here is my definition of the macro GetVaultHash:
@GetVaultHash()
=
CONVERT(CHAR(32), HASHBYTES('MD5', LOWER(CONCAT(N''
, :1, '|~|', :2, '|~|', :3, '|~|', :4, '|~|', :5, '|~|', :6, '|~|', :7, '|~|', :8, '|~|', :9, '|~|', :10, '|~|'
, :11, '|~|', :12, '|~|', :13, '|~|', :14, '|~|', :15, '|~|', :16, '|~|', :17, '|~|', :18, '|~|', :19, '|~|', :20, '|~|'
, :21, '|~|', :22, '|~|', :23, '|~|', :24, '|~|', :25, '|~|', :26, '|~|', :27, '|~|', :28, '|~|', :29, '|~|', :30, '|~|'
, :31, '|~|', :32, '|~|', :33, '|~|', :34, '|~|', :35, '|~|', :36, '|~|', :37, '|~|', :38, '|~|', :39, '|~|', :40, '|~|'
))), 2)
Today I got an issue when the hashkey did not change for changed data. Finaly I found, that real and float data is rounded in the CONCAT function
Try this:
select
concat(N'',1.234567890123,'|~|',2.3456789012345678)
, concat(N'',CAST(1.234567890123 as float),'|~|',cast(2.3456789012345678 as float))
, concat(N'',CAST(1.234567890123 as real),'|~|',cast(2.3456789012345678 as real))
- the result of the first column is "1.234567890123|~|2.3456789012345678"
- but the result of the two other columns is "1.23457|~|2.34568"
