Hi Xavier,
The games are all quite different in tone and style.
If you're looking for a real cerebral work-out, then Nemesis (aka SH vs Arsene Lupin) might suit you very well.
Since you don't object to gore, then The Awakened and SH vs Jack the Ripper both have plenty of it. The Jack the Ripper game is factually close to the actual true crime, and puts forward an interesting theory.
My own personal favourite is Testament of SH. It's a loooooong, long game (around 16 hrs without a walkthrough), with a twisty-turny plot. The voice actor for Holmes is superb. The graphics are gorgeous and the puzzles are challenging (although not as challenging as Nemesis).