SCD 2 End data and CurrentRowFlag

Question

SCD 2 End data and CurrentRowFlag

SolveSQL

SSChasing Mays

Points: 647
More actions
April 22, 2016 at 9:03 am

#400060

I have a theoretical question.
When working with the Dimension table, if i have EffectiveDate and EndDate is there also a reason to have a CurrentRowFlag Column?
Looking for some scenarios for either or! (not a "It Depends" answer :-))

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Hugo Kornelis SSC Guru Points: 64780 More actions · Answer 1

A benefit of having a CurrentRowFlag column is that you can create a filtered index on it. That is not possible with EffectiveDate and EndDate only because you cannot compare to GETDATE() or CURRENT_TIMESTAMP in the WHERE clause of a filtered index.

A drawback of having a CurrentRowFlag column is that it increase the size of the row on disk, which depending on the size of the table may or may not be a problem. Also, you need to set up some process to keep this column current, not only when the EffectiveDate and/or EndDate change but also when time passes without the rows changing. If you do a complete reload often enough you will get this for free, but if you do incremental loads or if your load frequency is too low you will have to set up a process to update this column when the current date and time pass the EffectiveDate and/or EndDate thresholds.

Hugo Kornelis, SQL Server/Data Platform MVP (2006-2016)
Visit my SQL Server blog: https://sqlserverfast.com/blog/
SQL Server Execution Plan Reference: https://sqlserverfast.com/epr/

Jeff Moden SSC Guru Points: 1003863 More actions · Answer 2

SolveSQL (4/22/2016)
I have a theoretical question.
When working with the Dimension table, if i have EffectiveDate and EndDate is there also a reason to have a CurrentRowFlag Column?
Looking for some scenarios for either or! (not a "It Depends" answer :-))

It actually does "depend". 😉 Were the designers smart enough to make EndDate a NOT NULL column with a default of '99990101' (or some such) or were they the same as most people that have slow performance for such an SCD table and allowed NULLs in the EndDate column?

Personally, I'd avoid the need for the "CurrentRowFlag" column like the plague.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

SolveSQL SSChasing Mays Points: 647 More actions · Answer 3

Thank you Hugo Kornelis and Jeff Moden for the feedback.