- Logical and Physical Models
- Database Application Types
- Using Efficient Datatypes
- Partitioning Large Tables
- Bigger, Better, Faster
- Summary
Like this article? We recommend
Like this article? We recommend
Like this article? We recommend
Using Efficient Datatypes
When generating or optimizing the physical data model, it's important to use efficient datatypes. SQL Server (and other RDBMSs) offers several ways of storing each piece of data. For instance, you could use BIT, TINYINT, SMALLINT, INT, or BIGINT to store exact numeric data. If you're implementing a Boolean value, it's best to use the BIT datatype, because it serves the purpose perfectly well (it only accepts values of 0 or 1) and takes up the least amount of storage—1 byte per 8 columns of the BIT datatype. Similarly, if you store a person's age, you should use the TINYINT datatype, which accepts values between 0 and 255 and takes up 1 byte of storage (unless you track the age of Adam and Noah, in which case TINYINT won't suffice). Of course, you have the option of using BIGINT for either of these purposes, but doing so takes up 8 bytes of storage per row, which translates to 8 times more storage and adds up quickly when you have millions of rows in your tables. Using a BIGINT in this case is also like killing a bird with a bomb—it will work, but it's likely to have unpleasant circumstances.
SQL Server provides two ways of storing date and time data: DATETIME and SMALLDATETIME. The former supports dates from January 1, 1753 through December 31, 9999 and takes up 8 bytes of storage per row. The latter supports dates between January 1, 1900 and June 6, 2079 and takes up 4 bytes of storage per row. DATETIME lets you store milliseconds, while SMALLDATETIME only lets you store seconds. Most applications that I built won't be used past the year 2079 (and frankly, I don't hope to live that long), and most business transactions don't care about milliseconds. However, if you need to track the sequence of row modifications in a highly utilized database, then you do care about milliseconds.
TIP
As a rule, use the smallest datatype that will suffice for your needs—it will save storage space and will make querying your tables faster.