Sorting out SMALLDATETIME, DATETIME and TIMESTAMP in SQL Server

SQL Server 2005 offers three different data types that all sound like likely candidates for storing time values: DATETIME, SMALLDATETIME, and TIMESTAMP. Here's a quick rundown on each:

• DATETIME is the most common choice for storing date/time values. It's an eight-byte value that stores a date between January 1, 1753 and December 31, 9999 and a time with three millisecond accuracy.
• SMALLDATETIME is a good choice when space is a concern. It uses only four bytes to store dates between January 1, 1900 and June 6, 2079 along with times to one minute accuracy.
• TIMESTAMP values do not store timestamps (at least in the clock/calendar sense!) at all. It can be used for row version control, as it stores a value that SQL Server updates each time it modifies a row.

For more on this topic, Robert Sheldon has a great article over on SearchSQLServer.com.