 |
|
| SQL Server Tips: |
|
|
|
DATABASE DEVELOPMENT
Q&A: Writing stored procedures in SQL Server 2005
By Serdar Yegulalp
04.16.2007
Rating: -2.92- (out of 5)
|
In SQL Server 2000, there was really only one way to create a stored 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.
- 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.
- 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!
- 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.
- 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 ...
To continue reading for free, register below or login
To read more you must become a member of SearchSQLServer.com
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.
- 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.
ABOUT THE AUTHOR:
Serdar Yegulalp has been writing about Windows and related technologies for over 10 years, and is a regular contributor to various sections of TechTarget as well as other publications. He hosts the Web site WindowsInsider.com, where he posts regularly about Windows and has an ongoing feature guide to Vista for emigrants from Windows XP.
Copyright 2007 TechTarget
|
|
|
|
|
|
|
|
|
| TechTarget provides technology professionals with the information they need to perform their jobs - from developing strategy, to making cost-effective purchase decisions and managing their organizations' technology projects - with its network of . |
|
| |
All Rights Reserved, , TechTarget |
|
|
|
|
|
