Post reply

To Delete / Truncate / Drop & Create

  • May 23, 2017 at 1:34 pm

    #1943881

    Sean Lange - Tuesday, May 23, 2017 7:59 AM

    That isn't a natural key. It is using a surrogate key from another system.

    Spot on.

    --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)

  • May 23, 2017 at 1:40 pm

    #1943883

    RonKyle - Tuesday, May 23, 2017 7:43 AM

    RonKyle - Thursday, May 18, 2017 7:08 AM

    Often there is a natural key for employees or people. Often there is not one. If the natural key exists, it should be used. If it doesn't, use an identity.

    It sounds like you may have run into an "employee" table that does, in fact, have a natural key. I've run into a couple myself but none of the could withstand the test of time as a true unique row key. What have you seen?

    I have been able to use the actual employee number before.  There was no chance for duplication and I was able to enter years worth of employees who had come an gone.  The only downside was that is was possible to override the system and somethings a number with a different format was entered.  The unique was still enforced, but instead of being the same length as the others stuck out for one reason or the other.  The companies used ADP software, which I would imagine is in widespread use.  Also was able to use valid technician and installer numbers in other systems.  For a decade now, there has been no issue. 

    On the other hand, sometimes the system doesn't issue one or one that can be reused.  About a decade ago I set up an ETL for one of those that could be reused.  The reuse frequency wasn't high, so to make it unique (necessary for a 2SCD OLAP dimension) I appended the users initials to it.  So a duplicate was possible, but not likely.  But about a year ago there was a duplicate.  Two people with the same initials.  I'm about to upgrade that system from 2005 to 2016, so I may consider whether a sequence would be a better way to do this.  (An identity is out because it's not a table property).

    Bottom line, sometimes there's  one available.  If there is, I use it.  If there's not, I use an identity.  Just not a one size fits all solution in this case.

    Thanks for the feedback, Ron. I have to agree with Sean, though.  Employee Number isn't a natural key.  It's a surrogate key generated or maintained by another "system".  I'm also not keen on doing things like adding people's initials in an attempt to uniquify information for the very reason you state.  It's not bullet-proof and will not withstand the test of time.

    --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)

  • May 23, 2017 at 3:22 pm

    #1943898

    Jeff Moden - Tuesday, May 23, 2017 1:40 PM

    RonKyle - Tuesday, May 23, 2017 7:43 AM

    RonKyle - Thursday, May 18, 2017 7:08 AM

    Often there is a natural key for employees or people. Often there is not one. If the natural key exists, it should be used. If it doesn't, use an identity.

    It sounds like you may have run into an "employee" table that does, in fact, have a natural key. I've run into a couple myself but none of the could withstand the test of time as a true unique row key. What have you seen?

    I have been able to use the actual employee number before.  There was no chance for duplication and I was able to enter years worth of employees who had come an gone.  The only downside was that is was possible to override the system and somethings a number with a different format was entered.  The unique was still enforced, but instead of being the same length as the others stuck out for one reason or the other.  The companies used ADP software, which I would imagine is in widespread use.  Also was able to use valid technician and installer numbers in other systems.  For a decade now, there has been no issue. 

    On the other hand, sometimes the system doesn't issue one or one that can be reused.  About a decade ago I set up an ETL for one of those that could be reused.  The reuse frequency wasn't high, so to make it unique (necessary for a 2SCD OLAP dimension) I appended the users initials to it.  So a duplicate was possible, but not likely.  But about a year ago there was a duplicate.  Two people with the same initials.  I'm about to upgrade that system from 2005 to 2016, so I may consider whether a sequence would be a better way to do this.  (An identity is out because it's not a table property).

    Bottom line, sometimes there's  one available.  If there is, I use it.  If there's not, I use an identity.  Just not a one size fits all solution in this case.

    Thanks for the feedback, Ron. I have to agree with Sean, though.  Employee Number isn't a natural key.  It's a surrogate key generated or maintained by another "system".  I'm also not keen on doing things like adding people's initials in an attempt to uniquify information for the very reason you state.  It's not bullet-proof and will not withstand the test of time.
    Not to mention that initials can change.  I have comments in my code for who authorized a change that are no longer valid because the young lady got married.

    Tally Tables - Performance Personified
    String Splitting with True Performance
    Best practices on how to ask questions

  • May 24, 2017 at 7:11 am

    #1943972

    Not to mention that initials can change. I have comments in my code for who authorized a change that are no longer valid because the young lady got married.

    Initials can't change in this system.  It's a third party software to record telephony information.  I really would have preferred that they did not recycle key values.  But this is one of those cases where I have to work with what I have.  Now that a sequence object is available (it's been in PostgreSQL as long as I've worked with that product, but as you know it's a somewhat recent add (2012?) to SQL Server), I may have an alternative method.  I'll know this summer when I do the upgrade on the Telephony Cube.

  • May 24, 2017 at 9:06 am

    #1944011

    RonKyle - Wednesday, May 24, 2017 7:11 AM

    It's a third party software to record telephony information.

    It sounds like you should invite them to a high velocity pork chop dinner. 😉

    --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)

Viewing 5 posts - 46 through 49 (of 49 total)

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