Query optimization - VARCHAR equality vs Numeric equality

Question

I have a large SQL Server database with about 40 columns and hundreds of millions of rows.

This table is supposed to be loose in schema so I have a lot of columns as VARCHAR(MAX) even where it could have been BIGINT, DATETIME, INT etc. Does this have an impact on querying time/efficiency? e.g. will

SELECT TOP 100 * FROM CustomerId = 34343

be faster than

SELECT TOP 100 * FROM CustomerId = '34343'

? If yes, how much faster?

And what if I use VARCHAR(MAX) instead of fixed length VARCHAR.. And what about other DBs like mySQL etc. in this regard?

Answer 1

Yes, comparing strings is usually slower than comparing pure numbers. Whether it is measurable depends on how the query execution engine does the comparison. If the query engine does not compare to the end of the strings - which it often won't, then your penalty is not great. Try it and see. But in theory, you'd be better off with the numeric comparison for numeric quantities.