SQL Server staging table data type

Question

I want to know what data type I should define in a staging SQL Server table when I don't know the exact data type of the source (csv files).

What are the best practices for optimizing resource?

Example:

• For text column: varchar(255) or nvarchar(255)?
• For date column: date or datetime?
• For decimal column: decimal or float? And what is the best precision in case of decimal?

Answer 1

I believe that there is no general solution to your question. It depends on what data sources you are stagging. I will provide some questions and answers to help you choose the relevant data types.

Can the same column have different genres of data types (text, numeric, binary)?

If yes, then in the staging table, you should use the data type that supports implicit conversion from other types. You can refer to the following documentation to learn more about implicit conversion and SQL data types.

If No, you should use the data type that supports implicit conversion from the others. For instance, if the data source may be of date or datetime types, if the values contain time value, then you should use the DateTime type as it supports storing both types.

Am I working with clean data?

If the data is clean, you should choose a data type similar to the destination database. For instance, if the source column may have an integer or nvarchar data type. At the same time, it always contains an integer value, and no erroneous data exists. Then it would be best if you used an integer data type in the staging table since it decreases the data buffer size.

What do I need in the data destination?

This is the most critical question, as the data consumption should lead you to the relevant data type.

---

Finally, there may be other questions you should ask yourself before deciding. Be aware that using nvarchar for all columns may look like the safest method, but it highly affects the data staging performance. Implicit conversion has a significant impact. You can refer to the following article for more information:

• SSIS Bad Habits: Decreasing SSIS Package Performance

Understanding the task requirements should be your first step in making that decision.