1

I'm using this code to get a mix of Date and Time to use it like a unique ID

DateTime.Now.ToString("yyyyMMddHHmmssfffff")

In SQL Server 2017 I'm confused as to which best smallest size of data type would be that I can use to store this string.

What is the best smallest data type: bigint, timestamp or varchar(19)?

Improve this question
5
  • 4
    The answer totally depends on what you're planning to do with that value later.
    – mustaccio
    Commented Aug 16, 2018 at 2:55
  • 1
    You have data in TIMESTAMP(5) format, and no reason to store it in different format.
    – Akina
    Commented Aug 16, 2018 at 4:34
  • 1
    Please don't store dates and times as strings. Do the conversion in your presentation layer.
    – Colin 't Hart
    Commented Aug 16, 2018 at 8:28
  • 2
    @mustaccio I don't agree. Never store dates as string. What people are going to do with the data later always changes, and you just know someone is going to want to do date arithmetic on it in future. So just use a date/time datatype from the get go.
    – Colin 't Hart
    Commented Aug 16, 2018 at 8:33
  • Thanks all, In fact I am using sync framework that did not allow me to use "auto-increment". that the reason way I have to make it remotely. in the end I will choice BIGINT because that will let me use less conversion.
    – Nano
    Commented Aug 16, 2018 at 18:43

2 Answers 2

Reset to default
5

For sure you don't want to use a varchar: you are trying to store a number, so storing it into a string is a non sense. Plus, regardless of what you are going to do it later, varchar is the field that allows you the less flexibility: really no point in using it.

Your want to avoid timestamp, too, as it is deprecated and anyway it's intended for a different use (it's just a synonym for rowversion)

Your choice is then between bigint and datetime2(5), but given your requested precision of 5 they will both use 8 bytes, so no clear winner here. The only edge that datetime2 may have is that being a datetime it's better handled by datetime functions. Bigint, instead, will allow you to represent negative years (in case you need it).

Improve this answer
6
  • 2
    Stronger: don't store anything that looks like a date and/or time as a string. You many not do it now, but in future someone is going to want to do date arithmetic on it. So use a proper date/time datatype. In this case I would strongly lean towards using datetime2 for this reason.
    – Colin 't Hart
    Commented Aug 16, 2018 at 8:31
  • Better handling by using datetime functions is not the only edge. BIGINT will also allow for numbers like -73 or 2018134525606099999 which would be invalid as datetimes.
    – ypercubeᵀᴹ
    Commented Aug 16, 2018 at 12:42
  • @ypercubeᵀᴹ last time I checked, Earth existed way before (something like 4.543 billion years) than the birth of a random Palestinian guy, not to mention everything that came before the mud ball and the slight chance that this universe will last more than other 7981 years ;-D Not every developer has to deal only with the present....and in that case bigint does have an edge.
    – motoDrizzt
    Commented Aug 16, 2018 at 12:52
  • @motoDrizzt -73 is still invalid as a datetime, even if you consider years in the BC time
    – ypercubeᵀᴹ
    Commented Aug 16, 2018 at 13:08
  • @motoDrizzt - point about dates in general noted. That said, given the question as stated, it's reasonable to assume the OP is using the current date and time as a unique ID.
    – RDFozz
    Commented Aug 16, 2018 at 16:24
4

I'm using this code to get a mix of Date and Time to use it like a unique ID

Is this a small-scale or single-user application? Your code does not guarantee uniqueness. it is possible (albeit highly unlikely) that two computers running the application could generate the same value at the same time.

If you only want a unique value, then use add a sequence ("auto-increment") column to your table and let the database manage the values for you. Your DBMS should expose a method of accessing the last value allocated from that sequence for those cases where you need to insert that value into related tables.

If you're going to be doing anything even remotely Date-related with this value, then store it in a proper Date (or similar) Data Type. Never store Date values in anything else.

Improve this answer
2
  • thanks, that is right in general cases. in fact I am using sync framework that did not allow me to use "auto-increment". that the reason way I have to make it remotely . also dude I am using "fffff" micro seconds for real what the possibility to generate the same value at the same time?? @Phill W
    – Nano
    Commented Aug 16, 2018 at 17:00
  • @Nano: I have no idea what other limitations the Sync framework put on you, but in this cases your solution is to either use a natural Primary Key, or a GUID. (and yes, I checked after commenting: GUID is the way to go in Sync framework if you don't have a natural key.)
    – motoDrizzt
    Commented Aug 20, 2018 at 9:23

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.