GetDate() or CURRENT_TIMESTAMP

Question

Post reply

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.753 getdate

2010-12-21 04:33:29.753 getdate

2010-12-21 04:33:29.753 getdate

2010-12-21 04:33:29.753 getdate

2010-12-21 04:33:29.753 getdate

2010-12-21 04:33:29.753 getdate

2010-12-21 04:33:29.760 getdate

2010-12-21 04:33:29.760 getdate

2010-12-21 04:33:29.760 getdate

2010-12-21 04:33:29.760 getdate

2010-12-21 04:33:29.763 ct

2010-12-21 04:33:29.763 ct

2010-12-21 04:33:29.763 ct

2010-12-21 04:33:29.763 ct

2010-12-21 04:33:29.763 ct

2010-12-21 04:33:29.763 ct

2010-12-21 04:33:29.763 ct

2010-12-21 04:33:29.763 ct

2010-12-21 04:33:29.763 ct

2010-12-21 04:33:29.763 ct

*/

-- 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: 150457 More actions · Answer 2

Paul White

SSC Guru

Points: 150457

December 21, 2010 at 2:21 am

#1264948

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

vinothraj

Ten Centuries

Points: 1349

December 21, 2010 at 2:35 am

#1264956

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: 150457 More actions · Answer 4

Paul White

SSC Guru

Points: 150457

December 21, 2010 at 2:40 am

#1264958

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

vinothraj

Ten Centuries

Points: 1349

December 21, 2010 at 2:45 am

#1264961

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: 47068 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: 150457 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: 150457 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: 1000539 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)
Intro to Tally Tables and Functions

vinothraj Ten Centuries Points: 1349 More actions · Answer 14

vinothraj

Ten Centuries

Points: 1349

December 22, 2010 at 7:24 pm

#1265759

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..