Home Forums SQL Server 2008 SQL Server 2008 - General Understanding the difference between Join and Where filters - SQL 2008R2 RE: Understanding the difference between Join and Where filters - SQL 2008R2

  • September 26, 2012 at 8:39 am

    #1542462

    GilaMonster (9/26/2012)

    Yes, those two will be identical.

    While the string manipulation's in the select, that derived column is used in the where, so it's absolutely the same as having the string manipulation in the where.

    Both of these, for example, are not SARGable and will simplify to the same query structure.

    SELECT <columns> FROM SomeTable

    WHERE Substring(SomeColumn,2,3) = 'abc'

    SELECT * FROM (

    SELECT <columns>, Substring(SomeColumn,2,3) as TrimmedString FROM SomeTable

    ) sub

    WHERE sub.TrimmedString = 'abc'

    Thanks for that explanation.

    Just thinking out loud...could there ever be a benefit to having a calculated column that converts any nulls to something non-null for queries/indexes? I suppose the best solution is to not allow nulls but I import lots of data where I'm stuck with what I get. Would it be better to convert the data to a blank or some other value during insert/update even if the value is truly an unknown? What other options are there for avoiding an IsNull conversion or a 'WHERE col = val [or/and] col [is/is not] null"...or is that even something to avoid?