Thursday, January 31, 2013

ROWVERSION Walkthrough

ROWVERSION is used for versioning rows (did the name not give it away?)

Equivalent to VARBINARY(8), here is a demonstration of how it works (using the bestselling albums of 1983!)
First we create a table with a ROWVERSION column and populate it.

CREATE TABLE TopAlbums
( ID INT IDENTITY(1,1)
 ,Artist NVARCHAR(50)
 ,Title NVARCHAR(50)
 ,Version ROWVERSION)

GO

INSERT INTO TopAlbums (Artist,Title) VALUES ('Michael Jackson','Thriller')
INSERT INTO TopAlbums (Artist,Title) VALUES ('David Bowie','Let''s Dance')
INSERT INTO TopAlbums (Artist,Title) VALUES ('Culture Club','Colour By Numbers')
INSERT INTO TopAlbums (Artist,Title) VALUES ('Paul Young','No Parlez')
INSERT INTO TopAlbums (Artist,Title) VALUES ('Spandau Ballet','1')
INSERT INTO TopAlbums (Artist,Title) VALUES ('Wham!','Fantastic')
INSERT INTO TopAlbums (Artist,Title) VALUES ('Men At Work','Business As Usual')
INSERT INTO TopAlbums (Artist,Title) VALUES ('The Police','Synchronicity')
INSERT INTO TopAlbums (Artist,Title) VALUES ('Genesis','Genesis')
INSERT INTO TopAlbums (Artist,Title) VALUES ('Eurythmics','Sweet Dreams')

GO
Here we can see the value of the Version column that ROWVERSION has created.
SELECT * FROM TopAlbums WHERE ID = 10
10 Eurythmics Sweet Dreams 0x00000000000007F8 

Now we give album 10, it's full title -

UPDATE TopAlbums  
SET Title = Title + ' (Are Made Of This)'
WHERE ID = 10
and we can see that the amendment has updated the Version column.
SELECT * FROM TopAlbums WHERE ID = 10
10 Eurythmics Sweet Dreams (Are Made Of This) 0x00000000000007F9 

Practical uses of this are data warehousing systems, i.e. only reloading data that has changed.

No comments: