Posted by Christian Ulrich on 10/19/07 10:40

Erland Sommarskog wrote:

>
> I assume that you use SQL 2000? The overhead for calling scalar UDFs is
> considerable in SQL 2000. This is better in SQL 2005, but note that you
> still should be careful with UDFs that perform data access.
>

No actualy it is 2005.

> In any case, rather than using a function, you can use a derived table:
>
> SELECT COUNT(*), TimeValue
> FROM (SELECT convert(char(8), DischargeEventTime, 112)
> FROM tblItemData
> WHERE DischargeEventTime BETWEEN '20070202' AND '20071002') AS c
> GROUP BY TimeValue
>
> Logically, a derived table is a temp table within the query, but the actual
> computation order is often different, as the optimizer considers the query
> as a whole.
>
Lession learned :-)

> Note also the format of the dates. Don't use YYYY-MM-DD, as this format
> is subject to different interpretation depending on language and datetime
> settings.

Yes dates are the problem child.


Thanks for your help and reply.

Best regards,
Christian

[Back to original message]