CODEWITHSUNDEEP
t-sqlsql-server-2008-r2
Raj
Raj

Reputation: 10843

Effect of CONVERT_IMPLICIT

In the application which we are developing, there is a scenario where a stored procedure is called to claim a session. In a nut shell, this SP checks for a session based on the input parameter and deletes the session if (extract from specifications)

Here is the code to perform this task:

DELETE SESSIONS
WHERE SESSION_ID = @p_session_id
  AND EXPIRES > SYSUTCDATETIME();

The SESSION_ID column is the PK and has the default clustered index. The data type is uniqueidentifier. EXPIRES is of datatype datetimeoffset(0).

Here is the problem - SQL Server does a CONVERT_IMPLICIT and this causes an index scan. To add to my misery, the customer refuses to share meta-data about the DB and I have no clue how many rows of data the production version will have. The customer is of the opinion that the code needs to be as efficient as possible, no matter what the final DB size will be.

Is there any way to address this, apart from using CAST to do an explicit conversion of SYSUTCDATETIME() to datetimeoffset(0)?

Thanks,

Raj

Upvotes: 0

Views: 268

Answers (1)

Oleg Dok
Oleg Dok

Reputation: 21756

May be will be better:

DELETE SESSIONS
WHERE SESSION_ID = @p_session_id
  AND EXPIRES > SYSDATETIMEOFFSET();

Upvotes: 1

Related Questions