CODEWITHSUNDEEP
sql-serverdatabasedatabase-performancevarchar
disasterkid
disasterkid

Reputation: 7278

Difference in performance when inserting a value in a varchar(50) column vs a varchar(2000)?

I'm thinking about increasing the size of one of my columns in my table so that it accommodates lengthier values. But this table undergoes heavy INSERTs on a daily basis, I would like to know if increasing the size will effect my INSERT (and BULK INSERT) speed.

So is there a difference between execution time of inserting a value in a VARCHAR(50) and a, say, VARCHAR(2000) column?

Upvotes: 3

Views: 256

Answers (2)

t-clausen.dk
t-clausen.dk

Reputation: 44336

Changing the length of a varchar from 50 to 2000 doesn't affect anything.

Only issue is once you start populating the column with longer values

From Microsoft

varchar [ ( n | max ) ]

Variable-length, non-Unicode string data. n defines the string length and can be a value from 1 through 8,000. max indicates that the maximum storage size is 2^31-1 bytes (2 GB). The storage size is the actual length of the data entered + 2 bytes. The ISO synonyms for varchar are char varying or character varying.

Upvotes: 2

Patrick Hofman
Patrick Hofman

Reputation: 157048

The influence of the insert statement isn't your greatest concern. Selecting the data from your database can take longer too since the row length gets a lot longer. This will influence read performance and memory pressure.

How big the influence is, and if you should worry about it, depends on the frequency of querying, the total number of rows and possible indexes you have (on that column, or on others).

I would suggest to do some tests in your environment using queries you very often use and test its performance.

Upvotes: 5

Related Questions