Paramaterized query

Question

Paramaterized query

Viewing 5 posts - 16 through 19 (of 19 total)

You must be logged in to reply to this topic. Login to reply

onixsoft Mr or Mrs. 500 Points: 544 More actions · Answer 1

SQLkiwi (2/24/2011)
I think I have a handle on this now, but I just want to be sure you see a successful auto-param in all cases (index, no index) for this form of the query:
SELECT *
FROM SSNhistory1 AS SH
WHERE SH.IndId = '00000000-9C9A-4213-AF88-00069446D83B' + '';

Parametereization: Simple

With NONCLUSTERED INDEX

Auto-param with your query not work

Alex.

Paul White SSC Guru Points: 150467 More actions · Answer 2

Well, this is what I see:

USE Sandpit

ALTER DATABASE Sandpit

SET PARAMETERIZATION SIMPLE

;

SET ANSI_NULLS, ANSI_PADDING, ANSI_WARNINGS, ARITHABORT, CONCAT_NULL_YIELDS_NULL, QUOTED_IDENTIFIER ON

;

SET NUMERIC_ROUNDABORT OFF

;

DROP TABLE dbo.SSNhistory1

;

CREATE TABLE dbo.SSNhistory1

(

SSNHistoryID INTEGER IDENTITY(1,1) PRIMARY KEY,

IndID UNIQUEIDENTIFIER,

SSN CHAR(12) NOT NULL,

CreateDate DATETIME NOT NULL DEFAULT GETUTCDATE(),

UpdateUserID INTEGER NULL,

UpdateDate DATETIME NULL,

IsDeleted BIT NOT NULL DEFAULT 0,

SentDateToLoanTravel DATETIME NULL,

)

;

INSERT dbo.SSNhistory1

(

IndID, SSN, CreateDate, UpdateUserId, UpdateDate

)

VALUES (

{GUID '00000000-9C9A-4213-AF88-00069446D83B'},

'Some SSN',

GETDATE(),

1,

GETDATE()

)

;

CREATE NONCLUSTERED INDEX [IDX_SSNHistory_IndID1] ON [dbo].[SSNHistory1] ([IndID])

;

CHECKPOINT;

DBCC DROPCLEANBUFFERS;

DBCC FREEPROCCACHE

;

-- AUTO PARAM

-- [Sandpit].[dbo].[SSNhistory1].[IndID] as [SH].[IndID]=CONVERT_IMPLICIT(uniqueidentifier,[@1]+[@2],0)

SELECT *

FROM SSNhistory1 AS SH

WHERE SH.IndId = '00000000-9C9A-4213-AF88-00069446D83B' + ''

;

-- NO AUTO PARAM due to index hint

-- Prefix: [Sandpit].[dbo].[SSNhistory1].IndID = Scalar Operator({guid'00000000-9C9A-4213-AF88-00069446D83B'})

SELECT *

FROM SSNhistory1 AS SH WITH (INDEX([IDX_SSNHistory_IndID1]))

WHERE SH.IndId = '00000000-9C9A-4213-AF88-00069446D83B' + ''

;

SQL Server 2005 x64 Developer Edition build 9.0.5254

My conclusion is that parameterization (simple or forced) cannot be applied when constant-folding results in a uniqueidentiier literal in the form {guid'......'}. True on 2008 build 10.0.4272 as well. Probably a bug.

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi

onixsoft Mr or Mrs. 500 Points: 544 More actions · Answer 3

Auto-parameterization work properly with Parm=Forced, and NonClustered Index for both your queries

but when I back to Parm=Simple both not work. It seems previously nothing worked because my database was in the SQL2000 compatible mode. My SQL Server version is 2005 - 9.00.4053.00 (X64) .

So now I have 2 choices: change Prod database to compatible mode SQL2005 and Parameterization = Forced or replace most used queries to use Stored Procedures instead dynamic queries within ORMapper.

Thanks guys for all your help.

Alex.

Paul White SSC Guru Points: 150467 More actions · Answer 4

onixsoft (2/24/2011)
So now I have 2 choices: change Prod database to compatible mode SQL2005 and Parameterization = Forced or replace most used queries to use Stored Procedures instead dynamic queries within ORMapper.

Forced parameterization can have unexpected side effects. Do not enable it without thorough testing outside the production environment first. I would recommend the stored procedure approach.

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi

onixsoft Mr or Mrs. 500 Points: 544 More actions · Answer 5

SQLkiwi (2/24/2011)
onixsoft (2/24/2011)
So now I have 2 choices: change Prod database to compatible mode SQL2005 and Parameterization = Forced or replace most used queries to use Stored Procedures instead dynamic queries within ORMapper.
Forced parameterization can have unexpected side effects. Do not enable it without thorough testing outside the production environment first. I would recommend the stored procedure approach.

Thanks Paul, I will make many tests before any changes on the production server.

Alex.