Could you provide comparison in solving SQL problems by using set-based vs. procedural SQL?
Q: 1) Could you provide comparison in solving SQL problems by using set-based vs. procedural SQL? Please provide comparison with sample codes in two different methods. 2) How do you prevent DB programmers from writing inefficient SQL procedures? Would you provide the optimal codes in services and allow them to call your data layer service by using web methods? Please give some coding examples for this question as well.
Kevin Kline says: Your questions indicate that you’re quite new to database programming. You might not realize it, but you’re asking questions whose answers, if done properly, fill entire books.
Your first question is easier to answer. SQL, by its very nature is set-based. Every time you execute a SELECT statement (as well as INSERT, UPDATE, and DELETE statemetns), you’re acting upon a set of records. Compare a standard SELECT statement to the procedural, a.k.a. row-by-row, approach of cursors. Cursors enable you to work on records in a SQL Server table on a row-by-row basis. Cursors are a big topic, so read about them at http://www.databasejournal.com/features/mssql/article.php/1439731 for a nice little summary. But you sacrifice performance, memory space, and lock resources by doing so. Read about problems caused by cursors at http://www.sql-server-performance.com/dp_no_cursors.asp
Your second question is much more expansive and difficult to answer because there are a variety of ways to improve bad stored procedures. Rather than recount them all here, why don’t you check out the great resources for stored procedure performance tuning at SQL Server Performance.com – http://www.sql-server-performance.com/stored_procedures.asp
There are lots of examples in all of the URLs listed. I hope this helps.
