CODEWITHSUNDEEP
sql-serverrow
badpanda
badpanda

Reputation: 2454

Memory Usage in SQL Server?

I have been thinking about database design lately and I have the following question:

When a type, say a varchar(max), is set for a column is 2GB of space set aside every time a row is inserted?

Or is the space allocated on the server equal to the amount of data in the column?

Thanks!

Upvotes: 1

Views: 429

Answers (2)

Aliostad
Aliostad

Reputation: 81660

Of course not :)

Varchar(max) is a bastardised hybrid data type (if you do not mind me being a bit frank):

  • It is stored with the rest of the columns in the row if total row length does not exceed 8KB limit
  • Otherwise stored as a blob. A blob (TEXT,IMAGE) takes as much as its length.

Upvotes: 2

Paul Sasik
Paul Sasik

Reputation: 81489

The varchar data type in SQL Server and elsewhere means roughly variable-length character data. The max or any other constant value represents its upper bound and not its absolute size. So your latter observation is correct:

the space allocated on the server equal to the amount of data in the column

Now, if you define something like char(200) (notice the lack of var in front of char there) then yes, 200 characters are allocated regardless of how much data (up to 200 chars) you store in that field. The maximum upper bound for the char data type is 8000, by the way.

Upvotes: 4

Related Questions