Comparison of Dates in SQL

Question

Comparison of Dates in SQL

Viewing 6 posts - 106 through 111 (of 111 total)

You must be logged in to reply to this topic. Login to reply

Tao Klerks SSCarpal Tunnel Points: 4297 More actions · Answer 1

Lynn Pettis (5/1/2009)
Just for S & G's, try this with a CROSS APPLY:

Sorry, I'm in an all-2000 environment right now. Testing is just a question of copying Gail's code from the link above and adding an extra case, however, if you're interested...

http://poorsql.com for T-SQL formatting: free as in speech, free as in beer, free to run in SSMS or on your version control server - free however you want it.

Tao Klerks SSCarpal Tunnel Points: 4297 More actions · Answer 2

GilaMonster (5/1/2009)
Short answer. UDFs are not considered 'inline'. If they're run in a query, the run once for each row.
Long answer - http://sqlinthewild.co.za/index.php/2009/04/29/functions-io-statistics-and-the-execution-plan/

Do you have a ready blog article for every question ever raised?

I think I'm going to go print it all out and peruse over the weekend.

http://poorsql.com for T-SQL formatting: free as in speech, free as in beer, free to run in SSMS or on your version control server - free however you want it.

Paul White SSC Guru Points: 150468 More actions · Answer 3

Tao,

No; it just seems that way because the top N articles cover the top 80% of commonly-asked questions like this one about UDFs.

Paul

Paul White
All articles available on SQL.kiwi
@SQL_Kiwi

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 4

Tao Klerks (5/1/2009)
GilaMonster (5/1/2009)
Short answer. UDFs are not considered 'inline'. If they're run in a query, the run once for each row.
Long answer - http://sqlinthewild.co.za/index.php/2009/04/29/functions-io-statistics-and-the-execution-plan/
Do you have a ready blog article for every question ever raised?

I was wondering if you'd think that.

Actually it's the other way around. I use questions here as ideas for blogs and I've seen a lot of problems with UDFs over time. Half the reason I write blog entries is so that I have something I can direct people to instead of having to write a couple pages every time common questions come up.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 5

Okay, simple test, both returned the same actual execution plan.

Functions:

CREATE FUNCTION dbo.ufnDayOnly(@DateValue DateTime)

RETURNS table

AS

RETURN (select dateadd(dd, datediff(dd,0, @DateValue),0) as DateOnly )

GO

CREATE FUNCTION dbo.fn_DayOnly_DateTime (@DateValue DateTime)

RETURNS DateTime

WITH SCHEMABINDING

AS

BEGIN

RETURN dateadd(dd, datediff(dd,0, @DateValue),0)

END

GO

Test code:

create table dbo.LAPTest (

AccountID int,

Amount money,

Date datetime

);

go

create clustered index IX1_LAPTest on dbo.LAPTest (

AccountID asc,

Date asc

)

;

go

--===== Build the table 100 rows at a time to "mix things up"

DECLARE @Counter INT

SET @Counter = 0

WHILE @Counter < 1000000

BEGIN

--===== Add 100 rows to the test table

INSERT INTO dbo.LAPTest(

AccountID,

Amount,

Date)

SELECT TOP 100

AccountID =ABS(CHECKSUM(NEWID()))%50000+1,

Amount =CAST(CHECKSUM(NEWID())%10000 /100.0 AS MONEY),

Date = CAST(RAND(CHECKSUM(NEWID()))*3653.0+36524.0 AS DATETIME)

FROM

master.sys.columns t1

CROSS JOIN master.sys.columns t2

--===== Increment the counter

SET @Counter = @Counter + 100

END

;

go

select *, dbo.fn_DayOnly_DateTime (Date) from dbo.LAPTest;

select * from dbo.LAPTest cross apply dbo.ufnDayOnly(Date);

select *, dateadd(dd, datediff(dd,0, Date),0) as DateOnly from dbo.LAPTest;

go

drop table dbo.LAPTest;

go

Edit: Added a third query to the test code, but I haven't uploaded the third execution plan. Why, because it is identical to the other 2 I already uploaded.

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 6

I did a little more testing and wanted to post the results of the testing. These are in the same order as the queries in my previous post:

CpuMs LogRds Elapsed

9344 5164 29506

CpuMs LogRds Elapsed

844 5164 29276

CpuM LogRds Elapsed

921 5164 29339

Comparison of Dates in SQL

Cookies on SQLServerCentral