By Guy


2008-08-18 19:54:39 8 Comments

What are the advantages/disadvantages of keeping SQL in your C# source code or in Stored Procs? I've been discussing this with a friend on an open source project that we're working on (C# ASP.NET Forum). At the moment, most of the database access is done by building the SQL inline in C# and calling to the SQL Server DB. So I'm trying to establish which, for this particular project, would be best.

So far I have:

Advantages for in Code:

  • Easier to maintain - don't need to run a SQL script to update queries
  • Easier to port to another DB - no procs to port

Advantages for Stored Procs:

  • Performance
  • Security

30 comments

@Daniel 1.618 2014-02-18 18:11:30

Nobody mentioned unit testing!

If you have a saveOrder method you can call several methods inside and create a unit test for each one of those but if you are only calling a store procedure there is no way to do that.

@Matthew Farwell 2008-08-18 20:35:25

Advantages for Stored procedures:

More easily code reviewed.

Less coupled, therefore more easily tested.

More easily tuned.

Performance is generally better, from the point of view of network traffic - if you have a cursor, or similar, then there aren't multiple trips to the database

You can protect access to the data more easily, remove direct access to the tables, enforce security through the procs - this also allows you to find relatively quickly any code that updates a table.

If there are other services involved (such as Reporting services), you may find it easier to store all of your logic in a stored procedure, rather than in code, and having to duplicate it

Disadvantages:

Harder to manage for the developers: version control of the scripts: does everyone have their own database, is the version control system integrated with the database and IDE?

@hajirazin 2013-06-27 13:18:07

Yes you can have a version control in Stored procedures and database in visual studio 2012 database project and tfs.

@Orion Edwards 2008-08-18 21:50:33

I am not a fan of stored procedures

Stored Procedures are MORE maintainable because: * You don't have to recompile your C# app whenever you want to change some SQL

You'll end up recompiling it anyway when datatypes change, or you want to return an extra column, or whatever. The number of times you can 'transparently' change the SQL out from underneath your app is pretty small on the whole

  • You end up reusing SQL code.

Programming languages, C# included, have this amazing thing, called a function. It means you can invoke the same block of code from multiple places! Amazing! You can then put the re-usable SQL code inside one of these, or if you want to get really high tech, you can use a library which does it for you. I believe they're called Object Relational Mappers, and are pretty common these days.

Code repetition is the worst thing you can do when you're trying to build a maintainable application!

Agreed, which is why storedprocs are a bad thing. It's much easier to refactor and decompose (break into smaller parts) code into functions than SQL into... blocks of SQL?

You have 4 webservers and a bunch of windows apps which use the same SQL code Now you realized there is a small problem with the SQl code so do you rather...... change the proc in 1 place or push the code to all the webservers, reinstall all the desktop apps(clickonce might help) on all the windows boxes

Why are your windows apps connecting directly to a central database? That seems like a HUGE security hole right there, and bottleneck as it rules out server-side caching. Shouldn't they be connecting via a web service or similar to your web servers?

So, push 1 new sproc, or 4 new webservers?

In this case it is easier to push one new sproc, but in my experience, 95% of 'pushed changes' affect the code and not the database. If you're pushing 20 things to the webservers that month, and 1 to the database, you hardly lose much if you instead push 21 things to the webservers, and zero to the database.

More easily code reviewed.

Can you explain how? I don't get this. Particularly seeing as the sprocs probably aren't in source control, and therefore can't be accessed via web-based SCM browsers and so on.

More cons:

Storedprocs live in the database, which appears to the outside world as a black box. Simple things like wanting to put them in source control becomes a nightmare.

There's also the issue of sheer effort. It might make sense to break everything down into a million tiers if you're trying to justify to your CEO why it just cost them 7 million dollars to build some forums, but otherwise creating a storedproc for every little thing is just extra donkeywork for no benefit.

@redej 2012-10-02 13:18:28

The biggest advantage of sproc in the place I work is that we have way less code to port to VB.NET (from VB6) when time comes. And it's WAY less code because we use sprocs for all our queries.

