2

Can I select rows on row version?

I am querying a database table periodically for new rows. I want to store the last row version and then read all rows from the previously stored row version.

I cannot add anything to the table, the PK is not generated sequentially, and there is no date field.

Is there any other way to get all the rows that are new since the last query?

I am creating a new table that contains all the primary keys of the rows that have been processed and will join on that table to get new rows, but I would like to know if there is a better way.

EDIT

This is the table structure:

enter image description here

Everything except product_id and stock_code are fields describing the product.

Bvrce
  • 2,170
  • 2
  • 27
  • 45

2 Answers2

3

You can cast the rowversion to a bigint, then when you read the rows again you cast the column to bigint and compare against your previous stored value. The problem with this approach is the table scan each time you select based on the cast of the rowversion - This could be slow if your source table is large.

I haven't tried a persisted computed column of this, I'd be interested to know if it works well.

Sample code (Tested in SQL Server 2008R2):

DECLARE @TABLE TABLE
(
    Id INT IDENTITY(1,1) NOT NULL PRIMARY KEY,
    Data VARCHAR(10) NOT NULL,
    LastChanged ROWVERSION NOT NULL
)

INSERT INTO @TABLE(Data)
VALUES('Hello'), ('World')

SELECT
    Id,
    Data,
    LastChanged,
    CAST(LastChanged AS BIGINT)
FROM
    @TABLE  

DECLARE @Latest BIGINT = (SELECT MAX(CAST(LastChanged AS BIGINT)) FROM @TABLE)

SELECT * FROM @TABLE WHERE CAST(LastChanged AS BIGINT) >= @Latest

EDIT: It seems I've misunderstood, and you don't actually have a ROWVERSION column, you just mentioned row version as a concept. In that case, SQL Server Change Data Capture would be the only thing left I could think of that fits the bill: http://technet.microsoft.com/en-us/library/bb500353(v=sql.105).aspx

Not sure if that fits your needs, as you'd need to be able to store the LSN of "the last time you looked" so you can query the CDC tables properly. It lends itself more to data loads than to typical queries.

Meff
  • 5,889
  • 27
  • 36
  • Please post some sql to select the rowversion – Bvrce Oct 08 '13 at 07:59
  • @Bvrce there you go, best of luck. – Meff Oct 08 '13 at 08:08
  • Thank you; does the ROWVERSION column have to be declared? Can ROWVERSION only be used if it is a column? The problem is that I cannot create a new column. – Bvrce Oct 08 '13 at 08:51
  • @Bvrce You said the table had a row version column in your question? Does it? Post the table schema, lets see what you're working with :) – Meff Oct 08 '13 at 09:09
  • I have posted the schema. There is no ROWVERSION column; I was hoping that ROWVERSION could still be used. – Bvrce Oct 08 '13 at 09:23
  • @Bvrce Sorry then, when you mentioned storing the "row version" I assumed you meant there was a ROWVERSION column, as that's a type of column in SQL Server. – Meff Oct 08 '13 at 09:25
  • @Bvrce the only other thing I could suggest would be Change Data Capture (CDC) where SQL Server maintains a log of changed rows you could query. http://technet.microsoft.com/en-us/library/bb522489(v=sql.105).aspx – Meff Oct 08 '13 at 09:26
  • I will just stick with creating another table and inserting all product_ids that have been processed; what do you think about this approach? Thanks for spending time on this. – Bvrce Oct 08 '13 at 09:34
  • @Bvrce given that your primary key is a varchar(4), I'm guessing there aren't billions of rows in the table ;) So I think you'll be fine. – Meff Oct 08 '13 at 09:38
1

Assuming you can create a temporary table, the EXCEPT command seems to be what you need:

  1. Copy your table into a temporary table.
  2. The next time you look, select everything from your table EXCEPT everything from the temporary table, extract the keys you need from this
  3. Make sure your temporary table is up to date again.

Note that your temporary table only needs to contain the keys you need. If this is just one column, you can go for a NOT IN rather than EXCEPT.

Dennis Jaheruddin
  • 21,208
  • 8
  • 66
  • 122
  • I am going with a left outer join from here: http://stackoverflow.com/questions/4076098/how-to-select-rows-with-no-matching-entry-in-another-table – Bvrce Oct 08 '13 at 10:11
  • I am just creating one column that contains all the product_ids that have been processed. Is the above left outer join appropriate? – Bvrce Oct 08 '13 at 10:14
  • 1
    @Bvrce I think you can use that, but for just 1 column it can be done much simpler to find the ones that have not been processed. Something like: `Select id from t1 where id not in (select id from t2)` – Dennis Jaheruddin Oct 08 '13 at 10:18