Re: Database Engine Tuning Advisor suggestion to replace syntax.
- From: "Russell Fields" <RussellFields@xxxxxxxxxx>
- Date: Thu, 28 Aug 2008 14:27:09 -0400
Mike,
Yes, they both optimize for reuse, but the first choice is dynamic SQL and
the second choice is executable TSQL.
This has an impact on security. For example, if this code is running in a
stored procedure, the user needs EXECUTE rights to the stored procedure.
However, Query 1 requires the user to have SELECT rights on the Customers
table, but Query2 does not need these extra rights, since the stored
procedure permission has the needed rights to do the SELECT for the user.
(And a best practice is (IMHO) to create stored procedures for all such
accesses.
Also, FWIW, the dynamic SQL is only syntax checked at run time, not when the
procedure is created.
Generally speaking, it is better to avoid dynamic SQL, but there are time
when it is the only choice. See:
http://www.sommarskog.se/dynamic_sql.html
RLF
"Mike" <mssql@xxxxxxxxxxxxx> wrote in message
news:D4F42EBA-FE7B-4DE3-B04E-B723C598E755@xxxxxxxxxxxxxxxx
SQL Server 2005 Database Engine Tuning Advisor informs me that I should
replace query 1 below with the syntax in query 2 below. I'm just not
seeing
the reason since both the "SELECT" statements optimization plans are
stored
for reuse. Am I missing something?
Mike.
Query 1
EXEC sp_executesql N'SELECT FirstName, LastName, Company, StreetAddress,
City, State, ZIP5, ZIP4
FROM Customers
WHERE City = @P1
AND State = @P2',
N'@P1 char(32), @P2 varchar(32)', 'Poedunk', 'Iowa'
Query 2
DECLARE @P1 varchar(32)
DECLARE @P2 varchar(32)
SET @P1 = 'Poedunk'
SET @P2 = 'Iowa'
SELECT FirstName, LastName, Company, StreetAddress, City, State, ZIP5,
ZIP4
FROM Customers
WHERE City = @P1
AND State = @P2
.
- References:
- Prev by Date: Database Engine Tuning Advisor suggestion to replace syntax.
- Next by Date: Re: Database Engine Tuning Advisor suggestion to replace syntax.
- Previous by thread: Database Engine Tuning Advisor suggestion to replace syntax.
- Next by thread: Re: Database Engine Tuning Advisor suggestion to replace syntax.
- Index(es):
Relevant Pages
|
