Post reply

Modified DateTimeStamp - No Default on record creation

  • April 8, 2012 at 4:24 pm

    #391971

    I was creating Insert_Date and Modified Date Columns on a number of tables.

    It is customary to set a DEFAULT CONSTRAINT on the Insert_Date Column.

    But with the Modified_Date Column I don't believe that you would want a default constraint because it would give the false impression that the record had been modified, when it has only been inserted into the table?

    I know that this is very basic.

    For better, quicker answers on T-SQL questions, click on the following...
    http://www.sqlservercentral.com/articles/Best+Practices/61537/

    For better answers on performance questions, click on the following...
    http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

  • April 8, 2012 at 4:35 pm

    #1470547

    Depends on your design.

    In some designs created date = modified date means that the record has not been modified since creation. In others modified date = null means that the record has not been modified since creation.

    Pick the one you prefer (or use the company standard if there is one)

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • April 8, 2012 at 9:49 pm

    #1470567

    I prefer they both be NOT NULL and, like Gail identified, will make both the same on INSERT. I also use a trigger to enforce updates to the MODIFIED_DATE and MODIFIED_BY columns and to prevent the CREATED_DATE and CREATED_BY columns from being updated.

    Of course, if I have an Audit table trigger and an Audit table, I'll leave off the CREATED_DATE and CREATED_BY columns altogether. Same holds true if I'm using SCD TYPE 2.

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

  • April 9, 2012 at 12:34 pm

    #1470853

    Jeff Moden (4/8/2012)

    I prefer they both be NOT NULL and, like Gail identified, will make both the same on INSERT. I also use a trigger to enforce updates to the MODIFIED_DATE and MODIFIED_BY columns and to prevent the CREATED_DATE and CREATED_BY columns from being updated.

    Of course, if I have an Audit table trigger and an Audit table, I'll leave off the CREATED_DATE and CREATED_BY columns altogether. Same holds true if I'm using SCD TYPE 2.

    If the CREATED_DATE and MODIFIED_DATE were datetime columns I'd go with your method, Jeff, but if they are DATE columns there's scope for mods on the same date as creation so I'd use the day before CREATED_DATE as the ODIFIED_DATE meaning never modified in that case.

    Tom

  • April 9, 2012 at 4:28 pm

    #1470972

    L' Eomot Inversé (4/9/2012)

    Jeff Moden (4/8/2012)

    I prefer they both be NOT NULL and, like Gail identified, will make both the same on INSERT. I also use a trigger to enforce updates to the MODIFIED_DATE and MODIFIED_BY columns and to prevent the CREATED_DATE and CREATED_BY columns from being updated.

    Of course, if I have an Audit table trigger and an Audit table, I'll leave off the CREATED_DATE and CREATED_BY columns altogether. Same holds true if I'm using SCD TYPE 2.

    If the CREATED_DATE and MODIFIED_DATE were datetime columns I'd go with your method, Jeff, but if they are DATE columns there's scope for mods on the same date as creation so I'd use the day before CREATED_DATE as the ODIFIED_DATE meaning never modified in that case.

    Understood. Just to be sure and despite the name of the columns, I'd never use the DATE datatype for either of these columns.

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

  • April 9, 2012 at 4:45 pm

    #1470984

    L' Eomot Inversé (4/9/2012)

    Jeff Moden (4/8/2012)

    I prefer they both be NOT NULL and, like Gail identified, will make both the same on INSERT. I also use a trigger to enforce updates to the MODIFIED_DATE and MODIFIED_BY columns and to prevent the CREATED_DATE and CREATED_BY columns from being updated.

    Of course, if I have an Audit table trigger and an Audit table, I'll leave off the CREATED_DATE and CREATED_BY columns altogether. Same holds true if I'm using SCD TYPE 2.

    If the CREATED_DATE and MODIFIED_DATE were datetime columns I'd go with your method, Jeff, but if they are DATE columns there's scope for mods on the same date as creation so I'd use the day before CREATED_DATE as the ODIFIED_DATE meaning never modified in that case.

    Curious...if DATE were your datatype why use a manufactured date instead of NULL? Presumably this could amount to a non-trivial amount of data.

    There are no special teachers of virtue, because virtue is taught by the whole community.
    --Plato

  • April 10, 2012 at 10:34 am

    #1471331

    opc.three (4/9/2012)

    L' Eomot Inversé (4/9/2012)

    Jeff Moden (4/8/2012)

    I prefer they both be NOT NULL and, like Gail identified, will make both the same on INSERT. I also use a trigger to enforce updates to the MODIFIED_DATE and MODIFIED_BY columns and to prevent the CREATED_DATE and CREATED_BY columns from being updated.

    Of course, if I have an Audit table trigger and an Audit table, I'll leave off the CREATED_DATE and CREATED_BY columns altogether. Same holds true if I'm using SCD TYPE 2.

    If the CREATED_DATE and MODIFIED_DATE were datetime columns I'd go with your method, Jeff, but if they are DATE columns there's scope for mods on the same date as creation so I'd use the day before CREATED_DATE as the ODIFIED_DATE meaning never modified in that case.

    Curious...if DATE were your datatype why use a manufactured date instead of NULL? Presumably this could amount to a non-trivial amount of data.

    Well, I don't like unneccessary nulls, and I generally insist that the only meaning of NULL permissable in a base table in anything I design is "this value isn't here" with no indication at all why it isn't here. I imagine Jeff feels the same way, as he said he prefers both columns to be NOT NULL. Obviously the reason for a null is usually obvious in derived tables created using outer joins, but such a null isn't in a base table.

    But I probably wouldn't be stuck with a schema in which CREATED_DATE and MODIFIED_DATE were in the same table if it were my design, because if things can be modified more than once it's far better to have modifications in a separate table (like Jeff's Audit table) and because if many things aren't ever modified (the case you describe) I save space by having a separate table, and because I don't generally like the idea of using "funny defaults" to avoid using nulls, especially when the nulls concerned can easily be avoided by putting distinct entities in distinct tables.

    Tom

  • April 10, 2012 at 3:59 pm

    #1471580

    opc.three (4/9/2012)

    L' Eomot Inversé (4/9/2012)

    Jeff Moden (4/8/2012)

    I prefer they both be NOT NULL and, like Gail identified, will make both the same on INSERT. I also use a trigger to enforce updates to the MODIFIED_DATE and MODIFIED_BY columns and to prevent the CREATED_DATE and CREATED_BY columns from being updated.

    Of course, if I have an Audit table trigger and an Audit table, I'll leave off the CREATED_DATE and CREATED_BY columns altogether. Same holds true if I'm using SCD TYPE 2.

    If the CREATED_DATE and MODIFIED_DATE were datetime columns I'd go with your method, Jeff, but if they are DATE columns there's scope for mods on the same date as creation so I'd use the day before CREATED_DATE as the ODIFIED_DATE meaning never modified in that case.

    Curious...if DATE were your datatype why use a manufactured date instead of NULL? Presumably this could amount to a non-trivial amount of data.

    I think not. Both DATE and DATETIME are fixed datatypes and take the same amount of room whether they have a value or not.

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

  • April 10, 2012 at 4:18 pm

    #1471595

    Jeff Moden (4/10/2012)

    opc.three (4/9/2012)

    L' Eomot Inversé (4/9/2012)

    Jeff Moden (4/8/2012)

    I prefer they both be NOT NULL and, like Gail identified, will make both the same on INSERT. I also use a trigger to enforce updates to the MODIFIED_DATE and MODIFIED_BY columns and to prevent the CREATED_DATE and CREATED_BY columns from being updated.

    Of course, if I have an Audit table trigger and an Audit table, I'll leave off the CREATED_DATE and CREATED_BY columns altogether. Same holds true if I'm using SCD TYPE 2.

    If the CREATED_DATE and MODIFIED_DATE were datetime columns I'd go with your method, Jeff, but if they are DATE columns there's scope for mods on the same date as creation so I'd use the day before CREATED_DATE as the ODIFIED_DATE meaning never modified in that case.

    Curious...if DATE were your datatype why use a manufactured date instead of NULL? Presumably this could amount to a non-trivial amount of data.

    I think not. Both DATE and DATETIME are fixed datatypes and take the same amount of room whether they have a value or not.

    Uggh, I knew that. *palm meets forehead* That settles the storage issue...thanks as always, Jeff

    @tom I too am not a fan of designs that compel us to use unnecessary NULLs. I also like the idea of foregoing the "last mod date" column and offloading the storage to an audit table, depending on the level of audit data required. Thanks for the response.

    edit: strike comment about the NULL-property of columns in a table affecting record size one way or the other, it has no bearing, a record's NULL bitmap always has 1 bit per column regardless of the NULL-property of each column

    There are no special teachers of virtue, because virtue is taught by the whole community.
    --Plato

  • April 10, 2012 at 4:30 pm

    #1471600

    i to prefer to eleminate as many nulls in the data as possible. however it has to make sense. a last modified date IMHO is the inserted date if it has not been modified, but in the case of a finish time since the event has not happened i think it valid to have a null there.

    For faster help in answering any problems Please read How to post data/code on a forum to get the best help - Jeff Moden[/url] for the best way to ask your question.

    For performance Issues see how we like them posted here: How to Post Performance Problems - Gail Shaw[/url]

    Need to Split some strings? Jeff Moden's DelimitedSplit8K[/url]
    Jeff Moden's Cross tab and Pivots Part 1[/url]
    Jeff Moden's Cross tab and Pivots Part 2[/url]

  • April 22, 2012 at 11:40 am

    #1477203

    capn.hector (4/10/2012)

    i to prefer to eleminate as many nulls in the data as possible. however it has to make sense. a last modified date IMHO is the inserted date if it has not been modified, but in the case of a finish time since the event has not happened i think it valid to have a null there.

    Logically speaking, I absolutely agree. Technically speaking, I use 9999-12/30 (one short of the SQL "End of Time" date for other technical reasons) so that I don't have to do the "ISNULL OR" thing when trying to search for date ranges.

    --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 11 posts - 1 through 11 (of 11 total)

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