Sql string data type. SQL Server Data Types

Names.

Operators

SQL Concepts

Every SQL statement begins with a verb, i.e. a keyword that describes the action performed by the operator. Typical verbs are SELECT, CREATE, INSERT, DELETE, COMMIT. After the verb comes one or more clauses. The sentence describes the data the operator is working with or contains clarifying information about the action performed by the operator. Each clause also begins with a keyword such as WHERE, FROM, INTO, and HAVING. Some clauses in a statement are mandatory, while others are not. The specific structure and content of the proposal may vary. Many sentences contain table or column names; some of them may contain additional keywords, constants and expressions.

The ANSI/ISO standard defines keywords that are used as verbs and in operator clauses. According to the standard, these keywords cannot be used to name database objects such as tables, columns and users

Each object in the database has a unique name. Names are used in SQL statements and indicate on which database object the operator should perform the action. The ANSI/ISO standard specifies that tables, columns, and users have names. Many SQL implementations also support additional named objects, such as stored procedures, named primary key-foreign key relationships, and data entry forms.

According to the ANSI/ISO standard, names in SQL must be between 1 and 18 characters, begin with a letter, and not contain spaces or special punctuation characters. The SQL2 standard increases the maximum number of characters in a name to 128.

A fully qualified table name consists of the table owner's name and the table name itself, separated by a period (.). For example, full name The Students table, owned by a user named Admin, looks like this:

If a column name is specified in a statement, SQL itself determines which of the tables specified in the same statement contains this column. However, if your statement requires that you include two columns from different tables but with the same name, you must provide fully qualified column names that uniquely identify their location. A fully qualified column name consists of the name of the table containing the column and the column name (simple name), separated by a period (.). For example, the full name of the column StName from the Students table is: Students.StName

The ANSI/ISO standard defines the data types that can be used to represent information in a relational database. The data types available in the SQL1 standard constitute only a minimum set and are supported in all commercial DBMSs. The following are the data types defined in the SQL1 and SQL2 standards:

CHAR (length), CHARACTER (length) Constant-length character strings

VARCHAR(length), CHAR VARYING(length), CHARACTER VARYING(length) Variable length character strings*

NCHAP(length), NATIONAL CHAR(length), NATIONAL CHARACTER(length) Strings of localized characters of constant length*

NCHAR VARYING(length), NATIONAL CHAR VARYING(length),

NATIONAL CHARACTER VARYING(length) Localized character strings of variable length*

INTEGER, INT Integers

SMALLINT Small integers

BIT(length) Constant length bit strings*

BIT VARYNG(length) Variable length bit strings*

NUMERIC(precision, degree), DECIMAL(precision, degree),

DEC(precision, degree) Scalable integer (decimal) numbers

FLOAT(precision) Floating point numbers

REAL Low precision floating point numbers

DOUBLE PRECISION High precision floating point numbers

DATE Calendar date*

TIME(accuracy) Time

TIME STAMP(precision) Date and time*

INTERVAL Time interval*

SQL1 uses the following data types:

1. Strings of constant length characters. Columns that have this data type typically store names of people and companies, addresses, descriptions, etc.

2. Integers. Columns that have this data type typically store data about accounts, quantities, ages, etc. Integer columns are also often used to store identifiers, such as customer, employee, or order ID.

3. Scalable integers. Columns of this type store numbers that have a fractional part that need to be calculated accurately, such as exchange rates and percentages. In addition, monetary values ​​are often stored in such columns.

4. Floating point numbers. This type of column is used to store quantities that can be calculated approximately, such as weight and distance. Floating point numbers can represent a larger range of values ​​than decimal numbers, but rounding errors may occur in calculations.

In most commercial DBMSs, in addition to the data types defined in the SQL1 standard, there are many additional data types, most of which are included in the SQL2 standard. The most important ones are listed below:

1. Variable length character strings. Almost all DBMSs support the VARCHAR data type, which allows storing strings of characters whose length varies within a certain range. The SQL1 standard defined strings of constant length, which are padded on the right with spaces.

2. Monetary values. Many DBMSs support the MONEY or CURRENCY data type, which is usually stored as decimal number or floating point numbers. Having a separate data type for representing monetary values ​​allows them to be correctly formatted when displayed on the screen.

3. Date and time. Support for date/time values ​​is also widespread in various DBMSs, although the way it is implemented varies widely. Typically, you can perform various operations on values ​​of this data type. The SQL2 standard includes the definition of the DATE, TIME, TIMESTAMP, and INTERVAL data types, including support for time zones and the ability to specify the precision of the time representation (for example, tenths or hundredths of a second).

4. Boolean data. Some DBMSs explicitly support boolean values ​​(TRUE or FALSE).

Literature: I.F. Astakhova, A.P. Tolstobrov, V.M. Melnikov SQL in examples and problems. Tutorial: New knowledge, 2002

The data type determines what value the column can contain: integer data, character data, currency data, date and time data, binary string data, etc.

SQL Data Types

Each column in a database table must have a name and a data type.

The SQL developer must decide what type of data will be stored inside each column when creating the table. The data type is a guide for SQL to understand what type of data is expected within each column, and also determines how SQL will interact with the stored data.

List of MySQL data types

There are three main data types in MySQL: text, number, and date.

Text Data Types

Type	Description
CHAR(size)	Holds a fixed-length string (can contain letters, numbers, and special characters). The fixed size is indicated in parentheses. Can store up to 255 characters
VARCHAR(size)	Holds a string of variable length (can contain letters, numbers, and special characters). The maximum size is indicated in parentheses. Can store up to 255 characters. If you put a value greater than 255, it will be converted to TEXT type
TINYTEXT	Holds a string no longer than 255 characters
TEXT	Holds a string with a maximum length of 65535 characters
BLOB	For BLOBs (Binary Large OBjects). Stores up to 65535 bytes of data
MEDIUMTEXT	Holds a string with a maximum length of 16,777,215 characters
MEDIUMBLOB	For BLOBs (Binary Large OBjects). Holds up to 16,777,215 bytes of data
LONGTEXT	Holds a string with a maximum length of 4,294,967,295 characters
LONGBLOB	For BLOBs (Binary Large OBjects). Stores up to 4,294,967,295 bytes of data
ENUM(a,b,c,etc.)	Let us enter a list of possible values. You can list up to 65535 values ​​in an ENUM list. If a value is inserted that is not in the list, a blank value will be added. Note. The values ​​are sorted in the order in which you enter them. You enter possible values in this format: ENUM("a", "b", "c")
SET	Similar to ENUM, except that SET can hold up to 64 list elements and can store multiple options

Numeric Data Types