Also it helps a lot when we need to update the queries instead of updating the VB code, re-compile and re-install it on all computers.

@Bilal Khan 2011-10-30 15:16:30

Well obviously using stored procedures has several advantages over constructing SQL in code.

  1. Your code implementation and SQL become independent of each other.
  2. Code is easier to read.
  3. Write once use many times.
  4. Modify once
  5. No need to give internal details to the programmer about the database. etc , etc.

@holaSenor 2013-03-21 01:32:41

I've never been in a situation where having less info about a code problem or new feature has been of benefit to me, could you clarify number 5?

@Nicola Musatti 2011-08-19 14:43:55

One point I did not find in the other answers is the following:

If in your environment the database and its schema are the heart of the architecture and applications have a more satellite role then it may make sense to make heavier use of stored procedures, which may help provide a level base for all the applications that need to access the DB, and thus induce less code repetition (e.g. are you sure that all your DB accessing applications will always be written in C# or other .NET languages?).

If, on the other hand, the application has a more central role and the DB acts more as a backing store for the application, then it may be sensible to make less use of stored procedures and achieve reduced code repetition by providing a common persistence layer, possibly based on an ORM tool/framework.

In both cases it's important that the DB is not considered a convenient repository for stored procedures. Keep them in source files within a version control system and try and automate their deployment as much as possible (this is actually valid for all schema related artifacts).

@Gagnaire Eric 2011-03-24 10:43:50

A SQL stored proc doesn't increase the performance of the query

@Sandesh 2014-01-27 12:24:45

How it can be not ? Please Explain about this.

@TT. 2014-02-06 12:11:27

It will increase the performance if the SQL query can be compiled.

@yfeldblum 2010-11-06 06:38:16

Your programming language and application framework are likely:

  • high-level, especially as compared with SQL
  • easy to version and deploy via automated processes, especially as compared with SQL

If these two conditions are two, then skip the stored procedures.

@huo73 2008-10-23 13:27:55

In my opinion you can't vote for yes or no on this question. It totally depends on the design of your application.

I totally vote against the use of SPs in an 3-tier environment, where you have an application server in front. In this kind of environment your application server is there to run your business logic. If you additionally use SPs you start distributing your implementation of business logic all over your system and it will become very unclear who is responsible for what. Eventually you will end up with an application server that will basically do nothing but the following:

(Pseudocode)

Function createOrder(Order yourOrder) 
Begin
  Call SP_createOrder(yourOrder)
End

So in the end you have your middle tier running on this very cool 4 Server cluster each of them equipped with 16 CPUs and it will actually do nothing at all! What a waste!

If you have a fat gui client that directly connects to your DB or maybe even more applications it's a different story. In this situation SPs can serve as some sort of pseudo middle tier that decouples your application from the data model and offers a controllable access.

@Tom Anderson 2008-10-17 22:48:11

I am a huge supporter of code over SPROC's. The number one reasons is keeping the code tightly coupled, then a close second is the ease of source control without a lot of custom utilities to pull it in.

In our DAL if we have very complex SQL statements, we generally include them as resource files and update them as needed (this could be a separate assembly as well, and swapped out per db, etc...).

This keeps our code and our sql calls stored in the same version control, without "forgetting" to run some external applications for updating.

@craigmoliver 2011-08-17 03:38:55

What about when you "forget" to replicate changes to tables?

@Tom Anderson 2011-08-17 21:52:10

Process can solve deployment issues.

@SiN 2010-06-08 09:15:25

I'm not a big fan of stored procedures, but I use them in one condition:

When the query is pretty huge, it's better to store it in the database as a stored procedure instead of sending it from the code. That way, instead of sending huge ammounts of string characters from the application server to the database, only the "EXEC SPNAME" command will be sent.

This is overkill when the database server and the web server are not on the same network (For example, internet communication). And even if that's not the case, too much stress means a lot of wasted bandwith.

But man, they're so terrible to manage. I avoid them as much as I can.

@computinglife 2008-09-25 07:27:05

CON

I find that doing lots of processing inside stored procedures would make your DB server a single point of inflexibility, when it comes to scaling your act.

However doing all that crunching in your program as opposed to the sql-server, might allow you to scale more if you have multiple servers that runs your code. Of-course this does not apply to stored procs that only does the normal fetch or update but to ones that perform more processing like looping over datasets.

PROS

  1. Performance for what it may be worth (avoids query parsing by DB driver / plan recreation etc)
  2. Data manipulation is not embedded in the C/C++/C# code which means I have less low level code to look through. SQL is less verbose and easier to look through when listed separately.
  3. Due to the separation folks are able to find and reuse SQL code much easier.
  4. Its easier to change things when schema changes - you just have to give the same output to the code and it will work just fine
  5. Easier to port to a different database.
  6. I can list individual permissions on my stored procedures and control access at that level too.
  7. I can profile my data query/ persistence code separate from my data transformation code.
  8. I can implement changeable conditions in my stored procedure and it would be easy to customize at a customer site.
  9. It becomes easier to use some automated tools to convert my schema and statements together rather than when it is embedded inside my code where I would have to hunt them down.
  10. Ensuring best practices for data access is easier when you have all your data access code inside a single file - I can check for queries that access the non performant table or that which uses a higher level of serialization or select *'s in the code etc.
  11. It becomes easier to find schema changes / data manipulation logic changes when all of it is listed in one file.
  12. It becomes easier to do search and replace edits on SQL when they are in the same place e.g. change / add transaction isolation statements for all stored procs.
  13. I and the DBA guy find that having a separate SQL file is easier / convenient when the DBA has to review my SQL stuff.
  14. Lastly you don't have to worry about SQL injection attacks because some lazy member of your team did not use parametrized queries when using embedded sqls.

@Jiho Han 2010-02-27 18:08:05

Stored procedures can go out of sync between database and source control system more easily than code. The application code can too, but it's less likely when you have continuous integration.

Database being what it is, people inevitably make changes to production databases, just to get out of the woods for the moment. Then forget to sync it across the environments and source control system. Sooner or later, production db becomes the de facto record rather than the source control system - you get into a situation where you cannot remove any sprocs, because you don't know whether it's being used.

A good process should only allow changes to the production only through proper channels, so that you should be able to rebuild a database from scratch from the source control system (sans data). But I'm just saying just because it can be done and does get done - changes are made to production database at the heat of moment, between calls from yelling clients, managers breathing down your neck, etc.

Running ad-hoc queries is awkward with stored procedures - it's easier done with dynamic sql (or ORM), which may be the biggest drawback to using stored procedures for myself.

Stored procedures, on the other hand is nice in situations where you make a change but doesn't require re-deployment of app code. It also allows you to shape your data before sending it over the network where sql in code might have to make multiple calls to retrieve than shape it (although there are now ways to run multiple sql statements and return multiple result sets in a single "call", as in MARS in ADO.NET), resulting in probably more data travelling through your network.

I don't buy any other arguments regarding performance and security though. Either can be good or bad, and equally controlled.

@John Saunders 2010-02-27 18:11:03

Why isn't your stored procedure in source control? Why don't you have unit tests for your database? In other words, why is your database not part of your CI environment?

@JeffO 2009-06-15 13:56:21

Programmers want the code in their app. DBA's want it in the database.

If you have both, you can divide the work between the two by using stored procedures and the programmers don't have to worry about how all those tables join together etc. (Sorry, I know you want to be in control of everything.).

We have a 3rd party application that allows custom reports to be created on a View or Stored Procedure in the database. If I put all of my logic in the code in another application, I could not reuse it. If you are in a situation where you write all of the apps using the database, this isn't a problem.

@Justin 2009-06-08 19:38:31

For Microsoft SQL Server you should use stored procedures wherever possible to assist with execution plan caching and reuse. Why do you want to optimise plan re-use? Because the generation of execution plans is fairly expensive to do.

Although the caching and reuse of execution plans for ad-hoc queries has improved significantly in later editions of SQL server (especially 2005 and 2008) there are still far fewer issues with plan reuse when dealing with stored procedures than there are for ad-hoc queries. For example, SQL server will only re-use an execution plan if the plan text matches exactly - right down to comments and white space, for example, if each of the following lines of SQL were to be executed independently, none of them would use the same execution plan:

SELECT MyColumn FROM MyTable WHERE id = @id
select MyColumn from MyTable WHERE id = @id
SELECT MyColumn  FROM MyTable WHERE id = @id
SELECT MyColumn FROM MyTable WHERE id = @id -- "some comment"
SELECT MyColumn FROM MyTable WHERE id = @id -- "some other comment"

On top of this, if you don't explicitly specify the types of your parameters then there is a good chance that SQL Server might get it wrong, for example if you executed the above query with the input 4, then SQL Server will parametrise the query with @id as a SMALLINT (or possibly a TINYINT), and so if you then execute the same query with an @id of say 4000, SQL Server will parametrise it as an INT, and wont reuse the same cache.

I think there are also some other issues, and in honesty most of them can probably be worked around - especially with later editions of SQL Server, but stored procedures generally offer you more control.

@Neil 2009-06-08 18:49:02

Foot firmly in the "Stored Procs are bad for CRUD/business logic use" camp. I understand the need in reporting, data import, etc

Write up here...

@Rob Allen 2008-08-18 20:07:42

Advantages for in Code:

  • Easier to maintain - don't need to run a SQL script to update queries
  • Easier to port to another DB - no procs to port

Actually, I think you have that backwards. IMHO, SQL in code is pain to maintain because:

  • you end up repeating yourself in related code blocks
  • SQL isn't supported as a language in many IDE's so you have just a series of un-error checked strings performing tasks for you
  • changes in a data type, table name or constraint are far more prevalent than swapping out an entire databases for a new one
  • your level of difficulty increases as your query grows in complexity
  • and testing an inline query requires building the project

Think of Stored Procs as methods you call from the database object - they are much easier to reuse, there is only one place to edit and in the event that you do change DB providers, the changes happen in your Stored Procs and not in your code.

That said, the performance gains of stored procs is minimal as Stu said before me and you can't put a break point in a stored procedure (yet).

@kishore 2009-04-16 17:44:51

Pros to stored procedures 1). Improved security as the SQL in stored procedure is static in nature(Mostly). This will protect against SQL injection. 2). Reusability. If there is a need to return the same data for multiple applications/components, this may be a better choice instead of repeating the SQL statements. 3). Reduces calls between client and database server.

I am not sure about other databases but you can create stored procedures in host languages in db2 on mainframe which makes them very powerful.

@Theresa 2008-12-08 20:22:52

We use stored procedures with Oracle DB's where I work now. We also use Subversion. All the stored procedures are created as .pkb & .pks files and saved in Subversion. I've done in-line SQL before and it is a pain! I much prefer the way we do it here. Creating and testing new stored procedures is much easier than doing it in your code.

Theresa

@ObiWanKenobi 2008-10-27 14:12:58

My vote for stored procedures; as an abstraction layer close to the data, efficient with sets, reusable by many "clients" (client languages). The T-SQL language is a bit primitive (and I guess that's what most of the C# guys here at SO have been exposed to), but Oracle's PL/SQL is on par with any modern programming language.

As for version control, just put the stored procedure code in text files under version control, then run the scripts to create the procs in the database.

@Dave Sherohman 2008-10-23 14:30:45

I generally write OO code. I suspect that most of you probably do, too. In that context, it seems obvious to me that all of the business logic - including SQL queries - belongs in the class definitions. Splitting up the logic such that part of it resides in the object model and part is in the database is no better than putting business logic into the user interface.

Much has been said in earlier answers about the security benefits of stored procs. These fall into two broad categories:

1) Restricting direct access to the data. This definitely is important in some cases and, when you encounter one, then stored procs are pretty much your only option. In my experience, such cases are the exception rather than the rule, however.

