SQL Data Types
SQL Data Types
The concept of data types is very important. Mastering the distinctions and applications of each type offers valuable insight in to SQL.
Similar to other programming languages, SQL supports certain data types.
Each data type has different features, such as:
- what kind of data can hold
- what limit can hold
- what memory is needed
As mentioned previously, different SQL variations differ in some way or another. This holds true for data types as well. Some data types may have different names or features. Or others may not even exists in the particular relational database company.
It all depends on the vendor, and as such, documentation should always be checked. This page covers some of the most popular data types within the SQL Server system.
The following are some of the most commonly used data types.
- Text: char, varchar, and varchar(max)
- Numbers: bit, tinyint, smallint, int, bigint, decimal, float, smallmoney, and money
- Date and time: time, date, and datetime
There are many other data types in SQL. For full documentation, press here.
Text
In SQL, text (also strings or characters) data types deal with text.
This section covers char, varchar, and varchar(max).
- char stores fixed-size data
- varchar stores variable-size data
- varchar(max) stores variable-size data
| Type | Min | Max | Memory |
| char(n) | 1 | 8000 | n bytes |
| varchar(n) | 1 | 8000 | n bytes + 2 bytes |
| varchar(max) | 1 | 2,147,483,647 | n bytes + 2 bytes |
Numbers
In SQL, numbers (also numerics) data types deal with numbers.
There are exact numbers and approximate numbers.
This section covers exact numbers: bit, tinyint, smallint, int, bigint, decimal, smallmoney, and money. It also covers approximate numbers: float.
- bit stores 0 (FALSE), 1 (TRUE), or NULL data.
- tinybit stores whole numbers
- smallint stores whole numbers
- int stores whole numbers
- bigint stores whole numbers
- decimal stores fixed precision and scale numbers
- float stores approximate floating numbers
- smallmoney stores monetary or currency numbers
- money stores monetary or currency numbers
| Type | Min | Max | Memory |
| bit | 0 | 1 | 1 byte |
| tinyint | 0 | 255 | 1 byte |
| smallint | -32,768 | 32,767 | 2 byte |
| int | -2,147,483,648 | 2,147,483,647 | 4 bytes |
| bigint | -9,223,372,036,854,775,808 | 9,223,372,036,854,775,807 | 8 bytes |
| decimal | -10^38 +1 | 10^38 – 1 | 5-17 bytes |
| float | -1.79E+308 | 1.79E+308 | 4 or 8 bytes |
| smallmoney | -214,748.3648 | 214,748.3647 | 4 bytes |
| money | -922,337,203,685,477.5808 | 922,337,203,685,477.5807 | 8 bytes |
Date and time
In SQL, date and time data types deal with dates and times.
This section covers time, date, and datetime.
- time stores times data
- date stores dates data
- datetime stores times and dates data
| Type | Min | Max | Memory |
| time | 00:00:00.0000000 | 23:59:59.9999999 | 5 bytes |
| date | 0001-01-01 | 9999-12-31 | 3 bytes |
| datetime | 0001-01-01 00:00:00.000 | 9999-12-31 23:59:59.999 | 8 bytes |
Next: SQL Databases
by
Related Posts