Type	Description
TINYINT(size)	-128 to 127. 0 to 255 UNSIGNED *. The maximum number of digits can be specified in parentheses
SMALLINT(size)	-32768 to 32767 is normal. 0 to 65535 UNSIGNED*. The maximum number of digits can be specified in parentheses
MEDIUMINT(size)	-8388608 to 8388607 normal. 0 to 16777215 UNSIGNED*. The maximum number of digits can be specified in parentheses
INT(size)	-2147483648 to 2147483647 normal. 0 to 4294967295 UNSIGNED*. The maximum number of digits can be specified in parentheses
BIGINT(size)	-9223372036854775808 to 9223372036854775807 normal. 0 to 18446744073709551615 UNSIGNED *. The maximum number of digits can be specified in parentheses
FLOAT(size,d)	A small floating point number. The maximum number of digits can be specified in the size parameter. The maximum number of digits to the right of the decimal point is specified in parameter d
DOUBLE(size,d)	Large number floating point. The maximum number of digits can be specified in the size parameter. The maximum number of digits to the right of the decimal point is specified in parameter d
DECIMAL(size,d)	DOUBLE is stored as a string allowing a fixed decimal point. The maximum number of digits can be specified in the size parameter. The maximum number of digits to the right of the decimal point is specified in parameter d

UNSIGNED - additional parameter integer type. Typically an integer goes from negative to positive. Adding the UNSIGNED attribute will move this range up so that it starts at zero instead of a negative number.

All values ​​in a column must be the same data type. (The only exception to this rule is values ​​of the SQL_VARIANT data type.) The data types used in Transact-SQL can be divided into the following categories:

numeric types;

character types;

temporary types (dates and/or times);

other data types.

Numeric data types

As you might expect from their name, numeric data types are used to represent numbers. These types and their brief descriptions are given in the table below:

T-SQL Numeric Data Types

Data type	Description
INTEGER	Represents 4-byte integer values ​​in the range -2 32 to 2 32 - 1. INT is a short form of INTEGER.
SMALLINT	Represents 2-byte integer values ​​in the range -32,768 to 32,767
TINYINT	Represents 1-byte integer values ​​in the range 0 to 255
BIGINT	Represents 8 byte long integer values ​​in the range -2 63 to 2 63 - 1
DECIMAL(p,[s])	Represents fixed-point values. The argument p (precision) specifies the total number of digits, and the argument s (scale) specifies the number of digits to the right of the decimal point. Depending on the value of the p argument, decimal values ​​are stored in 5 to 17 bytes. DEC is a short form of DECIMAL.
NUMERIC(p,[s])	Synonym for DECIMAL.
REAL	Used to represent floating point values. The range of positive values ​​extends from approximately 2.23E -308 to -1.18E -38. A null value can also be represented.
FLOAT[(p)]	Like the REAL type, represents floating point values ​​[(p)]. The p argument determines the precision. At p value< 25 представляемые значения имеют одинарную точность (требуют 4 байта для хранения), а при значении p >= 25 - double precision (requires 8 bytes for storage).
MONEY	Used to represent monetary values. MONEY values ​​correspond to 8-byte DECIMAL values, rounded to four decimal places
SMALLMONEY	Represents the same values ​​as the MONEY type, but 4 bytes long

Character data types

There are two general kinds of character data types. Strings can be represented as single-byte characters or Unicode characters. (Unicode uses multiple bytes to represent a single character.) In addition, strings can be of different lengths. The table below lists the categories of character data types with their brief descriptions.

T-SQL Character Data Types

Data type	Description
CHAR[(n)]	Used to represent fixed-length strings consisting of n single-byte characters. The maximum value of n is 8000. CHARACTER(n) is an alternative equivalent form of CHAR(n). If n is not explicitly specified, then its value is assumed to be 1.
VARCHAR[(n)]	Used to represent a string of variable-length single-byte characters (0< n < 8 000). В отличие от типа данных CHAR, количество байтов для хранения значений типа данных VARCHAR равно их действительной длине. Этот тип данных имеет два синонима: CHAR VARYING и CHARACTER VARYING.
NCHAR[(n)]	Used to store fixed-length strings consisting of Unicode characters. The main difference between CHAR and NCHAR data types is that an NCHAR string requires 2 bytes to store each character, while a CHAR string requires 1 byte. Therefore, an NCHAR data type string can contain at most 4000 characters. The NCHAR type can be used to store, for example, characters of the Russian alphabet, because single-byte encodings do not allow this.
NVARCHAR[(n)]	Used to store variable length strings of Unicode characters. Each character of an NVARCHAR data type string requires 2 bytes to store, so an NVARCHAR data type string can contain at most 4000 characters.

The VARCHAR data type is identical to the CHAR data type, except for one difference: if the contents of a CHAR(n) string are shorter than n characters, the remainder of the string is padded with spaces. And the number of bytes occupied by a VARCHAR string is always equal to the number of characters in it.

Time Data Types

Transact-SQL supports the following temporary data types:

Data Types DATETIME And SMALLDATETIME are used to store date and time as integer values ​​of 4 and 2 bytes in length, respectively. Values ​​of type DATETIME and SMALLDATETIME are stored internally as two separate numeric values. The date component of values ​​of type DATETIME is stored in the range from 01/01/1753 to 31/12/9999, and the corresponding component of values ​​of type SMALLDATETIME is stored in the range from 01/01/1900 to 06/06/2079. The time component is stored in a second 4-byte (2-byte for SMALLDATETIME values) field as the number of three-hundredths of a second (for DATETIME) or the number of minutes (for SMALLDATETIME) that have elapsed since midnight.

If you only want to store the date or time component, using DATETIME or SMALLDATETIME values ​​is somewhat inconvenient. For this reason, data types were introduced in SQL Server DATE And TIME, which store only the date and time components of DATETIME values, respectively. DATE values ​​occupy 3 bytes, representing the date range from 01/01/0001 to 31/12/9999. TIME values ​​occupy 3-5 bytes and represent time with an accuracy of 100 ns.

Data type DATETIME2 used to represent date and time values ​​with high precision. Depending on the requirements, values ​​of this type can be defined in different lengths, and they occupy from 6 to 8 bytes. The time component represents time to the nearest 100 ns. This data type does not support daylight saving time.

All temporary data types discussed so far do not support time zones. Data type DATETIMEOFFSET has a component to store the time zone offset. For this reason, values ​​of this type take up 6 to 8 bytes. All other properties of this data type are the same as those of the DATETIME2 data type.

Transact-SQL default date values ​​are defined as a string of the format "mmm dd yyyy" (for example, "Jan 10 1993"), enclosed in single or double quotes. (But the relative order of the month, day, and year components can be changed using the instruction SET DATEFORMAT. In addition, the system supports numeric values ​​for the month component and the delimiters / and -.) Similarly, the time value is specified in 24-hour format as "hh:mm" (for example, "22:24").

