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.
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
- The code is maintained in a central repository
- A Stored Procedure is an interface between the application and the database
- Reduced network traffic – the work is done on the Server
- Reduced overhead – a minimal number of fields can be consistently returned
- Database security with Access Control
- Provides consistent logic across applications
- Stored Procedures invoked from a database trigger can be powerful
- There is less likelihood of data corruption
- 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 by Stored Procedures is crude and esoteric
- Stored Procedures are difficult to create, debug, change and 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
- Source control is not available
- 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 in code and not in the database
LINQ – Language Integrated Query
LINQ is integrated with VB.Net 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 they already exist – 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 all database activity.
Expect howls of derision and resistance from DBAs – their job security is being threatened
by LINQ.