1

I have been making modifications in already designed software. I have come across a table which needs a new column called ‘expiry date’ and it would not be a mandatory field. So I have got to options, whether add a column with default values or set it to Null.

Are there any performance issues when using Null values for date column?

Which logical condition is faster?

If ExpirtDate IS NULL

OR

IF ExpiryDate > GetDATE (system date)

user1263981
  • 2,953
  • 8
  • 57
  • 98
  • Have you tested it yourself? – Kermit Sep 10 '13 at 14:41
  • I have gone through the this link http://stackoverflow.com/questions/2116719/sql-using-null-values-vs-default-values but i have not tested it yet. – user1263981 Sep 10 '13 at 14:43
  • Should be comparable, both will match the index on multiple entries in a known range. NULL may have a _very_ slight edge since the index position will be known more quickly in the b-tree traversal, but nothing very significant. As FreshPriceOfSO says, try it out! – PinnyM Sep 10 '13 at 14:44
  • The two queries seem to have different semantics anyway. One will return those with no expiry date at all. And the other will return those with expiry date in the future. – Martin Smith Sep 10 '13 at 14:53
  • if default is to '01/01/2099' then it will bring those ones which are not expired. – user1263981 Sep 10 '13 at 15:02
  • Use NULL. That way there is no possibility of collisions. There is no 100% guarantee that '01/01/2099' will not be a valid date at some point in the applications lifecycle. – Declan_K Sep 10 '13 at 15:07
  • @user1263981 - So your second query should be `ExpiryDate = '20990101'` then. Not use `GETDATE`. Is this the first Y2.1K bug? – Martin Smith Sep 10 '13 at 15:40

0 Answers0