Tuesday, December 21, 2021

Recommendation for SQL Query if date column data type is NVARCHAR (String)

Problem: If date column in SQL Server table is of NVARCHAR (String) type.

1) Because of nvarchar datatype, date in the column looks like ‘0000-00-00’ or some other date related string.

2) As it is NVARCHAR datatype, we need to use string functions like LEFT,RIGHT or replace to get required value like year / month which will degrade the performance of the query

3) Sorting of data will take more time if date values are as NVARCHAR compare to datetime which reduce the performance the query.

Recommendation:

1) Data type for [date] should be to change to DATETIME

2) If any future dates available that can be converted to ‘1900-01-01’

3) ETL/ETL script should convert any invalid dates to ‘1900-01-01’

4) In this way, we can improve the performance of the query

2 comments:

  1. Henrik Staun PoulsenDecember 22, 2021 at 9:08 AM

    For new databases I would consider using Date or DateTime2(3). Only problem is; one more datatype, that SQL Server might need to implicit conversions on...
    Do you agree?

    ReplyDelete
  2. UnknownJuly 23, 2022 at 5:58 AM

    ZENBroadband
    Zen Internet Broadband is considered as the fastest & reliable broadband for both home & business purpose. The company is having the strength of approximately 413 employees & all of them being dedicated & hard working people making the internet services very reliable. ZENBroadband Because of the hard work of the employees as well as CEO (Paul Stobard) & Chairman (Richard Tang), the company won several awards in 2006 at ISPA i.e. Internet Service Provider’s Association.

    ReplyDelete

Subscribe to: Post Comments (Atom)