Schema - Do you use Bit/Numeric data type correctly?
3/12/2019 2:49 PM by
1. Bit data type
Bit data from 0 to 1 (2 values only). Storage size is 1 byte.
Columns of type bit cannot have indexes on them. Also, SQL Server 7 only allows True or False values in a bit column. SQL 2000 introduced the ability to store NULL as well. Applications built for SQL Server 7 often do not expect this behaviour and may create subtle runtime errors.
Columns of type bit should be prefixed with "Is" or a "Should" ie. IsInvoiceSent (y/n) or ShouldInvoiceBeSent (y/n) you can tell easily which way the boolean is directed. [more information on naming conventions]
This being said, fields of this type should generally be avoided because often a field like this can contain a date i.e. DateInvoiceSent (Date/Time) is prefered over InvoiceSent (y/n). If a date is inappropriate then we still recommend an int field over a bit field anyway, because bits are a pain in the butt :-)
2. Tinyint data type
Integer data from 0 through 255. Storage size is 1 byte.
3. Smallint data type
Integer data from -2^15 (-32,768) through 2^15-1 (32,767). Storage size is 2 bytes.
4. Int data type
Integer (whole number) data from -2^31 (-2,147,483,648) through 2^31-1 (2,147,483,647). Storage size is 4 bytes. The SQL-92 synonym for int is integer.
5. Bigint data type
Integer (whole number) data from -2^63 (-9223372036854775808) through 2^63-1 (9223372036854775807). Storage size is 8 bytes.
- Use smallint datatype instead of bit datatype - so it can be indexed;
- Use int datatype, where possible, instead of bigint datatype - for saving disk space;
- Use smallint datatype, where possible, instead of int datatype - for saving disk space;
- Use tinyint datatype, where possible, instead of smallint datatype - for saving disk space;
Do you feel this rule needs an update?