Microsoft developers may use LINQ or other data access technologies for applications that implement common business operations. But larger, more specialized applications are good candidates for .NET-enabled SQL Server. Microsoft’s José Blakely, a partner architect for SQL Server, offers three scenarios where programming the database from .NET makes sense:
Deep extensibility in the database. Developers can build core database constructs such as table-valued functions, user-defined aggregates or rich types using a CLR language (such as C# or VB.NET) in Visual Studio. The managed environment is safer and more robust compared with unmanaged extensions (built in C++, for example). And it runs faster and scales much better than similar functionality in Transact-SQL.
A more powerful environment for database developers. They can leverage .NET libraries (such as those for regular expressions, string manipulation and math functions) and use them in SQL Server.
The ability, via the CLR, to write specialized business logic using modern .NET programming languages and run those programs close to the data inside the database. With the very high data volumes being stored in databases—often hundreds of terabytes or even petabytes—managing data in the middle tier is unworkable. Embedding the CLR in SQL Server lets developers move complex business computations to the database.