Yesterday I have written a very quick blog post on SQL SERVER – Difference Between GETDATE and SYSDATETIME and I got tremendous response for the same. I suggest you read that blog post before continuing with this blog post today. I had asked people to honestly take part and share their view about the above two system functions.
There are few emails as well as few comments on the blog post asking a question on how did I come to know the difference between the same. The answer is from real world issues. I was called in for performance tuning consultancy, where I was asked a very strange question by one developer. Here is the situation faced by him.
System had a single table with two different columns of datetime. One column was datelastmodified and the second column was datefirstmodified. One of the columns was DATETIME and the other was DATETIME2. Developer was populating each of them with SYSDATETIME. He assumed that the value inserted in the table will be the same. This table was only accessed by INSERT statement, and there were no updates done over it in application. One fine day, he ran distinct on both these columns and was surprised by the result. He always thought that both the tables will have the same data, but in fact, they had very different data.
He presented this scenario to me. I said this is not possible, but I had to agree with him when I looked at the resultset. Here is the simple script to demonstrate the problem he was facing. This is just a sample of the original table.
DECLARE @Intveral INT
SET @Intveral = 10000
CREATE TABLE #TimeTable (FirstDate DATETIME, LastDate DATETIME2)
WHILE (@Intveral > 0)
BEGIN
INSERT #TimeTable (FirstDate, LastDate)
VALUES (SYSDATETIME(), SYSDATETIME())
SET @Intveral = @Intveral - 1
END
GO
SELECT COUNT(DISTINCT FirstDate) D_GETDATE, COUNT(DISTINCT LastDate) D_SYSGETDATE
FROM #TimeTable
GO
SELECT DISTINCT a.FirstDate, b.LastDate
FROM #TimeTable a
INNER JOIN #TimeTable b ON a.FirstDate = b.LastDate
GO
SELECT *
FROM #TimeTable
GO
DROP TABLE #TimeTable
GO
Let us see the resultset.
You can clearly see from result that SYSDATETIME() does not populate the same value in both the fields. In fact, the value is either rounded down or rounded up in the field which is DATETIME. Eventhough we are populating the same value, the values are totally different in both the columns, thus resulting in the SELF JOIN fail and displaying of different DISTINCT values.
The best way is to use GETDATE() if you are using DATETIME, and SYSDATETIME() if you are using DATETIME2 to populate them with current date and time for accurately addressing the precision. As DATETIME2 is introduced in SQL Server 2008, the above script will only work with SQL Server 2008 and later versions.
I hope I have answered some of the questions asked yesterday.
Reference: Pinal Dave (http://www.SQLAuthority.com)
