[Don’t start the flames until you read the entire post :). ]
While we could go on with the various syntax elements in a SELECT statement, I doubt that it would be as helpful as the items we’ve already covered. Most of what I use on a day to day basis is some combination of what I’ve shown already.
Where SQL really starts getting fun though, is with stored procedures.
But why use Stored Procedures at all?
The historic answer to this question has always been, “speed.” Every time you send a query statement to the SQL server from your application, it has to re-compile it. There are ways to compensate for this, but the code you’d need to write to do this is almost harder than just writing the stored procedure to begin with.
However, now that we have LINQ, it is really hard to make the performance argument any more. In fact, the argument of the day is, “.NET Programmers don’t need to learn SQL, they should learn LINQ instead.” And, if all you ever write is a basic SELECT, INSERT, UPDATE, or DELETE statement, you’d be right. LINQ has all that optimization code built into it, so you won’t get any significant performance gains by moving to a stored procedure.
However, there are two reasons why I continue to argue for Stored Procedures over LINQ.
- LINQ does not currently support all of the SQL syntax. For example, there is no clean way to use the IN keyword with a Sub-Select like we discussed a couple of days ago in Finding IN a List.
- There is no clean way of having LINQ process multiple tables and return just the data we are looking for. Instead, you’d need to pull the data from the multiple tables, process the data in .NET and throw out the data you don’t need once you have your result.While that method works, it is sending more data “over the wire” than is necessary and is clearly a performance issue.
In short, the reason you want to use stored procedures is because you gain flexibility you would not otherwise have.
In the same way that using a three layer architecture gives you the flexibility and control you will need in your applications, placing your data retrieval in a stored procedure allows you to do things with SQL that you would not otherwise be able to do using LINQ.
This is not to say that I’m anti LINQ. It definitely allows me to do things in .NET that would otherwise require many more lines of code. I just don’t think it is the right tool for the job when it comes to retrieving data from your SQL database.
So, next week, we’ll start looking at the syntax for stored procedures and some of the things we can do in a stored procedure other than just wrapping our SELECT, INSERT, UPDATE, and DELETE statements.
Other post in SQL For Programmers
- SQL For Developers - 9 Reasons to bother - August 13th, 2008
- MSSQL CREATE and DROP Database - SQL for Programmers - August 20th, 2008
- MSSQL CREATE TABLE - SQL For Programmers - August 22nd, 2008
- SQL For Programmers - ALTERing the TABLE - September 1st, 2008
- SQL For Programmers - Finding IN a List - September 8th, 2008
- SQL For Programmers - Stored Procedure Basics - September 16th, 2008
- Basic SQL Commands - SQL For Programmers - September 18th, 2008
- SQL IF/WHILE Blocks - SQL For Programmers - September 30th, 2008
- Temporary Tables - SQL For Programmers - October 7th, 2008
- SQL CURSOR - SQL For Programmers - October 13th, 2008
- SQL CURSOR Performance - SQL For Programmers - October 22nd, 2008
- Random in SQL - SQL For Programmers - November 4th, 2008
- SQL - Filtering WHERE condition on two rows - November 26th, 2008
- SQL - Transactions - April 15th, 2009
- SQL For Programmers – New Question - July 6th, 2009
- SQL SELECT CASE Instead of IIF - October 20th, 2009
- SQL For Programmers - Stored Procedures (Better than LINQ) - August 28th, 2012
- SQL for .NET Programmers–The Book - July 29th, 2013
- SQL For Programmers - Finding a String - December 4th, 2013
- SQL WHILE - SQL For Programmers - February 12th, 2014