SQL Server Performance

varchar(max)

Discussion in 'SQL Server 2005 General DBA Questions' started by ismailadar, May 24, 2010.

  1. ismailadar New Member

    hi all,
    i have a table with three columns.
    First Col is defined varchar(50),Second col is defined char(50),Third col is varchar(max)
    i inserted all colums a simple word like 'example'
    and i wonder how many bytes each columns occupy on my table?
    For eg second columns occupies 50 byte for each record.
    Thanx in advanced
  2. Madhivanan Moderator

    Size of other columns depends on the length of the data
  3. EugeneHwang New Member

    and also depends on if how the SET ANSI_PADDING is set on that table and if allows NULL or not on that column. So it could be net data size or use more space depending on the incoming data. char data type typically use more if PADDING in ON or not trimmed on the data source.
  4. ismailadar New Member

    i know it depends on the length of the data but i can not explain question clearly.
    I wanna to store Customers name on a table.I can select date type varchar(50) or Varchar(Max).
    but i wonder what is the main differences between varchar(50) and varchar(Max)?
    which one shoul be prefered when i store customers name? and why?
  5. FrankKalis Moderator

    Do you really expect a customer name to be of almost 2GB??? Seems to be very unrealistic to me. You should always try to keep column data types as narrow as possible. If you allow varchar(MAX), sooner or later some smart user will put in such data, just to see what happens.
    So, varchar(50) or even something like a varchar(100) sounds much more reasonable. Also, since relational indexes in SQL Server can only be 900 bytes in length, you would need to consider Full-Text indexes if you want to put an index on that column if it was varchar(MAX). You can't use a relational index on such a column.
  6. ismailadar New Member

    i still can not explain my question clearly I did not think to store customer name in a varchr(max) Coloum when i defined a column data type char(50) and insert frank to this column it occupies 50 byte
    if i defined varchar (50) and insert frank to this column how many bytes does it occupies?
  7. FrankKalis Moderator

    Ignoring now all maybe existing internal overhead, it requires 5 bytes. You can check this via the DATALENGTH() function.
  8. ghemant Moderator

Share This Page