David Ziffer (10/20/2010)
Hugo Kornelis (10/20/2010)
On SQL Server 2008 and later, you could use DATETIME2(7), to get a precision of 100ns. That would reduce the chance of collisions, but not completely remove it.Thanks for this post Hugo. I will look into this and almost certainly make this change. RAP was originally developed under SQL Server 2005, which did not have DATETIME2. By the way RAP detects (and resolves) such collisions.
Don't forget that all datetime2 does is reduce the probability of collisions. It does not completely prevent them.
With respect to the IDENTITY column, I just want to stress that my comment was specific about the audit tables. I am not a big fan of IDENTITY in the tables that hold the actual data. Not because they are bad per say, but because they are almost always used in a bad way. I estimate that well over 90% of the people using IDENTITY columns make at least one, and usually two of the following two mistakes:
1) Using the IDENTITY column as the only key in the table, instead of also including and enforcing the actual "business" key.
2) Exposing the IDENTITY values to the end user, instead of keeping them internal to the application and using the actual "busness" key for data entry and reporting.
When accompanied by a UNIQUE constraint on the actual business key and not exposed to the end users, there is nothing wrong with using an IDENTIY column as a surrogate key.