In all honesty, it depends on exactly how the query is processed, and it is certainly possible that changing the query (even though it yields the same end results) can make expressions (like attempted conversions required to fit a big string into a small column) happen before filters have eliminated those rows (and a different plan shape can occur simply because of the syntax, because stats have changed, because indexes have been added, etc. etc.).
Erland Sommarskog has always called those "illogical errors" - this UserVoice issue was migrated from a Connect item that was originally posted at least a decade ago.
In your specific case, there are multiple failsafes you can use here, assuming
small_col is a
large_col is something that can't have more than 10 characters when
col1 = 1:
SELECT CASE col1
WHEN 1 THEN CONVERT(varchar(10), large_col) END
WHERE col1 = 1;
In fact an additional failsafe, depending on the data types involved, would be to use
TRY_CONVERT() instead of
CONVERT(), in SQL Server 2012 and up. I don't think that applies in this specific case, since virtually any string type can be converted to
varchar(10), but might be useful in other cases.
There was a similar but not identical question in the past, where I added other examples and links to external references:
I also wrote about this in #1 here:
Anyway, to answer your explicit question, the error is only thrown when the query is processed in such a way that an expression is attempted on the violating rows before they are filtered out. This is why you tend to hear a lot of us old school folks lecturing about always using the right data type, not overloading columns, etc.