• Nice question. But the explanation is incorrect.

    There is no 8000 byte limit on REPLICATE. But there is one on the VARCHAR data type, and since 'A' is considered to be VARCHAR, so will be the result of REPLICATE. Run the following code to see for yourself:

    1. For lengths up to 8000, differences are detected as expected:

    IF HASHBYTES('SHA1', REPLICATE('A', 4000)) = HASHBYTES('SHA1', REPLICATE('A', 8000))

    PRINT 'yes.';

    ELSE

    PRINT 'no.';

    2. Change 'A' to N'A' and the limit is reduced to 4000 bytes (since NVARCHAR has a maximum length of 4000 rather than 8000 characters)

    IF HASHBYTES('SHA1', REPLICATE(N'A', 4000)) = HASHBYTES('SHA1', REPLICATE(N'A', 8000))

    PRINT 'yes.';

    ELSE

    PRINT 'no.';

    3. Use explicit CAST to use VARCHAR(MAX) and the 8000 byte limit disappears as snow in the sun (even though it does raise a warning - becaused the result of the HASHBYTES algorithm now exceeds 8000 bytes, but the defined return type of HASHBYTES is VARBINARY(8000)):

    EDIT: Disregard this example. Apparently, there IS an 8000-byte limit on the input of HASHBYTES (but not on REPLICATE!), as pointed out by vk-kirov (see his message below).

    IF HASHBYTES('SHA1', REPLICATE(CAST('A' AS varchar(max)), 9000)) = HASHBYTES('SHA1', REPLICATE(CAST('A' AS varchar(max)), 8000))

    PRINT 'yes.';

    ELSE

    PRINT 'no.';

    4. Generic proof that string functions like REPLICATE and LEN do work with VARCHAR(MAX) strings longer than 8000 characters:

    SELECT LEN(REPLICATE(CAST('A' AS varchar(max)), 9000));

    (The error message posted by ziangij indicates that (s)he used the depricated data type TEXT instead of VARCHAR(MAX). The TEXT data type did indeed have many limitations, which is only one of the many good reasons to replace it with VARCHAR(MAX) - the other good reason being that TEXT will be removed in a future version of SQL Server).

    (edit - added a warning to disregard example 3, after being corrected by vk-kirov)


    Hugo Kornelis, SQL Server/Data Platform MVP (2006-2016)
    Visit my SQL Server blog: https://sqlserverfast.com/blog/
    SQL Server Execution Plan Reference: https://sqlserverfast.com/epr/