First, to observe the rounding that occurs when working with DATETIME values, run the following:

SET NOCOUNT ON

DECLARE @Tb TABLE (Dt DATETIME)

DECLARE @dt DATETIME, @Cnt INT, @I INT

SET @Cnt = 1 --Change this value and observe...

SET @dt = '2008-05-15 17:11:06.390'

SET @I = 0

WHILE @I < 10 --Change this value and observe as well...

BEGIN

INSERT @Tb

SELECT @dt

SET @I = @I + 1

SET @dt = DATEADD(ms, @Cnt, @dt)

END

SELECT (@Cnt * @I) as Dif_Expected, DATEDIFF(ms, MIN(Dt), MAX(Dt)) as Dif_Actual FROM @Tb

Notice that the DATETIME value never changes if we are just adding 1ms at a time. Think about this over 10,000 iterations and the difference between actual and expected becomes significant.

Looking at GETDATE(), it is very clearly returns a DATETIME value and therefore will be rounded. Consider the results of this:

SELECT CAST('2008-05-15 17:11:06.390' AS DATETIME) UNION ALL

SELECT CAST('2008-05-15 17:11:06.391' AS DATETIME) UNION ALL

SELECT CAST('2008-05-15 17:11:06.392' AS DATETIME) UNION ALL

SELECT CAST('2008-05-15 17:11:06.393' AS DATETIME) UNION ALL

SELECT CAST('2008-05-15 17:11:06.394' AS DATETIME) UNION ALL

SELECT CAST('2008-05-15 17:11:06.402' AS DATETIME)

Making sense?

Now, looking at the GETDATE() as a part of the inserts, let's consider the loop above. Using it, if we are to do 10,000 iterations with an addition of 1ms each, we would expect 10,000ms or 10sec. Would you be happy if it took SQL 10sec to insert 10,000 dates? Having tried the INSERT loops in previous posts on several SQL servers, the worst time I saw was about 400ms total execution. MUCH better than 10sec. Now consider if there was a 3ms gap on each one, waiting for 30sec! To see my point, run this:

SET NOCOUNT ON

SELECT GETDATE()

DECLARE @Tb TABLE (Dt DATETIME)

DECLARE @I INT

SET @I = 1

WHILE @I < 10000

BEGIN

INSERT @Tb

SELECT GETDATE()

SET @I = @I + 1

END

SELECT GETDATE()

SELECT Dt, COUNT(Dt) FROM @Tb GROUP BY Dt

I am sure you will see that while there are "gaps" in the GETDATE() values, they are not there from processing a single record, but from processing several hundred.

Hopefully all this is helping me get to my point, which is that we can't expect to see a perfect interval on DATETIME values while inserting. As has been already said, other things are happening on the server. SQL is coordinating events behind the scene maintaining performance and integrity for us. Like Steve said above, SQL isn't for real time capture. For that matter, Windows isn't a real time OS.

Hopefully all this makes sense and it isn't too badly formed as I am now running out the door! I'll look to edit any major mistakes later.:)