Incrementing an Integer in SQL Server

Question

Noob question here, every time I change a certain record in an SQL Server 2008 R2 table, I want to increment a RevisionId record; to do so, I'm using the following syntax:

UPDATE TheTable SET RevisionId=(SELECT RevisionId FROM TheTable WHERE Id=@id)+1 WHERE Id=@id;

Btw, I'm going to put this into a trigger so that this happens automagically, but while this code works, it feels pretty clunky - any cleaner way to do this?

Answer 1

This is a SQL idiom for incrementing a field:

UPDATE TheTable
 SET RevisionId = RevisionId + 1
 WHERE Id=@id;

Answer 2

You don't need the inner select:

UPDATE TheTable SET RevisionId = RevisionId + 1 WHERE Id=@id

Answer 3

If you're just looking for a "row version" value, have you considered adding a TimeStamp column to your table? SQL Server updates it for you "automagically" every time. No code on your part at all. They just won't be sequentially numbered, if that's important to you.