Transact-SQL supports various formats for entering DATETIME values. As mentioned, each component is defined separately, so date and time values ​​can be specified in any order or separately. If one of the components is not specified, the system uses the default value for it. (The default time is 12:00 AM (before noon).)

Binary and bit data types

There are two types of binary data types: BINARY and VARBINARY. These data types describe data objects in the system's internal format and are used to store bit strings. For this reason, values ​​of these types are entered using hexadecimal numbers.

Values ​​of the bit type contain only one bit, so up to eight values ​​of this type can be stored in one byte. Brief description The properties of binary and bit data types are given in the table below:

LOB data type

Data type LOB (Large OBject) used to store data objects up to 2 GB in size. Such objects are typically used to store large amounts of text data and to load plug-ins and audio and video files. Transact-SQL supports the following LOB data types:

Beginning with SQL Server 2005, the same programming model is used to access values ​​of standard data types and values ​​of LOB data types. In other words, you can use convenient system functions and string operators to work with LOB objects.

In the Database Engine MAX parameter used with VARCHAR, NVARCHAR, and VARBINARY data types to define variable-length column values. When the default length value MAX is used instead of explicitly specifying the length of a value, the system examines the length of a particular string and decides whether to store that string as a regular value or as a LOB value. The MAX parameter specifies that the size of column values ​​can be up to the maximum LOB size of the system.

Although the system decides how LOB objects are stored, the default settings can be overridden by using the sp_tableoption system procedure with the LARGE_VALUE_TYPES_OUT_OF_ROW argument. If this argument is set to 1, then data in columns declared using the MAX parameter will be stored separately from other data. If the argument is 0, the Database Engine stores all values ​​up to 8,060 bytes in the table row as normal data, and stores larger values ​​off-row in the LOB storage area.

Starting in SQL Server 2008, you can use VARBINARY(MAX) for columns of type FILESTREAM attribute to save data BLOB (Binary Large OBject) directly in the file NTFS system. The main benefit of this attribute is that the size of the corresponding LOB object is limited only by the size of the file system volume.

UNIQUEIDENTIFIER data type

As its name suggests, the UNIQUEIDENTIFIER data type is single-valued identification number, which is stored as a 16-byte binary string. This data type is closely related to the identifier GUID (Globally Unique Identifier - globally unique identifier), which guarantees uniqueness on a global scale. Thus, this data type allows data and objects to be uniquely identified in distributed systems.

You can initialize a column or variable of type UNIQUEIDENTIFIER using the NEWID or NEWSEQUENTIALID function, or using a special format string constant consisting of hexadecimal digits and hyphens. These features are discussed in the next article.

A column with values ​​of the UNIQUEIDENTIFIER data type can be accessed using in a query keyword ROWGUIDCOL to indicate that the column contains ID values. (This keyword does not generate any values.) A table can contain multiple UNIQUEIDENTIFIER columns, but only one of them can have the ROWGUIDCOL keyword.

Data type SQL_VARIANT

The SQL_VARIANT data type can be used to store values different types simultaneously, such as numeric values, strings and dates. (The exception is TIMESTAMP values.) Each SQL_VARIANT column value consists of two parts: the value itself and information describing that value. This information contains all the properties of the actual value data type, such as length, scale, and precision.

To access and display information about the values ​​of a column of type SQL_VARIANT, use the SQL_VARIANT_PROPERTY function.

You should only declare a column type as SQL_VARIANT if it is truly necessary. For example, if the column is intended to store values ​​of different data types, or if the type of data that will be stored in this column is unknown when creating the table.

Data type HIERARCHYID

The HIERARCHYID data type is used to store a complete hierarchy. For example, a value of this type can store a hierarchy of all employees or a hierarchy of folders. This type is implemented as a CLR user-defined type that covers several system functions for creating and working with hierarchy nodes. The following functions, among others, belong to methods of this data type: GetLevel(), GetAncestor(), GetDescendant(), Read() and Write().

TIMESTAMP data type

The TIMESTAMP data type specifies a column that is defined as VARBINARY(8) or BINARY(8) , depending on the column's nullability. For each database, the system maintains a counter that increments each time any row containing a TIMESTAMP cell is inserted or updated, and assigns that value to that cell. Thus, using cells of type TIMESTAMP, you can determine relative time last change corresponding table rows. ( ROWVERSION is a synonym for TIMESTAMP.)

By itself, the value stored in a TIMESTAMP column is not important. This column is typically used to determine whether a particular table row has changed since it was last accessed.

Storage options

Starting with SQL Server 2008, there are two different storage options, both of which allow you to store LOBs and save disk space. These are the following options:

storing data of the FILESTREAM type;

storage using sparse columns.

These storage options are discussed in the following subsections.

Storing FILESTREAM data

As mentioned earlier, SQL Server supports large object (LOB) storage through the VARBINARY(MAX) data type. The property of this data type is that binary large objects (BLOBs) are stored in the database. This may cause performance problems when storing very large files, such as audio or video files. In such cases, this data is stored outside the database in external files.

FILESTREAM data storage supports the management of LOB objects that are stored in file system NTFS. The main advantage of this type of storage is that although the data is stored outside the database, it is managed by the database. Thus, this storage type has the following properties:

FILESTREAM data can be saved using the CREATE TABLE statement, and data modification statements (SELECT, INSERT, UPDATE and DELETE) can be used to work with this data;

The database management system provides the same level of security for FILESTREAM data as for data stored within the database.

Sparse columns

The purpose of the storage option provided by sparse columns is significantly different from the purpose of the FILESTREAM storage type. While the purpose of FILESTREAM storage is to store LOB objects outside the database, the purpose of sparse columns is to minimize the disk space consumed by the database.

Columns of this type allow you to optimize the storage of columns whose majority values ​​are null. When using sparse columns, storing null values ​​does not require disk space, but on the other hand, storing non-null values ​​requires an additional 2 to 4 bytes, depending on their type. For this reason, Microsoft recommends using sparse columns only in cases where at least 20% of the overall disk space savings are expected.

Sparse columns are defined in the same way as other table columns; the same applies to addressing them. This means that you can use SELECT, INSERT, UPDATE, and DELETE statements to access sparse columns in the same way that you access regular columns. The only difference concerns the creation of sparse columns: to define a specific column as sparse, use SPARSE argument after the column name, as shown in this example:

column_name data_type SPARSE

Multiple sparse table columns can be grouped into a column set. This set will be alternative way store and access values ​​in all sparse table columns.

NULL value

Null is a special value that can be assigned to a table cell. This value is typically used when the information in a cell is unknown or not applicable. For example, if you don't know the home phone number of a company employee, we recommend setting the corresponding cell in the home_telephone column to null.

If the value of any operand of any arithmetic expression is null, the value of the result of evaluating this expression will also be null. Therefore, in unary arithmetic operations, if the value of expression A is null, then both +A and -A return null. In binary expressions, if the value of one or both operands A and B is null, then the result of adding, subtracting, multiplying, dividing, and moduloing those operands will also be null.

If an expression contains a comparison operator and one or both of its operands is null, the result of that operation will also be null.

The value null must be different from all other values. For numeric data types, the value 0 and null are not the same. The same applies to the empty string and null for character data types.

Null values ​​can only be stored in a table column if it is explicitly allowed in the column definition. On the other hand, null values ​​are not allowed for a column unless its definition explicitly states NOT NULL. If a column with a data type (except TIMESTAMP) is not explicitly specified as NULL or NOT NULL, then the following values ​​are assigned:

NULL if the parameter value ANSI_NULL_DFLT_ON The SET instruction is on.

NOT NULL if the ANSI_NULL_DFLT_OFF parameter of the SET statement is on.

If the set statement is not activated, the column will default to NOT NULL. (Null values ​​are not allowed for TIMESTAMP columns.)

SQL Data Types are divided into three groups:
— string;
— floating point (fractional numbers);
— integers, date And time.

1. SQL Data Types string
   

   SQL Data Types	Description
   CHAR(size)	Fixed-length strings (can contain letters, numbers, and special characters). The fixed size is indicated in parentheses. Up to 255 characters can be recorded
   VARCHAR(size)
   TINYTEXT	Can store a maximum of 255 characters.
   TEXT
   BLOB	Can store a maximum of 65,535 characters.
   MEDIUMTEXT
   MEDIUMBLOB	Can store a maximum of 16,777,215 characters.
   LONGTEXT
   LONGBLOB	Can store a maximum of 4,294,967,295 characters.
   ENUM(x,y,z,etc.)	Allows you to enter a list of valid values. You can enter up to 65535 values ​​in SQL Data Type ENUM list. If, when inserting, the value will not be present in the list ENUM, then we will get an empty value. You can enter possible values ​​in the following format: ENUM ("X", "Y", "Z")
   SET	SQL Data Type SET reminds ENUM except that SET can contain up to 64 values.

2. SQL Data Types floating point (fractional numbers) and integers
   

   SQL Data Types	Description
   TINYINT(size)	Can store numbers from -128 to 127
   SMALLINT(size)	Range -32,768 to 32,767
   MEDIUMINT(size)	Range -8,388,608 to 8,388,607
   INT(size)	Range -2,147,483,648 to 2,147,483,647
   BIGINT(size)	Range -9,223,372,036,854,775,808 to 9,223,372,036,854,775,807
   FLOAT(size,d)	Low precision floating point number.
   DOUBLE(size,d)	Double precision floating point number.
   DECIMAL(size,d)	A fractional number stored as a string.

3. SQL Data Types— Date and time
   

MySQL Data Types

Types MySQL data are divided into the following types:

• Numerical data types
  

  Data Types	Byte	From	To
  TINYINT	1	-128	127
  SMALLINT	2	-32768	32767
  MEDIUMINT	3	-8388608	8388607
  INT	4	-2147483648	2147483647
  BIGINT	8	-9223372036854775808	9223372036854775807

• Data Types date and time
  
• Character Data Types
  

Oracle Data Types

Oracle Data Types

• CHAR– fixed text strings up to 2000 bytes. Type value CHAR padded to the specified length with spaces.
• VARCHAR 2— text strings of variable length up to 4000 bytes.
• NUMBER— numerical data.
• DECIMAL- numerical data
• DATE- used to store dates.
• RAW- used to store binary data up to 2000 bytes.
• LONG- used to store text data up to 2 GB in length
• LONG RAW- used to store binary data up to 2 GB
• ROWID- used to store identifiers ROWID databases Oracle in a special format (addresses of table rows).
• BLOB- Up to 4 GB of binary data is saved. Data of this type is stored outside the table, but in the table Oracle there are only pointers to objects
• CLOB, NCLOB— up to 4 GB of text data is saved. NCLOB is the data type NLS large fixed length ( NLS means National Language Set– a set for national languages ​​– and is used to work in Oracle in languages ​​other than English. In English, 1 byte is needed to store one character, and in some languages ​​of the world with large character sets (Japanese, Chinese, Korean), languages ​​where the text is read from right to left (Arabic, Hebrew) several bytes are required to store one character). Data of this type is stored outside the table, and the table contains only pointers to objects.
• BFILE- up to 4 GB of unstructured data is saved, and in files operating system(external files).

ANSI SQL the standard recognizes only text and number, while most commercial programs use other special types such as DATE And TIME- in fact, almost standard types. Some packages also support types such as MONEY And BINARY. Data Types, recognized by ANSI, consist of strings of characters and various types of numbers, which can be classified as accurate numbers and approximate numbers.

CHARACTER(length) defines a specification of character strings, where length specifies the length of strings of a given type. Values ​​of this type must be enclosed in single quotes. Most implementations support variable length strings for data types VARCHAR And LONG VARCHAR(or just LONG).

While a field like CHAR can always allocate memory for the maximum number of characters that can be stored in a field, field VARCHAR given any number of characters can only allocate a certain amount of memory to store the actual contents of the field, although SQL may set up some additional memory space to keep track of the current field length. Field VARCHAR can be of any length, including an implementation-defined maximum. This maximum can vary from 254 to 2048 characters for VARCHAR and up to 16,000 characters for LONG. LONG typically used for text of an explanatory nature or for data that cannot be easily compressed into simple field values; VARCHAR can be used for any text string whose length may vary.

Extracting and modifying fields VARCHAR- more complex, and therefore slower process, than extracting and modifying fields CHAR. In addition, some memory VARCHAR remains always unused to ensure that the entire length of the string is contained. When using such types, it should be possible for fields to be combined with other fields.

Exact numeric types are numbers, with or without a decimal point, that can be represented as [+|-]<целое без знака>[.<целое без знака>] and be specified as:

DECIMAL(precision [, scale])- the size argument has two parts: precision and scale. Scale cannot exceed precision. Precision indicates how many significant digits a number has. The scale specifies the maximum number of digits to the right of the decimal point. Scale = zero makes the field equivalent to an integer.

NUMERIC(precision [, scale])- same as DECIMAL except that the maximum decimal cannot exceed the precision argument

INTEGER- a number without a decimal point. Equivalent DECIMAL, but without the numbers to the right of the decimal point, i.e. with scale equal to 0. The size argument is not used (it is automatically set to an implementation-dependent value).

