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..?
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.
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
DECLARE @Nvar NVARCHAR(10)
DECLARE @var VARCHAR(10)
@Nvar = REPLICATE (N'a',20)
,@var = REPLICATE ('a',20)
@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
Posting Best Practices[/url]
Numbers / Tally Tables[/url]
In simple language....whenever you have special characters use nvarchar otherise always use varchar datatype
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)
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
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 ?
allan.ford17 - Wednesday, December 26, 2018 11:59 PM
Here is a query that shows you the difference.
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)
,CHAR(NM.N) AS VCHAR
,NCHAR(NM.N) AS NVCHAR
FROM NUMS NM
ORDER BY NM.N ASC;
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.
Follow me on Twitter: http://www.twitter.com/way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
Viewing 8 posts - 1 through 7 (of 7 total)