Prevent char() datatype field from padding right of field with spaces.

Posted on 2006-04-27
Last Modified: 2012-06-27
char() datatype is better because it is faster and it takes up less diskspace than varchar(). However, one problem with char() is that it pads it's contents with spaces on the right. This is a paid when creating reports from these fields because I have to use Trim() statements everywhere. Is there a way to have SqlServer automatically trim the spaces to the right when extracting the value of a char() field?
Question by:brokeMyLegBiking
    LVL 142

    Expert Comment

    by:Guy Hengel [angelIII / a3]
    >char() datatype is better because it is faster and it takes up less diskspace than varchar().

    this is no longer true as by today. only use a char only when you have short columns or really fixed-lenght columns
    LVL 142

    Expert Comment

    by:Guy Hengel [angelIII / a3]
    instead of trim(x) you could also use cast(x as varchar(300))   :=)
    LVL 75

    Expert Comment

    by:Aneesh Retnakaran
    U can use Trim or use CAST(urColumn as varchar(255))
    LVL 4

    Author Comment

    Angel, what do you mean that varchar() doesn't take up any more space? Is that as of version 2005?

    If that is the case, then that is good news. So indexes also don't take up any more space with varchar?

    (I am using SqlServer version 2005)
    LVL 20

    Expert Comment

    You can use RTrim to remove spaces on the right
    LVL 142

    Accepted Solution

    >char() datatype is better because it is faster and it takes up less diskspace than varchar().
    that sentence is acutally wrong, CHAR never uses less diskspace than varchar!

    let me clarify:

    CHAR(50) will occupy 50 bytes for every row you store. That was always so and is still a fact (not only for SQL Server)
    VARCHAR(50) will occupy only that much space per row as you give as non-trailing spaces, That is so since at least SQL Server 6.5.

    The main reason why both data types existed is that in earlier times, when CPU power was expensive while VARCHAR as varying-sized field required quite a lot of additional CPU power over the CHAR data type, the choice was much more crutial.
    Today, you will not really see the difference between CHAR() and VARCHAR(), but more about disk space usage, which is still the slowest part in database operations.

    Hence, you will "ALWAYS" use VARCHAR (resp. NVARCHAR) for the string fields, unless you have specify reasons to use CHAR().

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    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

    Performance is the key factor for any successful data integration project, knowing the type of transformation that you’re using is the first step on optimizing the SSIS flow performance, by utilizing the correct transformation or the design alternat…
    International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
    This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
    Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.

    779 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

    14 Experts available now in Live!

    Get 1:1 Help Now