ID or Id

Question

ID or Id

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

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

TomThomson SSC Guru Points: 104773 More actions · Answer 1

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

ScottPletcher SSC Guru Points: 101087 More actions · Answer 2

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) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

Jeff Moden SSC Guru Points: 1004310 More actions · Answer 3

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)

RonKyle SSC-Dedicated Points: 31555 More actions · Answer 4

RonKyle

SSC-Dedicated

Points: 31555

January 24, 2015 at 8:06 am

#1772908

What are the issues with DATETIME2 and DATEDIFF?

Eric M Russell SSC Guru Points: 125560 More actions · Answer 5

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

Jeff Moden SSC Guru Points: 1004310 More actions · Answer 6

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)

Jeff Moden SSC Guru Points: 1004310 More actions · Answer 7

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)

RonKyle SSC-Dedicated Points: 31555 More actions · Answer 8

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.

Eric M Russell SSC Guru Points: 125560 More actions · Answer 9

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

Jack Corbett SSC Guru Points: 184394 More actions · Answer 10

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

g.britton SSChampion Points: 13863 More actions · Answer 11

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:

6419766419754

So...what's going on here?

Gerald Britton, Pluralsight courses

ScottPletcher SSC Guru Points: 101087 More actions · Answer 12

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:
6419766419754
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) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

ChrisM@home SSC-Insane Points: 24260 More actions · Answer 13

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]

andrew gothard SSChampion Points: 12314 More actions · Answer 14

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.

g.britton SSChampion Points: 13863 More actions · Answer 15

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:
6419766419754
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