Data Type nvarchar or varchar

[Jose]

When we use upgrade wizard to migrate ms access to sql server, the fields
data type text is converted to nvarchar.
My question is, Can I change nvarchar to varchar? These changes does it not
affect nothing?

Thanks

JCP

 

[Scott Lichtenberg]

nvarchar supports Unicode data. Functionally, it's the same as varchar, but
it may require additional storage - I think Unicode requires two bytes per
character instead of one - but I could be wrong. If your data is always
going to be English (or a similar ASCII based language) you can switch the
data types back to varchar.

 

[Jose]

My data is enghish or portuguese, so, is derived from latin language.

Do you think can i change nvarchar to varchar without impact problems?
Thanks,
Jose

 

[Sylvain Lafontaine]

Portuguese has diacritical symbols in its alphabet, so you need to use a
code page if you want to go the ASCII road. Everytime you use a code page,
there is always a possibility that you will have problem if your data is to
be access by a machine which doesn't use the exact same code page; so for
example someone who is greek, russian, chinese or even french - whose
alphabet is close but not identical to the portuguese - will have problem
accessing or updating your data.

If I were you, I would stop bothering myself and Unicode. There is alreay
enough problems in life so that you don't really have the need (or the
luxury) to add more to it. You database was already running fine in Access
with Unicode, why would you want to start changing things now?

 

[a a r o n . k e m p f]

uh, twice the number of records per page is a compelling reason, IMHO

I think that yes, you should use varchar for almost everything,
nvarchar-- where appropriate.

-Aaron

 

[Sylvain Lafontaine]

uh, twice the number of records per page is a compelling reason, IMHO

Weak reason in my opinion. First, it's not 100% of fields who are of type
characters; if you add to that the space occuped by indexes and empty space
here and there, the difference between a database based on Ascii exclusively
and one based on Unicode is usually around 30% more of space disk. Most
operations inside a database are based on numeric key and/or datetime; which
give us an upper limit of 10% slower but this is an upper limit and it will
be around 1~2% for most operations in a real case scenario excerpt for
string searching. For string searching, this will be around 20% but in the
case of string searching, we usually don't care at all about speed.

IMHO, having a Damocles' sword hanging over your head for 1% or 2% more of
performance is totally absurd.

--
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
E-mail: sylvain aei ca (fill the blanks, no spam please)


message
uh, twice the number of records per page is a compelling reason, IMHO

I think that yes, you should use varchar for almost everything,
nvarchar-- where appropriate.

-Aaron

 

[Jose]

Thanks a lot Sylvain,

I'm thinking to change only to save space.
It´s true, until kmow, using nvarchar I neever had problems.
By another side, I think if varchar also doesn´t bring problems, probably is
good to change the datatype.

Thanks

I

 

[a a r o n . k e m p f]

This contradicts a lot of books from MS press.
I just don't believe in Nvarchar, for nearly any reason whatsoever.

 

[Sylvain Lafontaine]

If you don't believe in nVarchar, why should I care? You are entitled to
your own opinion and if what you see from the world doesn't extend any
further than the walls around you, it's not of my business (or interest) at
all.

--
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
E-mail: sylvain aei ca (fill the blanks, no spam please)


message
This contradicts a lot of books from MS press.
I just don't believe in Nvarchar, for nearly any reason whatsoever.

 

[a a r o n . k e m p f]

What you're saying is directly contradicted in MS Press books.

I just think that what _YOU_ say is your own opinon.
Varchar is just about half the size of Nvarchar.

Anyone with any serious experience in DW / DM / VLDB would be adamant
about using optimal datatypes.
Wasting space- and allowing corrupted data-- is a bad choice.

Using Varchar is safer, IMHO.

 

[Sylvain Lafontaine]

There is more in life and in database than just trying to save a few bytes
here and there; otherwise, everyone will still be using DOS instead of
Windows - and even better, CP/M before DOS.

Here's a tip: instead of using upper case and lower case letters, use only
upper case. This way, by using 6 bit characters and writing/compressing
your data into binary fields - or using the compression from SQL-2008 -
you'll save even more space.

--
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
E-mail: sylvain aei ca (fill the blanks, no spam please)


message
What you're saying is directly contradicted in MS Press books.

I just think that what _YOU_ say is your own opinon.
Varchar is just about half the size of Nvarchar.

Anyone with any serious experience in DW / DM / VLDB would be adamant
about using optimal datatypes.
Wasting space- and allowing corrupted data-- is a bad choice.

Using Varchar is safer, IMHO.

 

[a a r o n . k e m p f]

Anyone with any serious experience in DW / DM / VLDB would be adamant
about using optimal datatypes.
Wasting space- and allowing corrupted data-- is a bad choice

 

[Sylvain Lafontaine]

There is much more about the chosing an optimal datatypes than simply
counting its number of bytes. The number of bytes is only one thing to put
on the balance before making your choice and most of the people in the
world - including a lot of them in the U.S. - have chosen to switch to
Unicode; so its seems that they have found a lot of other things to put on
the balance.

Like I said, the horizon that you're seeing from your eyes seems to be very
closed around you. It's not my business but if I were you, I would start
beginning to enlarge it right now. In the long run, the view will be much
better.

--
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
E-mail: sylvain aei ca (fill the blanks, no spam please)


message
Anyone with any serious experience in DW / DM / VLDB would be adamant
about using optimal datatypes.
Wasting space- and allowing corrupted data-- is a bad choice

 

[a a r o n . k e m p f]

re:
There is much more about the chosing an optimal datatypes than simply
counting it's number of bites

I'm not sure I agree. 99 times out of 100, choose the smallest
datatype (so that you can fit more records in a page).
Anything else is just making excuses, being lazy.

If your data will fit in the field-- you should always use the
smallest type possible (but be consistent as heck). DataType
conversions shouldn't need to be used ever anywhere in any queries--
and if every query you're using has a convert/cast on it-- then you're
doing something wrong.

Keeping everything in nvarchar is _ALMOST_ as good of a strategy as
'Keeping everything in varchar'.
_EXCEPT_ for performance, size, indexing, etc.

This is ESPECIALLY true for keys.

In general-- MS Access and SQL Server use Nvarchar by default- for a
lot of stuff that doesn't need it.

When you have the NAMES of a month stored in a table, does it need
NVARCHAR?

ROFL

When you only do business in the U.S., and you have no plans to ever
serve people in China-- why would you possibly choose a datatype that
is twice as big as necessary?

I personally hate nvarchar.

Yes, nvarchar is twice verbose as Varchar datatype-- so thus it is
almost always-- nvarchar is the wrong choice.
Yes, nvarchar is twice verbose as Varchar datatype-- so thus it is
almost always-- nvarchar is the wrong choice.
Yes, nvarchar is twice verbose as Varchar datatype-- so thus it is
almost always-- nvarchar is the wrong choice.