Can I write a custom aggregate function? something like bult in 'SUM' function.
The reason is  that I want to culculate a sum of integer column as bitmaps
when I have a 'group by' clause.

Another question. Which way is better to write SELECT statement with
multiple tables? Use 'inner join' for multiple tables or list them in 'where'
clause like 't1.id=t2.id AND t2.id=t3.id AND ...' .

Oleg,

  User-defined aggregates will be introduced in SQL Server 2005, and they
are not available in SQL Server 2000.  However, many kinds of aggregates
can be written in T-SQL with the available language features and existing
aggregates.  If you want help with your particular situation, post table
definitions,
data, and the result you want, along with a description.

  There are two ways to express the kind of query you mention:

select <columns>
from A, B, C, D
where <all conditions>

and

select <columns>
from A inner join B
on <conditions referring to A and/or B>
inner join C
on <conditions referring to A and/or B and/or C>
inner join D
on <conditions>
where <more conditions>

The main differerence is in readability.  The meaning of the longer
second version is the same as if all the conditions (both ON and WHERE)
were in a single WHERE clause as in the first version.  It is a little
harder to make mistakes with the second version, however (such as
accidentally writing a cross join between large tables).

Which you choose, and how you order the tables and conditions
can influence the set of query plans considered by the query optimizer,
but neither version can be called "better", since it depends on the
particular
query, tables, indexes, and data.  Otherwise, it is largely a matter of
taste.

Steve Kass
Drew University


Oleg wrote:

Show quoteHide quote

Yes and no. No the way you do an UDF yet.  But you can put scalar
expression inside aggregate functions and do a lot of work if you
remember your high school math.  Log() and exp() function are really
handy.  Google the product function in SQL or i can post details or you
can get a copy of SQL FOR SMARTIES for details.

Please, start a second question in a new thread.

The results are the same.  But maintaining them is very different.
Simple example:

SELECT ..
   FROM X, Y, Z
WHERE X.a BETWEEN Y.b  AND Z.c

Easy to read, isn't it?  Easy to maintain, too!  Okay now re-write it
in infixed notation and try to figure out the 3-ary relationship on
sight.  I cover a lot of this in SQL PROGRAMMING STYLE.

You MUST use the [LEFT | RIGHT | LEFT] OUTER JOIN syntax.

is there a set-based solution to this task?
Select Query
Multilanguage support
Combining Two Tables ?
SQL performance problem