Sql-server – What are some efficient ways in using COUNT(*) with WHERE

(re-wrote, misunderstood)

If you want the remote ID, then the best way is to use a stored procedure on the remote server and return a result set.

Or don't redirect into a temp table/table variable. This isn't in scope on the remote server

See these on SO:

• https://stackoverflow.com/questions/4537560/get-identity-from-another-serverlinked-server/4537600#4537600
• https://stackoverflow.com/questions/2621887/sql-server-identity-issue/2621940#2621940

GROUP BY A.* is not allowed in SQL.

You can bypass this by using a subquery where you group by, and then join:

SELECT A.*, COALESCE(B.cnt, 0) AS Count_B_Foo
FROM TABLE1 AS A
  LEFT JOIN 
      ( SELECT FKey, COUNT(foo) AS cnt
        FROM TABLE2 
        GROUP BY FKey
      ) AS B 
    ON A.PKey = B.FKey ;

There is a feature in SQL-2003 standard to allow in the SELECT list, columns that are not in the GROUP BY list, as long as they are functionally dependent on them. If that feature had been implemented in SQL-Server, your query could have been written as:

SELECT A.*, COUNT(B.foo)
FROM TABLE1 A
LEFT JOIN TABLE2 B ON A.PKey = B.FKey
GROUP BY A.pk                          --- the Primary Key of table A

Unfortunately, this feature has not yet been implemented, not even in SQL-Server 2012 version - and not in any other DBMS as far as I know. Except for MySQL which has it but inadequately (inadequately as: the above query will work but the engine will do no checking for functional dependency and other ill-written queries will show wrong, semi-random results).

As @Mark Byers informed us in a comment, PostgreSQL 9.1 added a new feature designed for this purpose. It is more restrictive than MySQL's implementation.