Getting Started with Adobe After Effects - Part 6: Motion Blur
First Time? You can support us by signing up. It takes only 5 seconds. Click here to sign up. If you already have an account, click here to login.
Loading

1st Prize - Nokia Lumia 710


Win 31 copies of

SQLServer Quiz 2012 - It is saying that where you should use nail, you should not use sword

  • It is saying that where you should use nail, you should not use sword. Every little tool has its own usage and its own purpose. What are the differences of CHAR, VARCHAR, NVARCHAR and VARCHAR(MAX) datatypes?

    Posted on 01-15-2012 00:00 |
    InterviewQuestions
    73 · 2% · 775

11  Answers  

Subscribe to Notifications
Previous 1 | 2 Next
  • Score
    8

    nchar and nvarchar can store Unicode characters.

    char and varchar cannot store Unicode characters.

    char and nchar are fixed-length which will reserve storage space for number of characters you specify even if you don't use up all that space.

    varchar and nvarchar are variable-length which will only use up spaces for the characters you store. It will not reserve storage like char or nchar.

    varchar(max) data types can hold the same amount of data BLOBs can hold (2 GB) and they are stored in the same type of data pages used for other data types.

    Replied on Jan 15 2012 8:51PM  . 
    Anup Warrier
    209 · 1% · 224
  • Score
    9

    Character data types that are either fixed-length, nchar, or variable-length, nvarchar, Unicode data and use the UNICODE UCS-2 character set.

    nchar [ ( n ) ]

    Fixed-length Unicode character data of n characters. n must be a value from 1 through 4,000. The storage size is two times n bytes. The ISO synonyms for nchar are national char and national character.

    char[(n)]

    Fixed-length non-Unicode character data with length of n bytes. n must be a value from 1 through 8,000. Storage size is n bytes. The SQL-92 synonym for char is character.

    varchar[(n)]

    Variable-length non-Unicode character data with length of n bytes. n must be a value from 1 through 8,000. Storage size is the actual length in bytes of the data entered, not n bytes. The data entered can be 0 characters in length. The SQL-92 synonyms for varchar are char varying or character varying.

    nvarchar [ ( n | max ) ]

    Variable-length Unicode character data. ncan be a value from 1 through 4,000. max indicates that the maximum storage size is 2^31-1 bytes. The storage size, in bytes, is two times the number of characters entered + 2 bytes. The data entered can be 0 characters in length. The ISO synonyms for nvarchar are national char varying and national character varying.

    Fixed-length (char) or variable-length (varchar) character data types.


    When n is not specified in a data definition or variable declaration statement, the default length is 1. When n is not specified with the CAST function, the default length is 30.

    Objects using char or varchar are assigned the default collation of the database, unless a specific collation is assigned using the COLLATE clause. The collation controls the code page used to store the character data.

    Sites supporting multiple languages should consider using the Unicode nchar or nvarchar data types to minimize character conversion issues. If you use char or varchar:

    • Use char when the data values in a column are expected to be consistently close to the same size.

    • Use varchar when the data values in a column are expected to vary considerably in size.


    If SET ANSI_PADDING is OFF when CREATE TABLE or ALTER TABLE is executed, a char column defined as NULL is handled as varchar.


    When the collation code page uses double-byte characters, the storage size is still n bytes. Depending on the character string, the storage size of n bytes may be less than n characters.

    Char and Varchar cannot store Unicode characters. ‘n’ means it can store Unicode characters such as: nchar and nvarchar.


    For example, if we set the length to 10: char(10), varchar(10), and we set the value to ‘apple’: The stored data for Char(10) is: ‘apple ’. here, space is added The stored data for Varchar(10) is: ‘apple’.

    [Example 1]

    DECLARE @myChar CHAR(100) , @myVarchar VARCHAR(100)
    SET @myChar = 'SQL'
    SET @myVarchar = 'SQL'
    SELECT '[BEGIN]' + @myChar + '[END]' AS CHAR_Data
    SELECT '[BEGIN]' + @myVarchar + '[END]' AS VARCHAR_Data
    /*
    You can see in [Example 1] how the use of VARCHAR in most cases is preferred, to save space.  Let's take a look at a script; it declares a CHAR and a VARCHAR variable, sets each equal to the string, and then SELECTs each variable to display what is actually stored 
    */
    


    When we Execute with Like Query it returns diffrent output Please see following example

    [Example 2]

    CREATE TABLE #TBLChar (name CHAR(5) NOT NULL)
    CREATE TABLE #TBLVARCHAR (name VARCHAR(200) NOT NULL)
    GO
    
    Insert into #TBLChar values
    ('B'),('B '),('B  '),('B   ')
    
    Insert into #TBLVARCHAR values
    ('B'),('B '),('B  '),('B   ')
    
    Select * from #TBLChar where name like 'b %'
    
    Select * from #TBLVARCHAR where name like 'b %'
    
    --similarly for compairision it will differ
    Drop Table #TBLChar 
    
    Drop Table #TBLVARCHAR
    
    /*
        Here for like output get changed 
        returns 4 row for first case ie. all rows 
        returns 3 row for second case ie. specific rows 
    */
    


    If you look closely following Example Query Plan, the query that uses an nvarchar parameter does an index scan while the one that users varchar does an index seek. This is very important, because an index seek is orders of magnitude faster than a scan.

    The reason this occurs is because the parameter and column have different collation sets and nvarchar is for unicode.

    [Example 3]

    BEGIN TRANSACTION
    GO
    CREATE TABLE dbo.Users
         (
         id INT NOT NULL,
         username VARCHAR(50) NOT NULL
         )
    GO
    ALTER TABLE dbo.Users ADD CONSTRAINT
         PK_Users_id PRIMARY KEY CLUSTERED
         (
         id
         )
    
    GO
    CREATE UNIQUE NONCLUSTERED INDEX IX_Users_username ON dbo.Users
         (
         username
         )
    GO
    COMMIT
    


    Another problem with using char instead of varchar when the data length can vary is that you'll wind up using the RTrim() function throughout views and sprocs - any small performance advantage there used to be using char when the data length didn't vary too much is lost and more so by all the calls to RTrim().

    I agree with using char for a nullable field if the data length is always exactly the same (like a fixed length account number).Even though SQL Server will use a varchar behind the scene, it tells you that this field will return null or exactly X characters. How about this for a basic principal: The database schema should describe the data as accurately as possible.

    Those special characters are still ASCII characters, so you can store them in a varchar column.

    Here's a link to the entire ASCII set http://www.codetoad.com/html/text/ascii_characters.asp


    varchar is good for English, Spanish, German, French and many other European languages.

    You'll need nvarchar for languages like Japanese, Korean, Hindi and Arabic that aren't descendents of Latin languages.


    Thanks

    Yogesh

    Replied on Jan 15 2012 10:28PM  . 
    Yogesh Kamble
    143 · 1% · 349
  • Score
    6

    char vs varchar: CHAR datatype stores fix length non-unicode data bytes. VARCHAR datatype stores variable length non-unicode datatype. So if length of CHAR type is 10 , it will take 10 bytes memory irrespective of the data stored. If length of VARCHAR type is 10 , it will take whatever the length of the actual data stored .

    Vs NVARCHAR: NVARCHAR stores unicode data types. It is helpful in application where for example multiple language details are stored. It can hold maximum 4000 characters with each character taking 2 bytes.But VARCHAR can hold maximum 8000 chars with each char taking 1 byte.

    Vs NVARCHAR(MAX): MAX datatypes can hold data upto 2GB . It is helpful is storing BLOB datatypes(as it doesnot need LOB datapages as needed by older datatypes like TEXT,IMAGE etc.

    Replied on Jan 15 2012 10:51PM  . 
    satyajit
    126 · 1% · 402
  • Score
    4

    Char datatype used for non unicode data type and fixed length, i.e. it will store data only in english data. If user want to save SERVER and datatype defined as CHAR(10), in DB it will occupy 10 byte. Varchar datatype used for non unicode data type and variable length, i.e. it will store data only in english data. If user want to save SERVER and datatype defined as VARCHAR(10), in DB it will occupy 6 byte and it will save 4 byte in storage. NVarchar datatype used for unicode data type and variable length, i.e. it will store data in any langugage. If your application supports multi langugage then only use this datatype. As while savin for each charcater it requires 2 byte. If user want to save SERVER and datatype defined as NVARCHAR(10), in DB it will occupy 12 byte. For multilanguage saving N should be prefixed with data i.e. N'Server' VARCHAR(Max) it is same like varchar, using this data type text data can be saved upto 2 GB. Using Varchar can save only 8000 characters. In future TEXT datatype will be depreciated, so use Varchar(Max).

    Replied on Jan 16 2012 3:34AM  . 
    sk2000
    515 · 0% · 73
  • Score
    9

    Here are the differences between the datatypes:

    alt text

    Apart, Few Points/considerations for usage and performance:

    1. Always specify the narrowest columns you can. The narrower the column, the less amount of data SQL Server has to store, and the faster SQL Server is able to read and write data. In addition, if any sorts need to be performed on the column, the narrower the column, the faster the sort will be.

    2. Don’t use the NVARCHAR or NCHAR data types unless you need to store 16-bit character (Unicode) data. They take up twice as much space as VARCHAR or CHAR data types, increasing server I/O and wasting unnecessary space in your buffer cache.

    3. 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.

    4. If you are using fixed length columns (CHAR, NCHAR) in your table, consider avoiding storing NULLs in them. If you do, the entire amount of space dedicated to the column will be used up.

    5. Take care when using Unicode data in your queries, as it can affect query performance. A classic problem is related to an application passing in Unicode literals, while the column searched in the database table is non-Unicode. This, of course, may be visa-versa depending on your scenario.

    Replied on Jan 16 2012 6:04AM  . 
    Latheesh NK
    51 · 4% · 1178
  • Score
    4

    When you want to store non - unicode data in the column then you need to use char and varchar data types. When you wnat to store unicode data in the column then you need to use nchar and nvarchar datatypes.

    Here we need to decide the length of the data to be stored, When you decide to have fixed length data then use char and nchar datatypes.

    for example char(10), in this datatype if you store the data less than 10 charecters the remaining charecters will be padded with blank spaces. char(10) : 10 bytes of size will be allocated nchar(10) : 20 bytes of size will be allocated as it should store unicode data.

    When you decide to have variable length data then use varchar and nvarchar datatypes.

    for example varchar(10), in this datatype if you store the data less than 10 charecters the remaining charecters will be left free. char(10) : 10 bytes of size will be allocated nchar(10) : 20 bytes of size will be allocated as it should store unicode data.

    Replied on Jan 16 2012 7:03AM  . 
    prasaddvr
    1256 · 0% · 20
  • Score
    5

    CHAR [ ( n ) ]
    Fixed-length, non-Unicode character data with a length of n bytes. n must be a value from 1 through 8,000. The storage size is n bytes. The ISO synonym for char is character.Best used when data length is constant, e.g. social security numbers or ISBN numbers.

    VARCHAR [ ( n ) ]
    Variable -length, non-Unicode character data with a length of n bytes. n must be a value from 1 through 8,000. The storage size is n bytes. The ISO synonyms for varchar are char varying or character varying. e.g. last names or product SKU codes.

    NVARCHAR
    Variable-length Unicode character data. ncan be a value from 1 through 4,000. max indicates that the maximum storage size is 2^31-1 bytes. The storage size, in bytes, is two times the number of characters entered + 2 bytes. The data entered can be 0 characters in length. The ISO synonyms for nvarchar are national char varying and national character varying.

    VARCHAR[ (max ) ]
    Max indicates that the maximum storage size is 2^31-1 bytes. The storage size is the actual length of data entered + 2 bytes. The data entered can be 0 characters in length. The ISO synonyms for varchar are char varying or character varying.

    When n is not specified in a data definition or variable declaration statement, the default length is 1. When n is not specified when using the CAST and CONVERT functions, the default length is 30.

    Here are a few general rules that should help:

    • Use a variable length when the values vary a lot in size.
    • Use char when the sizes of the column data entries are consistent.
    • Use varchar when the sizes of the column data entries vary considerably.
    • Use varchar(max) when the sizes of the column data entries vary considerably, and the size might exceed 8,000 bytes.
    • SET ANSIPADDING is always ON for nvarchar. SET ANSIPADDING OFF does not apply to the nvarchar data types.
    • By default, nvarchar(MAX) values are stored exactly the same as nvarchar(4000) values would be, unless the actual length exceed 4000 characters; in that case, the in-row data is replaced by a pointer to one or more seperate pages where the data is stored.If you anticipate data possibly exceeding 4000 character, nvarchar(MAX) is definitely the recommended choice.

    Example

    DECLARE @myVarchar AS VARCHAR(5)
    DECLARE @myChar AS CHAR(5)
    DECLARE @myNVarchar AS NVARCHAR(3)
    SET @myVarchar = 'abc'
    SET @myChar = 'abc'
    SET @myNVarchar = 'abc'
    --The following returns number of bytes  for @myVarchar = 3, @myChar=5 and @myNVarchar = 6
    SELECT DATALENGTH(@myVarchar), DATALENGTH(@myChar),DATALENGTH(@myNVarchar);
    GO
    
    Replied on Jan 16 2012 11:02AM  . 
    Mitesh Modi
    18 · 10% · 3080
  • Score
    2

    Hi,

    All of them given most correct answer for comparion.

    If you specify nearest varchar lengh to the column(depends upon data size) in a table then sometimes query optimiser will provide less memory to the query which can cause serious performance issues.

    Thanks Nick

    Replied on Jan 16 2012 4:12PM  . 
    nachikethm
    2020 · 0% · 9
  • Score
    5

    Char

    Fixed-length, non-Unicode character data with a length of n bytes. n must be a value from 1 through 8,000. The storage size is n bytes. The ISO synonym for char is character.

    Varchar

    Variable-length, non-Unicode character data. n can be a value from 1 through 8,000. max indicates that the maximum storage size is 2^31-1 bytes. The storage size is the actual length of data entered + 2 bytes. The data entered can be 0 characters in length. The ISO synonyms for varchar are char varying or character varying.

    nchar

    Fixed-length Unicode character data of n characters. n must be a value from 1 through 4,000. The storage size is two times n bytes. The ISO synonyms for nchar are national char and national character.

    nvarchar

    Variable-length Unicode character data. ncan be a value from 1 through 4,000. max indicates that the maximum storage size is 2^31-1 bytes. The storage size, in bytes, is two times the number of characters entered + 2 bytes. The data entered can be 0 characters in length. The ISO synonyms for nvarchar are national char varying and national character varying.

    If you have sites that support multiple languages, consider using the Unicode nchar or nvarchar data types to minimize character conversion issues. 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.
    • Use varchar(max) when the sizes of the column data entries vary considerably, and the size might exceed 8,000 bytes.

    Ref: http://msdn.microsoft.com/en-us/library/ms176089.aspx

    Replied on Jan 17 2012 5:24AM  . 
    ATif-ullah Sheikh
    133 · 1% · 391
  • Score
    5

    CHAR [ ( n ) ]
    Fixed-length, non-Unicode character data with a length of n bytes. n must be a value from 1 through 8,000. The storage size is n bytes. The ISO synonym for char is character.Best used when data length is constant, e.g. social security numbers or ISBN numbers.

    VARCHAR [ ( n ) ]
    Variable -length, non-Unicode character data with a length of n bytes. n must be a value from 1 through 8,000. The storage size is n bytes. The ISO synonyms for varchar are char varying or character varying. e.g. last names or product SKU codes.

    CHAR pads blank spaces to the maximum length. VARCHAR does not pad blank spaces.

    NVARCHAR Variable-length Unicode character data. ncan be a value from 1 through 4,000. max indicates that the maximum storage size is 2^31-1 bytes. The storage size, in bytes, is two times the number of characters entered + 2 bytes. The data entered can be 0 characters in length. The ISO synonyms for nvarchar are national char varying and national character varying.

    VARCHAR[ (max ) ]
    Max indicates that the maximum storage size is 2^31-1 bytes. The storage size is the actual length of data entered + 2 bytes. The data entered can be 0 characters in length. The ISO synonyms for varchar are char varying or character varying.

    When n is not specified in a data definition or variable declaration statement, the default length is 1.
    When n is not specified when using the CAST and CONVERT functions, the default length is 30.

    Here are a few general rules that should help:

    • Use a variable length when the values vary a lot in size.
    • Use char when the sizes of the column data entries are consistent.
    • Use varchar when the sizes of the column data entries vary considerably.
    • Use varchar(max) when the sizes of the column data entries vary considerably, and the size might exceed 8,000 bytes.
    • SET ANSIPADDING is always ON for nvarchar. SET ANSIPADDING OFF does not apply to the nvarchar data types.
    • By default, nvarchar(MAX) values are stored exactly the same as nvarchar(4000) values would be, unless the actual length exceed 4000 characters; in that case, the in-row data is replaced by a pointer to one or more seperate pages where the data is stored.If you anticipate data possibly exceeding 4000 character, nvarchar(MAX) is definitely the recommended choice.

    Example

    DECLARE @myVarchar AS VARCHAR(5)
    DECLARE @myChar AS CHAR(5)
    DECLARE @myNVarchar AS NVARCHAR(3)
    SET @myVarchar = 'abc'
    SET @myChar = 'abc'
    SET @myNVarchar = 'abc'
    --The following returns number of bytes  for @myVarchar = 3, @myChar=5 and @myNVarchar = 6
    SELECT DATALENGTH(@myVarchar), DATALENGTH(@myChar),DATALENGTH(@myNVarchar);
    GO
    
    Replied on Feb 4 2012 3:21AM  . 
    Mitesh Modi
    18 · 10% · 3080
Previous 1 | 2 Next

Your Answer


Sign Up or Login to post an answer.
Please note that your answer will not be considered as part of the contest because the competition is over. You can still post an answer to share your knowledge with the community.

Copyright © Rivera Informatic Private Ltd.