Tom, ya got lucky... as Sushila stated, most don't read down a thread this deep especially when Sushila and Mike are having a cyber reunion

Running out of numbers?  It can't be because of SQL... BIGINT is H-U-G-E!  You could just change the datatype of the column and, bingo, more numbers.

If the restriction is because...

BANG!   I'm dead.  You guys finally proved your point with some cold, hard facts.  I didn't like Carl's test the way it was because...

Greg Wrote: Go ahead and use your Scalar UDFs on large datasets, and one day if the set becomes large enough, you will be re-evaluating those UDFs.

Allow me to quote...

Here's the late night (quiet server) run times for 25K rows...

Formula in SELECT

(25000 row(s) affected)

00:00:00:080

------------------------------

Function in SELECT

(25000 row(s) affected)

00:00:00:157

------------------------------

Formula in WHERE

(25000 row(s) affected)

00:00:00:593

------------------------------

Function in WHERE

(25000 row(s) affected)

00:00:00:580

------------------------------

==============================

Function in SELECT

(25000 row(s)...

Ah... now I see ... it was 200k rows, not 2M rows...  too late at night... It does support what we said earlier......

No fair!   You used a real server with the Enterprise Edition... I used a pc with the Developer's Edition!

Well, they say one measurement is worth a thousand speculations.... so, I did one.  I'll have to eat a little crow on the "clearly they are not slower" thing but...

Harley,

That's an outstanding idea!  Thank you.  It's nice to see people treating SQL as a computer language instead of just a repository.  I do have a couple of suggestions, though...

1....

Serqiy,  that was my finding, as well.  I was being conservative considering the volitile subject. 

My thought is that properly written UDF's are much too valuable to simply write off...

Outstanding, Sergiy!

I ran the following and the proc that uses the DateOnly function doesn't take any longer than the other...

USE NORTHWIND

GO

SET STATISTICS TIME ON

GO

CREATE PROCEDURE dbo.ProcTest1 AS

SELECT dbo.DateONLY(ShippedDate)

 ...

>The conclusion I am reaching is why you should never, ever, under any circumstances use UDFs. e.g. DENY CREATE FUNCTION to public should be standard.

My experience is that UDF's are...

Charles,

Very nice.   And if you reverse the dates, the numbers make even more sense!  I added a couple more to show the trend...

 Previous Week: DateDiff(wk,getdate(),datefield) = -1

  Current Week:...

Not sure... I think this is what you want... make sure you have a primary key on your table and this should only take a couple of seconds to run. ...

Those are all great methods and I applaud the innovations of all... but as Remi G and Adam Mechanic would remind us, you might just want to think about finally...