GetDate() or CURRENT_TIMESTAMP

Question

GetDate() or CURRENT_TIMESTAMP

Forum Etiquette: How to post Reporting Services problems

[/url]

Forum Etiquette: How to post data/code on a forum to get the best help - by Jeff Moden
[/url]

How to Post Performance Problems - by Gail Shaw
[/url]

vinothraj Ten Centuries Points: 1349 More actions · Answer 1

Jeff Moden (12/20/2010)
vinothraj (12/20/2010)
Jeff Moden (12/17/2010)
vinothraj (12/16/2010)
I've checked elapsing time for each of those and got approximate time is less for current_timestamp than getdate().
Is current_timestamp better? i think so....
Please post the test code. Thanks.
Here you go!!!!!
Declare @i as int
set @i = 10
if object_id('tempdb..#date') is not null
drop table #date
Create Table #date (dt datetime, type varchar(30))
while (@i > 0)
Begin
insert into #date
select getdate(), 'getdate'
Set @i = @i -1
End
Set @i = 10
while (@i > 0)
Begin
insert into #date
select current_timestamp,'ct'
Set @i = @i -1
End
select * from #date
And Result:
/*
2010-12-21 04:33:29.753getdate
2010-12-21 04:33:29.753getdate
2010-12-21 04:33:29.753getdate
2010-12-21 04:33:29.753getdate
2010-12-21 04:33:29.753getdate
2010-12-21 04:33:29.753getdate
2010-12-21 04:33:29.760getdate
2010-12-21 04:33:29.760getdate
2010-12-21 04:33:29.760getdate
2010-12-21 04:33:29.760getdate
2010-12-21 04:33:29.763ct
2010-12-21 04:33:29.763ct
2010-12-21 04:33:29.763ct
2010-12-21 04:33:29.763ct
2010-12-21 04:33:29.763ct
2010-12-21 04:33:29.763ct
2010-12-21 04:33:29.763ct
2010-12-21 04:33:29.763ct
2010-12-21 04:33:29.763ct
2010-12-21 04:33:29.763ct
*/
-- In the result you can see fluctuations in getdate() where as you cant find in current_timestamp. :w00t:
And yet, I can get this...
dt type
----------------------- ------------------------------
2010-12-21 00:49:01.610 getdate
2010-12-21 00:49:01.610 getdate
2010-12-21 00:49:01.610 getdate
2010-12-21 00:49:01.610 getdate
2010-12-21 00:49:01.610 getdate
2010-12-21 00:49:01.610 getdate
2010-12-21 00:49:01.610 getdate
2010-12-21 00:49:01.610 getdate
2010-12-21 00:49:01.610 getdate
2010-12-21 00:49:01.613 getdate
2010-12-21 00:49:01.613 ct
2010-12-21 00:49:01.613 ct
2010-12-21 00:49:01.613 ct
2010-12-21 00:49:01.617 ct
2010-12-21 00:49:01.617 ct
2010-12-21 00:49:01.617 ct
2010-12-21 00:49:01.617 ct
2010-12-21 00:49:01.617 ct
2010-12-21 00:49:01.617 ct
2010-12-21 00:49:01.617 ct
(20 row(s) affected)
The problem with such a test is that there's no guarantee that you're starting at the beginning of a 3.3 millisecond time slice. Also, the word "getdate" is longer than the word "ct" and it takes longer to store so you've given "ct" an unfair advantage.
Let's try a slightly different test with SQL Profiler running...
--------------------------------------------------------------------------------------------
GO
--===== Test GETDATE() =====================================================================
DECLARE @Counter INT,
@Bitbucket DATETIME; --Used to take both the disk and the display out of the picture
SELECT @Counter = 10000000;
WHILE @Counter > 0
SELECT @Bitbucket = GETDATE(),
@Counter = @Counter - 1;
GO
--===== Test CURRENT_TIMESTAMP ==============================================================
DECLARE @Counter INT,
@Bitbucket DATETIME; --Used to take both the disk and the display out of the picture
SELECT @Counter = 10000000;
WHILE @Counter > 0
SELECT @Bitbucket = CURRENT_TIMESTAMP,
@Counter = @Counter - 1;
GO
Yep... you're seeing right. It tests each function 10 MILLION times each run and I did 3 runs. Each run takes about a minute on my 8 year old single CPU 1.8GHz machine. The code will likely run much faster on a newer machine.
Here are the results from SQL Profiler. Remember... 10 MILLION rows each. I don't know about you but I can't really see a difference there especially when each takes turns winning and losing. Then there's that insane rowcount I used in the test.

Thanks, i did checked with profiler as well. I could get time elapses more to getdate rather than current_timestamp.

getdate - 7/10

ct - 3/10

I dont know why its gets differs.. attached screenshot.. by the by, my machine is i5

Paul White SSC Guru Points: 150467 More actions · Answer 2

vinothraj (12/21/2010)
I dont know why its gets differs.. attached screenshot.. by the by, my machine is i5

Do us all a favour and learn how to cut the quoted text down a bit! 😛

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi

vinothraj Ten Centuries Points: 1349 More actions · Answer 3

SQLkiwi (12/21/2010)
vinothraj (12/21/2010)
I dont know why its gets differs.. attached screenshot.. by the by, my machine is i5
Do us all a favour and learn how to cut the quoted text down a bit! 😛

