Getting the most recent Update

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Getting the most recent Update

Author

Topic

Sqlraider
Yak Posting Veteran

65 Posts

Posted - 2010-12-21 : 11:47:33

I Import a table to my server just after midnight. I then create an Index on DateChanged(datetime) in Descending order because I only want the update/inserts from yesterday.

I want to take those records (update/inserts from yesterday) & then load them into a DW database.

Then I query with the following select:
Select EID, Col2, Col3, Col4, DateChanged
from TBL1
where DateChanged = dateadd(day,datediff(day,0,getdate())-1,0))

The problem is if a user does the following:
Inserts> 4215, 6, 7, 8, 2010-12-20 09:18:00.003
Updates> 4215, 7, 7, 7, 2010-12-20 09:46:46.580

My query will use the Update record first and load it into my DW, then overwirte that record with the Insert record.

I use the 'select' in a MERGE statement.

How do I select just the most recent record?

Thanks.

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2010-12-21 : 12:14:33

There are a bunch of different ways to do this. One way is to use a ranking function like ROW_NUMBER(). For example:

SELECT
    Columns
FROM
    (   
        SELECT
            Columns,
            ROW_NUMBER() OVER(PARTITION BY KeyCOlumn(s) ORDER BY DateColumn DESC) AS RowNum
        FROM
            TableName
    ) AS T
WHERE
    RowNum = 1

Sqlraider
Yak Posting Veteran

65 Posts

Posted - 2010-12-21 : 16:36:32

Thanks,
I got it to work.

But now it does a Index Scan where on my other query it did a Index Seek. Is there away to get it do a Seek? Or do I need to select into a temp table just the rows I wish to do the ROW_NUMBER on?

SELECT
EID, Col2, Col3, Col4, DateChanged
FROM
(
SELECT
EID, Col2, Col3, Col4, DateChanged
ROW_NUMBER() OVER(PARTITION BY EID ORDER BY DateChanged DESC) AS RowNum
FROM
TableName
) AS T
WHERE DateChanged = dateadd(day,datediff(day,0,getdate())-1,0)
and RowNum = 1

Thanks again

Sachin.Nand

2937 Posts

Posted - 2010-12-23 : 00:52:04

How many logical reads is it doing ?
It does not matter if the optimiser is going for a seek or a scan in there is minimal difference in the number of logical reads.

PBUH

Sqlraider
Yak Posting Veteran

65 Posts

Posted - 2010-12-27 : 12:23:56

Keep in mind I'm new to this.

My table has 1.5 million rows growing by about 20k per year.

The most I would see is about 1K (updates/insertes) in a 24 hour period but the average would be 2 - 3 hundred.

Correct me if I'm wrong but wouldn't the Scan read ALL 1.5M rows to pull 1K versus the Seek which would stop once it hit the next day?

Thanks.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources