如何使用SQL生成DateTime.Ticks

在項目中我需要使用到一個隨機數（Random Number），該隨機數將作為 Hashtable 中的 Key 用於唯一索引數據，所以需要保持單機唯一性。

同時該隨機數還需要具備可排序性以便對數據進行排序。

此時，我可以考慮使用隨機字符串《C#生成MongoDB中的ObjectId》，這裡的 ObjectId 具備單機唯一性和可排序性。

但另一個需求是，我需要該隨機數為 int 或 long 類型，以便與依賴的 Service 進行通信，部分取代 TransactionId 或 Token 的職責。

所以，很自然的我就想到了時間。

查看了 MSDN 上關於 DateTime.MinValue 和 DateTime.Ticks 的定義，考慮到我所設計的服務每秒不會產生多於 10000000 的數據，

我決定使用 DateTime.Ticks 作為隨機數的起始點，這樣就有了下面這段代碼。

public static class UniqueId
  {
    private static long _sequence = DateTime.UtcNow.Ticks;
    
    public static long Generate()
    {
      return Interlocked.Increment(ref _sequence);
    }
    
    public static long Sequence
    {
      get
      {
        return _sequence;
      }
    }
  }

業務邏輯中會將 UniqueId 存儲到 SQL Server 數據庫中，使用 bigint 類型進行存儲。

1 CREATE TABLE [TableName](
2     [UniqueId] [bigint] NOT NULL,
3 );

這樣，在數據庫中，該 UniqueId 即可作為索引，也可進行排序。

現在問題來了，我需要將一個較老的服務升級到新的服務中，做 Date Migration。

老的服務中沒有 UniqueId 邏輯和數據，這就需要我在數據遷移時為每條表數據生成該 UniqueId。

顯然，SQL Server 不會幫我做這件事，我需要使用 SQL Script 來生成 UniqueId。

DateTime.Ticks 的定義是：

A single tick represents one hundred nanoseconds or one ten-millionth of a second. There are 10,000 ticks in a millisecond.

1 Tick 等於千萬分之一秒，或萬分之一毫秒。

The value represents the number of 100-nanosecond intervals that have elapsed since 12:00:00 midnight, January 1, 0001, which represents DateTime.MinValue.

DateTime.Ticks 代表著從時間 "00:00:00.0000000, January 1, 0001" 至當前時間的 Tick 總數。

這樣，我們就可以根據其原理通過 SQL 來計算其值。

DECLARE @current_datetime DATETIME;
DECLARE @days_from_0001_to_1900 BIGINT;
DECLARE @ticks_per_millisecond BIGINT;
DECLARE @ticks_per_day BIGINT;
DECLARE @millisecond_of_time BIGINT;
DECLARE @ticks_of_days BIGINT;
DECLARE @ticks_of_time BIGINT;
DECLARE @ticks BIGINT;
    
SET @current_datetime = GETUTCDATE();
SET @days_from_0001_to_1900 = 693595;
SET @ticks_per_millisecond = 10000;
SET @ticks_per_day = 24 * 3600 * 1000 * @ticks_per_millisecond;
SET @millisecond_of_time = 
    DATEPART(MILLISECOND, @current_datetime) 
    + (DATEPART(SECOND, @current_datetime)) * 1000 
    + (DATEPART(MINUTE, @current_datetime)) * 60000 
    + (DATEPART(HOUR, @current_datetime)) * 3600000;
SET @ticks_of_days = CAST(@days_from_0001_to_1900 + DATEDIFF(DAY, 0, @current_datetime) AS BIGINT) * @ticks_per_day;
SET @ticks_of_time = @millisecond_of_time * @ticks_per_millisecond;
SET @ticks = @ticks_of_days + @ticks_of_time;
    
SELECT @current_datetime, @ticks

這段 SQL 腳本的運行結果是：

我們通過下面這段 C# 代碼來驗證結果。

class Program
  {
    static void Main(string[] args)
    {
      DateTime d1 = new DateTime(2014, 04, 01, 02, 52, 54, 153, DateTimeKind.Utc);
      DateTime d2 = DateTime.Parse("2014-04-01 02:52:54.153");
      DateTime d3 = DateTime.Parse("2014-04-01 02:52:54.153",
        CultureInfo.CurrentCulture, DateTimeStyles.AdjustToUniversal);
    
      Console.WriteLine(d1.Ticks);
      Console.WriteLine(d2.Ticks);
      Console.WriteLine(d3.Ticks);
    
      Console.ReadKey();
    }
  }

瞧，它們的結果一致為 635319175741530000。