Tuesday, August 13, 2013

Row version

Rowversion

Here in this article I am trying to discuss related to rowversion.
Rowversion  is generally used as mechanism for version-stamping table row. It automatically generates 8 bytes unique binary numbers. The rowversion data type is an incrementing numbers and not preserves any date and time on it.
Each database has a counter that is incremented for each insert or update operation that is performed on a table that contains a rowversion column within the database.
A table can have only one rowversion columns. Every time the rowversion columns increments the value if any kind on Insert or Update operation occurs. Please never use the rowversion data type as a primary key as it change the state every time when an insert or update occurs.
Timestamp is the synonym for the rowversion data type.
CREATE TABLE test_tab
             (Roll             INT         NOT NULL PRIMARY KEY,
              SName       VARCHAR(50) NULL,
              Ver_Stamp  ROWVERSION)
GO         
INSERT INTO test_tab 
                       (Roll, SName)
VALUES         (1, 'Raja Ram'),(2, 'Bijay Anand'),(3, 'Jobe Clone')  

GO
SELECT  Roll, SName, Ver_Stamp  FROM   test_tab   
    
/* -- Output--
Roll      SName                  Ver_Stamp
1          Raja Ram              0x0000000000014C09
2          Bijay Anand           0x0000000000014C0A  <---
3          Jobe Clone            0x0000000000014C0B
*/

UPDATE test_tab
       SET SName='Tony Hillary'
       WHERE Roll=2

SELECT  Roll, SName, Ver_Stamp  FROM   test_tab   
/* -- Output--
Roll      SName                   Ver_Stamp
1          Raja Ram        0x0000000000014C09
2          Tony Hillary      0x0000000000014C0C <---
3          Jobe Clone      0x0000000000014C0B
*/

Look here in this example the marked area (ß) showing the changing of versions.
Hope the article is informative and thanking you to prove your valuable time on it.