Post reply

ID or Id

  • January 23, 2015 at 1:22 pm

    #1772816

    ScottPletcher (1/23/2015)

    TomThomson (1/23/2015)

    Sean Lange (1/23/2015)

    Eric M Russell (1/23/2015)

    Date/Time: datetime, offset, varchar?

    There is only one option here. Datetime.

    I suppose if your dBMS is anccient enough that's excusable. But it probably isn't if Datetime2 is available.

    An integer is also possible, and probably best in specific cases, such as joining to an existing column that also uses an integer and is already indexed.

    An integer will often work for date, but the requirement was for Date/Time - there are cases where an interger will work for that, but they all have a very simple problem: there isn't any standard conversion betwen interger and Date/Time unless it's really not Date/Time but just Date or just Time.

    Tom

  • January 23, 2015 at 1:25 pm

    #1772817

    TomThomson (1/23/2015)

    ScottPletcher (1/23/2015)

    TomThomson (1/23/2015)

    Sean Lange (1/23/2015)

    Eric M Russell (1/23/2015)

    Date/Time: datetime, offset, varchar?

    There is only one option here. Datetime.

    I suppose if your dBMS is anccient enough that's excusable. But it probably isn't if Datetime2 is available.

    An integer is also possible, and probably best in specific cases, such as joining to an existing column that also uses an integer and is already indexed.

    An integer will often work for date, but the requirement was for Date/Time - there are cases where an interger will work for that, but they all have a very simple problem: there isn't any standard conversion betwen interger and Date/Time unless it's really not Date/Time but just Date or just Time.

    It's easy enough to store a minutes offset -- such as minutes from 19000101 -- which requires only an int column.

    SQL DBA,SQL Server MVP(07, 08, 09) "Money can't buy you happiness." Maybe so, but it can make your unhappiness a LOT more comfortable!

  • January 23, 2015 at 11:44 pm

    #1772882

    TomThomson (1/23/2015)

    Sean Lange (1/23/2015)

    Eric M Russell (1/23/2015)

    Date/Time: datetime, offset, varchar?

    There is only one option here. Datetime.

    I suppose if your dBMS is anccient enough that's excusable. But it probably isn't if Datetime2 is available.

    I have a very large disliking for DATETIME2 and all the "Johnny-come-lately" (according to SQL Server) date and time datatypes as they've been written for SQL Server. They crippled most of the functionality that the DATETIME datatype offers. Yeah, sure... you can control the number of bytes and the precision of DATETIME2 but I happily sacrifice that to get the functionality of being able to easily get the difference between two dates and aggregate the result in a column without fear of the result or even the span on one row being too large for a millisecond or second based DATEDIFF (required if using something other than DATETIME) especially when trying to aggregate for total time accumulated.

    I also go bonkers when I see someone store the date and time for an event in two different columns. Why would someone cripple the ability to do simple purely mathematical temporal calculations that way? To do GROUP BYs by day??? My word.

    --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)
    Intro to Tally Tables and Functions

  • January 24, 2015 at 8:06 am

    #1772908

    What are the issues with DATETIME2 and DATEDIFF?

  • January 24, 2015 at 9:33 am

    #1772910

    I am thankful for introduction of the short Date and have made heavy use of it last few years.

    "Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho

  • January 24, 2015 at 10:53 am

    #1772913

    RonKyle (1/24/2015)

    What are the issues with DATETIME2 and DATEDIFF?

    Simple. Given a single row that contains StartDate and EndDate columns as DATETIME2 columns, write the code to calculate the duration between those two columns and display it in the HHHHHHHH:MI:SS.mil format. Once that's done, write the code to aggregate that duration that StartDate and EndDate represent for the entire table as a SUM. Post the code and then I'll show you how simple it can really be and how it avoids overflows.

    --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)
    Intro to Tally Tables and Functions

  • January 24, 2015 at 10:57 am

    #1772914

    Eric M Russell (1/24/2015)

    I am thankful for introduction of the short Date and have made heavy use of it last few years.

    Yes. I agree with that 100%. But do you ever store both the short Date and Time in separate columns in the same table?

    --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)
    Intro to Tally Tables and Functions

  • January 26, 2015 at 7:17 am

    #1773098

    Given a single row that contains StartDate and EndDate columns as DATETIME2 columns, write the code to calculate the duration between those two columns and display it in the HHHHHHHH:MI:SS.mil format.

    If I had something as a datetime2 I would never need the DATEDIFF in this format. I assumed you meant something about the number returned was incorrect. If I need time, I wouldn't use datetime2.

    But do you ever store both the short Date and Time in separate columns in the same table?

    In a certain sense, sometimes. In my data warehouse, the items for which time is also important have two columns for the item, one date and the other time. Otherwise the dimension storing the date and time values would explode in number of rows.

  • January 26, 2015 at 8:44 am

    #1773139

    Jeff Moden (1/24/2015)

    Eric M Russell (1/24/2015)

    I am thankful for introduction of the short Date and have made heavy use of it last few years.

    Yes. I agree with that 100%. But do you ever store both the short Date and Time in separate columns in the same table?

    Generally speaking, I don't see the point in storing related Date and Time seperately. Possibly a case could be made for for it in a transactional database that needs granular DateTime, but also had a need to index and query on just Date by itself. Also, I could potentially see storing Time by itself for something like a scheduling application.

    "Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho

  • January 26, 2015 at 8:59 am

    #1773143

    I use ID for surrogate keys using the Identity Property. If the column is not using the identity property I would be more likely to use Id. Since I haven't used SEQUENCE yet I haven't decided on what I would like there. I would learn toward ID if it is a being used as a Surrogate Key.

    Having said that, I don't mind Id.

    In my first job we used lower case with underscores and surrogate keys were order_skey.

    I've moved from advocating lower_case_with_underscores mainly because most discussions I have on the topic the others don't like it or use it so I consider mixed case to be more of an industry standard. The only thing I don't like is Mixed_Case_With_Underscores. That just hurts my eyes, and I have a database a my job that is like that.

    Jack Corbett
    Consultant - Straight Path Solutions
    Check out these links on how to get faster and more accurate answers:
    Forum Etiquette: How to post data/code on a forum to get the best help
    Need an Answer? Actually, No ... You Need a Question

  • January 26, 2015 at 1:20 pm

    #1773219

    Jeff Moden (1/24/2015)

    RonKyle (1/24/2015)

    What are the issues with DATETIME2 and DATEDIFF?

    Simple. Given a single row that contains StartDate and EndDate columns as DATETIME2 columns, write the code to calculate the duration between those two columns and display it in the HHHHHHHH:MI:SS.mil format. Once that's done, write the code to aggregate that duration that StartDate and EndDate represent for the entire table as a SUM. Post the code and then I'll show you how simple it can really be and how it avoids overflows.

    That's an interesting question. I worked on it a bit and found an anomaly. Consider this query:

    select datediff(microsecond, cast('00:00:00.1234567' as datetime2(7)), cast('00:00:00.7654321' as datetime2(7))), 7654321 - 1234567

    I expected that datediff would return the same result as the simple subtraction. WRONG! I got:

    641976 6419754

    So...what's going on here?

    Gerald Britton, Pluralsight courses

  • January 26, 2015 at 1:42 pm

    #1773223

    g.britton (1/26/2015)

    Jeff Moden (1/24/2015)

    RonKyle (1/24/2015)

    What are the issues with DATETIME2 and DATEDIFF?

    Simple. Given a single row that contains StartDate and EndDate columns as DATETIME2 columns, write the code to calculate the duration between those two columns and display it in the HHHHHHHH:MI:SS.mil format. Once that's done, write the code to aggregate that duration that StartDate and EndDate represent for the entire table as a SUM. Post the code and then I'll show you how simple it can really be and how it avoids overflows.

    That's an interesting question. I worked on it a bit and found an anomaly. Consider this query:

    select datediff(microsecond, cast('00:00:00.1234567' as datetime2(7)), cast('00:00:00.7654321' as datetime2(7))), 7654321 - 1234567

    I expected that datediff would return the same result as the simple subtraction. WRONG! I got:

    641976 6419754

    So...what's going on here?

    Your subtraction is flawed. You went down to ten-millionsth of a second, but microsecond only goes down to millionths of a sec.

    SQL DBA,SQL Server MVP(07, 08, 09) "Money can't buy you happiness." Maybe so, but it can make your unhappiness a LOT more comfortable!

  • January 26, 2015 at 1:44 pm

    #1773225

    Koen Verbeeck (1/20/2015)

    GilaMonster (1/20/2015)

    I use ID, everyone else in my company uses Id.

    So, you switch places with Phil and everything is settled then. 🙂

    Phail & Gil 😀

    [font="Arial"]Low-hanging fruit picker and defender of the moggies[/font]

    For better assistance in answering your questions, please read this[/url].

    Understanding and using APPLY, (I)[/url] and (II)[/url] Paul White[/url]

    Hidden RBAR: Triangular Joins[/url] / The "Numbers" or "Tally" Table: What it is and how it replaces a loop[/url] Jeff Moden[/url]

  • January 27, 2015 at 5:48 am

    #1773356

    Eric M Russell (1/21/2015)

    ProductID, ProductId, and product_id are all respectable options, so long as it's done consistently.

    However, I would take issue with ID by itself, on every table. Anytime I see that, I have low expectations for how the rest of the database is designed.

    Even worse. GUID as a column on every table ...

    I'm a DBA.
    I'm not paid to solve problems. I'm paid to prevent them.

  • January 27, 2015 at 6:40 am

    #1773369

    ScottPletcher (1/26/2015)

    g.britton (1/26/2015)

    Jeff Moden (1/24/2015)

    RonKyle (1/24/2015)

    What are the issues with DATETIME2 and DATEDIFF?

    Simple. Given a single row that contains StartDate and EndDate columns as DATETIME2 columns, write the code to calculate the duration between those two columns and display it in the HHHHHHHH:MI:SS.mil format. Once that's done, write the code to aggregate that duration that StartDate and EndDate represent for the entire table as a SUM. Post the code and then I'll show you how simple it can really be and how it avoids overflows.

    That's an interesting question. I worked on it a bit and found an anomaly. Consider this query:

    select datediff(microsecond, cast('00:00:00.1234567' as datetime2(7)), cast('00:00:00.7654321' as datetime2(7))), 7654321 - 1234567

    I expected that datediff would return the same result as the simple subtraction. WRONG! I got:

    641976 6419754

    So...what's going on here?

    Your subtraction is flawed. You went down to ten-millionsth of a second, but microsecond only goes down to millionths of a sec.

    Duh! Silly me! I changed the diff type to nanosecond and got matching results.

    Gerald Britton, Pluralsight courses

Viewing 15 posts - 76 through 90 (of 103 total)

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