SMALLINT- same as INTEGER, except that, depending on the implementation, the default size may (or may not) be less than INTEGER.

Approximate Numeric Types are numbers in exponential (base 10) notation, represented as <литеральное значение точного числа>E<целое со знаком> and specified as follows:

FLOAT[(precision)]- floating point number. The size argument consists of a single number that specifies the minimum precision.

REAL- same as FLOAT except that no size argument is used. The precision is set to implementation-dependent by default.

DOUBLE PRECISION- same as REAL, except that the implementation-defined accuracy for DOUBLE PRECISION must exceed implementation-defined accuracy REAL.

Access data types

Types Access data are divided into the following groups:

• Text– maximum 255 bytes.
• Memo - up to 64000 bytes.
• Numerical— 1,2,4 or 8 bytes. For a numeric type, the field size may be as follows:
  • byte— integers from -0 to 255, occupies 1 byte when stored
  • whole— integers from -32768 to 32767, takes 2 bytes
  • long integer— integers from -2147483648 to 2147483647, takes 4 bytes
  • floating point— numbers accurate to 6 digits from –3.4*1038 to 3.4*1038, takes 4 bytes
  • floating point— numbers with precision from –1.797*10308 to 1.797*10308, takes 8 bytes
• Date-time- 8 bytes
• Monetary- 8 bytes, data on monetary amounts, stored with 4 decimal places.
• Counter is a unique long integer generated by Access when each new entry- 4 bytes.
• Logical— logical data 1 bit.
• OLE Object Field– up to 1 gigabyte, pictures, diagrams and other objects OLE from applications Windows. Objects OLE may be linked or embedded.
• Hyperlinks— a field in which hyperlinks are stored. A hyperlink can be either type UNC(standard format for specifying a path including network server files), or URL (address of an object, document, page or other type of object on the Internet or Intranet. URL address defines the access protocol and the destination address).
• Substitution Wizard— a field that allows you to select a value from another table Accesses or from a list of values ​​using a combo box. Most often used for key fields. Has the same size as the primary key, which is also a lookup field, usually 4 bytes. ( Primary key– one or more fields, the combination of values ​​of which uniquely identifies each record in the table Accesses. Does not allow undefined.Null. values ​​must always have a unique index. Serves to link a table with secondary keys of other tables).

SQL Server Data Types

Microsoft SQL Server SQL 2003. Also SQL Server supports additional data types, used to uniquely identify rows of data in a table and across multiple servers, such as UNIQUEIDENTIFIER , which is consistent with Microsoft's "breadth-first" hardware philosophy (i.e., deploying a base across multiple servers on Intel platforms) instead of "tall-height" (i.e. implementation on one huge powerful UNIX server or Windows Data Center Server).

Data Types used in SQL Server:

• BIGINT ( data type SQL2003: B1GINT)
  Stores signed and unsigned integers in the range -9,223,372,036,854,775,808 to 9,223,372,036,854,775,807. Occupies 8 bytes. See the INT type for IDENTITY property rules that also apply to the BIGINT type.
• BINARY[(n)] ( data type SQL2003: BLOB)
  Stores a fixed-length binary value from 1 to 8000 bytes. A BINARY value takes up n + 4 bytes.
• BIT ( data type SQL2003: BOOLEAN)
  Stores the values ​​1, 0, or NULL, which stands for "unknown". One byte can store up to 8 values ​​from columns of the BIT table type. One more byte can accommodate an additional 8 BIT values. BIT type columns cannot be indexed.
• CHAR[(n)] , CHARACTER[(n)] ( data type SQL2003: CHARACTER[(n)])
  Stores fixed-length character data from 1 to 8000 characters. Any unused space is filled with spaces by default. (Automatic padding can be disabled.) The type takes up n bytes.
• CURSOR ( data type SQL2003: missing)
  A special data type used to describe a cursor in the form of a variable or stored procedure parameter OUTPUT. The type cannot be used in a CREATE TABLE statement. The CURSOR type can be NULL.
• DATETIME (SQL2003 data type: TIMESTAMP)
  Stores a date and time value in the range from 01-01-1753 00:00:00 to 31-12-9999 23:59:59. Storage requires 8 bytes.
• DECIMAL (p. s) , DEC (p. s) , NUMERIC (p, s) ( data type SQL2003: DECIMAL (p, s) , NUMERIC (p. s))
  Stores decimals up to 38 digits long. The p and s values ​​determine the precision and scale, respectively. The default scale is 0. The space the value takes up is determined by the precision used.
  For precision 1-9, 5 bytes are used.
  For precision 10-19, 9 bytes are used.
  With a precision of 20-28, 13 bytes are used.
  With precision 29-39, 17 bytes are used.
  See the INT type for IDENTITY property rules that also apply to the DECIMAL type.
• DOUBLE PRECISION ( data type SQL2003: missing)
  Synonym for FLOAT(53) .
• FLOAT[(n)] ( data type SQL2003: FLOAT, FLOAT(n))
  Stores floating point values ​​in the range -1.79E + 308 to 1.79E + 308. The precision, determined by the and parameter, can vary from 1 to 53. To store 7 digits (n - from 1 to 24) requires 4 bytes. Values ​​greater than 7 digits occupy 8 bytes.
• IMAGE ( data type SQL2003: BLOB)
  Stores a binary value of variable length up to 2,147,483,647 bytes. This data type is often used to store graphics, audio, and files such as MS Word documents and MS Excel spreadsheets. Values ​​of type IMAGE cannot be freely manipulated. IMAGE and TEXT columns have many restrictions on how they can be used. See the description of the TEXT type for a list of commands and functions that also apply to the IMAGE type.
• INT (data type SQL2003: INTEGER)
  Stores signed or unsigned integers in the range -2,147,483,648 to 2,147,483,647. Occupies 4 bytes. All integer data types, as well as types that store decimal fractions, support the IDENTITY property; identity is an automatically incremented row identifier. Refer to the “CREATE/ALTER TABLE Statement” section in Chapter 3.
• MONEY (data type SQL2003: missing)
  Stores currency values ​​in the range -922337203685477.5808 to 922337203685477.5807. The value takes 8 bytes.
• NCHAR(n) , NATIONAL CHAR(n) , NATIONAL CHARACTER(n) ( data type SQL2003: NATIONAL CHARACTER(n))
  Stores fixed-length UNICODE data of up to 4000 characters. Storage requires n*2 bytes.
• NTEXT, NATIONAL TEXT ( data type SQL2003:NCLOB)
  Stores text fragments in UNICODE format up to 1,073,741,823 characters long. See the description of the TEXT type for a list of commands and functions that also apply to the NTEXT type.
