0

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

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Liang
  • 867
  • 11
  • 13
  • 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 Aug 01 '12 at 09: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 Aug 01 '12 at 09:38

2 Answers2

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.

Remus Rusanu
  • 288,378
  • 40
  • 442
  • 569
  • link update: https://www.sqlskills.com/blogs/paul/inside-the-storage-engine-anatomy-of-a-record/ – jgreve Mar 27 '20 at 18:58
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?

Community
  • 1
  • 1
Johnny
  • 1,141
  • 9
  • 6
  • 1
    If he's storing just `ABC` in his column, that won't be using 50% of the declared size .... – marc_s Aug 01 '12 at 09:46
  • 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". – John Henckel Nov 04 '22 at 14:12