none
Outer Join Conversion from Old ANSI

    Question

  • Hello friends,

    Can someone help me convert this to the new standard way of doing outer joins.  thanks.  QUERY BELOW

     

     

    FROM SQLAuditSpaceReport sr,
     

    SQLAuditSpaceReport sr1

    ,
     

    SQLAuditSpaceReport sr4

    ,

    SQLAuditSpaceReport sr12

     

    WHERE sr.ServerName = @ServerName

     

    AND convert(char(10),sr.CaptureDate,111) = convert(char(10),@todays_date,111)

     

     

    AND sr.ServerName *= sr1.ServerName

     

    AND sr.DatabaseName *= sr1.DatabaseName

     

    AND convert(char(10),sr1.CaptureDate,111) = convert(char(10),DATEADD(wk,-1,@todays_date),111)

     

    AND sr.ServerName *= sr4.ServerName

     

    AND sr.DatabaseName *= sr4.DatabaseName

     

    AND convert(char(10),sr4.CaptureDate,111) = convert(char(10),DATEADD(wk,-4,@todays_date),111)

     

    AND sr.ServerName *= sr12.ServerName

     

    AND sr.DatabaseName *= sr12.DatabaseName

     

    AND convert(char(10),sr12.CaptureDate,111) = convert(char(10),DATEADD(wk,-12,@todays_date),111)
    Thursday, January 05, 2012 4:47 PM

