Data types in SQL quite diverse. Let's find out with Quantrimang.com Data types, including numeric in SQL!
In SQL Server, each column, local variable, expression, and parameter has an associated data type. A data type is an attribute that defines the type of data an object can hold, including: integer data, characters, currency, date & time, binary strings, etc.
SQL Server provides a set of system data types that define all the data types that can be used with SQL Server. You can also specify private data types in Transact-SQL or the Microsoft.NET Framework.
In general, datatype is a data classification, telling the compiler what type of data the programmer is storing. Most programming languages support different data types, such as integers, characters or strings, Boolean, etc.
What is SQL Server data type?
Datatype ensures the type of data a column or variable can store in SQL Server. When creating any table or variable, it requires specifying the data type to be stored in addition to the name of each column. For example, in SQL Server, each table has multiple columns and contains specific data types for each column. They can be integers, floats, varchar, Boolean…
How to use datatype?
You need to define the data type first for each column of a table to ensure the type of data it can store. Data type configuration also prevents users from entering any invalid or unwanted information.
When you assign the appropriate data type to a column, you can make efficient memory use by allocating only the amount of system memory needed for the data in the relevant column.
You can use the following characteristics to define data types in SQL Server:
- The data type (fixed or variable) it represents.
- Storage capacity depends on the value, fixed or variable length.
- Stored values may or may not be indexed.
- How SQL Server implements a value comparison of a particular data type.
Data types in SQL Server:
- String – String
- Numeric – Number
- Date and Time – Date and time
Character string data type in SQL Server
Below are the data types belonging to the character string group in SQL Server (Transact-SQL).
| Data type syntax | Maximum size | Explain |
| CHAR(kich_thuoc) | Maximum 8000 characters. |
|
| VARCHAR(kich_thuoc) or VARCHAR(toi_da) | Maximum 8000 characters or maximum number. |
|
| TEXT | Maximum 2GB. |
|
| NCHAR(kich_thuoc) | Maximum 4000 characters. |
|
| NVARCHAR(kich_thuoc) or NVARCHAR(toi_da) | Maximum 4000 characters or maximum number. |
|
| NTEXT | Maximum 1,073,741,823 bytes. |
|
| BINARY(kich_thuoc) | Maximum 8000 characters. |
|
| VARBINARY(kich_thuoc) or VARBINARY(toi_da) | Maximum 8000 characters or maximum number. |
|
| IMAGE | Maximum size is 2GB. |
|
Data types in SQL
Numeric data type in SQL Server
Below is a list of numeric data types in SQL Server (Transact-SQL).
| Data type syntax | Maximum size | Explain |
| BIT | integer 0, 1 or NULL | |
| TINYINT | from 0 to 255 | |
| SMALLINT | from -32768 to 32767 | |
| INT | -2,147,483,648 to 2,147,483,647 | |
| BIGINT | from -9,223,372,036,854,775,808 to 9,223,372,036,854,775,807 | |
| DECIMAL(m,d) |
|
m is the total number of numbers and d is the number of numbers after the comma. |
| DEC(m,d) |
|
m is the total number of numbers and d is the number of numbers after the comma. Synonymous with DECIMAL data type. |
| NUMERIC(m,d) |
|
m is the total number of numbers and d is the number of numbers after the comma. Synonymous with DECIMAL data type. |
| FLOAT(n) | floating point number n defaults to 53 if not specifically specified. | n is the number of bits stored in a chemical symbol. |
| REAL | equivalent to FLOAT(24) | |
| SMALLMONEY | from – 214,748.3648 to 214,748.3647 | |
| MONEY | from -922,337,203,685,477.5808 to 922,337,203,685,477.5807 |
Date/time data type in SQL Server
Below is a list of date/time data types in SQL Server (Transact-SQL)
| Data type syntax | Maximum size | Explain |
| DATE | value from '0001-01-01' to '9999-12-31. | displays as 'YYYY-MM-DD' |
| DATETIME |
|
displays as 'YYYY-MM-DD hh:mm:ss[.mmm] |
| DATETIME2 (accurate to decimal seconds) |
|
displays as 'YYYY-MM-DD hh:mm:ss[.số giây thập phân]' |
| SMALLDATETIME |
|
displays as 'YYYY-MM-DD hh:mm:ss |
| TIME |
|
displays as 'YYYY-MM-DD hh:mm:ss[.nnnnnnn]' |
| DATETIMEOFFSET (accurate to decimal seconds) |
|
displays as YYYY-MM-DD hh:mm:ss[.nnnnnnn]' [{+|-}hh:mm] |
Other data types in SQL Server
|
Data type |
Describe |
|
sql_variant |
Contains up to 8,000 bytes of data of various types, excluding text, ntext, and timestamp |
|
uniqueidentifier |
Stores a globally unique identifier (GUID) |
|
xml |
Stores data in XML format. Maximum 2GB |
|
cursor.cursor |
Stores a reference to the pointer used for database operations |
|
table. table |
Store a set of results for later processing |
Previous article: PIVOT clause in SQL Server
Next article: CREATE TABLE command in SQL Server