2) SQL injection/parametrized queries. This objection is a red herring. Inline SQL - even dynamically-generated inline SQL - can be just as fully parametrized as any stored proc and it can be done just as easily in any modern language worth its salt. There is no advantage either way here. ("Lazy developers might not bother with using parameters" is not a valid objection. If you have developers on your team who prefer to just concatenate user data into their SQL instead of using parameters, you first try to educate them, then you fire them if that doesn't work, just like you would with developers who have any other bad, demonstrably detrimental habit.)

@Libor 2008-10-23 12:42:18

Preference of stored procedures because: - enable fix some data related issues in production while system is running (this is num. one for me) - clean contract definition between DB and program (clean separation of concerns) - better portability to different DB vendor (if written well than code change is usually only on SP side). - better positioned for performance tuning

Cons - problematic in case WHERE clause has great variation in used conditions and high performance is needed.

@Timothy Lee Russell 2008-10-02 21:12:19

I prefer to use an O/R Mapper such as LLBLGen Pro.

It gives you relatively painless database portability, allows you to write your database access code in the same language as your application using strongly-typed objects and, in my opinion, allows you more flexibility in how you work with the data that you pull back.

Actually, being able to work with strongly-typed objects is reason enough to use an O/R Mapper.

@HLGEM 2008-09-28 18:47:49

I'm firmly on the side of stored procs assuming you don't cheat and use dynamic SQL in the stored proc. First, using stored procs allows the dba to set permissions at the stored proc level and not the table level. This is critical not only to combating SQL injection attacts but towards preventing insiders from directly accessing the database and changing things. This is a way to help prevent fraud. No database that contains personal information (SSNs, Credit card numbers, etc) or that in anyway creates financial transactions should ever be accessed except through strored procedures. If you use any other method you are leaving your database wide open for individuals in the company to create fake financial transactions or steal data that can be used for identity theft.

Stored procs are also far easier to maintain and performance tune than SQL sent from the app. They also allow the dba a way to see what the impact of a database structural change will have on the way the data is accessed. I've never met a good dba who would allow dynamic access to the database.

@Constantin 2008-09-28 18:32:42

Smaller logs

Another minor pro for stored procedures that has not been mentioned: when it comes to SQL traffic, sp-based data access generates much less traffic. This becomes important when you monitor traffic for analysis and profiling - the logs will be much smaller and readable.

@MattMcKnight 2008-09-27 02:49:34

Stored procedures are the worst when they are used to stand in-between applications and the database. Many of the reasons for their use stated above are better handled by views.

The security argument is spurious. It just moves the security problem from the application to the database. Code is code. I have seen stored procedures that take in SQL from the applications and use it build queries that were subject to SQL injection attacks.

In general, they tend to create a rift between so-called database developers and so-called application developers. In reality, all of the code that is written is application code, it is only a difference of the execution context.

Using rich SQL generation libraries like LINQ, Rails ActiveRecord, or Hibernate/NHibernate makes development faster. Inserting stored procedures in the mix slows it down.

@HLGEM 2008-09-28 18:51:01

I have to strongly disagree with this statement. All code is not application code, stored procs are more secure if you do not allow dynamic sql (which you should not) because then you can set security at the sp level and not the table level. This is prevent fraud.

@Bryan 2008-09-25 08:00:41

I have yet to find a good way of easily maintaining stored procs in source control that makes it as seamless as the code base. It just doesn't happen. This alone makes putting the SQL in your code worthwhile for me. Performance differences are negligible on modern systems.

@Constantin 2008-09-28 18:24:42

What's so difficult about pressing "Generate Script" button and committing it to repository?

@HLGEM 2008-09-28 18:51:55

We have no problem keeping all stored procs in source control. Of course our dbas delete any that aren't in it.

@Tom H 2008-09-17 14:40:22

When it comes to security, stored procedures are much more secure. Some have argued that all access will be through the application anyway. The thing that many people are forgetting is that most security breaches come from inside a company. Think about how many developers know the "hidden" user name and password for your application?

Also, as MatthieuF pointed out, performance can be much improved due to fewer round trips between the application (whether it's on a desktop or web server) and the database server.

In my experience the abstraction of the data model through stored procedures also vastly improves maintainability. As someone who has had to maintain many databases in the past, it's such a relief when confronted with a required model change to be able to simply change a stored procedure or two and have the change be completely transparent to ALL outside applications. Many times your application isn't the only one pointed at a database - there are other applications, reporting solutions, etc. so tracking down all of those affected points can be a hassle with open access to the tables.

I'll also put checks in the plus column for putting the SQL programming in the hands of those who specialize in it, and for SPs making it much easier to isolate and test/optimize code.

The one downside that I see is that many languages don't allow the passing of table parameters, so passing an unknown number data values can be annoying, and some languages still can't handle retrieving multiple resultsets from a single stored procedure (although the latter doesn't make SPs any worse than inline SQL in that respect).

@Jiho Han 2010-02-27 04:01:36

When the model changes, usually the code needs to change as well regardless of whether one is using sprocs or dynamic sql. And once you create tables/schema how often do you change just the table/schema? Not often. Usually changes come from business where they need to add another column or another table, in which case, I doubt you can do without a code change.

@yukondude 2008-09-05 16:17:30

Something that I haven't seen mentioned thus far: the people who know the database best aren't always the people that write the application code. Stored procedures give the database folks a way to interface with programmers that don't really want to learn that much about SQL. Large--and especially legacy--databases aren't the easiest things to completely understand, so programmers might just prefer a simple interface that gives them what they need: let the DBAs figure out how to join the 17 tables to make that happen.

That being said, the languages used to write stored procedures (PL/SQL being a notorious example) are pretty brutal. They typically don't offer any of the niceties you'd see in today's popular imperative, OOP, or functional languages. Think COBOL.

So, stick to stored procedures that merely abstract away the relational details rather than those that contain business logic.

@ObiWanKenobi 2009-08-05 22:51:01

"The languages used to write stored procedures (PL/SQL being a notorious example) are pretty brutal [and] don't offer any of the niceties you'd see in today's popular languages." You need to re-read the PL/SQL docs (download.oracle.com/docs/cd/B28359_01/appdev.111/b28370/toc‌​.htm). PL/SQL has encapsulation using packages, OOP via object types, exception handling, dynamic execution of code, debuggers, profilers, etc., plus hundreds of standard, Oracle-supplied packages/libraries for doing everything from HTTP calls to encryption and regular expressions. PL/SQL has LOTS of niceties.

@Joel Hendrickson 2008-09-05 16:01:03

In some circumstances, dynamically created sql in code can have better performance than a stored proc. If you have created a stored proc (let's say sp_customersearch) that gets extremely complicated with dozens of parameters because it must be very flexible, you can probably generate a much simpler sql statement in code at runtime.

One could argue that this simply moves some processing from SQL to the web server, but in general that would be a good thing.

The other great thing about this technique is that if you're looking in SQL profiler you can see the query you generated and debug it much easier than seeing a stored proc call with 20 parameters come in.

@Brannon 2008-09-25 07:53:12

Not sure why this answer was voted down.. it's true that a smaller query can perform better. This has even been commented on by the SQL Server team.

Related Questions

Sponsored Content

22 Answered Questions

[SOLVED] Search text in stored procedure in SQL Server

7 Answered Questions

18 Answered Questions

[SOLVED] Function vs. Stored Procedure in SQL Server

5 Answered Questions

16 Answered Questions

[SOLVED] What is a stored procedure?

15 Answered Questions

[SOLVED] SQL Server - SELECT FROM stored procedure

6 Answered Questions

[SOLVED] T-SQL Cast versus Convert

16 Answered Questions

[SOLVED] What is "with (nolock)" in SQL Server?

  • 2009-03-26 17:13:35
  • Andy White
  • 607164 View
  • 559 Score
  • 16 Answer
  • Tags:   sql-server nolock

15 Answered Questions

[SOLVED] Java Programming - Where should SQL statements be stored?

  • 2009-11-02 15:24:32
  • Adrian
  • 23930 View
  • 99 Score
  • 15 Answer
  • Tags:   java sql

Sponsored Content