CODEWITHSUNDEEP
sqlsql-server
Bvrce
Bvrce

Reputation: 2170

Select on Row Version

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.

Upvotes: 2

Views: 4317

Answers (2)

Dennis Jaheruddin
Dennis Jaheruddin

Reputation: 21563

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.

Upvotes: 1

Meff
Meff

Reputation: 5999

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.

Upvotes: 3

Related Questions