CODEWITHSUNDEEP
sql-server-2005
Mike Cole
Mike Cole

Reputation: 14703

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 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.

Upvotes: 0

Views: 992

Answers (2)

Cade Roux
Cade Roux

Reputation: 89661

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).

Upvotes: 2

Uriah Carpenter
Uriah Carpenter

Reputation: 6726

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

Upvotes: 1

Related Questions