Effect of CONVERT_IMPLICIT

Question

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)

• Logic
• Delete the session row where the following conditions exist:
• The session row has not expired (EXPIRES > SYSUTCDATETIME()).

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

Answer 1

May be will be better:

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