Database Engine Tuning Advisor suggestion to replace syntax.

#11

Russell,

Thanks for the input. I understand what your says and that is part of the
reason I posted this question. This seems more of security issue than
performance.

Mike.

"Russell Fields" wrote:

Quote:

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 (AT) nospam (DOT) nospam> wrote in message
news

4F42EBA-FE7B-4DE3-B04E-B723C598E755 (AT) microsoft (DOT) com...
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

#12

Russell,

Thanks for the input. I understand what your says and that is part of the
reason I posted this question. This seems more of security issue than
performance.

Mike.

"Russell Fields" wrote:

Quote:

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 (AT) nospam (DOT) nospam> wrote in message
news

4F42EBA-FE7B-4DE3-B04E-B723C598E755 (AT) microsoft (DOT) com...
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

#13

Russell,

Thanks for the input. I understand what your says and that is part of the
reason I posted this question. This seems more of security issue than
performance.

Mike.

"Russell Fields" wrote:

Quote:

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 (AT) nospam (DOT) nospam> wrote in message
news

4F42EBA-FE7B-4DE3-B04E-B723C598E755 (AT) microsoft (DOT) com...
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

#14

Russell,

Thanks for the input. I understand what your says and that is part of the
reason I posted this question. This seems more of security issue than
performance.

Mike.

"Russell Fields" wrote:

Quote:

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 (AT) nospam (DOT) nospam> wrote in message
news

4F42EBA-FE7B-4DE3-B04E-B723C598E755 (AT) microsoft (DOT) com...
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

#15

Russell,

Thanks for the input. I understand what your says and that is part of the
reason I posted this question. This seems more of security issue than
performance.

Mike.

"Russell Fields" wrote:

Quote:

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 (AT) nospam (DOT) nospam> wrote in message
news

4F42EBA-FE7B-4DE3-B04E-B723C598E755 (AT) microsoft (DOT) com...
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

#16

Russell,

Thanks for the input. I understand what your says and that is part of the
reason I posted this question. This seems more of security issue than
performance.

Mike.

"Russell Fields" wrote:

Quote:

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 (AT) nospam (DOT) nospam> wrote in message
news

4F42EBA-FE7B-4DE3-B04E-B723C598E755 (AT) microsoft (DOT) com...
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

#17

Russell,

Thanks for the input. I understand what your says and that is part of the
reason I posted this question. This seems more of security issue than
performance.

Mike.

"Russell Fields" wrote:

Quote:

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 (AT) nospam (DOT) nospam> wrote in message
news

4F42EBA-FE7B-4DE3-B04E-B723C598E755 (AT) microsoft (DOT) com...
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

#18

Russell,

Thanks for the input. I understand what your says and that is part of the
reason I posted this question. This seems more of security issue than
performance.

Mike.

"Russell Fields" wrote:

Quote:

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 (AT) nospam (DOT) nospam> wrote in message
news

4F42EBA-FE7B-4DE3-B04E-B723C598E755 (AT) microsoft (DOT) com...
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

#19

Russell,

Thanks for the input. I understand what your says and that is part of the
reason I posted this question. This seems more of security issue than
performance.

Mike.

"Russell Fields" wrote:

Quote:

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 (AT) nospam (DOT) nospam> wrote in message
news

4F42EBA-FE7B-4DE3-B04E-B723C598E755 (AT) microsoft (DOT) com...
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

#20

Mike (mssql (AT) nospam (DOT) nospam) writes:

Quote:

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

Russell seemed to assumed that query 2 is a stored procedure. I may be
missing something, but I cannot see any SP.

And as a loose query batch, it's a poor choice. If you change the
parameter values, it will be a new query text, and there will be no
cache it. On top of that, since SQL Server does not know the parameter
values, it will not "sniff" the parameters on the first invocation but
make some standard assumption.

--
Erland Sommarskog, SQL Server MVP, esquel (AT) sommarskog (DOT) se

Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
SQL 2000: http://www.microsoft.com/sql/prodinf...ons/books.mspx

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

Database Engine Tuning Advisor suggestion to replace syntax.

microsoft.public.sqlserver.tools microsoft.public.sqlserver.tools

Re: Database Engine Tuning Advisor suggestion to replace syntax. - 08-28-2008 , 04:48 PM

Re: Database Engine Tuning Advisor suggestion to replace syntax. - 08-28-2008 , 04:48 PM

Re: Database Engine Tuning Advisor suggestion to replace syntax. - 08-28-2008 , 04:48 PM

Re: Database Engine Tuning Advisor suggestion to replace syntax. - 08-28-2008 , 04:48 PM

Re: Database Engine Tuning Advisor suggestion to replace syntax. - 08-28-2008 , 04:48 PM

Re: Database Engine Tuning Advisor suggestion to replace syntax. - 08-28-2008 , 04:48 PM

Re: Database Engine Tuning Advisor suggestion to replace syntax. - 08-28-2008 , 04:48 PM

Re: Database Engine Tuning Advisor suggestion to replace syntax. - 08-28-2008 , 04:48 PM

Re: Database Engine Tuning Advisor suggestion to replace syntax. - 08-28-2008 , 04:48 PM

Re: Database Engine Tuning Advisor suggestion to replace syntax. - 08-28-2008 , 06:19 PM