Difference between varchar and nVarChar

Question

Difference between varchar and nVarChar

Charlottecb

Hall of Fame

Points: 3932
More actions
February 24, 2004 at 7:52 am

#161157

Hi all,
I am quite new to SqlServer so bear with me!
What is the difference between varchar and nVarchar, and Text and nText.
Also, If I set a column to text, I can't include that column in Unions or when auditing, refer to it in the deleted and inserted tables. Any way around this.
Thanks
CCB

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

fhanlon SSChampion Points: 13131 More actions · Answer 1

The n in front refers to Unicode data. If your data includes characters other than the regular english character set (ancented characters, National Character data types in the SQL-92 character set) you need to used ntext,, nchar or nvarchar. If you are dealing with unaccented, english characters do not use Unicode data. Unicode data takes twice as much space to store. See Books on line for further information. Books on line is a very valuable resource.

Francis

Lowell SSC Guru Points: 323518 More actions · Answer 2

Here's my answer ot the second half of your question:

a text column is used when you have data that is going to be really big; a varchar has a max value of 8000 characters.

I believe that SQL server will determine how the data in the field is actually stored.... if you declare a text field, and put just a little data in it, it might store it like a varchar; if it is big, internally it saves it as a separate file and saves a pointer to go get the file.

As I understand it, because of that...(it might be the text or a pointer to the text), you can't truely join on them because they are different data types....therefore you get some restrictions on how rows with text fields are handled.

Use varchar whenever possible to avoid the text field problem. also, you'll run into problems trying to concatenate varchars if they add up to more than 8000 characters;i found that if i tried concatenating anything over 4000 characters i might not get the results i wanted.

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Dinesh Asanka SSChampion Points: 11058 More actions · Answer 3

More information

http://www.experts-exchange.com/Databases/Microsoft_SQL_Server/Q_20781766.html

My Blog: http://dineshasanka.spaces.live.com/

Frank Kalis SSC Guru Points: 111183 More actions · Answer 4

First of all I would make sure if you really need unicode columns.

Maybe a little example will illustrate the difference

declare @a varchar(20)

declare @b-2 nvarchar(20)

set @a = 'Hello World'

set @b-2=@a

select datalength(@a) as nonunicode_size, datalength(@b) as unicode_size

nonunicode_size unicode_size

--------------- ------------

11 22

(1 row(s) affected)

HTH

--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]