We had the same exact problem. It was a field that was varchar(5) compared to a numeric.

WHERE var5 = 9999 

The lookup was run 100s times a minute and after updating the SP, the CPU usage went down 10-15%

 ThomasLL

Nice tip.  I am working on something similar right now and your tip gives me something else to look for :)

BTW, How do you capture the Show Plan graphical output from the SSMS GUI?  I have been trying to do that.

To turn on the Graphical Plan you can use Ctrl-M in a query window.

Then in the Graphical Plan you can right click and select "Save Execution Plan As..." to save an XML representation of the query plan as a ".sqlplan" file.

The images in the tip are just using an image capture program.

You had to go to the query plan to check for data type mismatch.  Shouldn't this article be about how insidiously harmful Microsoft magical converts are (even worse than weak data typing!)  Or how your job is to use accurate, proper code?

I got another one for you.  If you're having problems moving your SQL from Microsoft Word to SQL Server, check to see if smart quotes is on.  (They aren't the same ascii or unicode characters.)

 I was testing this out and it doesnt seem to be true for the reverse. Meaning if your where clause is a string but the field is an int, it seems the implicit conversion does not cause a scan as it is doing a seek on my test data base. I wouldn't recommend writing queries like that, but this caught my eye as i had to write bad queries in a application i support as the application is 3rd party and i could only work within the codeset we have. Anyway my queries are implicitly converted but still doing seeks...YAY!

The issue is really in two parts.  First, the data type mismatch and second the QueryEngine not using an index in the query.

The datatype mismatch is simply a programmer error and it is very common.  Seems unfair to blame a tool for a programmer mistake.  We have dealt with data type mismatch issues since day one in this business.  Various languages adopt differnt rules.  For example Pascal is strongly typed and forces the programmer to explicitly convert.  On the other hand VBScript pretty much converts anything to anything.  BOL clearly outlines T-SQL rules for data type implicit conversions.  Obviously, if the tool is going to do an Implicit datatype conversion then a function call is going to be involved.

And that leads to the second issue.  When a function appears on the left side of the compare in Where clause, the Query Engine cannot use indexes in the plan.  This also well documented behavior.  In general, all terms on the left side of the compares in a where clause should be direct table(column) references that are not wrapped in function calls (eg. DateDiff, Convert, SubString, ...).

I've run across this also when issuing prepared statements.  For instance, the Microsoft JDBC driver will implicitly convert String data types to NVARCHAR. If you prepare a statement and the indexed column is defined as VARCHAR, you'll get a table scan.

[quote user="imSQrLy"]

 I was testing this out and it doesnt seem to be true for the reverse. Meaning if your where clause is a string but the field is an int, it seems the implicit conversion does not cause a scan as it is doing a seek on my test data base. I wouldn't recommend writing queries like that, but this caught my eye as i had to write bad queries in a application i support as the application is 3rd party and i could only work within the codeset we have. Anyway my queries are implicitly converted but still doing seeks...YAY!

[/quote]

 The performance drop doesn't occurs for the revrse because the Implcit conversion is then being performed only once and on an item that is not neing searched for or locate don an indexed.  If the item on the right side of the equals where a numeric value in a table (I.e.e TABLEA.VARCol1 = TABLEB.NUMCol2) then you;d see the same kind of thing. 

 The m,ain SQL DB we use in our accounting software packge is plagued with query's doing implict conversions because of improperly trained/skilled develpers employeed by the vendor who produces this acounting suite we use.  Luckily the implict conversions are most often the case that the value being searched is properly typed (as  a NUMERIC value) and the search value is listed as a string like this:

 TABLEA.NUMCol1 = '12345'

 Why they do this, even though they are not experienced in T-SQL or DB Logic/Set Logic in general is beyound me but I see it happening a lot when I trace the apps activity via Profiler.

 I understand now about what side of the =. That is why i do not have the issue in my tests. Thanks for the clarification.

MarDukes, You imply you are using MSWord as your SQL editor.  I am sure you are joking aren't you?

The analogy being the Microsoft magic.  Smart quotes "does you a favor" and ends up hurting; implicit converts "does you a favor" and ends up hurting.  Likewise, a scripter shouldn't use Word nor allow the mystery of not EXPLICITLY casting/converting values -- let alone not KNOW that it's happening.

