Tech Off Thread

9 posts

Forum Read Only

This forum has been made read only by the site admins. No new threads or comments can be added.

Variable length fields and fixed length fields in MSSQL

Back to Forum: Tech Off
  • User profile image
    cheong

    Hello,

           I'm wondering: If I have a table with only fixed length fields, will the SQL server have any optimization to give me performance gain in accessing the table?

           If not, is there any case when we should still use nchar(4) instead of nvarchar(4)? Should I use nvarchar(or varchar) all the time?

           There seems not much authoritive answers online. (They just say "use fixed length fields if you expect they are of the same length and variable length fields if otherwise") The MSDN just explain they're fixed length/variable length instead of explicitly telling us when to use it.

    Recent Achievement unlocked: Code Avenger Tier 4/6: You see dead program. A lot!
    Last modified
  • User profile image
    littleguru

    Hi,

    you should use fixed length fields always if you have fixed length text. First it saves you disk space. If you have a varchar field, for example, the database engine always reserves the space you specified with the varchar, also if your string is not that long!

    Fixed length is also faster in searches and updates/inserts. The database engine knows that the fields are always that long and can optimize it therefore. There are optimizations for varchar in each database system that is sure, but working with fixed size items is a lot easier for the database system (search threes are flatter etc).

    char(x) and nchar(x) are fixed size.

  • User profile image
    pacelvi

    littleguru wrote:
    Hi,

    you should use fixed length fields always if you have fixed length text. First it saves you disk space. If you have a varchar field, for example, the database engine always reserves the space you specified with the varchar, also if your string is not that long!


    You're wrong.  Varchar's only store the amount of text entered, not reserved.

    littleguru wrote:
    
    Fixed length is also faster in searches and updates/inserts. The database engine knows that the fields are always that long and can optimize it therefore. There are optimizations for varchar in each database system that is sure, but working with fixed size items is a lot easier for the database system (search threes are flatter etc).

    char(x) and nchar(x) are fixed size.


    Here's a summary of how it works

    char , NOT NULL , ANSI PADDING (ON) : Trailing spaces to length of column created

    char, NOT NULL, ANSI PADDING (OFF): Trailing spaces to length of column created

    char, NULL, ANSI PADDING (ON): Trailing spaces to length of column created

    char, NULL, ANSI PADDING (OFF): Variable storage, no trailing spaces created, trailing spaces present are preseverd



    varchar , NOT NULL , ANSI PADDING (ON) : Variable storage, no trailing spaces created, yet trailing spaces present are preserved

    varchar, NOT NULL, ANSI PADDING (OFF): Variable storage, no trailing spaces created

    varchar, NULL, ANSI PADDING (ON): Variable storage, no trailing spaces created,yet trailing spaces present are preserved


    varchar, NULL, ANSI PADDING (OFF): Variable storage, no trailing spaces created

  • User profile image
    SimonJ

    Use fixed length if the data is always that long - EG a product code is ALWAYS 10 characters long then use char(10) or nchar(10). But if the product code might vary in length IE be UP TO 10 characters long, use varchar(10) or nvarchar(10).

    If you use char/nchar and supply less than the number of characters specified, SQL Server will pad the data out with extra spaces. This can be confusing.

    SQL Server uses 8KB pages and packs as many rows to a page as it thinks fit based on the defined column sizes and the actual data in the pages.

    While varchar and nvarchar data varies in length, SQL Server calculates the actual size of the rows and packs them into the database quite efficiently. If you specify varchar(2000) for a column but only store a few characters in each row, there is not a huge amount of wasted space as SQL Server recognises that it could pack the data more efficiently and does so. Updating an existing row so it no longer fits in it's currently allocated slot will either cause the whole row to be rewitten to a less densly packed page or cause the overflowing varchar data to be written to an overflow area (New SQL Server 2005 feature).

    SimonJ

  • User profile image
    littleguru

    I was wrong then. I was reading that somewhere, but it's still some time ago...

    Now I'm also confused. There seem to be no differences between using varchar or char. I mean at least from the performance standpoint.

    SimonJ wrote:
    
    Updating an existing row so it no longer fits in it's currently allocated slot will either cause the whole row to be rewitten to a less densly packed page or cause the overflowing varchar data to be written to an overflow area (New SQL Server 2005 feature).


    O well that moving around could it make slow...

  • User profile image
    pacelvi

    littleguru wrote:
    

    I was wrong then. I was reading that somewhere, but it's still some time ago...

    Now I'm also confused. There seem to be no differences between using varchar or char. I mean at least from the performance standpoint.

    SimonJ wrote: 
    Updating an existing row so it no longer fits in it's currently allocated slot will either cause the whole row to be rewitten to a less densly packed page or cause the overflowing varchar data to be written to an overflow area (New SQL Server 2005 feature).


    O well that moving around could it make slow...



    I would always go with nchar or nvarchar to get automatic Unicode support and never face the possibility of code page or collation issues.

  • User profile image
    SimonJ

    Yes, I always use nchar or nvarchar so you don't have to worry about "foreign" characters.

    You do, however, still have to pick the right collation sequence for the server. You should then leave the database and column collations as the default so you don't get collation mismatches between tables. These can get in the way of creating relations or joins.

    Use the Windows collations rather than the SQL Server specific ones whenever possible. EG Latin1_General_CI_AS (IE case insensitive, accent sensitive) rather than SQL_Latin1_General_CI_AS. The SQL variants are included for backwards compatibility with SQL Server 7. There may be very little difference between the two but SQL Server treates it like one is English and the other Hungarian. If the order of the letters in the dictionary is different, you can't use the index to find the records that match.

    SimonJ

  • User profile image
    cheong

    littleguru wrote:

    Now I'm also confused. There seem to be no differences between using varchar or char. I mean at least from the performance standpoint.


    Yes. That's exactly why I asked this question.

    A discussion made on another forum made me confused. Some people there suggested we should use varchar/nvarchar everywhere as the modern SQL server algorithm have been enhanced so that there's little or nothing you could gain by using char/nchar. While I was in doubt, I can't find anything effectively suggest otherwise.

    Although I usually "follow the rules" on using char/nchar on fixed length ones and varchar/nvarchar otherwise, I wasn't very sure if it'll do any good to overall database design.

    Thanks everyone for information provided. Smiley

    Recent Achievement unlocked: Code Avenger Tier 4/6: You see dead program. A lot!
    Last modified
  • User profile image
    skiplogic

    If a column's data does not vary widely in length, consider using a fixed-length CHAR field instead of a VARCHAR. While it may take up a little more space to store the data, processing fixed-length columns is faster in SQL Server than processing variable-length columns. [6.5, 7.0, 2000, 2005] Updated 8-21-2005

    ( stolen from http://www.sql-server-performance.com/tips/datatypes_p1.aspx )

Conversation locked

This conversation has been locked by the site admins. No new comments can be made.