|The compiled query plan for a stored procedure has a slightly higher weighting than that for a parameterized ad-hoc query, so it will tend to stay around in memory for a little longer.
In security terms, you can GRANT access to EXEC a stored procedure without having to grant access to the tables that the stored procedure accesses. This allows you to control the entry points to your database. This is a defence-in-depth measure in case you happen to accidentally allow a SQL injection or disclose the credentials used to connect to the database, or they're compromised. Best practice is to only permit each user and/or application the rights they actually need to get their job done.
Don't go overboard, though. Some people are seduced by the idea of building a single stored procedure to handle querying a table or view with optional parameters (e.g. looking for books by title or by author). This often looks something like:
CREATE PROCEDURE SelectBooks
(title = @title OR @title IS NULL) AND
(author = @author OR @author IS NULL)They've found a syntactically valid solution, but it makes harder work for the optimizer. Further, the query plan gets compiled the first time the procedure is used, using the parameters supplied. If you specify
@title the first time round, leaving
@author set to
NULL, the optimizer might choose to seek through an index on
title. If you then call it with
@author rather than
@title, it may still try to use that index and fall back on a table scan (i.e. reading every row in the table), even if there was an index on
title that it could have used. In this case, I think parameterized queries are better.
I've just tried an equivalent query on SQL Server 2000 SP4, with no primary key on the table but with a separate single-column index on each of the columns in the query. The table has 240,000 rows. SQL Server elected to do an Index Scan (that is, reading the index from start to finish) on the
title column, whether you specified
author. If instead you run the parameterized query specifying only the columns you actually want to filter on, it correctly picks an Index Seek to the value you're after. I'm not yet sure about SQL Server 2005.
In fact if you specify
EXEC ... WITH RECOMPILE it's using the opposite index from the value you specify! Flushing the data from the cache using
DBCC DROPCLEANBUFFERS causes the query using the above stored procedure to take 46 seconds! The database is 160MB but my laptop, a Core 2 Duo T7200 [2GHz], has 2GB of RAM so if you don't run from cold, it's sub-second. The index scan is so fast, even from cold, that the Client Statistics view in Query Analyzer shows a time of 0.
-- modified at 16:17 Thursday 28th June, 2007