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.