Intrusion Detection can really be a variety of technologies - NIDS, IPS, HIPS (Network Intrustion Detection, Intrusion Prevention System, Host Intrusion Prevention System). The difference between these is pretty straight-forward, NIDS uses a sensor or sensors to monitor network traffic and alert on anomalies, detection is usually signature-based. IPS is a NIDS setup that is inline with your Internet feed, this allows your IPS solution to actively block attacks. Some firewalls or UTMs have some IPS abilities, other IPS solutions are dedicated boxes. HIPS is an software solution that runs on endpoints (workstations, notebooks), the detection is usually behavioural-based, HIPS can be considered a last line of defense and is sometimes a component of modern endpoint security suites. HIPS, due to it's interaction with the system at a fairly low-level, can have adverse effects like stability and performance issues.
At AppliedUsers HQ (Parallel42) we use Snort in a NIDS configurable, although it is fully capable of being an IPS as well. I prefer NIDS over IPS because the ever-changing security landscape can make IPS management quite a chore. There can be significant tweaking of configs, signatures, thresholds and alerts to really get an IDS tuned - so in an IPS configuration you could be blocking traffic you want to let through. Snort is open source, free and often considered the de facto IDS. Detection rules are available from SourceFire as well as other sources (EmergingThreats) and you can write your own fairly easily. We have Snort running with BASE, which is a web front-end to display and manage the alerts. You can also combine Snort with Barnyard2, Squil or ACID.
An IDS can do so much more than just detect intrusion attempts - I also manage a Snort install in an enterprise environment and often it detects malware before our antivirus. It can also be used to detect network policy violations such as connections to certain websites, P2P traffic, IM traffic, porn - pretty much anything you can think up.
I think almost every network should deploy an IDS of some kind, a Snort solution is free but requires a little expertise or willingness to RTFM, but it pays off huge dividends in securing your network. There are a good number of drop-in solutions as well from your typical network vendors like Cisco, WatchGuard, etc.