Difference between char nchar varchar nvarchar fields

Query Result
Query Result

Almost most .Net Developers knows difference between char nchar varchar nvarchar fields. Or i they think they know.

Here are some samples to tell about differences with results,

In this sample our database is configured as Turkish_CI_AS Collation
/*
* Our Database>Tables>Fields are Turkish_CI_AS Collation
* So it is already for save Turkish specific fields
*/

/*Variables*/
Declare @char char(5) = ‘abcş ‘
Declare @nchar nchar(5) = ‘abcş ‘
Declare @varchar varchar(5) = ‘abcş ‘
Declare @nvarchar nvarchar(5) = ‘abcş ‘

/*Lets look at texts results*/
Select @char as txtChar,@nchar as txtNChar,@varchar as txtVarChar,@nvarchar as txtNVarChar
/*Lets look at character length*/
Select Len(@char) as txtLenChar,Len(@nchar) as txtLenNChar,Len(@varchar) as txtLenVarChar,Len(@nvarchar) as txtLenNVarChar
/*Lets look at fields byte length results*/
Select DataLength(@char) as txtDataLenChar,DataLength(@nchar) as txtDataLenNChar,DataLength(@varchar) as txtDataLenVarChar,DataLength(@nvarchar) as txtDataLenNVarChar

Results,

Query Result
Query Result

 

So “abcş ” totally 5 chars with space, but you will see that LEN function gives us only char length, but if you look at real storage size it will reflect the real size. Unicode (N) fields like nchar and nvarchar data types keeps more size on server because every character will be defined with 2 bytes. So in this example keeping data in N fields increase the storage size.

But what will be different, if my server was in a different collation ?

So to simulate this, i will change collations on demand of query like this,

Just change the declared variables and run the query,
/*Variables*/
Declare @char char(5) = 'abcş ' Collate Latin1_General_CI_AS
Declare @nchar nchar(5) = 'abcş ' Collate Latin1_General_CI_AS
Declare @varchar varchar(5) = 'abcş ' Collate Latin1_General_CI_AS
Declare @nvarchar nvarchar(5) = 'abcş ' Collate Latin1_General_CI_AS

And Results will be,

Query Result
Query Result

 

So you will see that “ş” character is saved as “s” because your local database will not able to keep unicode characters unless you marked them.

So you should change your query to store Unicode characters in a server is not match with your Collation setting,

 


/*Variables*/
Declare @char char(5) = N'abcş ' Collate Latin1_General_CI_AS
Declare @nchar nchar(5) = N'abcş ' Collate Latin1_General_CI_AS
Declare @varchar varchar(5) = N'abcş ' Collate Latin1_General_CI_AS
Declare @nvarchar nvarchar(5) = N'abcş ' Collate Latin1_General_CI_AS

will be give you the first result set and data lengths.

As a result,

  • Using N or not, means this data type can store Unicode Characters but you should specifiy this in your query like N’your chars’
  • If you don’t need to store N-Unicode fields do not use N-Unicode data types. Otherwise you will spend more storage area
  • So if you are using a ORM tool like Entity Frame Work, you can change default datatype settings or mappings for data types
  • If you don’t make an international project you prefer to use your own matching Collation settings for your server or your table or your field
  • Char fields are fixed-length data types, so use that fields if you are using Post Code or fixed number or like identity fields. It will increase query performance.
Reklamlar
sql içinde yayınlandı

Bir Cevap Yazın

Aşağıya bilgilerinizi girin veya oturum açmak için bir simgeye tıklayın:

WordPress.com Logosu

WordPress.com hesabınızı kullanarak yorum yapıyorsunuz. Çıkış  Yap /  Değiştir )

Google+ fotoğrafı

Google+ hesabınızı kullanarak yorum yapıyorsunuz. Çıkış  Yap /  Değiştir )

Twitter resmi

Twitter hesabınızı kullanarak yorum yapıyorsunuz. Çıkış  Yap /  Değiştir )

Facebook fotoğrafı

Facebook hesabınızı kullanarak yorum yapıyorsunuz. Çıkış  Yap /  Değiştir )

w

Connecting to %s