How to receive notifications in .NET application when database table changes?
<p>I've always wondered how huge applications (such as Facebook) handle notifying users about events (ex. someone messaged you) in practically real time without pooling the database every second/minute etc (I assume that Facebook doesn't check if there are any new messages for logged in user every second). Is there any way in Sql Server to receive notifications in C# application when database changes (row is inserted or updated)?</p>

<p>To (hopefully) make things a little bit more practical:</p>

<p>How would you handle a desktop chat application that is connected to a database server and allows users to message each other providing instant notifications for the person receiving message? </p>

<p>How would you handle notifying user about messages that they received while they were "offline"? </p>

<p>Let's assume that application would handle several hundreds/thousands users and notifications should be (relatively) instant (without pooling database every 10 seconds to see if there are any new messages).</p>