November 21, 2011 at 11:29 pm
1. What is the difference b/w char and varchar?
The main difference between these 2 data types is that a CHAR data type is fixed-length while a VARCHAR is variable-length. If the number of characters entered in a CHAR data type column is less than the declared column length, spaces are appended to it to fill up the whole length.
but why cant we have varchar with the specified length than why SQL server has two different data types there must be some specific reason for that. I would like to know what it would be any one could help me.
2.What is the difference b/w varchar and Nvarchar?
VARCHAR(n) NVARCHAR(n)
--------------------------------------------------------------------------------
Character Data Type Non-Unicode Data Unicode Data
Maximum Length 8,000 4,000
Character Size 1 byte 2 bytes
Storage Size Actual Length (in bytes) 2 times Actual Length (in bytes)
But i couldn't understand where to use which
Thanks
November 21, 2011 at 11:52 pm
but why cant we have varchar with the specified length than why SQL server has two different data types there must be some specific reason for that. I would like to know what it would be any one could help me.
char [ ( n ) ]
Fixed-length, non-Unicode character data with a length of n bytes. n must be a value from 1 through 8,000. The storage size is n bytes. The ISO synonym for char is character.
varchar [ ( n | max ) ]
Variable-length, non-Unicode character data. n can be a value from 1 through 8,000. max indicates that the maximum storage size is 2^31-1 bytes. The storage size is the actual length of data entered + 2 bytes. The data entered can be 0 characters in length. The ISO synonyms for varchar are char varying or character varying.
So if you know your data size, you can save +2 bytes overhead of varchar.
But i couldn't understand where to use which
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.
With nvarchar you can save multi-lingual data (upto 4000 chars).
November 22, 2011 at 12:01 am
If you know upfront that the data inside your column will always be around the same size (your customer code has for example always 10 characters), then it is more efficient to use CHAR instead of VARCHAR. If the data varies a lot (like the names of cities), it is more efficient to use VARCHAR.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply