Still some developer use improper data type when designing database.

Storage in very important part in RDBMS that’s why Microsoft provide various data type to solve this problem. OLTP and OLAP database behave same when they processing a query. It will effect overall performance and storage. Finally it will require more infrastructure cost.

Varchar vs Nvarchar:

  • Choice for Unicode / multilingual  data
  • If you want to store only English character and number not good for storage

Think below before you choose.

  • Consume 2 byte for each character (it will increase overall storage, storage will effect on performance)
  • Some byte used when you put index on that column
  • Take care variable data type when you use in store procedure if you used different data type in variable it will degrade your performance.( varchar and nvarchar both are different)

Date vs Datetime

Each date data type have different purpose and storage

Data type Format Range Accuracy Storage size (bytes) User-defined fractional second precision Time zone offset
Time hh:mm:ss[.nnnnnnn] 00:00:00.0000000 through 23:59:59.9999999 100 nanoseconds 3 to 5 Yes No
Date YYYY-MM-DD 0001-01-01 through 9999-12-31 1 day 3 No No
smalldatetime YYYY-MM-DD hh:mm:ss 1900-01-01 through 2079-06-06 1 minute 4 No No
Datetime YYYY-MM-DD hh:mm:ss[.nnn] 1753-01-01 through 9999-12-31 0.00333 second 8 No No
datetime2 YYYY-MM-DD hh:mm:ss[.nnnnnnn] 0001-01-01 00:00:00.0000000 through 9999-12-31 23:59:59.9999999 100 nanoseconds 6 to 8 Yes No
Datetimeoffset YYYY-MM-DD hh:mm:ss[.nnnnnnn] [+|-]hh:mm 0001-01-01 00:00:00.0000000 through 9999-12-31 23:59:59.9999999 (in UTC) 100 nanoseconds 8 to 10 Yes

Be careful when you used this data type in where condition. It may return improper result if you use wrong way.

Data type conversion

The following illustration shows all explicit and implicit data type conversions that are allowed for SQL Server system-supplied data types. These include xml, bigint, and sql_variant. There is no implicit conversion on assignment from the sql_variant data type, but there is implicit conversion to sql_variant.

Ref: https://docs.microsoft.com/en-us/sql/t-sql/data-types/data-type-conversion-database-engine?view=sql-server-2017

Spread the love

Leave a Comment