Databases
StackOverflow2013
Postsdbo
PO
 

Note that there are some explanatory texts on larger screens.

plurals
  1. PO
    primarykey
    data
    text
    <p>You can't easily control the way SQL Server processes your query. You can figure out some of the why by deep diving into the execution plan, but understanding that is the least of your problems in this specific case I think. You can do a little with join hints, perhaps, but that's hacky to me and the behavior is still not guaranteed (especially as you move to new versions etc). Two workarounds you could try are:</p> <pre><code>;WITH c AS ( SELECT varcharColumn, ParentID, TypeId FROM dbo.Child AS c WHERE c.TypeId = 2 AND ISNUMERIC(varcharColumn) = 1 --* ) SELECT CONVERT(INT, c.varcharColumn) FROM dbo.Parent AS p INNER JOIN c ON c.ParentId = p.Id WHERE p.TypeId = 13; </code></pre> <p>But I have heard of cases where even separating this out into a CTE could lead to the bad plan that led the convert to occur first. So it may be that you need to break it out even further:</p> <pre><code>SELECT varcharColumn, ParentID, TypeId INTO #c FROM dbo.Child AS c WHERE c.TypeId = 2 AND ISNUMERIC(varcharColumn) = 1; --* SELECT CONVERT(INT, c.varcharColumn) FROM dbo.Parent AS p INNER JOIN #c AS c ON c.ParentId = p.Id WHERE p.TypeId = 13; </code></pre> <p>(I also talk about the <code>CASE</code> expression solution in <a href="https://stackoverflow.com/a/21316088/61305">this answer</a>.)</p> <p>If you are on SQL Server 2012, you can simply do this - now it doesn't matter if the convert is attempted before the filter, and you don't have to rely on the wonky <code>ISNUMERIC()</code> function.*</p> <pre><code>SELECT TRY_CONVERT(INT, c.varcharColumn) FROM dbo.Parent AS p INNER JOIN dbo.Child AS c ON c.ParentId = p.Id WHERE c.TypeId = 2 AND p.TypeId = 13; </code></pre> <p><code>*</code> Please note that IsNumeric is not perfect. I wrote this article several years ago to help deal with this: <a href="http://classicasp.aspfaq.com/general/what-is-wrong-with-isnumeric.html" rel="nofollow noreferrer">http://classicasp.aspfaq.com/general/what-is-wrong-with-isnumeric.html</a></p>
    singulars
    1. This table or related slice is empty.
    1. POMSSQL cast( [varcharColumn] to int) in SELECT gets executed before WHERE clause filters out bad values
      singulars
      1. PTQuestion
    1. PTAnswer
    1. USCommunity
    1. USAaron Bertrand
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. VO
      singulars
      1. PO
      1. This table or related slice is empty.
      1. VTUpMod
    1. COHey Aaron, We didn't try a cte. I'm not sure if that would work, but it's a good idea. We did try a sub query (within the from clause) as invertedSpear recommends below, and it still busted. What would be the performance implications of IsNumeric?
      singulars
      1. PO
      1. USChris Barry
    2. COPerformance implications are not what I'm worried about. It's that it will identify values that are numeric according to at least one numeric type, but can't be converted to an `INT` (e.g. `e` or `+`). So you may be better off doing something like `WHERE varcharColumn NOT LIKE '%[^0-9]%'` - don't quote me on that specific clause, that's from memory, but it should be something like that.
      singulars
      1. PO
      1. USAaron Bertrand
    3. COAlso if the subquery didn't force it the CTE will probably not force it either. Can you post the actual execution plan somewhere? Is it possible the optimizer is making a bad choice due to a bad plan or outdated statistics? One thing you might try is updating the statistics on the table and/or evicting this specific plan from cache.
      singulars
      1. PO
      1. USAaron Bertrand
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload