|
|
|
SSC Rookie
      
Group: General Forum Members
Last Login: Wednesday, January 19, 2011 12:15 PM
Points: 33,
Visits: 40
|
|
|
|
|
|
SSCommitted
      
Group: General Forum Members
Last Login: Friday, May 17, 2013 2:53 AM
Points: 1,530,
Visits: 359
|
|
thanks for the question... i tried 8000 byte limitation with LEN function and even that throws an error.
Argument data type text is invalid for argument 1 of len function.
i guess all sql functions either throw an error (e.g. LEN or HASHBYTES) or generally ignore any data more than 8000 bytes (e.g. REPLICATE)...
|
|
|
|
|
Hall of Fame
       
Group: General Forum Members
Last Login: Friday, March 15, 2013 2:43 PM
Points: 3,924,
Visits: 1,554
|
|
Huh.....never used it. Ever. something new to think.
Thanks for the question.
SQL DBA.
|
|
|
|
|
SSCrazy
      
Group: General Forum Members
Last Login: Wednesday, April 24, 2013 5:02 AM
Points: 2,365,
Visits: 1,825
|
|
Knew the right answer. Clicked on the wrong option by mistake.
"Keep Trying"
|
|
|
|
|
SSCertifiable
       
Group: General Forum Members
Last Login: Today @ 3:22 AM
Points: 5,244,
Visits: 7,059
|
|
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 MVP Visit my SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis
|
|
|
|
|
SSCommitted
      
Group: General Forum Members
Last Login: Friday, May 17, 2013 2:53 AM
Points: 1,530,
Visits: 359
|
|
thanks Hugo for your comments. P.S. i am male...
|
|
|
|
|
SSCommitted
      
Group: General Forum Members
Last Login: Yesterday @ 8:00 PM
Points: 1,850,
Visits: 985
|
|
Nice question.Its new for me
Malleswarareddy I.T.Analyst MCITP(70-451)
|
|
|
|
|
Hall of Fame
       
Group: General Forum Members
Last Login: Today @ 6:30 AM
Points: 3,191,
Visits: 4,151
|
|
|
|
|
|
SSCertifiable
       
Group: General Forum Members
Last Login: Today @ 3:22 AM
Points: 5,244,
Visits: 7,059
|
|
You are absolutely right, and my explanation is incorrect.
SELECT HASHBYTES('SHA1', REPLICATE(CAST('A' AS varchar(max)), 8000)) returns a 20-byte value, and SELECT HASHBYTES('SHA1', REPLICATE(CAST('A' AS varchar(max)), 9000)) returns the same warning message and no value. Why the IF comparison between the two doesn't abort but chooses to execute the ELSE clause is, in all honesty, beyond me. I firsth thought it was because the too-long input causes the function to return NULL (in addition to the message), but when I use IF HASHBYTES(...) IS NULL, I still got the "no" result.
Anyhoo, I stand corrected. The HASHBYTES does apparently indeed have an (undocumented!!) limit of 8000 bytes on it's input string. Thanks for pointing that out!
Hugo Kornelis, SQL Server MVP Visit my SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis
|
|
|
|
|
Hall of Fame
       
Group: General Forum Members
Last Login: Today @ 6:30 AM
Points: 3,191,
Visits: 4,151
|
|
Hugo Kornelis (2/1/2010) The HASHBYTES does apparently indeed have an (undocumented!!) limit of 8000 bytes on it's input string.
SET CAPTIOUS_MODE ON To be fair, it is documented in BOL 2008 (http://msdn.microsoft.com/en-us/library/ms174415.aspx):
Remarks
Allowed input values are limited to 8000 bytes.
It is indeed undocumented in BOL 2005.
SET CAPTIOUS_MODE OFF
|
|
|
|