-5

Which type, between smalldatetime and datetime2(0), is the most performant to store datetime values with a minute precision?

Thanks in advance!

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Gink
  • 3
  • 2
  • 5
    What do you mean 'most performant' are you concerned with query speed, data size for storage? Also, how are you using this field? Are you calculating using it for example? – Rich Benner Jun 22 '16 at 10:46
  • I mean speed and data size. I use it to store data coming from user. For example "data\time insert record" – Gink Jun 22 '16 at 10:55
  • @Gink, If you are interested in accuracy, then DateTime is accurate to three-hundredths of a second. SmallDateTime is accurate to one minute. You may refer[link] (http://sqlcoach.blogspot.in/2007/08/sql-server-storing-time-coming-soon.html) – Ruhaan Jun 22 '16 at 10:56
  • _"Also, how are you using this field?"_ - @Gink, think of it very much. In some cases it could be much better even to store date/time in 2 or more columns of type smallint and so on. – Ivan Starostin Jun 22 '16 at 10:58
  • 1
    @Ruhaan, thanks for the answer. So smalldatetime takes 4 bytes and datetime2(0) takes 6 bytes. Smalldatetime seems to be the best for my scope, but i'm not sure Microsoft giude say **"Use the time, date, datetime2 and datetimeoffset data types for new work. These types align with the SQL Standard. They are more portable. time, datetime2 and datetimeoffset provide more seconds precision. datetimeoffset provides time zone support for globally deployed applications."** why? Is datetime2(0) faster? – Gink Jun 22 '16 at 11:12
  • @Gink, Well that depends upon how you want to use your data. And you are Welcome :) Also have look at here (http://stackoverflow.com/questions/1334143/sql-server-datetime2-vs-datetime) – Ruhaan Jun 22 '16 at 11:15

1 Answers1

0

These types align with the SQL Standard. They are more portable.

This means that if you want to develop applications against the database, you will have a wider flexibility choosing languages and platforms because those formats are more widely implemented.

datetimeoffset support for globally deployed applications.

This means that if your application is deployed to regional settings different from say the US it will be more compatible with the client OS settings.

smalldatetime is going to perform better because it is using less bytes, plain and simple. However it might not be the best choice for an application for the reasons above. Also, it depends on infrastruture limitations, the number of records and design (joins etc...). I have queried against datetime2 columns on tables with hundreds of millions of records and received sub second results.

Joe C
  • 3,925
  • 2
  • 11
  • 31