In SQL, knowledge varieties assist to outline the kind of worth that may be saved in a database cell, or, extra particularly, in a column. There are numerous sorts of information varieties in SQL and on this database programming and administration tutorial, we’ll take a look at each and be taught their syntax, methods to use them, when to make use of them, their use case, and the way they differ in particular relation database distributors, reminiscent of MySQL, Oracle, PostgreSQL and MS SQL Server.
Earlier than we start, for those who choose to be taught in a classroom or on-line surroundings, we have now an inventory of the High On-line Programs to Be taught SQL that can assist you get began.
What Information Sorts Does SQL Have?
Information varieties in SQL – and its offshoots – outline the kind, dimension, and vary of knowledge that may be saved in a database object. In SQL, objects that may have a knowledge sort embrace columns, expressions, parameters, and variables.
A column in a desk runs up and down or vertically and consists of cells that maintain knowledge which can be associated to 1 one other (therefore SQL being a relational database language). A variable may be considered a field that maintain knowledge; a database programmer can look contained in the field to see that knowledge, exchange it with one other piece of information, or change the information – as long as it shares the identical sort. Extra on that in a second.
An expression may be made up of a number of values, operators, and features {that a} programmer can use to guage to acquire a bit of information. Consider it as a mathematic formulation, although expressions don’t solely carry out math or work on numeric values.
Parameters are used to facilitate the trade of information in what are often known as SQL features and saved procedures.
SQL has three fundamental varieties of knowledge varieties, together with built-in, user-defined alias, and user-defined widespread language runtime (CLR). These may be additional damaged down into classes like numeric, string, date and time, spatial, and miscellaneous. Inside every of these classes, there are sub-categories as effectively, which we’ll talk about in-depth within the following part.
Learn: SQL Finest Practices
SQL Numeric Information Sorts
Numeric knowledge varieties in SQL signify quantity values as both INT (integer or complete numbers) or FLOATS (decimal level or floating-point numbers). Inside these two classes, there are subtypes as effectively that database programmers can use to signify variations of those two numeric knowledge varieties. Here’s a listing of all numeric knowledge sort in SQL:
- BIT: Used to signify numbers of a single byte, starting from 0 – 1
- TINYINT: Used to signify small integer values starting from 0 – 255
- SMALLINT: Used to retailer integer values starting from -32,768 – 32,767
- INT: Used to signify integer or complete quantity values starting from -2,147,483,648 – 2,147,483,647
- BIGINT: Used to signify integer values starting from -9,223,372,036,854,775,808 – 9,223,372,036,854,775,807
- DECIMAL: Used for decimal factors numbers starting from -10^38 + 1 – 10^38 -1
- NUMERIC: Used for numbers starting from -10^38 + 1 – 10^38 -1
- FLOAT: Used for decimal numbers starting from -1.79E+308 – 1.79E+308
- REAL: Used for approximate floating-point numbers starting from -3.40E + 38 – 3.40E + 38
- SMALLMONEY: Used for financial values starting from -214,748.3648 – 214,748.3647. Particular to MS SQL Server
- MONEY: Used for financial values starting from -922,337,203,685,477.5808 – 922,337,203,685,477.5807. Particular to MS SQL Server
SQL String and Character Information Sorts
Character and textual content knowledge varieties in SQL consists of text-based values, which may embrace the higher and lowercase model of any letter starting from a-z, A-Z, and particular characters like !@#$%^&*(). SQL has two varieties of character and string knowledge varieties: character and strings and Unicode characters and strings.
With out getting too deep into the topic (it’s past the scope of this tutorial), Unicode is an encoding normal used to assign a singular numeric worth to a letter, digit, or image, to make sure it really works no matter language or script getting used. As an illustration, the Unicode for an uppercase letter A is U+0041, whereas the Unicode for a lowercase letter a is U+0061.
Under is an inventory of the Character and String knowledge varieties in SQL:
- CHAR: Used for fastened size textual content with a most size of 8,000 characters
- VARCHAR: Used for variable-length textual content with a most size of 8,000 characters
- VARCHAR(max): Used for variable-length textual content with offered max characters. Word that this isn’t supported in MySQL taste of SQL
- TEXT: Variable-length textual content values with a most dimension of 2GB price of information
Here’s a listing of the totally different Unicode Character and String knowledge varieties allowed in SQL. It ought to be famous that these knowledge varieties will not be supported in MySQL:
- NCHAR: Used for fastened size textual content with a most size of 4,000 characters
- NVARCHAR: Used for variable-length textual content with a most size of 4,000 characters
- NVARCHAR(max): Used for variable-length storage with max characters offered
- NTEXT: Used for variable-length textual content with a most storage capability of 1GB
Be taught extra abut totally different flavors of SQL in our tutorials: What’s MySQL and What’s PostgreSQL.
Date and Time Information Sorts in SQL
As you might need guessed, date and time knowledge varieties are used for date and time values. Here’s a listing of the supported date and time knowledge varieties supported in SQL:
- DATE: Shops date values within the YYYY-MM-DD format
- TIME Shops time values within the HH:MI:SS format
- DATETIME: Shops each date and time values within the YYYY-MM-DD HH:MI:SS format
- TIMESTAMP: Used to retailer the variety of seconds which have handed because the Unix epoch, or 1970-01-01 00:00:00 UTC
- YEAR: Used to retailer a yr worth in both a 2-digit or 4-digit format, starting from 1901 – 2155 in 4-digit format, or 70 – 69 in a 2-digit format, which represents the timeframe of 1970 – 2069
Binary Information Sorts in SQL
Binary knowledge is knowledge that’s represented by 1s and 0s; in SQL, binary knowledge varieties are normally used for binary knowledge reminiscent of pictures or textual content information. SQL helps the next varieties of binary knowledge varieties:
- BINARY: Used when the dimensions of the information is constant; used for fixed-length binary knowledge with a most size of 8,000 bytes
- VARBINARY: Used for variable-length binary knowledge as much as 8,000 bytes
- VARBINARY(max): Used for variable-length binary knowledge with a offered most byte dimension
- IMAGE: Used for variable-length binary knowledge with 2GB most dimension
Different Information Sorts in SQL
SQL helps different knowledge varieties not listed on this tutorial (on the time of this writing) – the information varieties contained herein are probably the most generally used. As well as, it ought to be famous that vendor-specific relational databases – or different taste of SQL – like MySQL, T-SQL, Oracle, and MS SQL Server have their very own variations and syntax for the information varieties on this database programming tutorial. We are going to cowl these in a future tutorial, which will likely be linked right here, so make sure to verify again typically.
Earlier than we wrap up this tutorial, nonetheless, let’s talk about 4 extra generally used SQL knowledge varieties:
- BLOB: Used for storing giant binary objects
- CLOB: Used for storing giant character objects as much as 2GB in dimension
- JSON: Used to retailer JSON knowledge and JSON paperwork
- XML: Used to retailer XML schema knowledge
