 |
|
| SQL Server Tips: |
|
|
|
STORED PROCEDURES
Q&A: Writing stored procedures in SQL Server 2005
By Serdar Yegulalp
04.16.2007
Rating: -3.33- (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 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
|
|
|
Rate this Tip
|
To rate tips, you must be a member of SearchSQLServer.com.
Register now
to start rating these tips. Log in if you are already a member.
|
DISCLAIMER: Our Tips Exchange is a forum for you to share technical advice and expertise with your peers and to learn from other enterprise IT professionals. TechTarget provides the infrastructure to facilitate this sharing of information. However, we cannot guarantee the accuracy or validity of the material submitted. You agree that your use of the Ask The Expert services and your reliance on any questions, answers, information or other materials received through this Web site is at your own risk.
|
|
|
|
|
|
|
|
|
|
| TechTarget provides enterprise IT professionals with the information they need to perform their jobs - from developing strategy, to making cost-effective IT purchase decisions and managing their organizations' IT projects - with its network of . |
|
| | |
All Rights Reserved, , TechTarget |
|
|
|
|
|
