Results 1 to 5 of 5
  1. #1
    Join Date
    Nov 2004
    Location
    on the wrong server
    Posts
    8,835
    Provided Answers: 6

    Unanswered: Giant WTF: indexed views, very funny execution plans, null fie and unexpected results

    Environment: SQL 2008 comptibility level set to 80. Don't ask.

    I was aware that some times sql server will use an indexed view even when that view is not directly referenced, but the very presence of the indexed views appear to be causing bogus results.

    Query 1, 2 and 3 are very similar. The only diff between 1 and 2 is that 2 applies an extra filter to AddressID. Applying an extra filter Makes my data magically appear

    The diff between 1 and 3 is that I removed the "is null" where clauses. However the view and the tasble both contain null values for those fields.

    The indexed view is very similar to the base table except that it filters on countrycode.

    I am so confuzzled.

    query 1...

    Code:
    select addressid, StreetNum, StreetName, StreetType, City
    , State, zip, zip4, postcode, countrycode
     , StreetNumEndRange, buildingnumber
     , StreetSuffix, StreetDirection, CountryCode
    from Address a (nolocK)
    where --AddressID > 28487930 and
     a.StreetName = 'Fitch' and a.CountryCode = 'USA' 
     and a.StreetNum = '4234' 
     and a.StreetNumEndRange is null 
     and a.StreetType = 'Ave' 
     and a.StreetDirection is null 
     and a.StreetSuffix is null 
     and a.City like 'Nottingham%' 
     and a.State = 'MD' 
     and a.PostCode LIKE '212361222%' 
     and a.BuildingNumber is null
    0 rows returned

    execution plan 1

    Code:
    |--Nested Loops(Inner Join, OUTER REFERENCES:([Enterprise].[dbo].[vwIDXAddressUSA].[AddressID]) OPTIMIZED)
           |--Index Seek(OBJECT:([Enterprise].[dbo].[vwIDXAddressUSA].[VWndxStrtNumStrtNamStrtTypCityState]), SEEK:([Enterprise].[dbo].[vwIDXAddressUSA].[StreetNum]='4234' AND [Enterprise].[dbo].[vwIDXAddressUSA].[StreetName]='Fitch' AND [Enterprise].[dbo].[vwIDXAddressUSA].[StreetType]='Ave' AND [Enterprise].[dbo].[vwIDXAddressUSA].[City] IsNotNull),  WHERE:([Enterprise].[dbo].[vwIDXAddressUSA].[State]='MD' AND [Enterprise].[dbo].[vwIDXAddressUSA].[City] like 'Nottingham%') ORDERED FORWARD)
           |--Clustered Index Seek(OBJECT:([Enterprise].[dbo].[vwIDXAddressUSA].[ndxClust_vwIDXAddressUSA]), SEEK:([Enterprise].[dbo].[vwIDXAddressUSA].[AddressID]=[Enterprise].[dbo].[vwIDXAddressUSA].[AddressID]),  WHERE:([Enterprise].[dbo].[vwIDXAddressUSA].[StreetNumEndRange]=[Enterprise].[dbo].[vwIDXAddressUSA].[StreetDirection] AND [Enterprise].[dbo].[vwIDXAddressUSA].[StreetNumEndRange]=[Enterprise].[dbo].[vwIDXAddressUSA].[StreetSuffix] AND [Enterprise].[dbo].[vwIDXAddressUSA].[StreetNumEndRange]=[Enterprise].[dbo].[vwIDXAddressUSA].[BuildingNumber] AND [Enterprise].[dbo].[vwIDXAddressUSA].[StreetNumEndRange] IS NULL AND [Enterprise].[dbo].[vwIDXAddressUSA].[PostCode] IS NOT NULL AND [Enterprise].[dbo].[vwIDXAddressUSA].[PostCode] like '212361222%') LOOKUP ORDERED FORWARD)
    query 2

    Code:
    select addressid, StreetNum, StreetName, StreetType, City
    , State, zip, zip4, postcode, countrycode
     , StreetNumEndRange, buildingnumber
     , StreetSuffix, StreetDirection, CountryCode
    from Address a (nolocK)
    where AddressID > 28487930 and
     a.StreetName = 'Fitch' and a.CountryCode = 'USA' 
     and a.StreetNum = '4234' 
     and a.StreetNumEndRange is null 
     and a.StreetType = 'Ave' 
     and a.StreetDirection is null 
     and a.StreetSuffix is null 
     and a.City like 'Nottingham%' 
     and a.State = 'MD' 
     and a.PostCode LIKE '212361222%' 
     and a.BuildingNumber is null
    1 row returned for addressid 28487931 returned.

    Execution Plan 2

    Code:
    |--Clustered Index Seek(OBJECT:([Enterprise].[dbo].[Address].[PK_Address] AS [a]), SEEK:([a].[AddressID] > (28487930)),  WHERE:([Enterprise].[dbo].[Address].[StreetName] as [a].[StreetName]='Fitch' AND [Enterprise].[dbo].[Address].[CountryCode] as [a].[CountryCode]='USA' AND [Enterprise].[dbo].[Address].[StreetNum] as [a].[StreetNum]='4234' AND [Enterprise].[dbo].[Address].[StreetNumEndRange] as [a].[StreetNumEndRange] IS NULL AND [Enterprise].[dbo].[Address].[StreetType] as [a].[StreetType]='Ave' AND [Enterprise].[dbo].[Address].[StreetDirection] as [a].[StreetDirection] IS NULL AND [Enterprise].[dbo].[Address].[StreetSuffix] as [a].[StreetSuffix] IS NULL AND [Enterprise].[dbo].[Address].[State] as [a].[State]='MD' AND [Enterprise].[dbo].[Address].[BuildingNumber] as [a].[BuildingNumber] IS NULL AND [Enterprise].[dbo].[Address].[City] as [a].[City] like 'Nottingham%' AND [Enterprise].[dbo].[Address].[PostCode] as [a].[PostCode] like '212361222%') ORDERED FORWARD)
    query 3

    Code:
    select addressid, StreetNum, StreetName, StreetType, City
    , State, zip, zip4, postcode, countrycode
     , StreetNumEndRange, buildingnumber
     , StreetSuffix, StreetDirection, CountryCode
    from Address a (nolocK)
    where --AddressID > 28487930 and
     a.StreetName = 'Fitch' and a.CountryCode = 'USA' 
     and a.StreetNum = '4234' 
     --and a.StreetNumEndRange is null 
     and a.StreetType = 'Ave' 
     --and a.StreetDirection is null 
     --and a.StreetSuffix is null 
     and a.City like 'Nottingham%' 
     and a.State = 'MD' 
     and a.PostCode LIKE '212361222%' 
     --and a.BuildingNumber is null
    1 row returned for addressid 28487931.

    Plan 3
    Code:
      |--Nested Loops(Inner Join, OUTER REFERENCES:([Enterprise].[dbo].[vwIDXAddressUSA].[AddressID]) OPTIMIZED)
           |--Index Seek(OBJECT:([Enterprise].[dbo].[vwIDXAddressUSA].[VWndxStrtNumStrtNamStrtTypCityState]), SEEK:([Enterprise].[dbo].[vwIDXAddressUSA].[StreetNum]='4234' AND [Enterprise].[dbo].[vwIDXAddressUSA].[StreetName]='Fitch' AND [Enterprise].[dbo].[vwIDXAddressUSA].[StreetType]='Ave' AND [Enterprise].[dbo].[vwIDXAddressUSA].[City] IsNotNull),  WHERE:([Enterprise].[dbo].[vwIDXAddressUSA].[State]='MD' AND [Enterprise].[dbo].[vwIDXAddressUSA].[City] like 'Nottingham%') ORDERED FORWARD)
           |--Clustered Index Seek(OBJECT:([Enterprise].[dbo].[vwIDXAddressUSA].[ndxClust_vwIDXAddressUSA]), SEEK:([Enterprise].[dbo].[vwIDXAddressUSA].[AddressID]=[Enterprise].[dbo].[vwIDXAddressUSA].[AddressID]),  WHERE:([Enterprise].[dbo].[vwIDXAddressUSA].[PostCode] IS NOT NULL AND [Enterprise].[dbo].[vwIDXAddressUSA].[PostCode] like '212361222%') LOOKUP ORDERED FORWARD)
    1 row returned for addressid 28487931.
    “If one brings so much courage to this world the world has to kill them or break them, so of course it kills them. The world breaks every one and afterward many are strong at the broken places. But those that will not break it kills. It kills the very good and the very gentle and the very brave impartially. If you are none of these you can be sure it will kill you too but there will be no special hurry.” Earnest Hemingway, A Farewell To Arms.

  2. #2
    Join Date
    Nov 2004
    Location
    on the wrong server
    Posts
    8,835
    Provided Answers: 6
    FIX: You receive an incorrect result when you enable the ANSI_NULLS database option and run a query against an indexed view in SQL Server 2005 or in SQL Server 2008

    oh it worked in 2K, they broke it in 2005 and fixed it and broke it again in 2008 and fixed it again. WTF is going on in redmond?
    “If one brings so much courage to this world the world has to kill them or break them, so of course it kills them. The world breaks every one and afterward many are strong at the broken places. But those that will not break it kills. It kills the very good and the very gentle and the very brave impartially. If you are none of these you can be sure it will kill you too but there will be no special hurry.” Earnest Hemingway, A Farewell To Arms.

  3. #3
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11
    I know that in SQL 2005, there is a whole galaxy of set operations that need to be set not only for the connection querying the view, but for the connection that created the table as well. Can you have a look at the following columns in the column descriptions in the sp_help output for the Address table:

    Nullable TrimTrailingBlanks FixedLenNullInSource

    i will need to do some digging for the article that states what these should be, but I find it more alarming that the Address table is not being used directly. Although, I am assuming that the materialized view vwIDXAddressUSA joins a few other tables, and is not solely on the address table.

  4. #4
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    And the ANSWER is?

    Skip 2k5 entirley?
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  5. #5
    Join Date
    Nov 2004
    Location
    on the wrong server
    Posts
    8,835
    Provided Answers: 6
    Quote Originally Posted by MCrowley
    Nullable TrimTrailingBlanks FixedLenNullInSource.
    Yes No Yes for each of the columns getting the is Null treatment.

    we are running a mixture of 2000, 2005 and 2008. It's a mess. The mothership (my main responsibility) is 2K in production but I do not have a 2K server in Dev to test this out with to see if it is going to be an issue. The 2008 upgrade of the mothership has been on hold all year while the DBAs untangle the replication issues.
    “If one brings so much courage to this world the world has to kill them or break them, so of course it kills them. The world breaks every one and afterward many are strong at the broken places. But those that will not break it kills. It kills the very good and the very gentle and the very brave impartially. If you are none of these you can be sure it will kill you too but there will be no special hurry.” Earnest Hemingway, A Farewell To Arms.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •