A TimeStamp in Time
I was reading a tip in a daily Email from MSSQLTips and was reminded of the uniqueness of the TimeStamp data type in MS SQL Server.
Many SQL engines support the concept of a TimeStamp data type. A TimeStamp data type is not one that may be modified by the user. It is a data type that is maintained by the engine each time a row is inserted or updated, deletes are ignored because there is no place holder for the action as the row is deleted.
When you work with disconnected data, as is most common in modern software, you retrieve the information from tables in the database, and then insert or update your modifications back into the database. In systems with multiple users it is possible for one user to overwrite the modifications of another user (pessimistic locking). In order to detect and allow the developer to handle this condition, when an update occurs, the prior value of each row is compared to the value currently in the database before an update is begun. In this fashion, even with pessimistic locking, one user cannot, without intention, overwrite the modifications of another user.
As the number of columns in a table increase, the time required to compare the data as it was prior to updating increases also. This is the primary purpose of a TimeStamp data type. If the database maintains a TimeStamp every time a row is modified, then all that must be compared are any of the columns making up the primary key (seek) and the TimeStamp value that was received from the database when the records were Selected.
Because the purpose of a TimeStamp column in a table is to track the last time the record was modified, only one TimeStamp column is allowed per table. A good ORM will take advantage of the existence of a TimeStamp in a table for optimization.
The article I was reading was about implementing an Oracle like TimeStamp in SQL Server. MS SQL Server has a binary TimeStamp guaranteed to be unique each time a record is inserted or updated. The data contained in the TimeStamp is purely binary.
In contrast, other data engines (The blog I was reading specified Oracle) actually use a DateTime in the TimeStamp data type. This performs the purpose of defining uniqueness as well as providing the DateTime a row was modified. In some purest circles this is a violation where the value has two meanings. Personally, I don’t have a problem with it. In the blog there was a neat trick with a user defined type using a DateTime and triggers to establish the same behavior of a TimeStamp maintained by the database.
Regardless of your preference, I highly recommend a TimeStamp data type for your tables allowing updates by multiple processes at the same time, requiring optimistic locking. It really does increase performance of an active OLTP database.
Did you like this tip? Do you have others you’d like to share with our readers? Drop me a quick note on facebook, twitter or Email me at btaylor@sswug.org.
SelecTViews
An in-depth interview with two SQL Server MVPs – Eric Johnson and Ben Miller – about their shared experiences working with databases. Both experts are also presenters at the Spring 2011 DBTechCon, which will take place April 20-22 and is the largest virtual conference in the IT field.
[Watch the Show]
Accidental DBA Help
Kevin Kline is hosting a free webcast on a number of dates starting next Wednesday, May 4th titled "Database Administration Patterns and Practices". This is a great opportunity for you to tune your skills in database Adminstration…great for the developer turned DBA.
[Check It Out]
A SOLID Reader Comment
Michael Says:
Thanks for tackling the topic. I have found keeping SOLID in mind quite useful.
I’m afraid you lost me on the last post. Loose coupling, as in, not tight coupling. I’m afraid you lose for using lose instead of loose. It was too distracting to pay great attention to the rest of the post.
Kudos for taking on the topic – I think many intermediate programmers lose sight of the SOLID principles. It took HP, Intel, nVidia (and many others) years before unified driver sets written with SOLID in mind appeared.
That’s all for today.
Cheers,
Ben
$$SWYNK$$
Featured Article(s)
Tips for using jobs in SQL Server 2008
In this article, you can find some useful tips to use SQL Server 2008 jobs.
Featured White Paper(s)
Upgrading to Microsoft SQL Server 2008 R2 from Microsoft SQL Server 2008, SQL Server 2005, and SQL Server 2000
More than ever, organizations rely on data storage and analysis for business operations. Companies need the ability to deploy… (read more)