Character data types that are either fixed-length, nchar, or variable-length, nvarchar, Unicode data and use the UNICODE UCS-2 character set.
nchar [ ( n ) ]
Fixed-length Unicode character data of n characters. n must be a value from 1 through 4,000. The storage size is two times n bytes. The ISO synonyms for nchar are national char and national character.
char[(n)]
Fixed-length non-Unicode character data with length of n bytes. n must be a value from 1 through 8,000. Storage size is n bytes. The SQL-92 synonym for char is character.
varchar[(n)]
Variable-length non-Unicode character data with length of n bytes. n must be a value from 1 through 8,000. Storage size is the actual length in bytes of the data entered, not n bytes. The data entered can be 0 characters in length. The SQL-92 synonyms for varchar are char varying or character varying.
nvarchar [ ( n | max ) ]
Variable-length Unicode character data. ncan be a value from 1 through 4,000. max indicates that the maximum storage size is 2^31-1 bytes. The storage size, in bytes, is two times the number of characters entered + 2 bytes. The data entered can be 0 characters in length. The ISO synonyms for nvarchar are national char varying and national character varying.
Fixed-length (char) or variable-length (varchar) character data types.
When n is not specified in a data definition or variable declaration statement, the default length is 1. When n is not specified with the CAST function, the default length is 30.
Objects using char or varchar are assigned the default collation of the database, unless a specific collation is assigned using the COLLATE clause. The collation controls the code page used to store the character data.
Sites supporting multiple languages should consider using the Unicode nchar or nvarchar data types to minimize character conversion issues. If you use char or varchar:
• Use char when the data values in a column are expected to be consistently close to the same size.
• Use varchar when the data values in a column are expected to vary considerably in size.
If SET ANSI_PADDING is OFF when CREATE TABLE or ALTER TABLE is executed, a char column defined as NULL is handled as varchar.
When the collation code page uses double-byte characters, the storage size is still n bytes. Depending on the character string, the storage size of n bytes may be less than n characters.
Char and Varchar cannot store Unicode characters.
‘n’ means it can store Unicode characters such as: nchar and nvarchar.
For example, if we set the length to 10: char(10), varchar(10), and we set the value to ‘apple’:
The stored data for Char(10) is: ‘apple ’. here, space is added
The stored data for Varchar(10) is: ‘apple’.
[Example 1]
DECLARE @myChar CHAR(100) , @myVarchar VARCHAR(100)
SET @myChar = 'SQL'
SET @myVarchar = 'SQL'
SELECT '[BEGIN]' + @myChar + '[END]' AS CHAR_Data
SELECT '[BEGIN]' + @myVarchar + '[END]' AS VARCHAR_Data
/*
You can see in [Example 1] how the use of VARCHAR in most cases is preferred, to save space. Let's take a look at a script; it declares a CHAR and a VARCHAR variable, sets each equal to the string, and then SELECTs each variable to display what is actually stored
*/
When we Execute with Like Query it returns diffrent output Please see following example
[Example 2]
CREATE TABLE #TBLChar (name CHAR(5) NOT NULL)
CREATE TABLE #TBLVARCHAR (name VARCHAR(200) NOT NULL)
GO
Insert into #TBLChar values
('B'),('B '),('B '),('B ')
Insert into #TBLVARCHAR values
('B'),('B '),('B '),('B ')
Select * from #TBLChar where name like 'b %'
Select * from #TBLVARCHAR where name like 'b %'
--similarly for compairision it will differ
Drop Table #TBLChar
Drop Table #TBLVARCHAR
/*
Here for like output get changed
returns 4 row for first case ie. all rows
returns 3 row for second case ie. specific rows
*/
If you look closely following Example Query Plan, the query that uses an nvarchar parameter does an index scan while the one that users varchar does an index seek. This is very important, because an index seek is orders of magnitude faster than a scan.
The reason this occurs is because the parameter and column have different collation sets and nvarchar is for unicode.
[Example 3]
BEGIN TRANSACTION
GO
CREATE TABLE dbo.Users
(
id INT NOT NULL,
username VARCHAR(50) NOT NULL
)
GO
ALTER TABLE dbo.Users ADD CONSTRAINT
PK_Users_id PRIMARY KEY CLUSTERED
(
id
)
GO
CREATE UNIQUE NONCLUSTERED INDEX IX_Users_username ON dbo.Users
(
username
)
GO
COMMIT
Another problem with using char instead of varchar when the data length can vary is that you'll wind up using the RTrim() function throughout views and sprocs - any small performance advantage there used to be using char when the data length didn't vary too much is lost and more so by all the calls to RTrim().
I agree with using char for a nullable field if the data length is always exactly the same (like a fixed length account number).Even though SQL Server will use a varchar behind the scene, it tells you that this field will return null or exactly X characters.
How about this for a basic principal: The database schema should describe the data as accurately as possible.
Those special characters are still ASCII characters, so you can store them in a varchar column.
Here's a link to the entire ASCII set
http://www.codetoad.com/html/text/ascii_characters.asp
varchar is good for English, Spanish, German, French and many other European languages.
You'll need nvarchar for languages like Japanese, Korean, Hindi and Arabic that aren't descendents of Latin languages.
Thanks
Yogesh