CODEWITHSUNDEEP
sql-server
Liang
Liang

Reputation: 937

SQL Server varchar memory use

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

Upvotes: 0

Views: 7133

Answers (2)

Remus Rusanu
Remus Rusanu

Reputation: 294487

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.

Upvotes: 5

Johnny
Johnny

Reputation: 1143

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?

Upvotes: 1

Related Questions