From http://msdn.microsoft.com/en-us/library/ms176089.aspx 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. … Read more
No. In some other uses, there are some advantages to use structures with a power of two size, mostly because you can fit a nice (power of two) number of these inside another power-of-two-sized structure. But this doesn’t apply to a DB fieldsize. The only power-of-two-sizing related to VARCHARs is about the exact type of … Read more
Note the MySQL Versions. http://dev.mysql.com/doc/refman/5.0/en/char.html Values in VARCHAR columns are variable-length strings. The length can be specified as a value from 0 to 255 before MySQL 5.0.3, and 0 to 65,535 in 5.0.3 and later versions. The effective maximum length of a VARCHAR in MySQL 5.0.3 and later is subject to the maximum row size … Read more
To answer the question, on disk MySql uses 1 + the size that is used in the field to store the data (so if the column was declared varchar(45), and the field was “FooBar” it would use 7 bytes on disk, unless of course you where using a multibyte character set, where it would be … Read more
The “length” of the VARCHAR is not the length of the contents, it is the maximum length of the contents. The max length of a VARCHAR is not dynamic, it is fixed and therefore has to be specified. If you don’t want to define a maximum size for it then use VARCHAR(MAX).
100 characters. This is the var (variable) in varchar: you only store what you enter (and an extra 2 bytes to store length upto 65535) If it was char(200) then you’d always store 200 characters, padded with 100 spaces See the docs: “The CHAR and VARCHAR Types”
Looks like mysql is case insensitive by default: You probably need to create the column with a case sensitive collation (e.g. utf8_bin): CREATE TABLE WORDS ( ID BIGINT AUTO_INCREMENT, WORD VARCHAR(128) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL UNIQUE, PRIMARY KEY(ID) );
Of course you can, in the sense that your RDBMS will let you do it. The answer to a question of whether or not you should do it is different, though: in most situations, values that have a meaning outside your database system should not be chosen to be a primary key. If you know … Read more
By using VARCHAR(MAX) you are basically telling SQL Server “store the values in this field how you see best”, SQL Server will then choose whether to store values as a regular VARCHAR or as a LOB (Large object). In general if the values stored are less than 8,000 bytes SQL Server will treat values as … Read more
This is a metadata change only: it is quick. An observation: specify NULL or NOT NULL explicitly to avoid “accidents” if one of the SET ANSI_xx settings are different eg run in osql not SSMS for some reason