You must Sign In to post a response.
  • Category: SQL Server

    Difference between Varchar and nvarchar

    what is the difference between Varchar and nvarchar
  • #260241
    One fairly major change to both VARCHAR and NVARCHAR in SQL Server 2005 is the creation of the VARCHAR(MAX) and NVARCHAR(MAX) data types. If you create a VARCHAR(MAX) column, it can hold up to 2^31 bytes of data, or 2,147,483,648 characters; NVARCHAR(MAX) can hold 2^30 bytes, or 1,073,741,823 characters.

    These new data types are essentially replacements for the Large Object or LOB data types such as TEXT and NTEXT, which have a lot of restrictions. They can't be passed as variables in a stored procedure, for instance. The (MAX) types don't have those restrictions; they just work like very large string types. Consequently, if you're in the process of re-engineering an existing data design for SQL Server 2005, it might make sense to migrate some (although not all!) TEXT / NTEXT fields to VARCHAR(MAX) / NVARCHAR(MAX) types when appropriate.

    The big difference between VARCHAR and NVARCHAR is a matter of need. If you need Unicode support for a given data type, either now or soon enough, go with NVARCHAR. If you're sticking with 8-bit data for design or storage reasons, go with VARCHAR. Note that you can always migrate from VARCHAR to NVARCHAR at the cost of some room -- but you can't go the other way 'round. Also, because NVARCHAR involves fetching that much more data, it may prove to be slower depending on how many table pages must be retrieved for any given operation.

  • #260243
    Hi,

    Read This Link ...

    http://geekswithblogs.net/vivek/archive/2007/05/04/112237.aspx

    I hope it will help u

    Regards,

    Vijay

  • #260252
    VARCHAR is an abbreviation for variable-length character string. It's a string of text characters that can be as large as the page size for the database table holding the column in question. The size for a table page is 8,196 bytes, and no one row in a table can be more than 8,060 characters. This in turn limits the maximum size of a VARCHAR to 8,000 bytes.

    The "N" in NVARCHAR means uNicode. Essentially, NVARCHAR is nothing more than a VARCHAR that supports two-byte characters.

    The key difference between the two data types is how they're stored. VARCHAR is stored as regular 8-bit data. But NVARCHAR strings are stored in the database as UTF-16 — 16 bits

  • #260261
    hi,
    The broad range of data types in SQL Server can sometimes throw people through a loop, especially when the data types seem to be highly interchangeable. Two in particular that constantly spark questions are VARCHAR and NVARCHAR: what's the difference between the two, and how important is the difference?

    VARCHAR is an abbreviation for variable-length character string. It's a string of text characters that can be as large as the page size for the database table holding the column in question. The size for a table page is 8,196 bytes, and no one row in a table can be more than 8,060 characters. This in turn limits the maximum size of a VARCHAR to 8,000 bytes.

    The "N" in NVARCHAR means uNicode. Essentially, NVARCHAR is nothing more than a VARCHAR that supports two-byte characters. The most common use for this sort of thing is to store character data that is a mixture of English and non-English symbols — in my case, English and Japanese.

    The key difference between the two data types is how they're stored. VARCHAR is stored as regular 8-bit data. But NVARCHAR strings are stored in the database as UTF-16 — 16 bits or two bytes per character

  • #260277
    The difference is that nvarchar is used to store unicode data, which is used to store multilingual data in the database tables. Other languages have an extended set of character codes that need to be saved and this datatype allows for this extension. If the database will not be storing multilingual data we should use the varchar database instead.

    The reason for this is that nvarchar takes twice as much space as varchar, this is because of the need to store the extended character codes for other languages.


    char and varchar

    Fixed-length (char) or variable-length (varchar) character data types.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.RemarksWhen 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.


    nchar and nvarchar

    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. Storage size is two times n bytes. The SQL-92 synonyms for nchar are national char and national character.nvarchar(n)Variable-length Unicode character data of n characters. n must be a value from 1 through 4,000. Storage size, in bytes, is two times the number of characters entered. The data entered can be 0 characters in length. The SQL-92 synonyms for nvarchar are national char varying and national character varying.RemarksWhen 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.Use nchar when the data entries in a column are expected to be consistently close to the same size.Use nvarchar when the data entries in a column are expected to vary considerably in size.Objects using nchar or nvarchar are assigned the default collation of the database, unless a specific collation is assigned using the COLLATE clause.SET ANSI_PADDING OFF does not apply to nchar or nvarchar. SET ANSI_PADDING is always ON for nchar and nvarchar.

  • #263683
    The difference is that nvarchar is used to store unicode data, which is used to store multilingual data in the database tables. Other languages have an extended set of character codes that need to be saved and this datatype allows for this extension. If the database will not be storing multilingual data we should use the varchar database instead.

    The reason for this is that nvarchar takes twice as much space as varchar, this is because of the need to store the extended character codes for other languages.

    Please rate this post, if it is useful for you.

    Thanks & Regards
    Ashok

  • #267192
    the basic difference between a varchar and a nvarchar datatype is that for each variable the latter one takes double the space needed by the former one. Now why double? Because of the extra "n", which means that it *can* store Unicode characters as well.

    Unicode characters represent a wide variety of foreign locales and many times they need an extra byte for the same char storage. Hence nvarchar can store an extended character set.

    In SQL Server 2000, varchar has the maximum limit of 8000 characters (when you need to give a particular size), and nvarchar has 4000 characters only (remember it needs double the space needed by a varchar, hence the storage capacity becomes half). Infact a given row in a SQL Server 2000 table cannot exceed 8000 characters in size.

    But if you need to store more than 8000 characters? There was no option other than to use TEXT or the NTEXT datattypes, common called as BLOBS (Binary Large Objects), both of which have their own limitations.

    Hence in SQL Server 2005, the MAX identifier was introduced, which allows us to go beyond 8000 characters and store upto 2^31- 1 bytes,which comes around 2 GB!! But note that you still cannnot specify a size greater than 8000 characters in n/varchar (like varchar(12000) is still not allowed, need to use MAX for data greater than 8000 bytes).

    We can use them as: varchar (MAX) nvarchar (MAX)

    So as a general practice, avoid using TEXT/NTEXT datattypes and use varchar(MAX) and nvarchar(MAX) instead



    Thanks & Regards
    G.Renganathan
    Nothing is mine ,Everything is yours!!!


  • This thread is locked for new responses. Please post your comments and questions as a separate thread.
    If required, refer to the URL of this page in your new post.