SQL Server DateTime get 3.3ms resolution at best, and an officially unspecified amount worse than that at worst, depending on hardware and bugs on the target system.
In practice, it’s not enough resolution to allow adding a DateTime to make a row unique. Uniqueness is best achieved with either a GUID or a rowversion (“TimeStamp”, which is confusingly named in that it is just a sequentially incrementing rowversion and so has nothing to do with the clock); special cases like “this state should only happen once” should generally be done with unique constraints that accurately model whatever contributes to the state involved.
DateTime resolution is likely to actually be more like 10ms or 15ms due to Windows NT clock (See 15ms comment in http://v8.googlecode.com/svn/trunk/src/platform-win32.cc ; MSDN article at http://msdn.microsoft.com/en-us/magazine/cc163996.aspx )
SQL Server timing functions http://msdn.microsoft.com/en-us/library/ms186724.aspx notes:
“Higher-Precision System Date and Time Functions
SQL Server 2008 obtains the date and time values by using the GetSystemTimeAsFileTime() Windows API. The accuracy depends on the computer hardware and version of Windows on which the instance of SQL Server is running. The precision of this API is fixed at 100 nanoseconds. The accuracy can be determined by using the GetSystemTimeAdjustment() Windows API.“
-David