Just in Time

H.H. Munro, writing under the pseudonym of Saki in late 19th-century Britain, opined, "A little inaccuracy sometimes saves tons of explanation." Unfortunately, a discussion about datetime data's inaccuracy is necessary, even though the data is only a little inaccurate. Although a datetime data type's time can be precise to milliseconds, SQL Server can't represent every millisecond. In fact, within the millisecond value of the datetime data type, SQL Server rounds to within 0.00333 seconds, a period of roughly 3.33 milliseconds. After you run Web Listing A (available at http://www.tsqlsolutions.com, InstantDoc ID 25174) to create and populate a sample table, look at the output that the listing's SELECT statement returns. Notice that the values that the query returns aren't identical to the values inserted. This example is significant because it shows that returning duplicates is possible—even when explicit values are supplied. Additionally, when database inserts occur rapidly (e.g., through multiple clients or testing with fast loops), you're even more likely to yield duplicate values. Moreover, you probably can't use a datetime-based column as a unique identifier. To further prove this point, you can download and run this column's DateTimeDataExamples.sql commented script file from http://www.tsqlsolutions.com, InstantDoc ID 25173. This script includes the 5-second loop that Web Listing B uses to rapidly insert data into the TestDates table. The script also uses the getdate() system function to input the current date and time value. After the loop inserts the data, the SELECT query in Web Listing B shows the total number of rows added, the number of distinct values in the datetime column, and the number of distinct values in the smalldatetime column. Notice that the datetime column has few unique values, and the smalldatetime column has only one or two unique values. Also, when you review the data, you'll immediately see that the smalldatetime data is rounded to the nearest minute.