azure sql database – Best practice of creating table with CSV file & using Varchar(MAX)

I have a question regards to creating a table in Azure SQL Server by importing a flat file (csv).

I am having an issue with size of length with table I created (if size exceeds, I get an error), so I decided to go with all varchar(max) and allow null on all columns.

I don’t think allow null is any issue, but I am curious about implication of using varchar(max) for all the time.

I also noticed that when I create table with fixed length (example, varchar(500)), the size of overall each table gets so big.

But, when I tried with varchar(max), the size of table is much smaller.

Why does using varchar(max) create smaller size of storage?
Does using (max) truncate unused space in SQL?

Read more here: Source link