When I have an NVARCHAR(250) column or Variable containing a value of 'N ' CHECKSUM returns a value of 0. It appears that CHECKSUM is taking the letter N as a reference to convert the trailing space to UNICODE.
A workaround is to alter the datatype to varchar which then ignores the N as a "CAST" I'm wondering if this is a reportable bug or not ?
DECLARE @TestN NVARCHAR(250) = 'N '
DECLARE @TestV VARCHAR(250) = 'N '
DECLARE @TestN2 NVARCHAR(250) = 'P '
DECLARE @TestV2 VARCHAR(250) = 'P '
SELECT CHECKSUM(@TestN) AS [ckTest DATATYPE NVARCHAR],
CHECKSUM(TRIM(@TestN)) AS ckTestTRIM
SELECT CHECKSUM(@TestV) AS [ckTest DATATYPE VARCHAR],
CHECKSUM(TRIM(@TestV)) AS ckTestTRIMV
SELECT CHECKSUM(@TestN2) AS [ckTest DATATYPE NVARCHAR P],
CHECKSUM(TRIM(@TestN2)) AS ckTestTRIM
SELECT CHECKSUM(@TestV2) AS [ckTest DATATYPE VARCHAR P],
CHECKSUM(TRIM(@TestV2)) AS ckTestTRIMP