It’s that time of the month again, and this time it’s an interesting topic. The invitation is from Iceland, where Brent Ozar has relocated for the foreseeable future. I’m slightly jealous, and wish I could go visit. I enjoy winter, and the pictures he’s posted look amazing. Definitely a bucket list trip for me.
However, this month, he’s asking about data types. Are there some you love or hate, and I’ve got a thought on this. In case you wonder, there is a list, broken into types. Apparently MS went into a “categorize everything” frenzy in the docs, which is OK, but I often don’t intuit the way they’ve broken things down. I wish they kept a long list on a page somewhere that was easy to find.
Naming Confusion
It has been deprecated, but the timestamp type is still around. It’s not in the list, but it is mentioned as a synonym for rowversion. This is a unique binary number in each database, which is often used to detect changes in a row. If you have two people editing a row, and a change updates a rowversion column, then each can detect if that value is different from the original one. Handy in terms of client side conflict resolution, which can prevent last-writer-wins scenarios for applications.
I haven’t seen it used lately, but in the 90s and early 2000s, I often saw code that checked this before letting a user make an update in some data entry application. However, this was often a “timestamp” column, which was constantly confusing to me as a DBA or developer. I kept thinking I’d get some sort of datetime stamp in there, rather than a binary value.
This shouldn’t be a problem in the future, as timestamp isn’t really doc’d, though timestamp can be found on Google searches.
The other reason I dislike this type is that we can’t change it to rowversion. An ALTER TABLE … ALTER COLUMN doesn’t work.