Instructions for writing SQL Server 2005 stored procedures

In this article, let's talk about five common questions about this new method of writing stored procedures. They deserve your first reading ...

In SQL Server 2000, there is only one way to write stored procedures: using T-SQL statements. Learning to write stored procedures in previous versions of SQL Server is a course. But SQL Server 2005 lets you use. NET language family (primarily vb.net and C #) to write stored procedures (as well as functions, triggers, and other things) is possible.

1. Why do I use CLR models to write stored procedures?

Mainly because of the data. The SQL CLR performs faster in some ways: string processing is much faster than in T-SQL, and there are many robust ways to handle errors. Also, if the stored procedure must interact with something other than the database (file system or Web Service), it is better to use the CLR SP because it is more convenient to use the CLR to handle these things.

2. What types of stored procedures benefit the most from the CLR?

Typically, the SP that performs heavy data-computing tasks can benefit from the CLR more than the SP that simply extracts the data. If you write a CLR SP that wraps only a complex SELECT statement, you may not see a noticeable performance boost, because SQL statements are validated once every time the SP executes in the CLR sp. In fact, such a CLR SP would be worse than select as the T-SQL SP performance.

A good rule of thumb would be to keep SQL as a traditional SP if there are only a few lines of SQL for the problem. If you use the CLR to manipulate large datasets, you can use the traditional SP to get this large dataset and invoke the traditional SP in the CLR sp. In this way, the traditional SP is precompiled and accelerated, and data transformations can be done in the CLR SP that facilitates data processing.

Note: This assumes that you want to perform these fine data transformations at the data layer rather than at the presentation level. Ideally, you need to make some of these decisions before you start coding.

3. Should I convert my existing stored procedures to the CLR model?

The simple answer is "whether this is going to have practical benefits." "One way to verify that this standard is met is to write a CLR implementation that is equivalent to an existing stored procedure and to test the two implementations with actual data." Leave the old stored procedure unless you can be sure that the new CLR implementation is fulfilling: (a) implemented according to plan and (b) provides performance benefits. The CLR, like everything else, is not a panacea.

4. Can you write common Language runtime stored procedures without using the development IDE?

Yes, you can do it using the C # compiler. But using Visual Studio or a similar IDE can make things easier, especially if you are completing a transformation for an enterprise or implementing a complete set of SPS.

5. Is it difficult to automate this conversion?

Obviously, you need the experience of using vb.net, C # one language. The real SQL commands are wrapped in CLR code, so once you have mastered the usage method, it is not difficult to rewrite the existing T-SQL code in the CLR. The real difficulty is learning how to use the language to optimize your current job, which cannot be clearly explained in a few words.