none
Choosing between char(n) and varchar(n) RRS feed

  • Question

  • I have two fields one is nullable another is non nullable
    max size of both field is 1 character long.
    Now which type should be better to use
    May I use char(1) or varchar(1) in both of my fields
    I have actually million of records to insert so i am thinking about preformance and storage point of view

    Also in another case I have a field which can have from 1 to 10 characters in it then char(10) is better or i should use varchar(10)



    Kamran Shahid Senior Software Engineer/Analyst (MCP,MCAD,MCSD.NET,MCTS,MCPD.net[web])
    Wednesday, February 17, 2010 1:28 PM

Answers

  • Variable length data types take more space and more time to process. Each row holds additional two bytes to keep track of the length because the length of [N]VARCHAR can be different in every row. Also, each row that has variable length columns has another 2 bytes of overhead.

    In addition, SQL Server has to use some processing time to calculate how long each [N]VARCHAR column is in the row.

    On the other side, with fixed length data types the length and starting position of each column is constant and there is no extra overhead.

    In your case using VARCHAR(1) is really a waste because there are at least two bytes of overhead for it, plus the data (could be up to 5 bytes). If you use CHAR(1) then you always have one byte.

    You can do the same calculations for VARCHAR(10) vs. CHAR(10) and decide what makes sense.


    Plamen Ratchev
    • Marked as answer by Kamran Shahid Wednesday, February 17, 2010 6:47 PM
    Wednesday, February 17, 2010 4:58 PM
    Moderator

