Home > SQL Server Tips > Stored Procedures > Q&A: Writing stored procedures in SQL Server 2005
SQL Server Tips:
EMAIL THIS
 TIPS & NEWSLETTERS TOPICS 

STORED PROCEDURES

Q&A: Writing stored procedures in SQL Server 2005


By Serdar Yegulalp
04.16.2007
Rating: -3.33- (out of 5)


Expert advice on database development
Digg This!    StumbleUpon Toolbar StumbleUpon    Bookmark with Delicious Del.icio.us    Add to Google


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.

  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
    More writing stored procedures in SQL Server:
  3. Stored procedures in SQL Server: A dozen must-have tips

  4. SQL Server change management tools
  5. 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!

  6. 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.

  7. 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.

  8. 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.




Digg This!    StumbleUpon Toolbar StumbleUpon    Bookmark with Delicious Del.icio.us    Add to Google


RELATED CONTENT
SQL Server stored procedures
Check SQL Server database and log file size with this stored procedure
SQL Server source code analysis and management adds database security
SQL and SQL Server Tutorial and Reference Guide
Configure SQL Server Service Broker for sending stored procedure data
Find size of SQL Server tables and other objects with stored procedure
Track changes to SQL Server 2000 and 2005 with one simple utility
Troubleshoot SQL Server 2005 temporary table performance problems
Use SQL Profiler to find long running stored procedures and commands
Stored procedure to monitor long-running jobs in SQL Server 2000
Using BULK INSERT to insert rows from SQL Server dataset to table

.NET development for SQL Server
Secure SQL Server from SQL injection attacks
Code to restore SQL Server databases in VB.NET
Custom VB.Net scripting in SQL Server Integration Services
Connect to SQL Server database with Visual Basics
SQL Server Blog Watch
Top 10 SQL Server development questions
Developing CLR database objects: 10 tips, 10 minutes
CLR architecture
CLR assemblies in SQL Server 2005
CLR stored procedures
.NET development for SQL Server Research

SQL Server 2005 (Yukon)
How to use rank function in SQL Server 2005
Manage traces in SQL Server 2005 Analysis Services with XMLA commands
Tutorial: SQL Server 2005 Analysis Services
Create a computed column in SQL Server using XML data
Open SSIS packages without validation using these SQL properties
Using the OUTPUT clause for practical SQL Server applications
Create DDL table in SQL Server 2005 to audit DDL trigger activity
How to process SQL Server 2005 Analysis Services for data availability
Configure SQL Server Service Broker for sending stored procedure data
SQL Server 2005 log shipping setup using the wizard
SQL Server 2005 (Yukon) Research

RELATED GLOSSARY TERMS
Terms from Whatis.com − the technology online dictionary
library  (SearchSQLServer.com)
trigger  (SearchSQLServer.com)

RELATED RESOURCES
2020software.com, trial software downloads for accounting software, ERP software, CRM software and business software systems
Search Bitpipe.com for the latest white papers and business webcasts
Whatis.com, the online computer dictionary

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.

HomeNewsTopicsITKnowledge ExchangeTipsAsk the ExpertsMultimediaWhite PapersIT Downloads
About Us  |  Contact Us  |  For Advertisers  |  For Business Partners  |  Site Index  |  RSS
SEARCH 
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 technology-specific Web sites, events and magazines.

TechTarget Corporate Web Site  |  Media Kits  |  Reprints  |  Site Map




All Rights Reserved, Copyright 2005 - 2008, TechTarget | Read our Privacy Policy
  TechTarget - The IT Media ROI Experts