CODEWITHSUNDEEP
azure-sql-databasevarcharmax
Java
Java

Reputation: 1292

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?

Upvotes: -1

Views: 472

Answers (1)

Saideep Arikontham
Saideep Arikontham

Reputation: 6124

Does using varchar(max) truncate unused space in SQL?

  • The space used when using either varchar(n) or varchar(max) depends on the length of the input data. When referring to this official Microsoft documentation, it can be understood that varchar is used when the input string data differ in size (amount of space taken to store this data is given in the document).

  • So, both varchar(n) and varchar(max), by definition, allocate space based on how much the input data requires.

It is recommended to use varchar(n) unless the storage limit exceeds 8000 bytes.

Why does using varchar(max) create smaller size of storage?

  • The following is a demonstration that I have done to verify whether using varchar(500) and varchar(max) is the reason for different storage sizes of the tables.

  • I have also imported a flat file to create 2 different tables, country_data_500 where all columns are varchar(500) allowing nulls and country_data_max where all columns are varchar(max) allowing nulls.

enter image description here

  • Now when I look at the size of these tables, they both use same amount of space.

  • For country_data_500:

enter image description here

  • For country_data_max:

enter image description here

  • I have used sys.dm_db_index_physical_stats to get details about the statistics of these tables. I run the following query in My azure SQL Database:
SELECT OBJECT_NAME([object_id]) AS TableName, 
       alloc_unit_type_desc, 
       record_count, 
       min_record_size_in_bytes, 
       max_record_size_in_bytes
FROM sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, 'DETAILED')
WHERE OBJECT_NAME([object_id]) LIKE 'country_data%';

enter image description here

  • From all the above results, it can be concluded that the usage of varchar(n) or varchar(500) is not the reason for different table storage sizes.

Please recheck the procedure because there might be some other underlying issue for why your tables are taking up different amounts of space (more for varchar(500) and less for varchar(max))

Upvotes: 1

Related Questions