nvarchar or varchar for a lot of records ?

Question

Hello
what is different between nvarchar and varchar ?
they have limit for records count ? For example 10,000,000 or more ?
Thanks

Answer 1

Hi...


NVarchar = 2 * Varchar

Varchar(100) = 100 characters
nvarchar(100) = 200 characters

---

Cheers, Sridhar -------------- Please Mark it as Answer if it helps u so that it will be useful to other forum guys

Answer 2

If you dont need UNICODE support in yopur database, then varchar is ok.

---

____________________________________ Jasmin Azemovic PhD candidate , SQL Server MVP, MCT

Answer 3

They don't directly affect record count limits.  As Jasmin said, nvarchar allows you to use Unicode characters, so if you don't need them you can use varchar.

Answer 4

Hello
i remember that i couldnt insert record while my datatype was nvarchar. (almost 1.600.000 record). Then i turned nvarchar to varchar. then i could go on insert.
i m sure that there is limit insert record for both of them. but how many limit is ?
Thanks

Answer 5

HI....



Go through this link.....u will find more than u want


http://www.bigresource.com/MS_SQL-Difference-advantage-of-varchar-vs-nvarchar-PQfJeW9V.html

---

Cheers, Sridhar -------------- Please Mark it as Answer if it helps u so that it will be useful to other forum guys

Answer 6

I am not sure that understand. Do you have problems with size of nvarchar values (MAX parameter can solve it). I belive that there is no limitation with number of recoreds based on datatype.

---

Jasmin Azemovic PhD candidate , SQL Server MVP, MCT

Answer 7

Hi...


NVarchar = 2 * Varchar

Varchar(100) = 100 characters
nvarchar(100) = 200 characters

---

Cheers, Sridhar -------------- Please Mark it as Answer if it helps u so that it will be useful to other forum guys

This is NOT correct!

Both varchar(100) and nvarchar(100) allow you to store 100 characters. But varchar(100) uses 100 bytes for it (if the maximum string length is used), and nnvarchar(100) needs 200 bytes.

---

-- Hugo Kornelis, SQL Server MVP

Answer 8

Hello
i remember that i couldnt insert record while my datatype was nvarchar. (almost 1.600.000 record). Then i turned nvarchar to varchar. then i could go on insert.
i m sure that there is limit insert record for both of them. but how many limit is ?
Thanks

Maybe you were using a database that was sized too small and has disabled autogrow.
There is no data type in SQL Server that restricts the maximum number of rows. The only restriction is size. And that restrictition is more of a monetary than a technical issue; the maximum size SQL Server is able to handle would require buildings filled with specialized storage.

There is no limit for either of them. At least not in SQL Server. And I would be very surprised if there did exist such a limit in any other relational database.

---

-- Hugo Kornelis, SQL Server MVP

Answer 9

If I need to use Hebrew or Arabic so I have to use nvarchar(???)??

How can I define autoNumber generated in SQl 2008 management studio?? is it that identifier which called "UniqueIdentifier"??

Thank you

Answer 10

Yes, you need Unicode for Hebrew and Arabic.

The SQL Server version of autonumber is called "Identity". Check it out in Books Online.

---

-- Hugo Kornelis, SQL Server MVP

Answer 11

Yes, you need Unicode for Hebrew and Arabic.

The SQL Server version of autonumber is called "Identity". Check it out in Books Online.

---

-- Hugo Kornelis, SQL Server MVP

So I have to use nvarchar??


Have  a look on the following table please.....

CustomrID	     varchar(9)	Unchecked
Address	     varchar(60)	Checked
City	     varchar(20)	Checked
WorkName	     varchar(40)	Checked
ContactName   varchar(30)	Checked
Country	     varchar(15)	Checked
Fax	     varchar(24)	Checked
Phone	     varchar(24)	Checked
PostalCode	     varchar(10)	Checked
Region	      varchar(15)	Checked 

Assume that I have to support Hebrew and Arabic.... What changes I have to do ??
What does it mean if I allow null in one of the c olums??


