 |
VOOZH | about |
|
VOOZH | about |
In SQL, each column must be assigned a data type that defines the kind of data it can store, such as integers, dates, text or binary values. Choosing the correct data type is important for data integrity, query performance and efficient indexing.
👁 sql_data_typesNumeric data types are used to store integer and decimal values. They support mathematical operations and are commonly used for financial, scientific and analytical data.
Exact numeric types are used when precise numeric values are needed, such as for financial data, quantities and counts. Some common exact numeric types include:
| Data Type | Description | Range |
|---|---|---|
| BIGINT | Large integer numbers | -9,223,372,036,854,775,808 to 9,223,372,036,854,775,807 |
| INT | Standard integer values | -2,147,483,648 to 2,147,483,647 |
| SMALLINT | Small integers | -32,768 to 32,767 |
| TINYINT | Very small integers | 0 to 255 |
| DECIMAL | Exact fixed-point numbers (e.g., for financial values) | -10^38 + 1 to 10^38 - 1 |
| NUMERIC | Similar to DECIMAL, used for precision data | -10^38 + 1 to 10^38 - 1 |
Example:
CREATE TABLE Product_Sales (
ProductID INT PRIMARY KEY,
Quantity SMALLINT,
UnitPrice DECIMAL(10,2),
TotalAmount DECIMAL(10,2)
);
These types are used to store approximate values, such as scientific measurements or large ranges of data that don't need exact precision.
| Data Type | Description | Range |
|---|---|---|
| FLOAT | Approximate numeric values | -1.79E+308 to 1.79E+308 |
| REAL | Similar to FLOAT, but with less precision | -3.40E+38 to 3.40E+38 |
Example:
CREATE TABLE Measurements (
SensorID INT,
Temperature FLOAT,
Humidity REAL
);
| Data Type | Description |
|---|---|
| CHAR | Stores fixed-length non-Unicode characters with a maximum length of 8000 characters |
| VARCHAR | Stores variable-length non-Unicode characters with a maximum length of 8000 characters |
| VARCHAR(MAX) | Stores variable-length non-Unicode data with a maximum size of 2³¹ − 1 characters (introduced in SQL Server 2005) |
| TEXT | Stores variable-length non-Unicode data with a maximum size of 2,147,483,647 characters |
Character data types are used to store text or character-based data. The choice between fixed-length and variable-length data types depends on the nature of your data.
Example:
CREATE TABLE Employee_Info (
EmpID INT PRIMARY KEY,
FirstName VARCHAR(50),
LastName CHAR(30),
Bio Nvarchar(max)
);
Unicode data types are used to store characters from any language, supporting a wider variety of characters. These are given in below table.
Data Type | Description |
|---|---|
Nchar | The maximum length of 4000 characters(Fixed-Length Unicode Characters) |
Nvarchar | The maximum length of 4000 characters.(Variable-Length Unicode Characters) |
Nvarchar(max) | The maximum length of 2^31 - 1 characters(SQL Server 2005 only). (Variable Length Unicode data) |
Example:
CREATE TABLE International_Users (
UserID INT PRIMARY KEY,
FullName NVARCHAR(100),
Country NCHAR(50)
);
SQL provides several data types for storing date and time information. They are essential for managing timestamps, events and time-based queries. These are given in the below table.
| Data Type | Description | Storage Size |
|---|---|---|
DATE | Stores the data of date (year, month, day) | 3 Bytes |
TIME | Stores the data of time (hour, minute,second) | 3 Bytes |
DATETIME | Stores both the data and time (year, month, day, hour, minute, second) | 8 Bytes |
Example:
CREATE TABLE Orders (
OrderID INT PRIMARY KEY,
OrderDate DATE,
OrderTime TIME,
ShippedAt DATETIME
);
Binary data types are used to store binary data such as images, videos or other file types. These include
| Data Type | Description | Max Length |
|---|---|---|
| Binary | Fixed-length binary data. | 8000 bytes |
| VarBinary | Variable-length binary data. | 8000 bytes |
| Image | Stores binary data as images. | 2,147,483,647 bytes |
Example:
CREATE TABLE Product_Images (
ImageID INT PRIMARY KEY,
ImageName VARCHAR(100),
ImageData VARBINARY(MAX)
);
Boolean data types store logical values such as TRUE and FALSE. In SQLite, these values are typically represented using integers. where:
Example:
CREATE TABLE User_Status (
UserID INT PRIMARY KEY,
IsActive INTEGER,
IsVerified INTEGER
);
SQL also supports some specialized data types for advanced use cases:
Example:
CREATE TABLE XML_Records (
RecordID INT PRIMARY KEY,
ConfigData XML
);
Example:
CREATE TABLE Locations (
LocationID INT PRIMARY KEY,
Area GEOMETRY
);
