CODEWITHSUNDEEP
sql-servert-sqldatetime
mgmedick
mgmedick

Reputation: 700

tsql datetime vs int query performance

Would it make any sense to store a datetime as an int or bigint for performance purposes. Wondering if indexing/querying the integer column would yield better performance than a datetime column.

If there is a performance difference does anyone know if it is substantial, or relatively negligible.

Upvotes: 2

Views: 2165

Answers (2)

radar
radar

Reputation: 13425

If you are getting the time from client side and storing it in the database that would come in different formats.

In SQL Server you can set only single collation for datetime, so it is ideal to store the value as bigint in UNIX time format ( i.e number of seconds since January 1st, 1970 )

In case the time is relative and only generated on the machine where SQL server is residing , the date time would make sense, but store it in UTC so that it is easy to convert to other time zones for reporting or display purpose.

The conversion from Unix time to Date time for display purpose should not be of much performance impact.

Upvotes: 0

Bryan
Bryan

Reputation: 17703

Use datetime2. Any storage benefit realized using an int datatype will be negated by the conversion and parsing code required to display, aggregate, or compare values.

Upvotes: 1

Related Questions