All replies


  • May I use char(1) or varchar(1) in both of my fields

    varchar(1) makes no sense for me, if u go through the meaning of varchar(n) it says that your column is going to have maximum of n no of charaters i.e 0 to n

    so CHAR(1)

    Also in another case I have a field which can have from 1 to 10 characters in it then char(10) is better or i should use varchar(10)

    i)if you are not sure of definite number of characters that are going to  occupy the column , use varchar(10)

    ii)if you need to allow special characters(non alphanumerics) like chinese use nvarchar(10)

    iii)if you are sure that exactly 10 no  of characters are going to sit in the column, use char(10)

    [Note:you will be wasting your memory in case of char(10) if it is not going to occupy exactly 10 characters ]


    My Name Is Khan should get Oscar!
    • Marked as answer by Kamran Shahid Wednesday, February 17, 2010 1:49 PM
    • Unmarked as answer by Kamran Shahid Wednesday, February 17, 2010 2:04 PM
    Wednesday, February 17, 2010 1:43 PM
  • Use Varchar(10) if that length is variable..  if u always get 10 characters, use char(10)
    U can do a small test like below..


    see the space occupied by char(10) column for 1,00,000 rows--> 12,360 KB
    see the space occupied by varchar(10) column for 1,00,000 rows--> 7576 KB

    create table teststorage
    (
    	col1 char(100)
    )
    
    ;with N as
    (
    	select 0 as num union all select 1 union all select 2 union all select 3 union all select 4 union all 
    	select 5 as num union all select 6 union all select 7 union all select 8 union all select 9 
    ),
    Numbers as
    (
    	select row_number() over (order by N1.Num) as rn from N N1,N N2,N N3,N N4,N N5
    )
    insert into teststorage
    select  replicate('a',abs(checksum(newid()))% 100) from Numbers
    
    
    
    create table teststorage1
    (
    	col1 varchar(100)
    )
    
    ;with N as
    (
    	select 0 as num union all select 1 union all select 2 union all select 3 union all select 4 union all 
    	select 5 as num union all select 6 union all select 7 union all select 8 union all select 9 
    ),
    Numbers as
    (
    	select row_number() over (order by N1.Num) as rn from N N1,N N2,N N3,N N4,N N5
    )
    insert into teststorage1
    select  replicate('a',abs(checksum(newid()))% 100) from Numbers
    
    
    sp_spaceused teststorage
    GO
    sp_spaceused teststorage1
    Wednesday, February 17, 2010 1:49 PM
  • Just thinking if Empty string can be inserted into char(n) or not?
    Like for example i have three scenario
    I might insert A, I might insert empty String [via String.Empty] or i might pu DBNull.Value
    Is all three scenario works with char(1)

    Kamran Shahid Senior Software Engineer/Analyst (MCP,MCAD,MCSD.NET,MCTS,MCPD.net[web])
    Wednesday, February 17, 2010 1:51 PM

  • Just thinking if Empty string can be inserted into char(n) or not?
    Like for example i have three scenario
    I might insert A, I might insert empty String [via String.Empty] or i might pu DBNull.Value
    Is all three scenario works with char(1)


    That is decidd by "Allow Nulls" constraint when you create/modify the table.
    Remember a space is also a character
    My Name Is Khan should get Oscar!
    Wednesday, February 17, 2010 1:55 PM
  • No if we use String.Empty it would not neither insert string with space and neither be Null.
    it is like ''
    Kamran Shahid Senior Software Engineer/Analyst (MCP,MCAD,MCSD.NET,MCTS,MCPD.net[web])
    Wednesday, February 17, 2010 2:03 PM
  • No if we use String.Empty it would not neither insert string with space and neither be Null.
    it is like ''
    Kamran Shahid Senior Software Engineer/Analyst (MCP,MCAD,MCSD.NET,MCTS,MCPD.net[web])

    '' is nothing but NULL
    My Name Is Khan should get Oscar!
    Wednesday, February 17, 2010 2:07 PM
  • Although I have checked that '' is not equal to null
    But my problem is solved
    I have checked it via

    declare

     

    @a char(1)

    set

     

    @a = ''

    if

     

    @a = NULL

    Select

     

    'It is null'

    else

     

    if @a = ''

    Select

     

    'It is empty string'        --- this is waht i am getting

    else

    Select

     

    @a


    Kamran Shahid Senior Software Engineer/Analyst (MCP,MCAD,MCSD.NET,MCTS,MCPD.net[web])
    Wednesday, February 17, 2010 2:16 PM
  • '' is nothing but NULL

    Welcome to the SQL Server forums. Perhaps you meant in Oracle? :)

    In Oracle, '' can be treated like NULL.

    Wednesday, February 17, 2010 2:17 PM
    Moderator
  • SQL Server recommends :

    If you use char or varchar , we recommend the following:

    • Use char when the sizes of the column data entries are consistent.
    • Use varchar when the sizes of the column data entries vary considerably.
     Personally, i never use CHAR. I think CHAR is an artifact of older systems.  I like Tom Kyte's advice ,  "because you'll not be tempted to use a char. If you use it once, you might use it twice, so just DO NOT USE IT"

    He writes for Oracle, but i think it is applicable everywhere. VARCHAR works the way we expect it to. CHAR has special rules on it. Why use CHAR and possibly cause problems for ourselves later?
    Wednesday, February 17, 2010 2:28 PM
    Moderator
  • '' is nothing but NULL

    Welcome to the SQL Server forums. Perhaps you meant in Oracle? :)

    In Oracle, '' can be treated like NULL.


    Hi,
    I have seen two servers installed with SQL server (one with UK dateformat and other with US)
    both have same database with same data in all tables and when i open one of the table, a column shows NULL in one server  and other shows empty(blank).
    Isnt this empty same as NULL?
    My Name Is Khan should get Oscar!
    Wednesday, February 17, 2010 2:49 PM
  • When we're talking about State Abbreviation or 1 character code, I use char, otherwise - varchar.
    Premature optimization is the root of all evil in programming. (c) by Donald Knuth

    Naomi Nosonovsky, Sr. Programmer-Analyst

    My blog
    Wednesday, February 17, 2010 2:51 PM
    Moderator
  • Variable length data types take more space and more time to process. Each row holds additional two bytes to keep track of the length because the length of [N]VARCHAR can be different in every row. Also, each row that has variable length columns has another 2 bytes of overhead.

    In addition, SQL Server has to use some processing time to calculate how long each [N]VARCHAR column is in the row.

    On the other side, with fixed length data types the length and starting position of each column is constant and there is no extra overhead.

    In your case using VARCHAR(1) is really a waste because there are at least two bytes of overhead for it, plus the data (could be up to 5 bytes). If you use CHAR(1) then you always have one byte.

    You can do the same calculations for VARCHAR(10) vs. CHAR(10) and decide what makes sense.


    Plamen Ratchev
    • Marked as answer by Kamran Shahid Wednesday, February 17, 2010 6:47 PM
    Wednesday, February 17, 2010 4:58 PM
    Moderator
  • Although I have checked that '' is not equal to null
    But my problem is solved
    I have checked it via

     


    Kamran Shahid Senior Software Engineer/Analyst (MCP,MCAD,MCSD.NET,MCTS,MCPD.net[web])
    Be careful here.  You can't check is something is "equal" to NULL, because NULL doesn't have a value.

    For example:

    DECLARE @A AS INT = NULL;
    SELECT CASE WHEN @A = NULL THEN 'Null' ELSE 'Not Null' END

    Returns "Not Null"

    You need to check if something IS NULL, rather than checking for equality, so instead:

    DECLARE @A AS INT = NULL;
    SELECT CASE WHEN @A IS NULL THEN 'Null' ELSE 'Not Null' END

    Returns "Null"

    Wednesday, February 17, 2010 6:28 PM
  • No, blank is not the same as NULL.

    SELECT 'Blank_Blank'    WHERE '' = ''        UNION ALL
    SELECT 'Blank_Null'        WHERE '' = NULL        UNION ALL
    SELECT 'Null_Null'        WHERE NULL = NULL;

    Returns 'Blank_Blank' but not the others.

    Wednesday, February 17, 2010 6:37 PM
    Moderator
  • Thanks all guys specially Plamen Ratchev.
    Plamen Ratchev you have explained it Very nicely.
    Kamran Shahid Senior Software Engineer/Analyst (MCP,MCAD,MCSD.NET,MCTS,MCPD.net[web])
    Wednesday, February 17, 2010 6:44 PM
  • Variable length data types take more space and more time to process. Each row holds additional two bytes to keep track of the length because the length of [N]VARCHAR can be different in every row. Also, each row that has variable length columns has another 2 bytes of overhead.

    In addition, SQL Server has to use some processing time to calculate how long each [N]VARCHAR column is in the row.

    On the other side, with fixed length data types the length and starting position of each column is constant and there is no extra overhead.

    In your case using VARCHAR(1) is really a waste because there are at least two bytes of overhead for it, plus the data (could be up to 5 bytes). If you use CHAR(1) then you always have one byte.

    You can do the same calculations for VARCHAR(10) vs. CHAR(10) and decide what makes sense.


    Plamen Ratchev

    Hi,
    Nice explanation on drawbacks of varchar
    But u did not mention any drawback of char?.It has got 2 important disadvantages
    1)wasing the space if data is not of exact size
    2)using the trim function as a consequence of the above
    My Name Is Khan should get Oscar!
    Thursday, February 18, 2010 4:02 AM
  • One thing to consider is that there actually IS a difference if you use a LIKE comparison instead of equals...

    VARCHAR(1) = '' is NOT LIKE ' ' while CHAR(1) = '' IS LIKE ' '

    However, VARCHAR(1) = '' is = ' ' and CHAR(1) = '' is also = ' '

    This is such a specific difference that I cannot believe anyone in their right mind is relying on it. Heck, I only found out about it by accident (one of my "stock" queries used LIKE because I normally include a wildcard but I knew the specific value so did not include the wildcard on one occasion).

    DECLARE @Test_VC1	VARCHAR(1),
    		@Test_VC2	VARCHAR(1),
    		@Test_VC3	VARCHAR(1),
    		@Test_Ch1	CHAR(1),
    		@Test_Ch2	CHAR(1),
    		@Test_Ch3	CHAR(1)
    
    SET @Test_VC1 = ''
    SET @Test_VC2 = ' '
    SET @Test_VC3 = NULL
    SET @Test_Ch1 = ''
    SET @Test_Ch2 = ' '
    SET @Test_Ch3 = NULL
    
    SELECT CASE WHEN @Test_VC1 = '' AND @Test_VC1 = ' ' THEN 'Both'
    			WHEN @Test_VC1 = '' THEN 'Blank'
    			WHEN @Test_VC1 = ' ' THEN 'Space'
    			WHEN @Test_VC1 IS NULL THEN 'NULL'
    			ELSE 'Unknown'
    		END AS "@Test_VC1",
    		CASE WHEN @Test_VC2 = '' AND @Test_VC2 = ' ' THEN 'Both'
    			WHEN @Test_VC2 = '' THEN 'Blank'
    			WHEN @Test_VC2 = ' ' THEN 'Space'
    			WHEN @Test_VC2 IS NULL THEN 'NULL'
    			ELSE 'Unknown'
    		END AS "@Test_VC2",
    		CASE WHEN @Test_VC3 = '' AND @Test_VC3 = ' ' THEN 'Both'
    			WHEN @Test_VC3 = '' THEN 'Blank'
    			WHEN @Test_VC3 = ' ' THEN 'Space'
    			WHEN @Test_VC3 IS NULL THEN 'NULL'
    			ELSE 'Unknown'
    		END AS "@Test_VC3",
    		CASE WHEN @Test_Ch1 = '' AND @Test_Ch1 = ' ' THEN 'Both'
    			WHEN @Test_Ch1 = '' THEN 'Blank'
    			WHEN @Test_Ch1 = ' ' THEN 'Space'
    			WHEN @Test_Ch1 IS NULL THEN 'NULL'
    			ELSE 'Unknown'
    		END AS "@Test_Ch1",
    		CASE WHEN @Test_Ch2 = '' AND @Test_Ch2 = ' ' THEN 'Both'
    			WHEN @Test_Ch2 = '' THEN 'Blank'
    			WHEN @Test_Ch2 = ' ' THEN 'Space'
    			WHEN @Test_Ch2 IS NULL THEN 'NULL'
    			ELSE 'Unknown'
    		END AS "@Test_C2",
    		CASE WHEN @Test_Ch3 = '' AND @Test_Ch3 = ' ' THEN 'Both'
    			WHEN @Test_Ch3 = '' THEN 'Blank'
    			WHEN @Test_Ch3 = ' ' THEN 'Space'
    			WHEN @Test_Ch3 IS NULL THEN 'NULL'
    			ELSE 'Unknown'
    		END AS "@Test_Ch3",
    		CASE WHEN @Test_VC1 LIKE '' AND @Test_VC1 LIKE ' ' THEN 'Both'
    			WHEN @Test_VC1 LIKE '' THEN 'Blank'
    			WHEN @Test_VC1 LIKE ' ' THEN 'Space'
    			WHEN @Test_VC1 IS NULL THEN 'NULL'
    			ELSE 'Unknown'
    		END AS "@Test_VC1",
    		CASE WHEN @Test_VC2 LIKE '' AND @Test_VC2 LIKE ' ' THEN 'Both'
    			WHEN @Test_VC2 LIKE '' THEN 'Blank'
    			WHEN @Test_VC2 LIKE ' ' THEN 'Space'
    			WHEN @Test_VC2 IS NULL THEN 'NULL'
    			ELSE 'Unknown'
    		END AS "@Test_VC2",
    		CASE WHEN @Test_VC3 LIKE '' AND @Test_VC3 LIKE ' ' THEN 'Both'
    			WHEN @Test_VC3 LIKE '' THEN 'Blank'
    			WHEN @Test_VC3 LIKE ' ' THEN 'Space'
    			WHEN @Test_VC3 IS NULL THEN 'NULL'
    			ELSE 'Unknown'
    		END AS "@Test_VC3",
    		CASE WHEN @Test_Ch1 LIKE '' AND @Test_Ch1 LIKE ' ' THEN 'Both'
    			WHEN @Test_Ch1 LIKE '' THEN 'Blank'
    			WHEN @Test_Ch1 LIKE ' ' THEN 'Space'
    			WHEN @Test_Ch1 IS NULL THEN 'NULL'
    			ELSE 'Unknown'
    		END AS "@Test_Ch1",
    		CASE WHEN @Test_Ch2 LIKE '' AND @Test_Ch2 LIKE ' ' THEN 'Both'
    			WHEN @Test_Ch2 LIKE '' THEN 'Blank'
    			WHEN @Test_Ch2 LIKE ' ' THEN 'Space'
    			WHEN @Test_Ch2 IS NULL THEN 'NULL'
    			ELSE 'Unknown'
    		END AS "@Test_C2",
    		CASE WHEN @Test_Ch3 LIKE '' AND @Test_Ch3 LIKE ' ' THEN 'Both'
    			WHEN @Test_Ch3 LIKE '' THEN 'Blank'
    			WHEN @Test_Ch3 LIKE ' ' THEN 'Space'
    			WHEN @Test_Ch3 IS NULL THEN 'NULL'
    			ELSE 'Unknown'
    		END AS "@Test_Ch3"
    
    


    Thursday, December 5, 2019 4:57 PM
  • >>  Why use CHAR(n) and possibly cause problems for ourselves later?<<

    Because the length of a column is very important for many encoding schemes. Consider this:
    ..
    customer_zip_code CHAR(5) NOT NULL 
      CHECK (customer_zip_code LIKE '[0-9][0-9][0-9][0-9][0-9]'), 
     ..

    A ZIP Code is always defined as five digits. The constraints on the table now assure that, so it's preventing problems and avoiding a lot of extra code in the host programs.

    --CELKO-- Books in Celko Series for Morgan-Kaufmann Publishing: Analytics and OLAP in SQL / Data and Databases: Concepts in Practice Data / Measurements and Standards in SQL SQL for Smarties / SQL Programming Style / SQL Puzzles and Answers / Thinking in Sets / Trees and Hierarchies in SQL

    Thursday, December 5, 2019 8:44 PM
  • A ZIP Code is always defined as five digits.
    No, they are not. Ever seen a British zip code?

    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

    Thursday, December 5, 2019 10:49 PM