I have a sproc that has these lines

DECLARE @newTimestamp datetime = CURRENT_TIMESTAMP
UPDATE  dbo.Accounts
SET     Modified = @newTimestamp

However, in the Accounts DB, we have this

[Modified]         DATETIME2 (7) NOT NULL,

And then whenever we do comparisons (particular for equality), we don't get the desired answers. We have worked around this by setting the compatibility level low enough (110) to not trip on this, but is there any easy way to find any instances where we're mismatching data types in our sprocs vs our tables?

I'd rather not have to write a parser, but it seems like if I had to, I'd trawl through every table definition and record the column types, then find every use of each column and try to determine if it's being set or compared to a variable declared as a different type.

Are there any tools out there that already do this, or is there a complaint somewhere in a system view or log?

I've found that Brent Ozar's sp_blitzcache can probably find this in the query plans, but there's no guarantee that the cache will have every instance of this for a given DB; I think I'd feel more comfortable with a static analysis of the code instead of a live look at a running SQL server. I've got access to (many) live copies of the DBs, as well as the source code for the SQL solution in Visual Studio.

Edit - not really relevant to my question, but I can give the background. When we have a datetime, it has 3 digits accuracy after the decimal point. When we have a datetime2, it has 7 digits accuracy. If you take a single timestamp, and try to compare a datetime and a datetime2 of the same moment, they won't be equal.

If we fix our compatibility level to 110, it ignores the extra accuracy of the datetime2, which is our current behavior.

  • Can edit your question to add some detail about whenever we do comparisons ... we don't get the desired answers and worked around this by setting the compatibility level low enough, perhaps with reproducible examples? Oct 5 '21 at 21:44
  • I've added that edit, for interesting background, but it doesn't really help me to answer my question. I've got a workaround, I want to find where the problems arise so we don't have to keep our compat level low.
    – mfinni
    Oct 5 '21 at 22:23
  • To be a bit more specific: datetime is only accurate to 3ms--a bit of an odd precision since it's not base-10. Meanwhile, datetime2 can be declared ranging from datetime2(0) (whole seconds) to datetime2(7) (⅒ of a microsecond... or 100 nanoseconds...Whatever that's called).
    – AMtwo
    Oct 5 '21 at 23:02

You can use the DMVs to view the query plan cache, and use XQuery on the plan's XML to filter for implicit conversion

WITH XMLNAMESPACES (DEFAULT 'http://schemas.microsoft.com/sqlserver/2004/07/showplan')
FROM sys.dm_exec_cached_plans AS cplan
CROSS APPLY sys.dm_exec_query_plan(plan_handle) AS qplan
WHERE qplan.query_plan.exist('//Convert[@Implicit="1"]') = 1;

You can do the same for Query Store, which will keep plans in between service restarts.

WITH XMLNAMESPACES (DEFAULT 'http://schemas.microsoft.com/sqlserver/2004/07/showplan')
FROM sys.query_store_plan AS cplan
CROSS APPLY (VALUES(CAST(query_plan AS xml))) v(query_plan)
WHERE v.query_plan.exist('//Convert[@Implicit="1"]') = 1;

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.