DATETIMEデータ型のミリ秒には丸めがあり、利用する場合には注意が必要です。
なので、DATETIMEデータ型をキーにレコードを更新しようとするとマッチしない場合が発生します。
DATETIMEデータ型のミリ秒には丸めがあります
SQLServerのDATETIMEデータ型のユニークキーを設定し、システム時間を挿入してあるテーブルを管理していたのですが、どうもDATETIMEデータ型のユニークキーで更新に行くと「更新レコードがない」という現象に見舞われました。
処理としてはレコード挿入時のシステム時間を取得して、そのシステム時間をキー情報にレコードを挿入し、その後その取得したシステム時間にレコード更新しにいくというもの。
基本的には「挿入エラー」が無い限りレコードは更新対象レコードは1件あるはず。
複数の端末から同時挿入以外は「挿入エラー」はまずない。
しかも使用するユーザー数が少ないため、まず衝突することはないはず。
しかしながら「更新レコードがない」という現象が日に何度か発生。
挿入した日付と日付が挿入されていることが判明
試しにテスト用のテーブルを作成しデータを挿入してみます。
CREATE TABLE test_table ( no INT, hiduke DATETIME) INSERT into test_table VALUES ( 0, '2013/11/25 23:59:59.990' ) INSERT into test_table VALUES ( 1, '2013/11/25 23:59:59.991' ) INSERT into test_table VALUES ( 2, '2013/11/25 23:59:59.992' ) INSERT into test_table VALUES ( 3, '2013/11/25 23:59:59.993' ) INSERT into test_table VALUES ( 4, '2013/11/25 23:59:59.994' ) INSERT into test_table VALUES ( 5, '2013/11/25 23:59:59.995' ) INSERT into test_table VALUES ( 6, '2013/11/25 23:59:59.996' ) INSERT into test_table VALUES ( 7, '2013/11/25 23:59:59.997' ) INSERT into test_table VALUES ( 8, '2013/11/25 23:59:59.998' ) INSERT into test_table VALUES ( 9, '2013/11/25 23:59:59.999' )
で、挿入したデータの「no=1」のデータの取得を目指してミリ秒が「990」の行を選択してみます。
SELECT no, hiduke FROM test_table WHERE hiduke = '2013/11/25 23:59:59.990'
結果は
no hiduke 0 2013-11-25 23:59:59.990 1 2013-11-25 23:59:59.990
２件取得。
noが「0」と「1」になっているので、どうやら「990」と「991」は同じになっている模様。
試しに「991」で検索してみると
no hiduke 0 2013-11-25 23:59:59.990 1 2013-11-25 23:59:59.990
２件取得。
結果は同じなので「990」=「991」と扱われているようです。
「992」で検索すると
SELECT no, hiduke FROM test_table WHERE hiduke = '2013/11/25 23:59:59.992'
結果は
no hiduke 2 2013-11-25 23:59:59.993 3 2013-11-25 23:59:59.993 4 2013-11-25 23:59:59.993
と3件ヒット。
「992」で検索すると
SELECT no, hiduke FROM test_table WHERE hiduke = '2013/11/25 23:59:59.993'
結果は
no hiduke 2 2013-11-25 23:59:59.993 3 2013-11-25 23:59:59.993 4 2013-11-25 23:59:59.993
と結果は同じ3件。
どうやらミリ秒は丸められているようです。
ミリ秒の丸めについて調べてみると
という記述をつけた。
実験してみると
.990->990 .991->990 .992->993 .993->993 .994->993 .995->997 .996->997 .997->997 .998->997 .999->000(※日付が次の日になる）
にそれぞれ丸めが発生する。
結果、挿入したレコードの日付と更新しようとした日付でミリ秒単位でずれが発生し「更新レコードがない」という現象が発生することが判明。
ミリ秒単位で丸めが発生しないようにするには
このようなミリ秒単位で丸めが発生しないようにするには「datetime」型をやめ「datetime2」型にすることで解決する。
CREATE TABLE test_table ( no INT, hiduke DATETIME2) INSERT into test_table VALUES ( 0, '2013/11/25 23:59:59.990' ) INSERT into test_table VALUES ( 1, '2013/11/25 23:59:59.991' ) INSERT into test_table VALUES ( 2, '2013/11/25 23:59:59.992' ) INSERT into test_table VALUES ( 3, '2013/11/25 23:59:59.993' ) INSERT into test_table VALUES ( 4, '2013/11/25 23:59:59.994' ) INSERT into test_table VALUES ( 5, '2013/11/25 23:59:59.995' ) INSERT into test_table VALUES ( 6, '2013/11/25 23:59:59.996' ) INSERT into test_table VALUES ( 7, '2013/11/25 23:59:59.997' ) INSERT into test_table VALUES ( 8, '2013/11/25 23:59:59.998' ) INSERT into test_table VALUES ( 9, '2013/11/25 23:59:59.999' ) SELECT no, hiduke FROM test_table ORDER BY no
実行結果は
no hiduke 0 2013-11-25 23:59:59.9900000 1 2013-11-25 23:59:59.9910000 2 2013-11-25 23:59:59.9920000 3 2013-11-25 23:59:59.9930000 4 2013-11-25 23:59:59.9940000 5 2013-11-25 23:59:59.9950000 6 2013-11-25 23:59:59.9960000 7 2013-11-25 23:59:59.9970000 8 2013-11-25 23:59:59.9980000 9 2013-11-25 23:59:59.9990000
でミリ秒の単位での丸めは発生していない。
本来はユニークキーを独自に生成するにはUNIQUEIDENTIFIER型
本来はこのような場合は同時挿入の衝突を避けて、ユニークキーを独自に生成するにはUNIQUEIDENTIFIER型を利用し、挿入する値をNEWID()で取得して挿入するべきである。
そうすればこのような丸めの問題にぶち当たることもなく、トラブルを未然に防げる。
