Why is SQL Server '=' comparator case insensitive?
No, case sensitivity has nothing to do with the equals sign.
Case sensitivity is determined by the collation for the database -- see the documentation for details.
Case sensitivity depends only on the collation.You can specify the collation within each '=' operation
SELECT * FROM [Table_1] a inner join [Table_2] b on a.Col1=b.Col2 collate Modern_Spanish_CS_AI
I have been using the lower function to ensure the text comparison is insensitive till now. Is it still a good idea to follow the same?
Absolutely not. You will generally preclude the use of an index if you do this. Plain old = (or < or > or whatever) will either work or not depending on the collating you have chosen. Do not do this "just to be safe". Testing will make sure you've got it right.