So the gauntlet has been thrown down by Sir Brent Ozar and his merry band of Supra-Genius Ultimate’s. Make a blog he says. Pick a topic he says. Well as a famous philosopher once said “Go do it right now. Seriously, you have nothing better to do. You’re reading my blog, for crying out loud.” Ok, it was that Brent guy again.

He’s right so here we go.  What to write about for the first blog…..??? I know, something near and dear to my pain. Implicit Conversions and SQL. Insert screaming at random intervals. So, what exactly are implcit conversions and why are they bad?

1) Per Microsoft – Implicit conversions are not visible to the user. SQL Server automatically converts the data from one data type to another. For example, when a smallint is compared to an int, the smallint is implicitly converted to int before the comparison proceeds.

2) Can result in increased CPU usage for query (bad).

3) Can cause the query to do a table scan instead of a seek (bad SQL, very bad).

4) Can lead to the DBA getting an increase in the number of “My app is running slow” calls from their clients.

Microsoft even has a URL with a nice little picture of all the data types and which ones can create implicit conversions when used together –https://msdn.microsoft.com/en-us/library/ms191530.aspx.

Next week, how to identify said implicit conversions……or maybe something else. Only the Shadow knows….I may know also.

UPDATE – Jonathan Kehayias of SQL Skills has an updated mapping of data types and what data type matchings cause either scans or seeks.  Please use this going forward, thanks you.

https://www.sqlskills.com/blogs/jonathan/implicit-conversions-that-cause-index-scans/