Microsoft SQL Server: Stored Procedures Versus LINQ
A Stored Procedure is a subroutine comprising SQL statements that are held in a SQL Server database. The Stored Procedure can accept parameter values passed in, and can return values back from the database to the client.
As the Stored Procedure is fast, it is usual for a SQL Server database application to make use of many Stored Procedures.
The SQL language, developed in 1974, is showing its age. SQL has been continually extended. And as the SQL language has grown – so have the complexities.
The Advantages of Stored Procedures
There are many reasons for using Stored Procedures in a database:
- Shared application logic – functions can be re-used
- The logic need only be designed, coded, and tested once
- Reduced network traffic – the work is done on the Server
- Provides consistent logic across applications
- Can ensure data integrity and consistency and enforce database policy
The Disadvantages of Stored Procedures
There are also many reasons why Stored Procedures should not be used:
- The logic used to create Stored Procedures is crude and esoteric
- Stored Procedures are difficult to maintain
- The routines for debugging Stored Procedures are primitive
- There is a loss of control that only the constructs of Visual Basic can provide
- Visual Basic with ADO.Net provides better error handling
- Data validation is far easier to maintain with Visual Basic
- Stored Procedures provide minimal protection from SQL injection attacks
- Stored Procedures are not cached and are not pre-compiled
- Stored Procedures are no longer faster than dynamic SQL and not better optimised
- Business logic should be stored in code and not in the database
LINQ – Language Integrated Query
LINQ is integrated with Visual Basic and adds querying capabilities to many different data sources. The LINQ to SQL provider allows LINQ to be used to query SQL Server databases.
The LINQ functionality provides statement completion, auto correction and IntelliSense. LINQ enables compile-time validation of queries so that bugs are caught before code execution. Strongly typed data provides greater reliability, fewer mistakes, easier debugging and faster development. LINQ creates code that is easy to understand and maintain.
LINQ is a much better platform for database development than Stored Procedures. LINQ is a more productive querying language than SQL. As the LINQ technology matures, it will become more widely accepted. See The Wonderful World of LINQ
When to use Stored Procedures
Stored Procedures should be used with circumspection. Use Stored Procedures:
- To encapsulate complex Queries
- For data-intensive applications
- With Triggers to create Audit trails
- To provide a security layer
When a Stored Procedure already exists, it can be called from LINQ.
Do not use Stored Procedure for:
- Complex Business logic
- Standard Create, Read, Update and Delete (CRUD) procedures
There are no longer efficiency reasons for using Stored Procedures. They should be used minimally – rather than being the default option for most database activity.
Expect howls of derision and resistance from DBAs – their job security is being threatened by LINQ.