And, yes, I do blame the tool -- TSQL is not some amatuer or web scripting tool.  It should error out for mismatched data types.  Nothing drives me more nuts than having people believe that "4/29/2009" is a date.  (FYI, it's a string!)  A misbelief fostered by implicit conversion.

Well, we can agree to disagree then. "Smart Quotes" are a feature in a Word Processor for document processing purposes.  If someone chooses to use the chisel as a drill then that is not the chisel's fault.  Implicit conversion is and always has been part of T-SQL.

Your position seems to be that comparing strings of different lengths or BigInts to Ints or Floats to Ints or Varchar to Char should yield an exception since Implicit Conversion is used in all of these cases.  That extention of strongly typed data rules would probably break more code than requiring "Option Strict" in every VB program :)

Implicit data conversions, coersions, etc. have been and will continue to be, part of the specifications for many programming environments.The strongly typed languages like Pascal quickly lost following, as strongly typed as C++, etc are there are execptions and folks still manange to create interesting bugs.  Free for all languages like Perl, C, and Basic have issues also.

So far no one has found the right way.  By which I mean that programmers can and will abuse any feature, cross any line, ignore any warning, blah blah.  I no longer argue with them about how "efficient" their coding practices are, particularly associated with database access.

[quote user="mardukes"]

The analogy being the Microsoft magic.  Smart quotes "does you a favor" and ends up hurting; implicit converts "does you a favor" and ends up hurting.  Likewise, a scripter shouldn't use Word nor allow the mystery of not EXPLICITLY casting/converting values -- let alone not KNOW that it's happening.

And, yes, I do blame the tool -- TSQL is not some amatuer or web scripting tool.  It should error out for mismatched data types.  Nothing drives me more nuts than having people believe that "4/29/2009" is a date.  (FYI, it's a string!)  A misbelief fostered by implicit conversion.

[/quote]

Mardukes -

I'm curious as to how you think a date should be displayed/written/shown within a T-SQL statement if not within a set of quotes?  How would you re-write the below so that the date does not include quotes?

SELECT T.title, T.PubDate, T.ytd_sales
FROM dbo.TITLES T
WHERE 1 = 1
  AND T.PubDate Between '1991-01-01' AND '1991-12-31'

...and t.PubDate BETWEEN CONVERT(datetime, '19910101', 112) AND CONVERT(datetime, '19920101', 112)

Note that I changed it to Jan 1, 1992 because what you wrote would miss anything on the last day of 1991 -- time of 00:00:000000.

Now anyone maintaining this code knows the stored data is a datetime and not a string.

 Wow, you sure know your sql.

[quote user="mardukes"]

...and t.PubDate BETWEEN CONVERT(datetime, '19910101', 112) AND CONVERT(datetime, '19920101', 112)

Note that I changed it to Jan 1, 1992 because what you wrote would miss anything on the last day of 1991 -- time of 00:00:000000.

Now anyone maintaining this code knows the stored data is a datetime and not a string.

[/quote]

 While you are technically correct with the date range, you missed the point.  But if you want to go that route then..

I have re-rewritten your query because it will include any data with a date in January 1st, 1992 and the query should return only that data that is within 1991.

SELECT T.title, T.PubDate, T.ytd_sales
FROM dbo.TITLES T
WHERE 1 = 1
  AND T.PubDate >= '1991-01-01'
  AND T.PubDate < '1992-01-01'

Now that we have that out of the way..

 While the explicit conversion does make it very clear what the query is looking for, comparison of a date & time value verses a string, it isn't necessary to do this because the column being searched (PubDate) is of DATETIME and so the query engine knows the value listed is a date and not a string.  You can perform the explcit conversion if you want to and there are times most certainly when a query should do this instead of using implict conversion logic but when working with datetime data types your just over complicating your query by doing this. 

Much to the contrary, you missed the point.

The point is not left side / right side but implicit conversion.  This is a technical topic and being "technically correct" is the requirement.  In terms of "proper practices" you've under-complicated the query and are doing a disservice to yourself and those that follow you in supporting the system.