Problem solve Get help with specific problems with your technologies, process and projects.

Q&A: Writing stored procedures in SQL Server 2005

When writing stored procedures in SQL Server 2005, you have options beyond T-SQL. Contributor Serdar Yegulalp explains the benefits of writing stored procedures using CLR and how to convert existing stored procedures to the CLR model.

In SQL Server 2000, there was really only one way to create a stored

More tips on stored procedures for SQL Server:

 procedure: as a T-SQL statement. That was the course to take in every previous edition of SQL Server. But SQL Server 2005 makes it possible to write stored procedures (and functions, triggers and a number of other things) in the .NET family of languages -- mainly, VB.NET and C#. Take a look at these five common questions that come to mind concerning the new method of working with stored procedures. You'll see they're well worth talking about.

  1. Why should we write stored procedures using the CLR model?
    Speed, mainly. SQL CLR runs faster in a number of ways: string handling, for one, is much speedier than it is in T-SQL, and it has a far more robust way of handling errors. Also, any stored procedures that have to interact with something outside of the database -- the file system, for instance, or a Web service -- work best as a CLR SP, since the framework for doing that kind of thing is provided more elegantly by CLR.


  2. So what kinds of stored procedures benefit best from CLR?
    Generally, a SP that performs heavy computations on data instead of just pulling down data by itself benefits most from CLR. If you have a CLR SP that is simply a wrapper for a complicated SELECT statement, you may not see a significant performance gain because the SQL statement within the CLR has to be validated every time the SP is run. In fact, it may be markedly worse than just leaving the SELECT as a T-SQL SP.

    One good rule of thumb to use goes something like this: If the amount of SQL in question can't be passed in more than a few lines, keep the SQL in a conventional SP. If you want to do CLR-style manipulations on big datasets, you can obtain the big dataset from a conventional SP that's invoked from within a CLR SP. This way, the conventional SP is precompiled and it benefits from being accelerated in that fashion, and the data transformations can be done in a way that benefits them.

    Note: This assumes you want to perform such elaborate transformations on data in the data layer and not in the presentation layer. Ideally those are the kinds of decisions you need to make before you start writing any code!


  3. Should I convert my existing stored procedures to the CLR model?
    The easy answer is "Only if it yields any real benefit." One way to determine if this is the case is to create a parallel CLR-implemented version of a given stored procedure, then run tests on each using live data. Keep the old stored procedure running as-is until you can determine that the new one is: (a) running as intended and (b) actually provides some performance benefit. CLR, like anything else, is not a magic bullet.


  4. Is it possible to create Common Language Runtime stored procedures without a development IDE?
    Yes, you can do this sort of thing by hand with the C# compiler. But using Visual Studio or a similar IDE may make things a lot simpler, especially if you're working on translating or implementing a whole bunch of SPs in an enterprise setting.


  5. How tough is it to do the conversion itself?
    Obviously, you'll need working knowledge of one of the supported languages, VB.NET or C#. Actual SQL commands are "wrapped" in the CLR code, so once you get the hang of how to do it, it's not hard to rework existing T-SQL in CLR. What's tougher is learning how to use the language to optimize the kind of work you're doing, which is not something you can condense into a few tips.

Serdar Yegulalp
has been writing about computers and information technology for more than 15 years for a variety of publications, including InformationWeek and Windows Magazine.

Dig Deeper on Microsoft SQL Server 2005

Start the conversation

Send me notifications when other members comment.

Please create a username to comment.