• NUMERIC(p, s) ( data type SQL2003: DECIMAL (p, s))
  Synonym for DECIMAL type. See the description of the INT type for rules related to the IDENTITY property.
• NVARCHAR(n) , NATIONAL CHAR VARYING(n) , NATIONAL CHARACTER VARYING(n) ( data type SQL2003: NATIONAL CHARACTER VARYING(n))
  Stores UNICODE data of variable length up to 4000 characters.
  The space taken up is calculated as twice the length of all characters inserted in the field (number of characters * 2).
  In SQL Server, the SET ANSI_PADDINGX system option for NCHAR and NVARCHAR fields is always ON.
• REAL , FLOAT(24) ( data type SQL2003: REAL)
  Stores floating point values ​​in the range -3.40E+38 to 3.40E+38. Occupies 4 bytes. The REAL type is functionally equivalent to the FLOAT(24) type.
• ROWVERSION ( data type SQL2003: missing)
  A unique number stored in the database that is updated whenever a row is updated, called TIMESTAMP in earlier versions.
• SMALLDATETIME( data type SQL2003: missing)
  Stores date and time in the range from '01-01-1900 00:00' to '06-06-2079 23:59' with minute precision. (Minutes are rounded down if the seconds value is 29.998 or less, otherwise they are rounded up.) The value occupies 4 bytes.
• SMALLINT( data type SQL2003: SMALLINT)
  Stores signed or unsigned integers in the range -32,768 to 32,767. Occupies 2 bytes. See the description of the INT type for rules related to the IDENTITY property that also apply to this type.
• SMALLMONEY ( data type SQL2003: missing)
  Stores currency values ​​in the range from 214748.3648 to -214748.3647. The values ​​occupy 4 bytes.
• SQLVARIANT( data type SQL2003: missing)
  Stores values ​​related to other data types supported by SQL Server, excluding TEXT, NTEXT, ROWVERSION, and other SQL VARIANT values. Can store up to 8016 bytes of data, NULL and DEFAULT values ​​are supported. The SQL VARIANT type is used in columns, parameters, variables, and return values ​​of functions and stored procedures.
• TABLE ( data type SQL2003: missing)
  A special type that stores the resulting data set as a result of the last process. Used exclusively for procedural processing and cannot be used in CREATE TABLE statements. This data type reduces the need to create temporary tables in many applications. Can reduce the need for procedure recompilations, thereby speeding up the execution of stored procedures and user-defined functions.
• TEXT( data type SQL2003: CLOB)
  Stores very large chunks of text up to 2,147,483,647 characters long. TECHNIC IMAGE values ​​are often much more difficult to manipulate than, say, VARCHAR values. For example, you cannot create an index on a column of type TEXT or IMAGE. TEXT values ​​can be manipulated using the DATALENGTH, PATINDEX, SUBSTRING, TEXTPTR, and TEXTVALID functions, as well as the READTEXT, SET TEXTSIZE, UPDATETEXT, and WRITETEXT commands.
• TIMESTAMP ( data type SQL2003: TIMESTAMP)
  Stores an automatically generated binary number that is unique in the current database and therefore distinct from the ANSI TIMESTAMP data type. The TIMESTAMP type takes up 8 bytes. Currently, instead of TIMESTAMP, it is better to use ROWVERSION values ​​to uniquely identify rows.
• TINYINT
  Stores unsigned integers in the range 0 to 255 and occupies 1 byte. See the description of the INT type for rules related to the IDENTITY property that also apply to this type.
• UNIQUEIDENTIFIER ( data type SQL2003: missing)
  Represents a value that is unique across all databases and all servers. Represented in the form xxxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxxxxxx, in which each "x" represents hexadecimal number in the range 0-9 or a - f. The only operations that can be performed on values ​​of this type are comparison and testing for NULL. Columns of this type can use constraints and properties, with the exception of the IDENTITY property.
• VARBINARY[(n)] ( data type SQL2003: BLOB)
  Represents a variable-length binary value, up to 8000 bytes. The space occupied is the size of the inserted data plus 4 bytes.
• VARCHARf(n)] , CHAR VARYING [(n)] , CHARACTER VARYING [(n)] ( data type SQL2003: CHARACTER VARYING (n))
  Stores fixed-length character data ranging in size from 1 to 8000 characters. The space taken up is the actual size of the entered value in bytes, not the value n.

PostgreSQL Data Types

Database PostgreSQL supports most data types SQL2003 plus a huge set of types for storing spatial and geometric data. PostgreSQL boasts a rich set of operators and functions specifically designed for geometric data types. This includes features such as rotation, intersection search, and scaling. IN PostgreSQL there is also support additional versions existing data types that are characterized by occupying less disk space than the corresponding original versions. For example, in PostgreSQL Several variants of the INTEGER type are offered for storing large and small numbers, respectively taking up more or less space.

• BJGSERJAL
• BIT ( data type SQL2003: BIT)
  Fixed length bit string.
• BIT VARYING(n) varbit(n) ( data type SQL2003: BIT VARYING)
  Denotes a variable length bit string of n bits.
• BOOL , BOOLEAN ( data type SQL2003: BOOLEAN)
  Stores a Boolean value (true/false/unknown). The recommended values ​​are the TRUE and FALSE keywords, although PostgreSQL allows multiple literal values ​​for "true": TRUE, t, true, y, yes, and 1. Valid values ​​for "false" are: FALSE, f, false, n, no, and 0.
• BOX ((xl, y I), (x2, y2)) ( data type SQL2003: missing)
  Stores values ​​that define a rectangular area on a plane. The values ​​occupy 32 bytes and are presented in the form ((xl, yl), (x2, y2)), which corresponds to the opposite corners of the rectangle (upper right and lower left, respectively). The outer parentheses are optional.
• BYTEA ( data type SQL2003: BINARY LARGE OBJECT)
  Raw, binary data used, for example, to store graphics, sound, and documents. This type of storage requires 4 bytes plus the actual size of the bit string.
• CHAR(n) , CHARA CTER(n) ( data type SQL2003: CHARACTER(n))
  Contains a fixed-length character string padded with spaces up to length n. Attempting to insert a value longer than n results in an error (unless the extra characters are spaces, in which case they are trimmed to be n characters long).
• CIDR(x.x.x.xZy) ( data type SQL2003: missing)
  Describes a network or host address in IP protocol version 4 format. The address occupies 12 bytes. Valid values ​​are any network addresses allowed by the IPv4 protocol. In the CIDR type, the data is represented in the form x.x.x.x/y, where x.x.x.x is the IP address and y is the number of netmask bits. CIDR does not allow non-zero bits to the right of the zero bit of the netmask.
