SQL Datatypes
CIW Course in a Nutshell
| MySQL Type | MS SQL Type | Length | Usage |
|---|---|---|---|
| TINYINT BIT BOOL BOOLEAN |
BINARY BIT |
1 byte | If unsigned, stores values from 0 to 255; otherwise, from -128 to 127. |
| SMALLINT | SMALLINT | 2 bytes | If unsigned, stores value from 0 to 65535; otherwise, from -32768 to 32767. |
| MEDIUMINT | 3 bytes | If unsigned, stores values from 0 to 16777215; otherwise, from -8388608 to 8388607. | |
| INT INTEGER |
INT | 4 bytes | If unsigned, stores values from 0 to 4294967295; otherwise, from -2147483648 to 2147483647. |
| BIGINT | BIGINT | 8 bytes | If unsigned, stores values from 0 to 18446744073709551615; otherwise, from -9223372036854775808 to 9223372036854775807. You may experience strange results when performing arithmetic with unsigned integers of this size. |
| FLOAT | REAL | 4 bytes | Single-precision floating point number. |
| DOUBLE | FLOAT | 8 bytes | Double-precision floating point number. |
| DECIMAL DEC NUMERIC FIXED |
DECIMAL MONEY NUMERIC |
An unpacked floating point number that is stored like a CHAR. | |
| DATE | 3 bytes | ||
| DATETIME | DATETIME | 8 bytes | |
| TIMESTAMP | 4 bytes | ||
| TIME | 3 bytes | ||
| YEAR | 1 byte | ||
| CHAR(N) | CHAR(N) | N bytes | A fixed length string, padded with spaces as required. |
| VARCHAR(N) | VARCHAR(N) | Up to N bytes | Variable length string up to a maximum of 255 characters. |
| TINYBLOB TINYTEXT |
Up to 255 bytes | BLOB is case-sensitive for sorting and searching, TEXT is not. | |
| BLOB TEXT |
TEXT | Up to 64KB | As above. |
| MEDIUMBLOB MEDIUMTEXT |
Up to 16MB | As above. | |
| LONGBLOB LONGTEXT |
Up to 4GB | As above. |
NOTE: The Microsoft SQL types are not necessarily a direct equivalent to the MySQL types.