Here are a couple of key use scenarios for SQL CLR:

1. Complex computation that processes lots of data: before SQL CLR, when you had complex computation over substantial data, you had to choose between two evils. The first is writing your logic in .NET, keeping it on the mid-tier, and shipping your data. But with substantial data, the cost of moving it around is prohibitive. The second option is to write your logic in T-SQL to keep it near the data. However, if your logic is complex, writing it in T-SQL can be difficult, awkward, or even impossible, and is a productivity hit for many developers (they don't know T-SQL, don't have access to sophisticated structures and libraries, etc.). SQL CLR lets you have your cake and eat it too: you can write the complex logic in .NET, and the logic can stay near the data.

2. Streaming results of TVF: T-SQL table-valued functions materialize their results in temp tables, whereas CLR TVFs can stream them.

3. Easier access to external data sources: much simpler in .NET than T-SQL.

4. Rich types: when you need an atomic type for a column, but also need it to have rich functionality (e.g., methods). For instance, look at hierarchyid and the spatial data type. Both of these were implemented with CLR.

For a more in-depth discussion, including a break-down of when to use CLR and when to not, check out this article: http://msdn.microsoft.com/en-us/library/ms345136%28SQL.90%29.aspx

Hope that's of some help.