I have always learned that in a declarative language, we specify what we
want, not how we want it to be done. Maybe the current version of the
query engine will work as you described. Maybe the next service pack
changes the. I do not want to have to change my code after every service
pack, so I will stick to writing declarative code without worrying how SQL
Server implements this.
Since you brought it up, I decided to test anyway. On SQL Server 2005, I
used both versions of the "all numeric" test against a table with over
120,000 rows. The NOT LIKE version consistently ran in less than 0.13
seconds on my compter. The nested replace took just as consistently over
1.7 seconds. That's a factor 13!
Hugo Kornelis, SQL Server MVP
My SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis