1

I am able to convert rowversion column in a table to varchar using below code

select CONVERT(VARCHAR, CONVERT(BINARY(8), roversion_col), 1) rwa from TBL

How do I convert it back to rowversion datatype? Is it NOT possible to do so?

Update:
This should not be treated as duplicate with How to convert TIMESTAMP values to VARCHAR in T-SQL as SSMS does?

That question is about asking rowversion datatype to varchar. However, this question is vice-versa and wanted to get rowversion data using varchar.

N Raghu
  • 706
  • 4
  • 13
  • 26
  • 4
    Why do you want to convert it at all? a rowversion is binary value with no specific significance apart from the guarantee it will always increase *for that specific row only*. It's not a database version number, nor is it guaranteed to have unique values. Newer rows are *not* guaranteed to get a larger value than existing rows – Panagiotis Kanavos Sep 05 '18 at 12:43
  • 1
    Furthermore, `rowversion` fields can't be written so a `rowversion` value can't be stored anywhere in the database except as a `binary(8)` – Panagiotis Kanavos Sep 05 '18 at 12:44
  • Yep, I understand. During transformation to PostgreSQL, Talend has converted rowversion to character varying data type. I have a record of latest rowversion and wanted to know the latest changes but since it is recorded as character varying, I'm unable to identify the recent changes – N Raghu Sep 05 '18 at 12:47
  • Why not? If a row was modified in any way, the rowversion value will also change. When you try to reload the data, the rowversion will be different from whatever you've stored. Did you assume `rowversion` is a *database version* perhaps and try to find stuff that has a rowversion than the largest one stored? You can't use `rowversion` like this – Panagiotis Kanavos Sep 05 '18 at 13:10
  • BTW if you want to find new entries, SQL Server provides [change tracking](https://learn.microsoft.com/en-us/sql/relational-databases/track-changes/about-change-tracking-sql-server?view=sql-server-2017) in all versions and editions since 2005. That *does* use a database version number. You can ask for all changes, including deletions, for any table that has change tracking enabled. Instead of loading everything and trying to find changes by comparing rows, you can just ask for any change records since the last database version you've pulled – Panagiotis Kanavos Sep 05 '18 at 13:12
  • From [Working with Change Tracking](https://learn.microsoft.com/en-us/sql/relational-databases/track-changes/work-with-change-tracking-sql-server?view=sql-server-2017) `SELECT CT.ProductID, P.Name, P.ListPrice, CT.SYS_CHANGE_OPERATION, CT.SYS_CHANGE_COLUMNS, CT.SYS_CHANGE_CONTEXT FROM SalesLT.Product AS P RIGHT OUTER JOIN CHANGETABLE(CHANGES SalesLT.Product, @last_synchronization_version) AS CT ON P.ProductID = CT.ProductID ` will return all Product changes. – Panagiotis Kanavos Sep 05 '18 at 13:14
  • @PanagiotisKanavos As I understand, rowversion changes upon every update and insert, so, I take max(rowversion) and store it in a table. In my job I can use select * from table where rowversion>(storedversion) which gives me only the incremental data. – N Raghu Sep 05 '18 at 13:48
  • @PanagiotisKanavos I have used Change Tracking in the past and knew about it. In my present scenario I already rowversion enabled and wanted to utilize it. – N Raghu Sep 05 '18 at 13:49
  • What's the problem then? binary or text, the values will be different if the row is modified. You'll have to load all the data into a staging table though and then join with the target table in order to find deletions (missing rows from the staging table) and updates (different rowversions). You *can't* use a rowversion as if it were a change tracking version though. There's *no* guarantee that new rows will have a larger value than existing ones. – Panagiotis Kanavos Sep 05 '18 at 13:52
  • Deletions are handled differently, boolean column will be marked. The job only will actual delete the record from table after it processes. – N Raghu Sep 05 '18 at 14:15
  • You still haven't explained what the problem is. – Panagiotis Kanavos Sep 05 '18 at 14:17
  • My first comment explains the most of the problem. But since you missed I would be glad to help you. During transformation from SQL to PostgreSQL ETL has marked rowversion column as character varying (I missed to check this and landed into trouble ) . Now to get the incremental data I need to check the latest rowversion from PostgreSQL and query against the SQL. Since the rowversion is stored as character varying in PostgreSQL, I need to convert it back to rowversion datatype. – N Raghu Sep 05 '18 at 14:24
  • Let us [continue this discussion in chat](https://chat.stackoverflow.com/rooms/179479/discussion-between-n-raghu-and-panagiotis-kanavos). – N Raghu Sep 05 '18 at 14:25
  • You don't need to do that. You can compare binary(8) with `rowversion`. Check [this](https://www.mssqltips.com/sqlservertip/4545/synchronizing-sql-server-data-using-rowversion/) article. This *can* fail though. Bulk operations capture and use entire blocks of rowversion numbers. Concurrent operations can grab different rowversion blocks. One operation could grabe vlues in the range 100-200 while a faster one grabs and uses 300-400 What happens if you read the *second* batch because the first operation hasn't commited yet? – Panagiotis Kanavos Sep 05 '18 at 14:32
  • PS: I've run into such "ghost" updates when I tried to use `rowversion` as a change indicator. The problem is transient - the script may seem to work perfectly for months until you start finding 2 mismatched rows here, 10 there etc. I used `rowversion` for change detection in the past and had do abandon it when I realised I was missing updates this way – Panagiotis Kanavos Sep 05 '18 at 14:34
  • Seems not working. For any rowversion in varchar format it gives same output. select convert(binary(8),'0x0000000027DF446D'), convert(binary(8),'0x0000000027DF446E') both the values give same output as 0x3078303030303030 – N Raghu Sep 05 '18 at 14:36
  • `CONVERT` works on individual characters. `30` is the ASCII value of the `0` character. It doesn't try to parse the HEX value. Even if you use proper parsing you'll *still* lose updates as I explained – Panagiotis Kanavos Sep 05 '18 at 14:38
  • So that means, I have no way to get the correct timestamp now?? – N Raghu Sep 05 '18 at 14:40
  • You can, as [this shows](https://stackoverflow.com/questions/14145904/sql-server-hex-string-to-varbinary-conversion). You can pass a format parameter to `CONVERT`, eg `select convert(binary(8),'0x0000000027DF446D',1), convert(binary(8),'0x0000000027DF446E',1) ` . It won't help you though. You'll lose updates – Panagiotis Kanavos Sep 05 '18 at 14:42

1 Answers1

3

Try this:

CONVERT(rowversion, CONVERT(BINARY(8), @teststring, 1))
herrbischoff
  • 3,294
  • 1
  • 29
  • 50
Alex
  • 31
  • 3