Comportamento estranho da cláusula WHERE. Por que isso retorna uma linha?

O que está acontecendo é que o SQL preenche os espaços no final das strings para que tenham o mesmo comprimento. Então, se você tentar fazer algo como:

SELECT 1 WHERE '' = ' ' 

Na verdade, você receberá 1 de volta, embora não sejam iguais. No entanto, se você fizer algo como:

SELECT 1 WHERE 'a' = ' a'

Ele não retornará nada, pois estaria comparando 'a' com 'a' que não correspondem.

No entanto, isso retornaria 1:

SELECT 1 WHERE 'a' = 'a '

Como está comparando 'a' com 'a'

E se alguém precisar consultar para encontrar '1234', mas não '1234'

Se você estiver usando dados Unicode, basta usar LIKE. Da documentação :

Quando todos os argumentos ( match_expression, pattern e escape_character , se presentes) são tipos de dados de caracteres ASCII, a correspondência de padrão ASCII é executada. Se qualquer um dos argumentos for do tipo de dados Unicode, todos os argumentos serão convertidos em Unicode e a correspondência de padrão Unicode será executada. Quando você usa dados Unicode ( tipos de dados nchar ou nvarchar ) com LIKE, os espaços em branco à direita são significativos; no entanto, para dados não Unicode, os espaços em branco à direita não são significativos. O Unicode LIKE é compatível com o padrão ISO. ASCII LIKE é compatível com versões anteriores do SQL Server.

Com dados não Unicode, você ainda pode obter o LIKEcomportamento Unicode, ao custo de algumas conversões implícitas (observe que o LIKEpadrão na segunda consulta tem um 'N' inicial, denotando uma string Unicode literal):

DECLARE @T AS table 
(
    pk integer IDENTITY PRIMARY KEY,
    v varchar(10) NOT NULL,
    UNIQUE (v, pk)
);

INSERT @T (v) 
VALUES 
    ('1234'), 
    ('1234' + SPACE(1)),
    ('1234' + SPACE(2)),
    ('1234' + SPACE(3));

SELECT REPLACE(T.v, SPACE(1), '@')
FROM @T AS T 
WHERE T.v LIKE '1234 ';

SELECT REPLACE(T.v, SPACE(1), '@')
FROM @T AS T 
WHERE T.v LIKE N'1234 ';

Resultados:

O código com a conversão implícita ainda pode usar um índice, por meio da mágica da geração de busca dinâmica :

Em relação à seguinte parte da pergunta:

E se alguém precisar consultar para encontrar '1234', mas não '1234'

Parece que usar um Collation binário (ou seja, um que termina em _BIN2) não funciona para isso, mas você também pode comparar DATALENGTHos dois valores:

SET NOCOUNT ON;
DECLARE @T TABLE (Col1 VARCHAR(10));
INSERT INTO @T (Col1) VALUES ('12345  ');
INSERT INTO @T (Col1) VALUES ('12345');

SELECT DATALENGTH(Col1) AS [Col1Bytes], '~' + Col1 + '~' AS [Col1] FROM @T;

SELECT DATALENGTH(Col1) AS [Col1Bytes], '~' + Col1 + '~' AS [Col1] FROM @T
WHERE Col1 = '12345  '
AND   DATALENGTH(Col1) = DATALENGTH('12345  ');

Retorna:

Col1Bytes    Col1
7            ~12345  ~
5            ~12345~

Col1Bytes    Col1
7            ~12345  ~

Uma alternativa à resposta de srutzky para a parte "e se" da pergunta:

E se alguém precisar consultar para encontrar '1234', mas não '1234'

Apenas certifique-se de que os últimos caracteres do que você está comparando não sejam espaços em branco.

SELECT      *
FROM        (
                            SELECT CONVERT(VARCHAR(50),'1234 ') AS TestVal
                UNION ALL   SELECT CONVERT(VARCHAR(50),'1234')
            ) AS TestTable
WHERE       TestTable.TestVal = '1234 '
AND         TestTable.TestVal + '.' = '1234 ' + '.';

É melhor manter ambas as condições na cláusula WHERE (a comparação direta e a comparação calculada) para preservar o uso do índice.