|
I guess, the question is, why would I use
char(255)
instead of
varchar(255)
When I specify varchar(255), does SQL sever allocate [255] per cell? or does it allocate dynamically? If it statically allocate [255] bytes, then I don't see if there's much practical difference between char(255) and varchar(255) -- well, except varchar always store a one (or two) byte termination character internally.
If SQL server allocates 255 bytes when i specify varchar(255), then what about varchar(MAX)?
Thanks
dev
|
|
|
|
|
I did but not sure if I interpreted MSDN correctly.
<br />
<br />
char [ ( n ) ]<br />
<br />
Fixed-length, non-Unicode string data. n defines the string length and must be a value from 1 through 8,000. The storage size is n bytes. The ISO synonym for char is character.<br />
varchar [ ( n | max ) ]<br />
<br />
Variable-length, non-Unicode string data. n defines the string length and can be a value from 1 through 8,000. max indicates that the maximum storage size is 2^31-1 bytes (2 GB). The storage size is the actual length of the data entered + 2 bytes. The ISO synonyms for varchar are char varying or character varying.
varchar(255) --- this means SQL server will always allocate 255bytes? Or depending on actual values being inserted for a particular row?
If say row 1, "Name" = 25 bytes and row 2, "Name" = 225 bytes (say no other row longer than 225), then...
POSSIBILITY 1: SQL server always allocate 225 bytes (actual length of data, taken from row 2)
POSSIBILITY 2: SQL server always allocate 255 bytes for all rows as defined in column definition varchar(255)
POSSIBILITY 3: SQL Server allocate 25 bytes for row 1, and 225 bytes for row 2 (actual length of data vary from one row to the next)
My guess is - POSSIBILITY 3 is how SQL server actually behalves. This said, then why we bother specify varchar(n)? We should always specify varchar(MAX). Two reasons I can think of after a bit of digging...[^]
a. You cannot index anything varchar longer than 900 bytes
b. by MAX, you remove column max length validation provided for you at database level.
Am I missing something?
dev
modified 2-Feb-13 21:55pm.
|
|
|
|
|
Allocation of storage is the primary difference, but it won't affect how you work with the data.
The main difference you'll have to deal with is in trailing SPACEs -- CHAR stores them, VARCHAR doesn't. Which can occasionally cause confusion, in that with VARCHAR 'BOB' and 'BOB ' will test equal and LEN report the same length; you'll want to use DATALENGTH for VARCHAR -- this sort of thing bit me again this week. 
|
|
|
|
|
I think it's the opposite. CHAR don't store trailing termination char (2 bytes for SQL), varchar does.
I think I found the answer. With VARCHAR(MAX), you can't index the column and also you don't have length checks provided for you by default by database, that's why you should always use VARCHAR(n) as supposed to VARCHAR(MAX)
CHAR(n) when lenght same for all/most rows.
dev
|
|
|
|
|
devvvy wrote: I think it's the opposite. CHAR don't store trailing termination char (2 bytes for SQL), varchar does
Not sure what that statement means.
The 'char' data type is fixed length. That means that there will ALWAYS be 255 characters. And the way normal APIs to databases handle that is that they pad shorter lengths with spaces. Pretty sure that is an ANSI requirement as well.
devvvy wrote: that's why you should always use VARCHAR(n) as supposed to VARCHAR(MAX)
Unless of course you know that you will be storing data larger than the largest integer value allowed for varchar(n).
devvvy wrote: CHAR(n) when lenght same for all/most rows.
In normal database design most columns will be varchar because
1. User data is almost always variable
2. Application data that is text almost always has a variable length.
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
