SQL data types specify a specific value, such as a currency or a time, in a predefined column. They are divided into categories.
SQL data types: Definition of a specific value type
If you use a SQL Server database engine, you will find columns in the SQL table. Each column can be defined with a specific data type.
- The SQL data type assigned to a column defines a specific value type that applies to the entire column. These can be, for example, whole numbers, currencies, times or dates.
- You use specific parameters that are precisely assigned to the respective data type. You can find a list of these in the second section. It is important to know that you can only store values of the same data type in each column.
- There are different categories of data types. The relatively standardized type contains strings and numbers. The supported type, but different in each DBMS contains long character strings, binary data, character strings in national character sets as well as date and time values. The third type type is custom and DBMS specific.
- If two expressions combined by an operator are assigned different data types, sorting, precision, scale, or length, the result characteristics are determined based on certain conditions.
- When determining the SQL data type of the results, the precedence of the data types on the input expressions plays a role. If the result value is of data type char, varchar, text, nchar, nvarchar, or ntext, the sorting precedence rule determines the listing of results.
- The precision, decimal places and length of the result value also depend on the precision, decimal places and length of the input expressions.
Categories of SQL data types
SQL data types can be classified into different categories. These in turn contain certain parameters that can be used to define the respective column.
- SQL date and time data types: DATE (storage of data; YYYY-MM-DD), DATETIME (storage of date and time information; YYYY-MM-DD HH:MM:SS), TIME (storage of time information in the format HH:MM:SS), TIMESTAMP ( Stores the number of seconds since Unix epoch '1970-01-01 00:00:00' UTC), YEAR (year in two- or four-digit format; 79 or 1979).
- SQL Numeric Data Types: BIGINT (from -9,223,372,036,854,775,808 to 9,223,372,036,854,775,807), BIT (from 1 to 0), DECIMAL (from -10^38 + 1 to 10^38 – 1), FLOAT (from -1.79E+308 to 1.79E+308), INT (from -2,147,483,648 to 2,147,483,647), NUMERIC (from -10^38 + 1 to 10^38 – 1), REAL (from -3.40E+38 to 3.40E+38), SMALLINT (from -32,768 to 32,767), TINYINT (from 0 to 255).
- SQL Binary Data Types: BINARY (Fixed length with a maximum of 8,000 bytes), IMAGE (Variable length with a maximum of 2 GB of binary data), VARBINARY (Variable length with a maximum of 8,000 bytes), VARBINARY(max) (Variable length with a maximum number of bytes).
- SQL character and string data types: CHAR (Fixed length with a maximum of 8,000 characters), TEXT (Variable length with a maximum of 2 GB of data), VARCHAR (Variable length with a maximum of 8,000 characters), VARCHAR(max) (Variable length with a maximum number of characters).
- SQL Unicode character and string data types: NCHAR (Fixed length with a maximum of 4,000 characters), NTEXT (Variable length with a maximum of 1 GB of data), NVARCHAR (Variable length with a maximum of 4,000 characters), NVARCHAR(max) (Variable length with a maximum number of characters), Important: This data type is not used in MySQL databases supported.
- SQL Different data types: BLOB (For large binary objects), CLOB (Character large objects that can store a maximum of 2GB), JSON (storage of JSON data), XML (storage of XML data).