SQL Server Performance

Group by difference between SQL 2005 and 2000?

Discussion in 'SQL Server 2005 T-SQL Performance Tuning' started by amu_27, Feb 27, 2008.

  1. amu_27 New Member

    I came across situation where I want to make sure this is known.
    in SQL 2000 in group by clause I have to put the calculate field as its defined in Select to make it work i.e.
    select a.1, a.2 * (a.3 - a.4)
    from a
    group by a.1, a.2 * (a.3 - a.4)
    rather in SQL 2005 it is not necessary and just need the partial as well for grouping it
    select a.1, a.2 * (a.3 - a.4)
    from a
    group by a.1, a.3 - a.4
    now SQL 2005 select fails in SQL 2000. Is this new feature of SQL 2005?
  2. FrankKalis Moderator

    [quote user="amu_27"]
    I came across situation where I want to make sure this is known.
    in SQL 2000 in group by clause I have to put the calculate field as its defined in Select to make it work i.e.
    select a.1, a.2 * (a.3 - a.4)
    from a
    group by a.1, a.2 * (a.3 - a.4)
    rather in SQL 2005 it is not necessary and just need the partial as well for grouping it
    select a.1, a.2 * (a.3 - a.4)
    from a
    group by a.1, a.3 - a.4
    now SQL 2005 select fails in SQL 2000. Is this new feature of SQL 2005?
    [/quote]
    I can not reproduce your issue. Can you post a repro script?

    Your statement *SHOULD* fail on both versions as this is a grouping statement and columns in the SELECT list must also appear in the GROUP BY clause.
  3. Madhivanan Moderator

    Frank
    This works in 2005 and fails in 2000
    select a1, 2 * (a3 - a4)
    from
    (
    select 23 as a1, 56 as a2,8 as a3,6 as a4
    ) as a
    group by a1, a3 - a4
    I think in SQL Server a3-a4 becomes an expression or a literal value rather than a new column
  4. Adriaan New Member

    Not sure why SQL 2005 would accept this, but the query structure is a bit fuzzy to begin with ...
    * why not do the calculation inside the derived table?
    * why not use DISTINCT instead of GROUP BY?
    Also, you're not mentioning the table or alias for each column - there may be some ambiguity that we're not seeing in this simplified query.
  5. FrankKalis Moderator

    [quote user="Madhivanan"]
    Frank
    This works in 2005 and fails in 2000
    select a1, 2 * (a3 - a4)
    from
    (
    select 23 as a1, 56 as a2,8 as a3,6 as a4
    ) as a
    group by a1, a3 - a4
    I think in SQL Server a3-a4 becomes an expression or a literal value rather than a new column
    [/quote]
    Madhi,
    you're multiplying (a3-a4) by the constant 2. This will work. However if you change your expression 2 * (a3 - a4) to a2 * (a3 - a4) it will fail. [;)]
  6. Madhivanan Moderator

    Good point Frank [:)]

Share This Page