SSMS - Openrowset maximum file size Apologies if this has been answered elsewhere but I couldn't find anything here or online.

Does openrowset have a file size limit? I'm trying to write a stored procedure, part of which checks if certain characters exist within the file and if they do I'd have to skip to the next section of the stored procedure. So far I've simply bulk inserted the entire file into a one column table then did:

IF(SELECT COUNT(*) FROM #fulltable WHERE fulltable LIKE '%}%')&gt;0 GOTO NEXTSECTION
IF(SELECT COUNT(*) FROM #fulltable WHERE fulltable LIKE '%~%')&gt;0 GOTO NEXTSECTION
IF(SELECT COUNT(*) FROM #fulltable WHERE fulltable LIKE '%#%')&gt;0 GOTO NEXTSECTION


It's reliable but very slow when dealing with large files (sometimes over 10GB). I'm thinking the below query would be quicker for large files

DECLARE @FILE NVARCHAR(MAX)
select @FILE = BULKCOLUMN from (
select * from openrowset(BULK N'filpath', single_clob) [a]
)a

IF(SELECT IIF(@FILE LIKE '%{%',1,0)) = 1 GOTO NEXTSECTION
IF(SELECT IIF(@FILE LIKE '%}%',1,0)) = 1 GOTO NEXTSECTION
IF(SELECT IIF(@FILE LIKE '%~%',1,0)) = 1 GOTO NEXTSECTION
IF(SELECT IIF(@FILE LIKE '%@%',1,0)) = 1 GOTO NEXTSECTION
IF(SELECT IIF(@FILE LIKE '%£%',1,0)) = 1 GOTO NEXTSECTION


But as I say Bulk insert is reliable and I'd hate to risk the file being truncated when using OPENROWSET if there is some sort of file size limit.

Any advice on the matter would be appreciated.
I know it's not your main question, but you mentioned slow performance. When checking if a certain character or substring exists within a larger string, you should try using CHARINDEX('x', strColumn) instead of LIKE '%x%'. This will boost your performance drastically. Using LIKE with % surrounding your search-term is very slow and costly.