Comparing strings in SQL with trailing spaces

-- Testing for this issue:
-- https://improvingsoftware.com/2009/09/09/beware-of-this-trap-when-comparing-strings-in-t-sql-with-trailing-spaces/
-- https://www.brentozar.com/archive/2017/04/surprising-behavior-trailing-spaces/

DECLARE @Value1 NVARCHAR(100), @Value2 NVARCHAR(100), @RESULT NVARCHAR(100)

SET @Value1 = 'apple'
SET @Value2 = 'juice'
SET @RESULT = ''
IF ((@Value1 + 'EndOfString' = @Value2 + 'EndOfString' COLLATE SQL_Latin1_General_CP1_CS_AS) OR (ISNULL(@Value1, @Value2) IS NULL))
SET @RESULT = 'Equal' ELSE SET @RESULT = 'Not Equal'
PRINT 'Test 1: NVARCHAR. Values differ. Expected: Not Equal. Actual: ' + @RESULT

SET @Value1 = 'apple'
SET @Value2 = 'apple'
SET @RESULT = ''
IF ((@Value1 + 'EndOfString' = @Value2 + 'EndOfString' COLLATE SQL_Latin1_General_CP1_CS_AS) OR (ISNULL(@Value1, @Value2) IS NULL))
SET @RESULT = 'Equal' ELSE SET @RESULT = 'Not Equal'
PRINT 'Test 2: NVARCHAR. Values identical. Expected: Equal. Actual: ' + @RESULT

SET @Value1 = NULL
SET @Value2 = 'apple'
SET @RESULT = ''
IF ((@Value1 + 'EndOfString' = @Value2 + 'EndOfString' COLLATE SQL_Latin1_General_CP1_CS_AS) OR (ISNULL(@Value1, @Value2) IS NULL))
SET @RESULT = 'Equal' ELSE SET @RESULT = 'Not Equal'
PRINT 'Test 3: NVARCHAR. Left side null. Expected: Not Equal. Actual: ' + @RESULT

SET @Value1 = 'apple'
SET @Value2 = NULL
SET @RESULT = ''
IF ((@Value1 + 'EndOfString' = @Value2 + 'EndOfString' COLLATE SQL_Latin1_General_CP1_CS_AS) OR (ISNULL(@Value1, @Value2) IS NULL))
SET @RESULT = 'Equal' ELSE SET @RESULT = 'Not Equal'
PRINT 'Test 4: NVARCHAR. Right side null. Expected: Not Equal. Actual: ' + @RESULT

SET @Value1 = NULL
SET @Value2 = NULL
SET @RESULT = ''
IF ((@Value1 + 'EndOfString' = @Value2 + 'EndOfString' COLLATE SQL_Latin1_General_CP1_CS_AS) OR (ISNULL(@Value1, @Value2) IS NULL))
SET @RESULT = 'Equal' ELSE SET @RESULT = 'Not Equal'
PRINT 'Test 5: NVARCHAR. Both sides null. Expected: Equal. Actual: ' + @RESULT

SET @Value1 = 'apple'
SET @Value2 = 'APPLE'
SET @RESULT = ''
IF ((@Value1 + 'EndOfString' = @Value2 + 'EndOfString' COLLATE SQL_Latin1_General_CP1_CS_AS) OR (ISNULL(@Value1, @Value2) IS NULL))
SET @RESULT = 'Equal' ELSE SET @RESULT = 'Not Equal'
PRINT 'Test 6: NVARCHAR. Case sensitive. Cases differ. Expected: Not Equal. Actual: ' + @RESULT

SET @Value1 = 'apple'
SET @Value2 = 'APPLE   '
SET @RESULT = ''
IF ((@Value1 + 'EndOfString' = @Value2 + 'EndOfString' COLLATE SQL_Latin1_General_CP1_CI_AS) OR (ISNULL(@Value1, @Value2) IS NULL))
SET @RESULT = 'Equal' ELSE SET @RESULT = 'Not Equal'
PRINT 'Test 7: NVARCHAR. Second value has trailing spaces. ANSI padding quirk workaround applied. Case insensitive. Cases differ. Expected: Not Equal. Actual: ' + @RESULT

SET @Value1 = 'apple'
SET @Value2 = 'APPLE   '
SET @RESULT = ''
IF ((@Value1 + 'EndOfString' = @Value2 + 'EndOfString' COLLATE SQL_Latin1_General_CP1_CS_AS) OR (ISNULL(@Value1, @Value2) IS NULL))
SET @RESULT = 'Equal' ELSE SET @RESULT = 'Not Equal'
PRINT 'Test 8: NVARCHAR. Second value has trailing spaces. ANSI padding quirk workaround applied. Case sensitive. Cases differ. Expected: Not Equal. Actual: ' + @RESULT

SET @Value1 = 'apple'
SET @Value2 = 'apple   '
SET @RESULT = ''
IF ((@Value1 + 'EndOfString' = @Value2 + 'EndOfString' COLLATE SQL_Latin1_General_CP1_CS_AS) OR (ISNULL(@Value1, @Value2) IS NULL))
SET @RESULT = 'Equal' ELSE SET @RESULT = 'Not Equal'
PRINT 'Test 9: NVARCHAR. Second value has trailing spaces. ANSI padding quirk workaround applied. Case sensitive. Cases identical. Expected: Not Equal. Actual: ' + @RESULT

SET @Value1 = 'apple'
SET @Value2 = 'APPLE   '
SET @RESULT = ''
IF ((@Value1 = @Value2 COLLATE SQL_Latin1_General_CP1_CI_AS) OR (ISNULL(@Value1, @Value2) IS NULL))
SET @RESULT = 'Equal' ELSE SET @RESULT = 'Not Equal'
PRINT 'Test 10: NVARCHAR. Second value has trailing spaces. ANSI padding quirk with no workaround. Case insensitive. Cases differ. Expected: Equal. Actual: ' + @RESULT

SET @Value1 = 'apple'
SET @Value2 = 'APPLE   '
SET @RESULT = ''
IF ((@Value1 = @Value2 COLLATE SQL_Latin1_General_CP1_CS_AS) OR (ISNULL(@Value1, @Value2) IS NULL))
SET @RESULT = 'Equal' ELSE SET @RESULT = 'Not Equal'
PRINT 'Test 11: NVARCHAR. Second value has trailing spaces. ANSI padding quirk with no workaround. Case sensitive. Cases differ. Expected: Not Equal. Actual: ' + @RESULT

SET @Value1 = 'apple'
SET @Value2 = 'apple   '
SET @RESULT = ''
IF ((@Value1 = @Value2 COLLATE SQL_Latin1_General_CP1_CS_AS) OR (ISNULL(@Value1, @Value2) IS NULL))
SET @RESULT = 'Equal' ELSE SET @RESULT = 'Not Equal'
PRINT 'Test 12: NVARCHAR. Second value has trailing spaces. ANSI padding quirk with no workaround. Case sensitive. Cases identical. Expected: Equal. Actual: ' + @RESULT


--SELECT CONVERT (varchar, SERVERPROPERTY('collation'));