Post reply

Old SQL Code and Compatibility

  • January 30, 2014 at 11:24 am

    #287869

    We have a couple of databases running in 2000 compatibility code due to old style left & right joins such as

    [font="Courier New"]Select * from TableA, TableB

    Where TableA.MemID *= TableB.MemID[/font]

    I understand that 2012 does not have compatibility 2000, so we have to fix those types of joins before upgrading.

    Are old style inner joins confined to 2000 also ?

    [font="Courier New"]Where TableA.MemID = TableB.MemID[/font]

  • January 30, 2014 at 11:46 am

    #1684805

    Not yet, but I wouldn't continue to use them in current or future development.

    Luis C.
    General Disclaimer:
    Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

    How to post data/code on a forum to get the best help: Option 1 / Option 2

  • January 30, 2014 at 11:50 am

    #1684807

    We haven't written any code like that in a while. It is easy to search for '*=' and find the left joins. Not so easy to find the inner joins with just '='

  • January 30, 2014 at 1:05 pm

    #1684828

    homebrew01 (1/30/2014)

    We haven't written any code like that in a while. It is easy to search for '*=' and find the left joins. Not so easy to find the inner joins with just '='

    Old-style inner joins are likely to stick around for a while yet. We're using 2012 and I'm sure some of the system queries are written like this.

    [font="Arial"]Low-hanging fruit picker and defender of the moggies[/font]

    For better assistance in answering your questions, please read this[/url].

    Understanding and using APPLY, (I)[/url] and (II)[/url] Paul White[/url]

    Hidden RBAR: Triangular Joins[/url] / The "Numbers" or "Tally" Table: What it is and how it replaces a loop[/url] Jeff Moden[/url]

  • January 30, 2014 at 4:15 pm

    #1684882

    ChrisM@home (1/30/2014)

    homebrew01 (1/30/2014)

    We haven't written any code like that in a while. It is easy to search for '*=' and find the left joins. Not so easy to find the inner joins with just '='

    Old-style inner joins are likely to stick around for a while yet. We're using 2012 and I'm sure some of the system queries are written like this.

    I agree. Correlated subqueries would be a whole lot tougher to write. I've never tried it but I'm not sure an ANSI JOIN would actually work in a correlated subquery.

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • January 30, 2014 at 5:45 pm

    #1684901

    Jeff Moden (1/30/2014)

    ChrisM@home (1/30/2014)

    homebrew01 (1/30/2014)

    We haven't written any code like that in a while. It is easy to search for '*=' and find the left joins. Not so easy to find the inner joins with just '='

    Old-style inner joins are likely to stick around for a while yet. We're using 2012 and I'm sure some of the system queries are written like this.

    I agree. Correlated subqueries would be a whole lot tougher to write. I've never tried it but I'm not sure an ANSI JOIN would actually work in a correlated subquery.

    Careful Jeff - don't hurt yourself using big-boy terms like "correlated subquery"!! :hehe:

    See you next weekend in Cleveland buddy!!

    Best,
    Kevin G. Boles
    SQL Server Consultant
    SQL MVP 2007-2012
    TheSQLGuru on googles mail service

  • February 1, 2014 at 5:23 am

    #1685284

    ChrisM@home (1/30/2014)

    homebrew01 (1/30/2014)

    We haven't written any code like that in a while. It is easy to search for '*=' and find the left joins. Not so easy to find the inner joins with just '='

    Old-style inner joins are likely to stick around for a while yet. We're using 2012 and I'm sure some of the system queries are written like this.

    Could you or someone confirm that Old-style inner joins will work on 2012 ?? I only have 2008 & 2005 installed here. Do you get a syntax error if you parse code with *= in it ?

  • February 2, 2014 at 9:33 am

    #1685349

    SQL 2012 SP1 CU8 Dev edition, the first of these works fine, second throws the listed error:

    SELECT A.*, B.*

    FROM A, B

    WHERE A.A = B.a

    SELECT A.*, B.*

    FROM A, B

    WHERE A.A *= B.a

    Msg 102, Level 15, State 1, Line 3

    Incorrect syntax near '*='.

    Best,
    Kevin G. Boles
    SQL Server Consultant
    SQL MVP 2007-2012
    TheSQLGuru on googles mail service

  • February 2, 2014 at 10:46 am

    #1685352

    Thanks !

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply