Performance difference when entering a value in varchar (50) vs varchar (2000) column?
I am thinking about increasing the size of one of my columns in my table to accommodate longer values. But this table is exposed to heavy daily INSERT
, I would like to know if the increase in size will affect the speed INSERT
(s BULK INSERT
).
So, is there a difference between the time it takes to insert a value into a column VARCHAR(50)
and a, say VARCHAR(2000)
?
source to share
Changing the varchar length from 50 to 2000 has no effect.
The error is only after you start filling the column with longer values
From Microsoft
varchar [(n | max)]
Variable length string data, not Unicode. n specifies the length of the string and can range from 1 to 8,000. max indicates that the maximum storage size is 2 ^ 31-1 bytes (2 GB). The storage size is the actual data length entered + 2 bytes. ISO synonyms for varchar: char differ or change characters.
source to share
The influence of expression is insert
not your biggest concern. It can take longer to fetch data from your database as the string length gets much longer. This will affect read performance and memory pressure.
How big the impact is, and if you're worried about it, depends on the query rate, the total number of rows, and the possible indexes you have (on this column or others).
I would suggest doing some tests in your environment using queries you frequently use and check its performance.
source to share