Using .NET on SQL Server

How should I use Microsoft .NET on SQL Server?

I recommend you use .NET in two ways. The first is for creating functions. You can then have your code call these functions to carry out processing. Many organizations require that all logic used to verify data reside at the database level, thus ensuring consistency of the application data. Because .NET is a better tool for processing logic, for example looping string concatenations and arithmetic operations, you should use .NET when you have a chunk of logic that will reside on a database server that is processing (as opposed to saving) data. Limited use of the Common Language Runtime (CLR) to improve processing might eventually expand into using a .NET stored procedure that processes business logic, then calls a T-SQL stored procedure to save data (instead of the T-SQL stored procedure calling a .NET function). But for most applications, the use of .NET in functions can provide a benefit that doesn't require reactivating an existing application.

The second powerful use of .NET is for defining custom data types. A generic example is the ability to define a complex type consisting of two or three underlying data values. The idea is that you can take key data elements from your business class and define them as objects in context in the database. This way you protect your data integrity by ensuring that only valid objects are saved to your database. Remember, however, that you'll be tying a specific version of the class in your application to your database, so long-term maintenance is a challenge. That's why you'll want to create only simple custom data types initially.

Finally, keep in mind that the use of .NET stored procedures replaces the use of extended stored procedures (XPs). Let me clarify that your existing calls to COM will continue to work. However, given that XPs are by definition a security risk because they can access portions of the server OS without over-sight, you want to remove XPs from your database. The SQL CLR provides a much more robust security model, which will let you ensure that such code doesn't place your database at risk.