Answers

  • Your date conditions are non-sargable. Can you post the intent of this query in regards to dates? And also SQL Server version, as in SQL 2008 you can simply cast to DATE.
    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog

    Thursday, January 05, 2012 5:01 PM
  • When going from  SQL Server 2000 to SQL 2005 and converting old style outer joins to new style, the biggest problem we faces was ending up with de facto inner joins.  Note that the responses you received were all inner joins.  In this syntax:

    FROM SQLAuditSpaceReport sr,  
    SQLAuditSpaceReport sr1
    ,  
    SQLAuditSpaceReport sr4
    , 
    SQLAuditSpaceReport sr12
    
    WHERE sr.ServerName = @ServerName 
    AND convert(char(10),sr.CaptureDate,111) = convert(char(10),@todays_date,111) 
    AND sr.ServerName *= sr1.ServerName 
    AND sr.DatabaseName *= sr1.DatabaseName 
    AND convert(char(10),sr1.CaptureDate,111) = convert(char(10),DATEADD(wk,-1,@todays_date),111) 
    AND sr.ServerName *= sr4.ServerName 
    AND sr.DatabaseName *= sr4.DatabaseName 
    AND convert(char(10),sr4.CaptureDate,111) = convert(char(10),DATEADD(wk,-4,@todays_date),111) 
    AND sr.ServerName *= sr12.ServerName 
    AND sr.DatabaseName *= sr12.DatabaseName 
    AND convert(char(10),sr12.CaptureDate,111) = convert(char(10),DATEADD(wk,-12,@todays_date),111)
    
    
    

    The AND clauses do NOT mean the same thing as the AND clauses listed below:

    FROM SQLAuditSpaceReport sr 
    inner join SQLAuditSpaceReport sr1 on sr.ServerName = sr1.ServerName AND sr.DatabaseName = sr1.DatabaseName 
    inner join SQLAuditSpaceReport sr4 on sr.ServerName = sr4.ServerName and sr.DatabaseName = sr4.DatabaseName 
    inner join SQLAuditSpaceReport sr12 on sr.ServerName = sr12.ServerName AND sr.DatabaseName = sr12.DatabaseName 
    WHEREAND convert(char(10),sr1.CaptureDate,111) = convert(char(10),DATEADD(wk,-1,@todays_date),111) AND convert(char(10),sr4.CaptureDate,111) = convert(char(10),DATEADD(wk,-4,@todays_date),111)AND convert(char(10),sr12.CaptureDate,111) = convert(char(10),DATEADD(wk,-12,@todays_date),111)
    
    


    To get the AND clauses to mean the same thing, you will probably need to move the AND clause up with their associated ON conditions in the JOIN clause -- perhaps something like this (based on previous code posted by Badii:

    FROM SQLAuditSpaceReport sr 
    inner join SQLAuditSpaceReport sr1 on sr.ServerName = sr1.ServerName AND sr.DatabaseName = sr1.DatabaseName 
      AND sr1.CaptureDate<Dateadd(day,1,convert(char(10),DATEADD(wk,-1,@todays_date),112))
      AND sr1.CaptureDate>=convert(char(10),DATEADD(wk,-1,@todays_date),112) 
    inner join SQLAuditSpaceReport sr4 on sr.ServerName = sr4.ServerName and sr.DatabaseName = sr4.DatabaseName 
      AND sr4.CaptureDate>=convert(char(10),DATEADD(wk,-4,@todays_date),112) 
      AND sr4.CaptureDate<Dateadd(day,1,convert(char(10),DATEADD(wk,-4,@todays_date),112))
    inner join SQLAuditSpaceReport sr12 on sr.ServerName = sr12.ServerName AND sr.DatabaseName = sr12.DatabaseName 
      AND sr12.CaptureDate>=convert(char(10),DATEADD(wk,-12,@todays_date),112)
      AND sr12.CaptureDate<Dateadd(day,1,convert(char(10),DATEADD(wk,-12,@todays_date),112))
    WHERE sr.ServerName = @ServerName 
    AND sr.CaptureDate>=convert(char(8),@todays_date,112)
    AND sr.CaptureDate<Dateadd(day,1,convert(char(8),@todays_date,112))
    


    ( well, maybe; maybe not )

    Here are some previous posts that dealt with de facto inner joins being spun when converting from old style outer join syntax to the newer style:

    De Facto Inner Joins from Old Style Outer Joins:

       http://social.msdn.microsoft.com/forums/en-US/transactsql/thread/13224212-d460-499e-bff9-b4d636e04fed/
          Rich Brown
          Kent Waldrop

       http://social.msdn.microsoft.com/Forums/en-US/transactsql/thread/9b93074c-119a-4bfb-9a3e-9467d106275d
          Plamen Ratchev

       http://social.msdn.microsoft.com/Forums/en-US/transactsql/thread/75212f7a-0a04-46c5-89f2-93632de87b52
          Kent Waldrop
          Phil Brammer
          Abdallah

       http://social.technet.microsoft.com/Forums/en-US/transactsql/thread/ebbff72c-9cbd-4f75-98b4-500c3a19eafe
          Naomi Nosonovsky
          Kent Waldrop

       http://social.technet.microsoft.com/Forums/en-US/transactsql/thread/5f0e8d3d-0fdb-4625-9772-36e359521a1c
          Umachandran Jayachandran

       http://social.technet.microsoft.com/Forums/en-US/transactsql/thread/972f9089-c3bb-439f-b4f1-00598aaef834
          ?


    Thursday, January 05, 2012 5:35 PM
  • We have a DATE data type today. And we have the CASE expression. Without any speCS or eve DDL(that was rude!) My guess is that you want so,mething like this: 
    SELECT capture_date, server_name, database_name, 
           CASE WHEN capture_date = DATEADD(wk, -1, @in_report_date) 
                THEN 1 ELSE 0 END AS wks_ago_1 
           CASE WHEN capture_date = DATEADD(wk, -4, @in_report_date) 
                THEN 1 ELSE 0 END AS wks_ago_4,
           CASE WHEN capture_date = DATEADD(wk, -12, @in_report_date) 
                THEN 1 ELSE 0 END AS wks_ago_12
      FROM SQL_Audit_Space_Report 
     WHERE server_name = @in_server_name
       AND capture_date = @in_report_date;
    As a general statement, re-write the old *= from scratch. It has a lot of side effects. 

    --CELKO-- Books in Celko Series for Morgan-Kaufmann Publishing: Analytics and OLAP in SQL / Data and Databases: Concepts in Practice Data / Measurements and Standards in SQL SQL for Smarties / SQL Programming Style / SQL Puzzles and Answers / Thinking in Sets / Trees and Hierarchies in SQL
    Thursday, January 05, 2012 8:03 PM

All replies

  • Hi, try :

    FROM SQLAuditSpaceReport sr 
    inner join SQLAuditSpaceReport sr1 on sr.ServerName = sr1.ServerName AND sr.DatabaseName = sr1.DatabaseName 
    inner join SQLAuditSpaceReport sr4 on sr.ServerName = sr4.ServerName and sr.DatabaseName = sr4.DatabaseName 
    inner join SQLAuditSpaceReport sr12 on sr.ServerName = sr12.ServerName AND sr.DatabaseName = sr12.DatabaseName 
    WHERE sr.ServerName = @ServerName 
    AND convert(char(10),sr.CaptureDate,111) = convert(char(10),@todays_date,111) 
    AND convert(char(10),sr1.CaptureDate,111) = convert(char(10),DATEADD(wk,-1,@todays_date),111) 
    AND convert(char(10),sr4.CaptureDate,111) = convert(char(10),DATEADD(wk,-4,@todays_date),111) 
    AND convert(char(10),sr12.CaptureDate,111) = convert(char(10),DATEADD(wk,-12,@todays_date),111)
    

     

     

    Best regards
    Thursday, January 05, 2012 4:55 PM
  • Your date conditions are non-sargable. Can you post the intent of this query in regards to dates? And also SQL Server version, as in SQL 2008 you can simply cast to DATE.
    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog

    Thursday, January 05, 2012 5:01 PM
  • Your date conditions are non-sargable. Can you post the intent of this query in regards to dates?
    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog


    Naomi is right, the condition are non-sargable, I will be like below :

    FROM SQLAuditSpaceReport sr 
    inner join SQLAuditSpaceReport sr1 on sr.ServerName = sr1.ServerName AND sr.DatabaseName = sr1.DatabaseName 
    inner join SQLAuditSpaceReport sr4 on sr.ServerName = sr4.ServerName and sr.DatabaseName = sr4.DatabaseName 
    inner join SQLAuditSpaceReport sr12 on sr.ServerName = sr12.ServerName AND sr.DatabaseName = sr12.DatabaseName 
    WHERE sr.ServerName = @ServerName 
    AND sr.CaptureDate>=convert(char(8),@todays_date,112)
    AND sr.CaptureDate<Dateadd(day,1,convert(char(8),@todays_date,112))
    AND sr1.CaptureDate>=convert(char(8),DATEADD(wk,-1,@todays_date),112) 
    AND sr1.CaptureDate<Dateadd(day,1,convert(char(8),DATEADD(wk,-1,@todays_date),112))
    AND sr4.CaptureDate>=convert(char(8),DATEADD(wk,-4,@todays_date),112) 
    AND sr4.CaptureDate<Dateadd(day,1,convert(char(8),DATEADD(wk,-4,@todays_date),112))
    AND sr12.CaptureDate>=convert(char(8),DATEADD(wk,-12,@todays_date),112)
    AND sr12.CaptureDate<Dateadd(day,1,convert(char(8),DATEADD(wk,-12,@todays_date),112))

     

     


    Best regards


    Thursday, January 05, 2012 5:19 PM
  • When going from  SQL Server 2000 to SQL 2005 and converting old style outer joins to new style, the biggest problem we faces was ending up with de facto inner joins.  Note that the responses you received were all inner joins.  In this syntax:

    FROM SQLAuditSpaceReport sr,  
    SQLAuditSpaceReport sr1
    ,  
    SQLAuditSpaceReport sr4
    , 
    SQLAuditSpaceReport sr12
    
    WHERE sr.ServerName = @ServerName 
    AND convert(char(10),sr.CaptureDate,111) = convert(char(10),@todays_date,111) 
    AND sr.ServerName *= sr1.ServerName 
    AND sr.DatabaseName *= sr1.DatabaseName 
    AND convert(char(10),sr1.CaptureDate,111) = convert(char(10),DATEADD(wk,-1,@todays_date),111) 
    AND sr.ServerName *= sr4.ServerName 
    AND sr.DatabaseName *= sr4.DatabaseName 
    AND convert(char(10),sr4.CaptureDate,111) = convert(char(10),DATEADD(wk,-4,@todays_date),111) 
    AND sr.ServerName *= sr12.ServerName 
    AND sr.DatabaseName *= sr12.DatabaseName 
    AND convert(char(10),sr12.CaptureDate,111) = convert(char(10),DATEADD(wk,-12,@todays_date),111)
    
    
    

    The AND clauses do NOT mean the same thing as the AND clauses listed below:

    FROM SQLAuditSpaceReport sr 
    inner join SQLAuditSpaceReport sr1 on sr.ServerName = sr1.ServerName AND sr.DatabaseName = sr1.DatabaseName 
    inner join SQLAuditSpaceReport sr4 on sr.ServerName = sr4.ServerName and sr.DatabaseName = sr4.DatabaseName 
    inner join SQLAuditSpaceReport sr12 on sr.ServerName = sr12.ServerName AND sr.DatabaseName = sr12.DatabaseName 
    WHEREAND convert(char(10),sr1.CaptureDate,111) = convert(char(10),DATEADD(wk,-1,@todays_date),111) AND convert(char(10),sr4.CaptureDate,111) = convert(char(10),DATEADD(wk,-4,@todays_date),111)AND convert(char(10),sr12.CaptureDate,111) = convert(char(10),DATEADD(wk,-12,@todays_date),111)
    
    


    To get the AND clauses to mean the same thing, you will probably need to move the AND clause up with their associated ON conditions in the JOIN clause -- perhaps something like this (based on previous code posted by Badii:

    FROM SQLAuditSpaceReport sr 
    inner join SQLAuditSpaceReport sr1 on sr.ServerName = sr1.ServerName AND sr.DatabaseName = sr1.DatabaseName 
      AND sr1.CaptureDate<Dateadd(day,1,convert(char(10),DATEADD(wk,-1,@todays_date),112))
      AND sr1.CaptureDate>=convert(char(10),DATEADD(wk,-1,@todays_date),112) 
    inner join SQLAuditSpaceReport sr4 on sr.ServerName = sr4.ServerName and sr.DatabaseName = sr4.DatabaseName 
      AND sr4.CaptureDate>=convert(char(10),DATEADD(wk,-4,@todays_date),112) 
      AND sr4.CaptureDate<Dateadd(day,1,convert(char(10),DATEADD(wk,-4,@todays_date),112))
    inner join SQLAuditSpaceReport sr12 on sr.ServerName = sr12.ServerName AND sr.DatabaseName = sr12.DatabaseName 
      AND sr12.CaptureDate>=convert(char(10),DATEADD(wk,-12,@todays_date),112)
      AND sr12.CaptureDate<Dateadd(day,1,convert(char(10),DATEADD(wk,-12,@todays_date),112))
    WHERE sr.ServerName = @ServerName 
    AND sr.CaptureDate>=convert(char(8),@todays_date,112)
    AND sr.CaptureDate<Dateadd(day,1,convert(char(8),@todays_date,112))
    


    ( well, maybe; maybe not )

    Here are some previous posts that dealt with de facto inner joins being spun when converting from old style outer join syntax to the newer style:

    De Facto Inner Joins from Old Style Outer Joins:

       http://social.msdn.microsoft.com/forums/en-US/transactsql/thread/13224212-d460-499e-bff9-b4d636e04fed/
          Rich Brown
          Kent Waldrop

       http://social.msdn.microsoft.com/Forums/en-US/transactsql/thread/9b93074c-119a-4bfb-9a3e-9467d106275d
          Plamen Ratchev

       http://social.msdn.microsoft.com/Forums/en-US/transactsql/thread/75212f7a-0a04-46c5-89f2-93632de87b52
          Kent Waldrop
          Phil Brammer
          Abdallah

       http://social.technet.microsoft.com/Forums/en-US/transactsql/thread/ebbff72c-9cbd-4f75-98b4-500c3a19eafe
          Naomi Nosonovsky
          Kent Waldrop

       http://social.technet.microsoft.com/Forums/en-US/transactsql/thread/5f0e8d3d-0fdb-4625-9772-36e359521a1c
          Umachandran Jayachandran

       http://social.technet.microsoft.com/Forums/en-US/transactsql/thread/972f9089-c3bb-439f-b4f1-00598aaef834
          ?


    Thursday, January 05, 2012 5:35 PM
  • We have a DATE data type today. And we have the CASE expression. Without any speCS or eve DDL(that was rude!) My guess is that you want so,mething like this: 
    SELECT capture_date, server_name, database_name, 
           CASE WHEN capture_date = DATEADD(wk, -1, @in_report_date) 
                THEN 1 ELSE 0 END AS wks_ago_1 
           CASE WHEN capture_date = DATEADD(wk, -4, @in_report_date) 
                THEN 1 ELSE 0 END AS wks_ago_4,
           CASE WHEN capture_date = DATEADD(wk, -12, @in_report_date) 
                THEN 1 ELSE 0 END AS wks_ago_12
      FROM SQL_Audit_Space_Report 
     WHERE server_name = @in_server_name
       AND capture_date = @in_report_date;
    As a general statement, re-write the old *= from scratch. It has a lot of side effects. 

    --CELKO-- Books in Celko Series for Morgan-Kaufmann Publishing: Analytics and OLAP in SQL / Data and Databases: Concepts in Practice Data / Measurements and Standards in SQL SQL for Smarties / SQL Programming Style / SQL Puzzles and Answers / Thinking in Sets / Trees and Hierarchies in SQL
    Thursday, January 05, 2012 8:03 PM