It's slightly more complex than that (as is true of a lot of British swearing) - it can be used affectionately, and I would probably find it less surprising to hear it used that way than as an insult, but that's really because it is a seriously strong insult and if it's used that way it generally implies a quite extreme level of anger/hatred (at least in my experience/understanding). I wouldn't expect it at a football match, for example, even if people are swearing at the players with a very high amount of vehemence (using words like 'fuck' and 'twat' and whatever).

Similarly with things like 'bastard', people will use it as a fairly strong insult even if they refer to their friend who's just been dumped as a 'poor bastard' five minutes later. There's definitely more than one axis to our hierarchy!