September 19, 2008 at 9:56 am
Dear All,
While creating tables, few people in my office always select NVARCHAR as datatype.can anyone tell me the advantages and disadvantages of using NVARCHAR...
Wot's the diff between nVarchar nad Varchar..?
Which is more better..?
Pelase guide..
Santhosh Nair.
September 19, 2008 at 10:06 am
Nvarchar, nchar, and ntext are unicode datatypes that are designed to cover all the characters of all languages, so they should be used in databases that are used internationally, particularly if data will be modified by users of East Asian languages.
Storage in unicode uses twice the space as non-unicode characters. If the data isn't shared internationally, unicode datatypes generally aren't needed.
See "Storage and Performance Effects of Unicode" in BooksOnLine.
Greg
September 19, 2008 at 10:16 am
Hi There,
Firstly I would recommend using BOL as the explanations are pretty good.
However seeing as though I'm here I'll try.
OK Firstly they both have different lengths.
NVARCHAR tends to take twice as much 'room' for each charater
e.g
[font="Courier New"]
DECLARE @Nvar NVARCHAR(10)
DECLARE @var VARCHAR(10)
SELECT
@Nvar = REPLICATE (N'a',20)
,@var = REPLICATE ('a',20)
SELECT
@Nvar AS [Nvar],
@var AS [Var],
LEN(@Nvar) AS [Nvar Len],
LEN(@var) AS [var Len],
DATALENGTH(@Nvar) AS [Nvar DLen],
DATALENGTH(@var) AS [var DLen][/font]
This is because NVARCHAR holds unicode charaters.
So the MAX you can store in a NVARCHAR is 4000 where in a VARCHAR it's 8000, this excludes the MAX storage of VARCHARS.
HOPE that helps.
----------------------------------------------
Try to learn something about everything and everything about something. - Thomas Henry Huxley
:w00t:
Posting Best Practices[/url]
Numbers / Tally Tables[/url]
September 23, 2008 at 6:43 am
In simple language....whenever you have special characters use nvarchar otherise always use varchar datatype
-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)
September 23, 2008 at 8:37 am
Even more importantly, I recommend for sanity purposes in the future, you come up with a formal process whether or not to use varchar/nvarchar in tables. This keeps things civil between different departments and you don't swear at them underneath your breath. 🙂 (on a separate, come up with a consistent collation for all your databases if you can).
Gaby________________________________________________________________"In theory, theory and practice are the same. In practice, they are not." - Albert Einstein
December 26, 2018 at 11:59 pm
Does anyone have data examples? e.g. what is allowed and not allowed in each of varchar and nvarchar ?
e.g. for a surname ... should nvarchar be used ? or varchar ?
December 27, 2018 at 12:29 am
allan.ford17 - Wednesday, December 26, 2018 11:59 PMDoes anyone have data examples? e.g. what is allowed and not allowed in each of varchar and nvarchar ?
e.g. for a surname ... should nvarchar be used ? or varchar ?
Here is a query that shows you the difference.
😎
USE TEEST;
GO
SET NOCOUNT ON;
DECLARE @SAMPLE_SIZE INT = 65535;
;WITH T(N) AS (SELECT X.N FROM (VALUES (0),(0),(0),(0),(0),(0),(0),(0),(0),(0)) X(N))
, NUMS(N) AS (SELECT TOP(@SAMPLE_SIZE) ROW_NUMBER() OVER (ORDER BY @@VERSION) AS N
FROM T T1,T T2,T T3,T T4,T T5,T T6)
SELECT
NM.N
,CHAR(NM.N) AS VCHAR
,NCHAR(NM.N) AS NVCHAR
FROM NUMS NM
ORDER BY NM.N ASC;
December 28, 2018 at 8:52 am
Eirikur's query lets you see characters that are allowed in each. In general, if you stick with Western languages and data, varchar is fine.
The important thing is that you don't want those varchar <-> nvarchar implicit conversions between your app and the database as they can cause some performance problems. Anyone querying the data should be sure their parameter datatypes match.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply