char() data type with null values

Posted on 2011-04-27
Last Modified: 2012-05-11
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?

Question by:dthansen
    LVL 11

    Assisted Solution

    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))
    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

    LVL 9

    Accepted Solution

    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.


    Featured Post

    How to run any project with ease

    Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
    - Combine task lists, docs, spreadsheets, and chat in one
    - View and edit from mobile/offline
    - Cut down on emails

    Join & Write a Comment

    In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
    JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
    Via a live example, show how to setup several different housekeeping processes for a SQL Server.
    Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

    745 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    18 Experts available now in Live!

    Get 1:1 Help Now