dbTalk Databases Forums  

Database Engine Tuning Advisor suggestion to replace syntax.

Go Back dbTalk Databases Forums Databases microsoft.public.sqlserver.tools Database Engine Tuning Advisor suggestion to replace syntax.

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


Discuss Database Engine Tuning Advisor suggestion to replace syntax. in the microsoft.public.sqlserver.tools forum.



Reply
Page 1 of 14 1 2 3 4 5 6 7 8 9 10 11 > Last »
 
Thread Tools Display Modes
  #1  
Old   
Mike
 
Posts: n/a

Default Database Engine Tuning Advisor suggestion to replace syntax. - 08-28-2008 , 11:20 AM





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


Reply With Quote
  #2  
Old   
Russell Fields
 
Posts: n/a

Default Re: Database Engine Tuning Advisor suggestion to replace syntax. - 08-28-2008 , 01:27 PM





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

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



Reply With Quote
  #3  
Old   
Russell Fields
 
Posts: n/a

Default Re: Database Engine Tuning Advisor suggestion to replace syntax. - 08-28-2008 , 01:27 PM


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

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



Reply With Quote
  #4  
Old   
Russell Fields
 
Posts: n/a

Default Re: Database Engine Tuning Advisor suggestion to replace syntax. - 08-28-2008 , 01:27 PM


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

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



Reply With Quote
  #5  
Old   
Russell Fields
 
Posts: n/a

Default Re: Database Engine Tuning Advisor suggestion to replace syntax. - 08-28-2008 , 01:27 PM


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

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



Reply With Quote
  #6  
Old   
Russell Fields
 
Posts: n/a

Default Re: Database Engine Tuning Advisor suggestion to replace syntax. - 08-28-2008 , 01:27 PM


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

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



Reply With Quote
  #7  
Old   
Russell Fields
 
Posts: n/a

Default Re: Database Engine Tuning Advisor suggestion to replace syntax. - 08-28-2008 , 01:27 PM


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

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



Reply With Quote
  #8  
Old   
Russell Fields
 
Posts: n/a

Default Re: Database Engine Tuning Advisor suggestion to replace syntax. - 08-28-2008 , 01:27 PM


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

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



Reply With Quote
  #9  
Old   
Russell Fields
 
Posts: n/a

Default Re: Database Engine Tuning Advisor suggestion to replace syntax. - 08-28-2008 , 01:27 PM


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

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



Reply With Quote
  #10  
Old   
Russell Fields
 
Posts: n/a

Default Re: Database Engine Tuning Advisor suggestion to replace syntax. - 08-28-2008 , 01:27 PM


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

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



Reply With Quote
Reply
Page 1 of 14 1 2 3 4 5 6 7 8 9 10 11 > Last »

« Previous Thread | Next Thread »



Thread Tools
Display Modes
Linear Mode Linear Mode

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts
vB code is On
Smilies are On
[IMG] code is On
HTML code is Off



Powered by vBulletin Version 3.5.3
Copyright ©2000 - 2012, Jelsoft Enterprises Ltd.


Contact Us - dbTalk Databases Forums - Archive - Top