CODEWITHSUNDEEP
sql-serversqlperformance
Eli
Eli

Reputation: 2608

SQL Server DATE vs INT as primary key

I have a reference table which will be keeping track of a given range of dates over a large number of years, with the most granular level being a given day.
Several colleagues have advised me to use a surrogate INT as a key, with the format of YYYYMMDD. While that does seem to be the logical course, I was wondering about using the simple DATE datatype as a PK instead of the INT. My reasoning for this is two-fold:

  1. DATE displaces 3 bytes of data, whereas an INT displaces 4. While this is not exactly saving much in terms of space, it can save an awful lot in terms of performance. Considering that this table will have covering non-clustered indexes as well, the size of the primary key is quite important as well (hidden index columns).
  2. Since the context in which this data will be used does indeed revolve around dates, I am worried about conversions between INT and DATE. On a small scale, it doesn't make much of a difference; however, once we scale up it may be noticeable.


Upvotes: 1

Views: 2101

Answers (1)

Kevin
Kevin

Reputation: 781

Since I'm doing datawarehousing I would go for the surrogate key, and refer that key in your other tables. Joins based on integers are still faster than on date datatypes

Create a table that you fill with dates, something like this ... and use that datekey in your other tables.

+---------+-----------+-----+------+----+
|DateKey  |Date       |Year |Month |Day |
+---------+-----------+-----+------+----+
|20170320 |20-03-2017 |2017 |3     |20  |
|20170321 |21-03-2017 |2017 |3     |20  |
|20170322 |22-03-2017 |2017 |3     |20  |
+---------+-----------+-----+------+----+

Upvotes: 1

Related Questions