• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 502
  • Last Modified:

char() data type with null values

I have a char(4) field that allwos null. I understand that a char(4) field will always use 4 bytes even if the value in only 3 characters but does it still use 4 bytes if the value is null?

Thanks,
Dean
0
dthansen
Asked:
dthansen
2 Solutions
 
brutaldevCommented:
Yes, there will still be a row with space allocated for the char(4) even though it's NULL.
 
create table Test (Val char(4))
go
sp_spaceused Test  -- Everything is zero
insert into Test values (null)
sp_spaceused Test  -- Allocations were made just the same as passing in a value.

Open in new window

0
 
Philippe DamervalSenior Analyst ProgrammerCommented:
Hi Dean,
fields take at least the amount of memory required to store pointers to the location of their value, and this can vary depending on the field size and the choices made for how data is paged in the database. However, null values do not take the space that non-nulls would. This means that a table with 250k rows and a nullable 4-byte char field will be 1 megabyte smaller if the char field contains nothing but nulls, than if it contains all values. However, the table will be larger with the all-nulls char field than without it.

Philippe
0

Featured Post

Restore individual SQL databases with ease

Veeam Explorer for Microsoft SQL Server delivers an easy-to-use, wizard-driven interface for restoring your databases from a backup. No expert SQL background required. Web interface provides a complete view of all available SQL databases to simplify the recovery of lost database

Tackle projects and never again get stuck behind a technical roadblock.
Join Now