I've had occasion to use COLLATE when I needed an accent insensitive search (I needed to find é as well as e when the user typed e), when all of our data has accent sensitive collation. The collation name has _AI on the end instead of _AS.