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
- 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
- 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.
- Dates and times should always be set to "NOT NULL" since time always exists.
- Character Strings are sometimes referred to as varchar(s).
Found Something Wrong in this Lesson?
Report a Bug or Comment on This Lesson - Your input is what keeps Tizag improving with time!