I am sorry I am just too new to teh databases world :-)

Thank you

Answer 12

You have to identify which columns need to support the Hebrew and Arabic characters. WorkName and Address are examples of columns where you probably need that support; Phone and Fax are columns that don't need it. You fill in the blanks for those other columns. Then, for the columns that do need Arabic and Hebrew characters, replace "varchar" with "nvarchar".

If you enter a NULL in a column, you basically are not supplying a value. There may be situations where this is required. For instance, if not every customers' birthday is known but you do want to store those that are known, then you would allow NULLs in that column, for the customers that never told you their birthday. Or maybe for customers who are not a natural person, but a legal person (companies).
Since there are some issues with NULL (though far less than with any alternative I've ever seen), you should restrict the use of NULL values to only the columns where the business dictates a need for them. All other columns, that is, those that always need a "real" value, should be constrained to be NOT NULL.

Also check one of the other topics in this forum, where several resources for learning database design are mentioned. Since you are new, you might benefit from them as well.

---

-- Hugo Kornelis, SQL Server MVP

Answer 13

Dear Mr.Hugo,

Thank you so much for your great explanation. and ofcourse because they are also in simple words :-)
In the last few days I was reading a lot about DB, design and normalizations.

Actually I am starting to build PRISM , WPF and .Net C# Insurance Application which uses sql server.
I am weak in the sql side :-), but I am trying my best.

Maybe you can help me to build flexibile design to my databases ??!! In the nest few days I will gather all requirements and start to think about teh design. I should think about the idea that things can be changed and requirements maybe exapnaded to larger customers.

Maybe you can if you do not mind, to help me in that ??
I can give y9ou my email so we can discuss that privatly if you do not mind!!

Thank you
Wael

Answer 14

Since the requirement is to have Hebrew or Arabic characters (not both), this can be handled using VARCHAR and the appropriate collation (1256 for Arabic and 1255 for Hebrew):
http://msdn.microsoft.com/en-us/library/aa176553(SQL.80).aspx

---

Plamen Ratchev

Answer 15

Wael,

I think that I can speak for most Moderators that participate here, and I hope that you will understand when we say that most of us that volunteer our time on this Forum do so in order to help folks learn. We are very glad that you find the Forum to be useful. However, it is difficult or impossible for us to engage with folks privately. We don't have time to do both, and our commitment is help folks using the Forums.

Do not hesitate to read all of the threads, join in the discussions, offer your own experiences and information, and ask questions to increase your knowlege. Seek out blogs from those who write in a manner that you appreciate.

---

"You cannot do a kindness too soon because you never know how soon it will be too late." -Ralph Waldo Emerson

Answer 16

Dear Mr.Hugo,

Thank you so much for your great explanation. and ofcourse because they are also in simple words :-)
In the last few days I was reading a lot about DB, design and normalizations.

Actually I am starting to build PRISM , WPF and .Net C# Insurance Application which uses sql server.
I am weak in the sql side :-), but I am trying my best.

Maybe you can help me to build flexibile design to my databases ??!! In the nest few days I will gather all requirements and start to think about teh design. I should think about the idea that things can be changed and requirements maybe exapnaded to larger customers.

Maybe you can if you do not mind, to help me in that ??
I can give y9ou my email so we can discuss that privatly if you do not mind!!

Thank you
Wael

Hi Wael,

Thank you for the kind words. I am glad that you are reading; you'll find that this subject can appear overwhelming at first, but will start to get easier, and even fun (!), as you gain knowledge and experience.

Assistance through private email is usually called consulting. I don't provide consulting services for free. And if you are willing to pay for consulting services, then I think you're better off employnig someone who lives near you. Living in the same time zone helps ensure quick responses and enables phone calls, and being within driving distance enables on-site help, which can be even more effective.

Flexible databases are the hardest to design properly. Since you are new to this, I do indeed think that it would be wise to get the help of an experienced consultant.

Good luck!

---

-- Hugo Kornelis, SQL Server MVP