SQL - Data Types

SQL data takes shape in several different forms, including character strings, numbers, file stores, and dates. SQL developers call the shots as to what types of data will be stored inside each and every table column when creating a SQL table. The developer must specify the column type of each new SQL table column.


Column types are synonymous with data types as the column type is what designates the type of data that will be stored inside the column. In other words, a SQL data type is a label and a guideline for SQL to understand what type of data is expected inside of each table column and this identifies how SQL will interact with the stored data. Below, we will give you an overview on the types of data that can be stored within a SQL table.

SQL - Numbers, Decimals, and Dates

Data Types:

• Integers - (3, -17)
• Point(Decimal) - (3.23415)
• Date - (2004-06-22 10:33:11.840)

Storing numbers and decimals allows the developer to collect statistical data and create reports based on the data contained inside the table. SQL can even perform mathematical calculations against numeric data, providing endless number-crunching abilities.
In SQL, decimals are often referred to as point or floating-point numbers. These data types are slightly different from the normal 'integer' data types.
For the most part, date values are treated like numbers and they can even be added together and subtracted offering the developer the option to add days, months, or years together to create new dates (more on this later). Additionally, specific data can be extracted from date values, allowing the developer to pull specific date information from a date value like only the month number, the year, or the day of the week.

SQL - Boolean Data

• ("TRUE" / "FALSE")
• ( 1 / 0 )

Boolean values are true/false types of data. A Boolean table column will contain either string values of "True" and "False" or the numeric equivalent representation, with 0 being false and 1 being true.

SQL - Character Strings

Character Strings:

• VARCHAR - ('Words or numbers')
• Text - ('Once upon a time...')

Strings range from a single word or character to large blocks of text including multiple paragraphs and unique symbols. Set the table column type to VARCHAR or Text in order to incorporate string data types into SQL tables.

SQL Server Table Column Types:

bigint	Integer value (-9,223,372,036,854,775,808 - 9,223,372,036,854,775,807)	2^63
int	smaller Integer value (-2,147,483,648) - (2,147,483,647)	2^31
smallint	smaller Integer value (-32,768) - (32,767)	2^15
tinyint	smaller Integer values 0 - 255	2^8
bit	Integer data value (either 1 or 0 value)	1 or 0
decimal	Decimal values from -10^38 - 10^38	10^38
numeric	Decimal values from -10^38 - 10^38	10^38
money	Money values (-922,337,203,685,477.5808) - (922,337,203,685,477.5807)	2^63
smallmoney	Smaller Money Values (-214,748.3648) - (214,748.3647)	2^31
datetime	Date value (January 1, 1753) - (December 31, 9999)
smalldatetime	Smaller Date Value (January 1, 1900) - (June 6, 2079)
timestamp	Unique Number Value (updates when row is updated)
char	Character String Value (max 8,000 characters)
varchar	Character String Value maximum of 8,000 characters, unless otherwise noted)
nchar	Character String Value (max 4,000 characters)
nvarchar	Character String Value (max 4,000 characters)
text	Character String Value (max 2,147,483,647 characters)	2^31
ntext	Character String Value (max 1,073,741,823 characters)	2^30
binary	Binary Value (max 8,000 bytes)
varbinary	Binary Value (max 8,000 bytes)
image	Binary Value (max 2,147,483,647 bytes)	2^31
uniqueidentifier	Global Unique ID (GUID)

SQL - Defaults and Null Values

NULL values are 'nothing' values. When a value is null, it means the value is empty and contains no value -- not even '0'. NULLs are unique data types that are usually the default setting for all table columns. When a SQL developer runs across a NULL value in a database, it is generally an indication that this value is either new or has not been modified.
The SQL developer may specify to allow or disallow the NULL values eliminating the possibility of running across 'empty' table columns when creating a SQL table. If the developer chooses not to allow NULL values he/she may specify a custom default value instead of the NULL (nothing) value. Primary Key table columns do not allow NULL values since this column's sole purpose is to be the unique identifier for a table column. Having a NULL unique identifier would be similar to having a car license plate that is blank.
By default, NULL values are allowed on all newly created table columns meaning a table column is allowed to be 'empty', except primary key columns. A NULL value is a special type of value that can be tested for by most programming languages including SQL and can provide the developer a means to 'test' and see if data exists or has been modified. As a new programmer you may not fully understand the benefits a NULL value can bring, but with experience, you will learn to hate/appreciate them.

Posted in: SQL