SQL Server varchar memory use

Question

There is a column field1 varchar(32)

If I just store 'ABC' in this field, it takes 3 bytes. When SQL Server access this column and caches it in memory, how much memory does it take? 3 bytes or 32 bytes, and how to prove your answer?

Thanks in advance

Actually, storing ABC in a VARCHAR(32) column uses about 5-7 bytes. There's some overhead involved - two bytes for the fact it's a variable length column, at least one or two more bytes of "general" overhead (like the NULL byte and so on) per row. — marc_s
– marc_s, Commented Aug 1, 2012 at 9:36
This at least 2 byte overhead is one of the reasons why short strings (like 5 characters or less) should not be stored as varchar. A varchar(2) column takes 2-4 bytes of storage - a char(2) always only 2 bytes (since it's not variable length) — marc_s
– marc_s, Commented Aug 1, 2012 at 9:38

Remus Rusanu · Accepted Answer · 2012-08-01 12:08:35Z

5

SQL Server will not cache the column. It will cache the entire page that happen to contain the value, so it will always cache 8192 bytes. The location of the page is subject to things like in-row vs. row-overflow storage and whether the column is sparse or not.

Now a better question would be how much does such a value occupy in the page? The answer is again not straight forward because the value could be stored uncompressed, row-compressed, page compressed or column-compressed. Is true that row compression has no effect on varchar fields, but page compression does.

Now for a straight forward way of answering how much storage does a varchar(32) type value occupy in-row in a non-compressed table the best resource is Inside the Storage Engine: Anatomy of a record. After you read Paul Randal's article you will be able to answer the question, and also prove the answer.

In addition you must consider any secondary index that has this column as a key or includes this column.

answered Aug 1, 2012 at 12:08

Remus Rusanu

296k42 gold badges459 silver badges583 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

jgreve Over a year ago

link update: sqlskills.com/blogs/paul/…

Community · Accepted Answer · 2017-05-23 11:48:14Z

1

50% of their declared size on average. There is a lot documentation on this. Here is where I pulled this from How are varchar values stored in a SQL Server database?

edited May 23, 2017 at 11:48

CommunityBot

11 silver badge

answered Aug 1, 2012 at 9:37

Johnny

1,1539 silver badges7 bronze badges

2 Comments

marc_s Over a year ago

If he's storing just ABC in his column, that won't be using 50% of the declared size ....

John Henckel Over a year ago

Johnny your answer is misleading. The declared size has very little to do with the memory used. In the page you referenced it says... "[The memory consumed is] dependant upon the length of the actual data not the column declaration".

Collectives™ on Stack Overflow

SQL Server varchar memory use

2 Answers 2

1 Comment

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related