Is there any reason why I should stop using SYSDATETIME() everytime, instead of GETDATE() ?
Don't they both ask the cpu what time it is or does sysdatetime need more instructions to calculate the fractions? Does Getdate work on rounding it? Can sysdatetime be faster because it's not working on rounding?
I obviously wouldn't use sysdatetime if I'm not storing the nanoseconds, but I'm asking about the costs other than the storage size. (The current app I'm developing runs sysdatetime() at least 280 times a second)
SELECT SYSDATETIME();
GO
DECLARE @d DATETIME2(7) = SYSDATETIME();
GO 10000
SELECT SYSDATETIME();
GO
DECLARE @d DATETIME = SYSDATETIME();
GO 10000
SELECT SYSDATETIME();
GO
DECLARE @d DATETIME2(7) = GETDATE();
GO 10000
SELECT SYSDATETIME();
GO
DECLARE @d DATETIME = GETDATE();
GO 10000
SELECT SYSDATETIME();
Results:
- Assigning SYSDATETIME to DATETIME2(7) : 3.4 s
- Assigning SYSDATETIME to DATETIME : 3.3 s
- Assigning GETDATE to DATETIME2(7) : 3.4 s
- Assigning GETDATE to DATETIME : 3.3 s
So it appears to not matter. What matters is what type of variable you assign it to, and even that is not by much. 10000/0.1 seconds means the delta is very, very small and not enough to worry about. I would rather be consistent in this case.
In a few extermely poorly designed tests, on my machine it appears that SYSDATETIME() may be faster:
select sysdatetime()
go
declare @Dt datetime
select @dt = sysdatetime()
select @dt = DATEADD(day,1,@dt)
go 15000
select sysdatetime()
go
declare @Dt datetime
select @dt = GETDATE()
select @dt = DATEADD(day,1,@dt)
go 15000
select sysdatetime()
go
On my machine, this is tending to produce a gap of ~1 second between the first two result sets and ~2 seconds between the 2nd and 3rd. Reversing the order of the two tests reverses the gaps.