• CIRCLE x, y, r (SQL2003 data type: missing)
  Describes a circle on a plane. The values ​​occupy!’ 24 bytes and are presented in the form x, y, r. The values ​​* and y represent the coordinates of the circle's center, and r is the length of its radius. The values ​​for x, y, and r can be delimited by parentheses or curly braces if desired.
• DATE (SQL2003 data type: DATE)
  Stores a calendar date (year, day and month) without time of day. Occupies 4 bytes. Dates must be in the range 4713 BC. up to 32767 and. e. The resolution limit for the DATE type is, of course, one day.
• DATETIME (SQL2003 data type: T1MESTAMP)
  Stores a calendar date indicating the time of day.
• DECIMAL [(p, s)], NUMERIC [(p. s)] (SQL2003 data type: DECIMAL (PRECISION SCALE), NUMERIC (x, p))
  Stores exact numeric values ​​with a precision (p) of 9 and a scale (s) of zero, with no upper limit.
• FLOAT4, REAL (SQL2003 data type: FLOAT(p))
  Stores floating point values ​​with a precision of 8 or less and 6 decimal places.
• FLOAT8, DOUBLE PRECISION (SQL2003 data type: FLOAT(p), 7
• INET (x.x.x.x/y)
  Stores a network or host address in IP protocol version 4 format. The address takes 12 bytes. Valid values ​​are any network addresses allowed by the IPv4 protocol, x.x.x.x is the IP address, y is the number of network mask bits. The default netmask is 32. Unlike CIDR, INET allows non-zero bits to the right of the netmask.
• SMALLINT (SQL2003 data type: SMALLINT)
  Stores two-byte signed and unsigned integers in the range -32,768 to 32,767. Synonym: INT72.
• INTEGER (SQL2003 data type: INTEGER)
  Stores 4-byte signed or unsigned integers in the range -2,147,483,648 to 2,147,483,647. Synonym: 1NT4.
• INT8 (SQL2003 data type: missing)
  Stores 8-byte signed or unsigned integers in the range -9,223,372,036,854,775,808 to 9,223,372,036,854,775,807.
• INTERVAL(p) (SQL2003 data type: missing)
  Stores commonly used time interval values ​​in the range -178,000,000 to 178,000,000 years. Occupies 12 bytes. The lowest resolution of the INTERVAL type is microsecond. This type of date storage differs from the ANSI standard, which requires an interval specifier, such as INTERVAL YEAR TO MONTH.
• LINE ((xl, y I), (x2, y2)) (SQL2003 data type: missing)
  Stores information about a straight line on a plane, without endpoints. The values ​​occupy 32 bytes and are represented in the form ((xl, yl), (x2, y2)), which indicates the starting and ending point of the line. The parentheses in LINE type syntax are optional.
• LSEG ((xl, yl), (x2, y2)) (SQL2003 data type: missing)
  Stores a line segment (LSEG) on a plane, with endpoints. The values ​​occupy 32 bytes and are represented in the form ((xl, yl), (x2, y2)). The parentheses in the LSEG type syntax are optional. For those interested, a "segment" is what most people commonly call a line. For example, the lines on the playing field are actually segments.
• MACADDR (SQL2003 data type: none)
  Can store MAC address value network card computer. Occupies 6 bytes.
  The MACADDR type allows several forms of address corresponding to industrial
  standards, for example:
  08002В:010203
  08002В-010203
  0800.2В01.0203
  08-00-2В-01-02-03
  08:00:2B:01:02:03
• MONEY, DECIMAL(9,2) (SQL2003 data type: none)
  Stores currency values ​​in US format in the range -21474836.48 to 21474836.47.
• NUMERIC [(p, s)], DECIMAL[(p, s)] (SQL2003 data type: none)
  Stores precise numeric data with precision (p) and scale (s).
• OID (SQL2003 data type: missing)
  Stores unique object identifiers.
• PATH ((xl, y), ... n), Path ((xl, yl), ... n) (SQL2003 data type: missing)
  Describes an open or closed geometric contour on a plane. The values ​​are presented in the form ((xl, yl), ... n) and occupy 4 + 32 bytes. Each pair of values ​​(x, y) corresponds to a contour point. Contours are either open, when the first and last points do not coincide, or closed, when the first and last points are the same. To indicate closed contours, the expression is enclosed in parentheses, and to indicate open ones - into square ones.
• POINT(x,y) (SQL2003 data type: none)
  Stores a value describing a geometric point on a plane. Occupies 16 bytes. Values ​​are presented as (x, y). Point is the basis of other two-dimensional spatial data types supported in PostgreSQL. The parentheses in this type of syntax are optional.
• POLYGONE ((x1,y1), ... n) (SQL2003 data type: none)
  Stores a value describing a closed geometric contour on a plane. Occupies 4 + 32n bytes. The values ​​are presented in the form ((xl, yl), ... n). The POLYGONE type is essentially equivalent to the type used to store a closed path.
• SERIAL, SER1AL4 (SQL2003 data type: none)
  Stores an automatically incremented, unique integer ID value used for indexing and cross-referencing. This type stores up to 4 bytes of data (number range 1 to 2,147,483,647). Tables defined with this data type cannot be dropped directly. First you need to issue the DROP SEQUENCE command, and only after that issue the DROP TABLE command.
• SER1AL8, BIGSERIAL (SQL2003 data type: missing)
  Stores an automatically incrementing, unique integer ID value used for indexing and cross-referencing. This type stores up to 8 bytes of data (number range from 1 to 9,223,372,036,854,775,807). Tables defined with this data type cannot be dropped directly. First you need to issue the DROP SEQUENCE command, and only after that issue the DROP TABLE command.
• TEXT (SQL2003 data type: CLOB)
  Stores a large array of character strings of variable length up to 1 gigabyte. PostgreSQL automatically compresses TEXT strings, so disk space can be smaller than the size of the strings.
• TIME [(p)] (SQL2003 data type: TIME) Stores the time of day either without time zone (uses 8 bytes) or with the time zone in which the database server is located (uses 12 bytes). Valid range of values: 00:00:00.00 - 23:59:59.99. The smallest value is 1 microsecond. Please note that in most UNIX systems Time zone information is only available for dates from 1902 to 2038.
• TIMESPAN (SQL2003 data type: missing)
  Stores a value that represents a specific period of time. The most similar type to TIMESPAN in the ANSI standard is the INTERVAL type.
• TIMESTAMP [(r)] ( data type SQL2003: TIMESTAMP)
  Stored date and time with and without the time zone of the database server. The acceptable range of values ​​is from 4713 BC. e. up to N1,465,001 e. One TIMESTAMP value takes 8 bytes. The smallest value is 1 microsecond. Note that on most UNIX systems, time zone information is only available for dates from 1902 to 2038.
• TIMETZ ( data type SQL2003: TIME WITH TIMEZONE)
  Stores the time of day value, taking into account the time zone.
• VARCHAR(n) , CHARACTER VARYLNG(n) ( data type SQL2003: CHARACTER VARYING(n))
  Stores variable-length character strings up to n in length. Trailing spaces are not stored.

Today in the world there is a huge amount of technologies and means designed for storing information. Databases are one of the most common tools. To work with them, various control systems are used. This storage method assumes that all information is clearly structured and entered into special tables. Tables, in turn, consist of attribute columns of a certain type of information.

What is a data type?

Today, there are several definitions that explain the essence of the concept of “data type”. Each of them has one general meaning. Conventionally, a data type can be defined as a group of data characterized by its values ​​- numeric, symbolic, and so on - as well as operations that can be applied to the values ​​in question. The scope of use of various types of data is very multifaceted. Data can be used not only for storing information, but also for programming to solve given problems. When developing programs, the practice of using own types data with a specific set of operations. User information is always based on basic data types. The SQL standard is based on the most common types of information, but with a number of specific additions.

Data types: classification

Grouping data by type has been around for a long time. It was caused by the need to structure all volumes of information to ensure ease of processing. Today, all existing data types are based on two basic ones: character and numeric. Based on these data, it was developed modern classification, which includes pointer, integer, boolean, floating point, and string information types. All of the above are fully covered by the SQL classification. However for modern systems there are special add-ons for database management. These include MySQL and Oracle.

Basic Data Types

The data types that are used to create table attributes that meet SQL language standards can be divided into 4 classes: string values, fractions, integer values, time and date values.

String data type

The first data type allows you to store any information presented in the form of characters. In this case we are talking about special characters and letters, which together will be processed in any SQL query like lines. Below are the data types that form the first group:

— CHAR (size) – this type is used to store strings. The parameter specified in brackets allows you to fix the length of the stored string. For a string you can set maximum size in bytes – 255.

— VAR CHAR (size) — similar to the previous type, this type allows you to store strings whose length does not exceed 255 characters. The main difference between this type and CHAR is that here the required amount of memory is allocated to store a value of this type. Thus, for a string that consists of 5 characters, 6 bytes of memory will be needed. In the first case, memory for storing the value will be allocated in accordance with the specified parameter.

— TINY TEXT - this type is used to store text information, the volume of which does not exceed 65535 characters.

— BLOB – this type is similar to the TEXT type. It allows you to save in the database text information, which can be up to 65,535 characters. However, in practice, this type can be used to store drawings, audio information, electronic documents and other things.

— MEDIUM TEXT – this type was developed based on the TEXT type. Due to the increased size to 16777215 characters, this type allows you to store more data.

— MEDIU MBLOB – used for storing electronic documents in the database, the size of which does not exceed 16777215 characters.

- LONG TEXT - by functionality similar to previous data types, but has a memory capacity increased to 4 GB.

— LONG BLOB – makes it possible to place large data in the database - 4294967295 characters.

- ENUM is a special data type that is used to specify a list of different values. This type allows you to specify 65535 values. Strings of this type can take only one of all values ​​specified in the set. If values ​​that are not in the given list are added, empty values ​​will be written to the table.

— SET – this data type specifies a set of valid values. Unlike the previous type, it is used to contain 64 parameters that can be initialized by several elements of the given arguments.

Table with fractional data types

The SQL fractional data type is used to store floating point numbers. As a rule, in practice, data of this type characterize various financial indicators. Depending on the accuracy required, you can use one of several types below.

FLOAT (sized) – used to store fractional numbers of specified precision – d;

— DOUBLE (sized) – used to store fractions with binary precision;

— DECIMAL (size, d) – used to store fractional values ​​in the form of strings. It is used in bank calculations, where the accuracy of the fractional part can reach 8 or 10 digits.

The first two types cannot be used in this area. Storing financial indicators in the form of strings can greatly facilitate solving many practical problems. When conducting transactions and solving financial issues, SQL data type conversion is of great importance. Developers should definitely consider the type of information storage and processing methods so that the data remains unchanged at all times.

Integer data

A separate group of numbers, forming one of the main classes, are integers. Integer data types are based on the base type INTEGER with some property extension.

• INT (size) – is responsible for storing integer data in the range [-231;231-1].
• TINYINT (size) – used to store numbers in the range from -128 to 127.
• SMALLINT (size) – characterized by a slightly increased range of stored data from -32768 to 32767.
• MEDIUMINT (size) – used to store numbers with dimensions from -223 to 223-1.
• BIGINT (size) – covers the range of integer values ​​from -263 to 263-1.

If you select correct type data, you can significantly save memory and at the same time reduce the cost of server time when executing the necessary SQL queries. The data types and their range determine the amount of storage required. Developers should be aware that using large ranges for attributes may incur increased memory costs. You should clearly analyze the problems being solved and identify situations in which the approximate range of data is known and the condition for using signed numbers is defined. If the range of arguments used is small, and all numbers are positive, then it would be more correct to use an unsigned type, for which the UNSIGNED attribute is intended.

Time and date data types

The time and date data types are of great interest when learning the basics of SQL. Using the following types can provide additional benefits when developing systems that are directly dependent on timing.

• DATE – the main purpose of this type is to store the date in the “year-month-day” format. Values ​​are usually separated by "-". However, sometimes any symbols except numbers can be used as a delimiter.
• TIME – allows you to enter time values ​​into a table cell. Values ​​are specified in the format “hh:mm:ss”.
• DATE TIME – this type combines the functions of the previous two. The data storage format in this case looks like this: “yyyy-mm-dd: hh:mm:ss”.
• TIME STAMP – this type stores the date and time, calculated by the number of seconds that have passed since midnight 01/01/1970 to the specified value.
• YEAR (M) – used to store annual values ​​in two- or four-digit format.

What else should you know? All types of data presented above were systematized by Microsoft. She also developed SQL data types in more detail. So, for example, the form described how much memory is allocated when using each data type. After studying all the available information, it should become easier for developers to design the structure of the database and tables based on the hardware capabilities of the server.

NULL - special pointer

In some cases, when populating a database, a situation arises when, when adding a record to a table, there is no need to enter information into all columns. To do this, you must use a special empty destination pointer or NULL. It uses the SQL language as an auxiliary tool. Column data types that are optional are specified with an allow-null clause when creating tables. Also, the NULL operator, when using the additional prefix NOT, can be used to indicate that all values ​​must be filled in. A NULL pointer has no type. It simply points to an empty value in the database tables. For this reason, it may well be combined with one of the data types presented above.