CLR Checklist

SQL Server 2005's common language runtime (CLR) is a core extension to the SQL Server engine–side programming model. The CLR lets you extend the functionality of the SQL Server database engine with scalar functions, table-valued functions (TVFs), procedures, triggers, aggregates, and user-defined data types (UDTs). Before you start using the CLR, you should evaluate the "whys and hows" of using CLR-based extensions inside SQL Server. To begin, check out the following list.

N-tier applications aren't dead. Just because the CLR lets you move data processing to the database server, that doesn't mean you have to. Remember what you're trading when you move code into the database server; you're trading server-side CPU and memory for network roundtrips and network latency of the application (server). If your data processing benefits from making the data local, moving your code might be a good alternative, but keep in mind that your server doesn't have an infinite amount of processing resources. The key criteria for moving processing into the database server are, first, that you use computation and logic-intensive rather than data-intensive processing, and second, that you can reduce roundtrips related to the data volume returning from the server.

Add only primitive data types. When you're designing UDTs, limit yourself to primitive data types such as ZIP code, Social Security number, point, signed integers, and so on. Think about how you can select a row that contains a UDT. What does the use of the UDT mean for the query plan? Can you build an index on the UDT? How?

Consider replacing extended stored procedures. If you've developed extended stored procedures in SQL Server 2000 and earlier installations, you should investigate replacing them with CLR procedures or functions. The CLR provides a safer, more-scalable execution environment.

Say goodbye to sp_OA. The sp_OA procedures are extended stored procedures that make calls to COM objects through a late-bound (IDispatch-based) model. (See http://msdn.microsoft.com/library/en-us/acdata/ac_8_qd_14_7bcc.asp for more details.) SQL CLR procedures or functions are a much better alternative because most of the time you can remove the dependency on the COM objects by using CLR-intrinsic framework classes. Even when the CLR procedures do require COM interoperability, you're switching to an early-bound model, which improves performance and reduces the risk of programming and execution errors. And the CLR provides a better programming environment for implementing logic flows, loops, and so on.

Don't forget your clients. You can extend SQL Server, but always keep in mind that on the other side of the wire is an application that needs to use these extensions. Most CLR SQL extensions (except UDTs) are totally opaque for the application. Accessibility of UDTs is related to the data-access API you use. Only the new CLR System.Data.SqlClient library—and to a certain degree, OLE DB—can access UDTs.

Think about versioning and servicing before deployment. Visual Studio 2005 makes developing and deploying SQL-CLR extensions simple, but you have to plan for factoring, deployment, and versioning. Which objects will you deploy in an assembly? Assemblies are registered in one of three security buckets: SAFE, EXTERNAL_ACCESS, and UNSAFE. This security registration influences the way you factor your assemblies. Cross-assembly references and dependencies complicate your versioning and servicing work later, so keep it simple.

There's nothing wrong with T-SQL. Simply changing your application to use CLR procedures that perform only the basic T-SQL DML operations (INSERT, UPDATE, DELETE) doesn't add any value for the application or the database server. It also introduces more overhead and layers of complexity. Using T-SQL from the application tier is still the primary way of manipulating SQL Server data.