T-SQL Tuesday #136–The Datatype Blog

tsqltuesdayIt’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.

About way0utwest

Editor, SQLServerCentral
This entry was posted in Blog and tagged , , . Bookmark the permalink.

5 Responses to T-SQL Tuesday #136–The Datatype Blog

  1. Glenn Berry says:

    Yeah, I had sort of forgotten about timestamp. It seems like last writer wins is what many applications do anymore.

    Like

  2. way0utwest says:

    I agree. Too many people never check for a change. When we worked in inventory apps, however, this was a big deal. We sold against live inventory, so if someone made a sale, they wanted to verify there actually was stock.

    Like

  3. Pingback: #TSQL2sday 136 Wrap-Up: Your Favorite (and Least Favorite) Data Types - Brent Ozar Unlimited®

  4. Andrew says:

    I worked with an ERP that had timestamp on almost all tables. Some were datetime datatype for start_time, end_time columns. Some should have been for rowversion info. But the application did not use them. They had a Logical Lock table that would record soft locks then upgrade to hard locks, when forms would detect changes. The issue was when the application lost connection to the database the locks would not be cleared. Then nobody could make changes to the records that had hard locks. It was not like the lock would be surfaced and remind the user to check if they had completed the update they thought they had, or the lock failed to clear when the update completed. A developer must of decided to test timestamp, and it was released, but is not used. Maybe the developer used other database systems and thought it provided audit information, time of last change.
    The rowversion datatype can be very useful. Like Steve mentioned in his comment you can check for stale information that might be cached, and changed at the source. But a datatype that SQL Server has to create and update on every INSERT and UPDATE and provides no information or functionality to the data or database, is my least favorite datatype for this database.
    If this datatype is useful in the application, check for stale records by returning just the rowversion and id, and insuring that old values are not saved back to the database in a last writer wins scenario. Then it is useful and improves data integrity, it might be my favorite datatype.

    Like

  5. way0utwest says:

    I tend to agree. The app I used checked it, mostly because they didn’t want to write code to check every field after someone had been editing for minutes. However, most of the time, it was confusing to users.

    Like

Comments are closed.