Entendo os problemas com o CONCAT_NULL_YIELDS_NULL

Hoje, depois de passar por alguns problemas com os SETs em configurações do SQL Server, fui questionado por um colega sobre por quê tantos problemas com estas configurações.

Execute os scripts abaixo no seu Sql Server e entenda por quê:

SET CONCAT_NULL_YIELDS_NULL ON;

SELECT 'Rodrigo ' + NULL + 'Ribeiro';

GO

SET CONCAT_NULL_YIELDS_NULL OFF;

SELECT 'Rodrigo ' + NULL + 'Ribeiro';

GO