I have done it using DATETIME2(3)
.
As you can see on the query below, it is more economic
:
declare @dt1 datetime2(3)
declare @dt2 datetime2
SELECT @DT1 = SYSDATETIME()
SELECT @DT2= SYSDATETIME()
SELECT [THE LENGTH OF DATETIME2]=DATALENGTH(@DT2)
,[THE LENGTH OF DATETIME2(3)]=DATALENGTH(@DT1)

The differences between datetime
and datetime2
are well explained here.
For this exercise
I create a temp table for testing purposes, and populate it with 999 different random dates
from 01-jan-2019
and today (23-july-2019
)
and then in order, I set the milliseconds from 1 to 999
SET NOCOUNT ON
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
SET NOEXEC OFF
IF OBJECT_ID ('TEMPDB..#T1') IS NOT NULL
DROP TABLE #T1
CREATE TABLE #t1(the_date DATETIME2(3) NOT NULL PRIMARY KEY CLUSTERED )
GO
-- run this 999 times - hopefully there will be no duplicates
-- SELECT 204*24*60*60 - today is 23-july-2019 - the 203rd day of the year
DECLARE @DT DATETIME2(3)
SELECT @DT = CONVERT(DATETIME2(3),
DATEADD(SECOND, ABS(CHECKSUM(NEWID()) % 17625600),
'2019-01-01'),120)
--SELECT @DT
IF NOT EXISTS( SELECT 1 FROM #T1 WHERE THE_DATE = @DT)
INSERT INTO #T1 VALUES (@DT)
GO 999
--check it out what we have
SELECT * FROM #T1
--get the date and the new date
SELECT
THE_DATE
,THE_NEW_DATE= DATEADD(MILLISECOND, ROW_NUMBER() OVER (ORDER BY THE_DATE), THE_DATE )
FROM #T1
and this is what I get: (partial view)
