开发者

High precision date/time stamp in SQL Server 2005

I have bulk records that are getting imported into a SQL Server 2005 table. I need to be able to maintain the same order that they were imported. I was using a DateTime field and populating it to GETDATE(), but that wasn't precise enough.

Any advice out there?

Edit, just to clear up... I don't care what order they appear in开发者_Python百科 the table. I need to be able to order them and maintain the order they were inserted. The problem now is that when I do ORDER BY with my DateTime field, several records fall on the same date because of the lack of precision.


The precision is not high enough because the import is importing more rows per second than there is precision in a datetime datatype?

What mechanism are you using to import the rows?

I would advise a standard identity column, which is pretty much guaranteed to be increasing (but someone can enable identity insert and insert a row anywhere there is a gap, which can occur during a transaction which rolls back due to a constraint violation or similar).

Note that if you have multiple parallel inserts (different connections or whatever), they will be interleaved, so you couldn't use a simple range to find all things which came from a particular source file, say.

A lot of times using SSIS, we had a batch identifier and a sequence number within the batch assigned to rows during the dataflow (separate from any identity which may have existed on the destination table).


You don't mention how you are importing the records, but adding an auto-incrementing identity column to your table should work fine.

0

上一篇:

下一篇:

精彩评论

暂无评论...
验证码 换一张
取 消

最新问答

问答排行榜