If I make for instance a datatype declaration:
varchar (30)
Does this mean that the system will accept up to 30 characters or 30 bytes in this particular column? If the value stored is less than the 30 character or 30 byte threshold, does the database server padd the remaining space with NULL 1's or 0's? Same goes for type text. The book says it can hold up to 2,147,483,647 bytes. Does this mean if I only put 10 bytes of info in it, the system will still allocate 2GB for this type? Or will it hold "up to" that maximum amount. What then, should I use for columns that will only hold one or two sentences? Should I varchar those types?
Lets say I wanted to store one of two values in a column, either "open" or "closed". If the value attempting to be stored was not either of these values, an error returns. Is this possible, or should I simply use the scripting language (php) to check for this?
MS Sql...I don't understand the datatype definitions I made
Moderator: General Moderators
- mydimension
- Moderator
- Posts: 531
- Joined: Tue Apr 23, 2002 6:00 pm
- Location: Lowell, MA USA
- Contact:
varchar(30) will hold up to 30 bytes. most characters are 1 byte unless you are dealing with unicode or win32 newlines. i believe varchar types are padded while text types are not padded.
if the string length you are receiving is extremely variant (like a comment system) then use the text type. if its fairly consistent (like usernames and passwords) then use the varchar type.
for the last question, the best thing to use is an enumerated type (ENUM). this will only permit the values you specify but no warning will be raised if you try to input a value that is not part of the enumerated set, it will just set to a default.
if the string length you are receiving is extremely variant (like a comment system) then use the text type. if its fairly consistent (like usernames and passwords) then use the varchar type.
for the last question, the best thing to use is an enumerated type (ENUM). this will only permit the values you specify but no warning will be raised if you try to input a value that is not part of the enumerated set, it will just set to a default.
-
fractalvibes
- Forum Contributor
- Posts: 335
- Joined: Thu Sep 26, 2002 6:14 pm
- Location: Waco, Texas
Most odd. In other DBMS a VarChar typically does not pad. If it is varchar(30) I think it uses 31 bytes - a little extra to denote the actual length in bytes of the column. So that is the reason to use VarChar over Char - less wasted space. In DB2 they suggest that your varchar field(s) be the last column(s) in the table, as it can better manage the variable sizes on the tail in within the architecture.
Phil J.
Phil J.
VarChar is not padded, VarChar is as the name indicates varying its size with stringsize + 1 byte. CHAR is not variable, but does not vaste the extra length-setting-byte and is therefor useful for strings that all has very similar length (such as a md5 sum). MySQL automatically switches to CHAR if you create a VARCHAR column under a minium length (is it 3? cant remember now).
http://www.mysql.com/doc/en/Storage_requirements.html
http://www.mysql.com/doc/en/Storage_requirements.html
- mydimension
- Moderator
- Posts: 531
- Joined: Tue Apr 23, 2002 6:00 pm
- Location: Lowell, MA USA
- Contact:
-
fractalvibes
- Forum Contributor
- Posts: 335
- Joined: Thu Sep 26, 2002 6:14 pm
- Location: Waco, Texas
No problem with that - good that we can discuss such things, as implmentations of various SQL [standards] do vary from RDBMS to another. Sure, there are some certain agreed-upon things, but each vendor has its own flavor and underlying implementation.
And we all learn!
ps - some other multi-byte charactor sets would include Kanji - a DBCS - (double-byte charactor set) to represent Japanese charactors - don't use
it, but plenty of refs in docs here and there...
And we all learn!
ps - some other multi-byte charactor sets would include Kanji - a DBCS - (double-byte charactor set) to represent Japanese charactors - don't use
it, but plenty of refs in docs here and there...
Stoker:
Varchar varies its size. Does this mean it allocates the entire 30 + 1 bytes of space for a varchar(30) declaration? Or does it mean that it can go "up to" 30 + 1 bytes? Whereas char(30) would saturate 30 bytes regardless of the string size that is stored in it?
So then if I'm looking at this from a speed standpoint, I would think that char's are faster on queries than varchars. As, it seems to me the system would not have to determine the "end" of a char as it would with the varying varchar. Right or way wrong?
Thanks for the help gents, it's always nice to see good verbouse educated responses. Good day...
vr
Rathlon[/quote]
fractalvibes:VarChar is as the name indicates varying its size with stringsize + 1 byte. CHAR is not variable, but does not vaste the extra length-setting-byte and is therefor useful for strings that all has very similar length (such as a md5 sum).
In other DBMS a VarChar typically does not pad. If it is varchar(30) I think it uses 31 bytes - a little extra to denote the actual length in bytes of the column. So that is the reason to use VarChar over Char - less wasted space.
Varchar varies its size. Does this mean it allocates the entire 30 + 1 bytes of space for a varchar(30) declaration? Or does it mean that it can go "up to" 30 + 1 bytes? Whereas char(30) would saturate 30 bytes regardless of the string size that is stored in it?
So then if I'm looking at this from a speed standpoint, I would think that char's are faster on queries than varchars. As, it seems to me the system would not have to determine the "end" of a char as it would with the varying varchar. Right or way wrong?
Does Microsoft SQL 7.0 support the ENUM type? I can't seem to find documentation on it. Makes sense what you're saying...sort of like a switch statement. You define the "conditions" or in this case the "values" that can be stored, if no value resolves to a "match" or a "non zero value" then the default is stored in the field. Right?for the last question, the best thing to use is an enumerated type (ENUM). this will only permit the values you specify but no warning will be raised if you try to input a value that is not part of the enumerated set, it will just set to a default.
Thanks for the help gents, it's always nice to see good verbouse educated responses. Good day...
vr
Rathlon[/quote]
-
fractalvibes
- Forum Contributor
- Posts: 335
- Joined: Thu Sep 26, 2002 6:14 pm
- Location: Waco, Texas
There are always tradeoff in such issues. And often not clear where your
best efficiencies lie - data storage, execution, retrieval, etc.
If you have a VarChar field of, say, 100 bytes and you store the word
HELLO - 5 bytes + a byte telling the length. Space-wise - much more efficient than storing 5 bytes plus 95 bytes of blanks or nulls. The DB may have to do a little more work - look at the length byte to determine the field to return. Conversly - if you define it as Char(100) and really need to send only 5 bytes across the network - plus padded with 95 spaces - that
is a waste also....
My Guess is that the DB is better suited and optimized to handle this, so use the Varchar type if a large field.
Client-side performance depends upon a mix of things, from the DB server to the web server and all the pipes in between ....
Phil
best efficiencies lie - data storage, execution, retrieval, etc.
If you have a VarChar field of, say, 100 bytes and you store the word
HELLO - 5 bytes + a byte telling the length. Space-wise - much more efficient than storing 5 bytes plus 95 bytes of blanks or nulls. The DB may have to do a little more work - look at the length byte to determine the field to return. Conversly - if you define it as Char(100) and really need to send only 5 bytes across the network - plus padded with 95 spaces - that
is a waste also....
My Guess is that the DB is better suited and optimized to handle this, so use the Varchar type if a large field.
Client-side performance depends upon a mix of things, from the DB server to the web server and all the pipes in between ....
Phil