hmm.. Yep, but we may lose continuity.. so we have to keep long thread some time.. 😀

Paul White SSC Guru Points: 150467 More actions · Answer 4

vinothraj (12/21/2010)
hmm.. Yep, but we may lose continuity.. so we have to keep long thread some time.. 😀

Not that long. Be reasonable.

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi

vinothraj Ten Centuries Points: 1349 More actions · Answer 5

SQLkiwi (12/21/2010)
vinothraj (12/21/2010)
hmm.. Yep, but we may lose continuity.. so we have to keep long thread some time.. 😀
Not that long. Be reasonable.

Ok, i will remove some of the thread.. if its not totally important..

Dugi SSCoach Points: 17998 More actions · Answer 6

WayneS (12/16/2010)
I think Microsoft prefers getdate().
CREATE TABLE #test (RowID INT IDENTITY, MyDate DATETIME DEFAULT (CURRENT_TIMESTAMP));
SELECT definition
FROM tempdb.sys.default_constraints
WHERE parent_object_id = object_id('tempdb..#test');
returns:
definition
-----------
(getdate())

Interesting Info Wayne! - However, I'm using Getdate() ... from the early time that I started to use the SQL Server .....

============================================================
SELECT YOUR PROBLEM FROM SSC.com WHERE PROBLEM DESCRIPTION =
http://www.sqlservercentral.com/articles/Best+Practices/61537/[/url]

Dugi SSCoach Points: 17998 More actions · Answer 7

Jeff Moden (12/17/2010)
Heh... I use GETDATE() for several reasons but these are my favorites... It has fewer characters to type, I've used it forever, I don't believe in the myth of portability, and it ticks people like Celko off. 😛

Hey I vote for this also >>> "...fewer characters to type..."

:hehe:

============================================================
SELECT YOUR PROBLEM FROM SSC.com WHERE PROBLEM DESCRIPTION =
http://www.sqlservercentral.com/articles/Best+Practices/61537/[/url]

mister.magoo SSC-Forever Points: 47174 More actions · Answer 8

I also don't care that much about portability - whenever I write a query it is on SQL Server and AFAIK there is no plan to remove GETDATE().

I also don't care for the name CURRENT_TIMESTAMP - when I want to get a date, it makes sense to me to call GETDATE()....

Now, if I wanted the current timestamp I would surely call MIN_ACTIVE_ROWVERSION wouldn't I ?

MM

select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);

vinothraj Ten Centuries Points: 1349 More actions · Answer 9

mister.magoo (12/21/2010)
I also don't care that much about portability - whenever I write a query it is on SQL Server and AFAIK there is no plan to remove GETDATE().
I also don't care for the name CURRENT_TIMESTAMP - when I want to get a date, it makes sense to me to call GETDATE()....
Now, if I wanted the current timestamp I would surely call MIN_ACTIVE_ROWVERSION wouldn't I ?

Casting it as datetime, we are getting '1900-01-01 00:09:41.003'.. what is the use of this?

Paul White SSC Guru Points: 150467 More actions · Answer 10

vinothraj (12/21/2010)
Casting it as datetime, we are getting '1900-01-01 00:09:41.003'.. what is the use of this?

Mr Magoo was joking: in SQL Server timestamp is a mechanism for version-stamping rows. It has been renamed to rowversion in recent releases. So, the joke is that 'current timestamp' ought to return a rowversion value, not a date and time.

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi

vinothraj Ten Centuries Points: 1349 More actions · Answer 11

SQLkiwi (12/21/2010)
Mr Magoo was joking: in SQL Server timestamp is a mechanism for version-stamping rows. It has been renamed to rowversion in recent releases. So, the joke is that 'current timestamp' ought to return a rowversion value, not a date and time.

Could you explain me in detail? Quite stucking with "version-stamping rows".. Thanks for your help

Paul White SSC Guru Points: 150467 More actions · Answer 12

vinothraj (12/21/2010)
Could you explain me in detail? Quite stucking with "version-stamping rows".. Thanks for your help

Jokes very rarely benefit from being explained in detail, but take a look at the following:

http://msdn.microsoft.com/en-us/library/ms182776(v=sql.90).aspx

http://msdn.microsoft.com/en-us/library/ms182776(v=SQL.105).aspx

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi

Jeff Moden SSC Guru Points: 1004309 More actions · Answer 13

vinothraj (12/21/2010)
I dont know why its gets differs.. attached screenshot.. by the by, my machine is i5

Which code did you use for that test? If the answer is that you used the same code that you used before, the reason why it differs is because it takes more time to store the word "getdate" than it does to store the word "ct".

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

vinothraj Ten Centuries Points: 1349 More actions · Answer 14

SQLkiwi (12/21/2010)
Mr Magoo was joking: in SQL Server timestamp is a mechanism for version-stamping rows. It has been renamed to rowversion in recent releases. So, the joke is that 'current timestamp' ought to return a rowversion value, not a date and time.

Thanks, will check kiwi..

vinothraj Ten Centuries Points: 1349 More actions · Answer 15

Jeff Moden (12/22/2010)
Which code did you use for that test? If the answer is that you used the same code that you used before, the reason why it differs is because it takes more time to store the word "getdate" than it does to store the word "ct".

Just used gd and ct however.. Keen to know one more thing, getdate() is a method / function, but what is current_timestamp? want to know, whether getdate() calling current_timestamp or just building the current date time..