It is a pseudo-data event framework designed to notify services of data change events. A simple example would be to use it for notifying customers of changes to their stock portfolio based on conditions that the customers could specify. It really worked by polling data on a scheduled basis.
It was not bad to work with, but it did not perform all that well. Almost everything that it could have done can now be done more efficiently through Service Broker.
Notification Services is not just Deprecated in SQL 2008, it is simply gone. So, it is pretty clearly a bad technology choice at this point.