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.