Microsoft sql data types. Standard SQL Data Types

All tips

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

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.

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.

SQL Data Types- Date and time

MySQL Data Types

MySQL Data Types are divided into the following types:

Numerical data types
Data types Byte From Before
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

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

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 Database 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 operating system files (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 simply 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

Access data types 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- a unique long integer generated by Access when creating each new record - 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(a standard format for specifying a path including a network file server), or URL (the address of an object, document, page, or other type of object on the Internet or Intranet. The URL defines the protocol for access and the final 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 as xxxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxxxxxx, in which each "x" represents a 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 detection, and scaling. IN PostgreSQL There is also support for additional versions of existing data types, which tend to take up 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 the MAC address value of the computer's network card. 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: missing)
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 I), ... 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 contours, it is enclosed in square brackets.
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 regard to the time zone (using 8 bytes) or taking into account the time zone in which the database server is located (using 12 bytes). Valid range of values: 00:00:00.00 - 23:59:59.99. The smallest value is 1 microsecond. Note that on 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 [(p)] ( 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.

First, let's look at what “data types” are.
Data types determine what values can be stored in a column and how much memory they will take up. When you create a table, you must specify a specific data type for all of its columns.
The main types used in SQL can be divided into several categories:

Integer types;
Fractional types;
Character types;
Money types;
Temporal types(date/time);

Integer data types

Fractional data types

Character data type

Data type	Description
CHAR(size)	Used to store strings. The parameter in brackets allows you to fix the length of the stored string. The maximum size in bytes that can be specified for a string is 255.
VARCHAR (size)	Similar to the previous type, it allows you to store strings up to 255 characters long. However, the difference from CHAR is that the required amount of memory is allocated to store a value of this type. That is, a string consisting of 5 characters will require 6 bytes of memory. In the first case, memory for the value will be allocated according to the specified parameter.
NCHAR(size)	Constant-length string data in Unicode. n specifies the length of the string and must be a value between 1 and 4000. The storage size is twice the value of size in bytes. It is recommended to use nchar if the sizes of the data elements in the columns are expected to be similar.
NVARCHAR (size\|max)	Variable-length string data in Unicode. Size specifies the length of the string and can have a value from 1 to 4000. The max value indicates that the maximum storage size is 2^31-1 characters (2 GB). It is recommended to use nvarchar if the sizes of the data elements in the columns are expected to be different.
TEXT	Variable length non-Unicode data in the server's code page and with a maximum line length of 2^31-1 (2,147,483,647).
NTEXT	Variable length Unicode encoded data with a maximum line length of 2^30 - 1 (1,073,741,823) bytes.

Currency data type

Time types(date/time)

Data type	Description
DATE	The main purpose is to store the date in the format YEAR-MONTH-DAY (“YYYY-MM-DD” or “uuuu-mm-dd”).
TIME	Allows you to enter temporary values into a table cell. All values are specified in the format “hh:mm:ss”.
DATETIME	Combines the functions of the previous two types. The storage format is presented as follows: “uuuu-mm-dd hh:mm:ss”.
TIMESTAMP	Stores the date and time, calculated by the number of seconds that have passed since midnight on January 1, 1970, until the specified value.

Examples of using data types

Let's look at an example of how to use data types in SQL.

An example of using data types

MySQL

CREATE TABLE Checks(id INT NOT NULL, Name VARCHAR (50) NOT NULL, DataToDay DATE NOT NULL, Cost FLOAT NOT NULL);

id — is a counter (it stores the individual check number), therefore it belongs to the integer data type, so we make it INT or INTEGER. NOT NULL — indicates that the variable cannot be empty.
IN Name— We will store the name of the product. Since the column will store characters we use VARCHAR. After specifying the type, we allocate memory for the name (in our case it is (50) ).
The following table columns are created in a similar way.

1.1. Character types

1) Constant length strings
CHAR()– a line of text in a format defined by the developer. Natural number defines strings.
In practice, the maximum number of characters ranges from 256 in MS SQL Server to 32767 in InterBase.
CHAR is treated as CHAR(1)

2) Variable length strings
VARCHAR|CHAR VARYING [()]– a string of text of variable length in a format defined by the developer. The natural number specifies the maximum line length, but the table only provides space for the actual length of the line.

3) Features of character types of a number of DBMSs
In a number of DBMSs, for example, MS SQL Server, if CHAR allows the value NULL, then it is treated as VARCHAR.
In Oracle, for VARCHAR2 fields, you can reserve space in each block for future field updates by defining the PCTFREE option.

1.2. Numeric types

1) Integer data types
INT– a number without a decimal point. The size depends on the specific implementation. Often this is 4 bytes.
SMALLINT– Same as INT, but usually smaller in size. Often 2 bytes.
BIGINT– Same as INT, but usually larger in size. This is 4 or more bytes.

2) Real numbers with fixed point
DEC|)]– fixed-point decimal number.
The number has:
— total number of significant decimal places,
— the maximum number of digits to the right of the decimal point.

3) Real floating point numbers
FLOAT– a floating point number represented in exponential form in base 10. Specifies the maximum precision.
REAL– is the same as FLOAT, but the accuracy depends on the implementation.
DOUBE– is the same as REAL, but the accuracy may be greater in a particular implementation.

1.3. Dates and time types

DATE– date in the format yyyy-mm-dd (ISO), mm/dd/yyyy (ANSI).
TIME– time in hh.mm.ss (ISO), hh:mm am/pm (ANSI) format.
INTERVAL– date and time in yyyy-mm-dd-hh.mm.ss.nnnnn (ISO) format. (often TIMESTAMP).

Note:
Date and time types can be specified as string literals.
Date: 'yyyy-mm-dd', time: 'hh.mm.ss',
Interval: ‘yyyy-mm-dd-hh.mm.ss.n…n’.

1.4. Boolean type

BOOLEAN– logical value (TRUE, FALSE, UNKNOWN).
To correctly understand the truth table in three-valued logic (3VL), we can conventionally assume that FALSE is 0, TRUE -1, and UNKNOWN is 0.5.
Then:
— The AND operator returns the smallest.
— The OR operator is the largest of the original values.
- NOT UNKNOWN = UNKNOWN.

2. Collections

Collections actually violate first normal form (1NF).

2.1. Array

[()] ARRAY– a set of values of the same type.

Note:
Arrays were introduced in SQL:99.
Example:
Thus, the WeekDays Varchar(10) ARRAY definition allows you to store the name of all seven days of the week in one field.
A number of DBMSs even allow multidimensional arrays. So in InterBase up to 16 changes are possible, Clarion – 4.

2.2. Multiset

[()] MULTISET– an unlimited set of values of the same type, allowing duplicates.
Values are created by the constructor - special functions.

Note:
Multisets were introduced in SQL:2003.

2.3. Anonymous string type

ROW ([()] , ...)– a set of different types of values, including nested ones.
Options can specify the sorting order for string type fields and a number of other settings.

Example:
So, by defining Address ROW(State Char(6), City Varchar(30), Street Varchar(50)) allows you to store a detailed address in one field.

3. LOB types

CLOB (Character Large Object)– behave much like character strings, but are not allowed to be used:
— In the restrictions Primary Key, Unique, Foreign Key.
— In comparisons other than pure equalities or inequalities, in the Order By and Group By sections.
BLOB (Binary Large Object)– a stream of bytes in a format in which the user can write them into a database column.

3.1. Problems using LOB

1) Storage problems
Storing LOBs directly in tables along with other data breaks the optimizer's ability to rely on data pages that are sized to match the disk pages.
Therefore, LOBs are stored in separate areas (segments) of disk memory.

2) Update problems
Since the size of LOB objects can reach tens and hundreds of megabytes, it is impossible to store them entirely in buffers. Therefore, LOB data is processed in parts, for example, by groups of pages. INSERT and UPDATE statements use special techniques for piecemeal processing that allow you to call the same API function multiple times on the same field. The same is true when reading data using the SELECT and FETCH statements.

3) Problems with transaction execution
To support transactions, most DBMSs maintain a transaction log, which records copies of the data before and after modifications.
However, due to their large size, LOBs are not logged.

4) Network forwarding problems
Often the client and server are running on different computers, and sending a LOB across the network can disrupt the work of everyone using the network at the time.

4. Different types

4.1. Locator

A unique binary (four-bit) value (in OOP - a descriptor) stored in the database.
Described in the main program and valid until the end of the transaction.
Designed for manipulating LOB values (or arrays) on the client side. Instead of a LOB, a link to it is sent to the client.
You can declare: LOC: Integer AS LOCATOR.

4.2. XML

The values are essentially XML documents.
This type defines a number of operations that provide access to XML type value elements, transformations of similar data, etc.

4.3. Datalink

Datalinks are part of SQL/MED 9075-9:2003.
Datalink is a special type of SQL designed to store URLs in a database, as well as a number of functions that can be used in SQL queries.
Features and supported functions can be found on the website:
Wiki.postgresql.org/wiki/DATALINK

Used in DB2, Oracle - to store data in an external file BFile.

The list below shows that Microsoft SQL Server supports most SQL 2003 data types. SQL Server also supports additional data types used to uniquely identify rows of data in a table and on many servers, such as UNIQUEIDENTIFIER, which is consistent with the "breadth-first" hardware philosophy. professed by Microsoft (that is, implementation of the base on many servers on Intel platforms), instead of “growing in height” (that is, implementation on one huge powerful UNIX server or Windows Data Center Server).

An interesting aside regarding dates on SQL Server: SQL Server supports dates back to 1753. You cannot store earlier dates in any SQL Server database data type. Why? The reason is that the English-speaking world began using the Gregorian calendar in 1753 (until September 1753 the Julian calendar was used), and converting Julian calendar dates to the Gregorian calendar could be quite difficult.

BIGINT (SQL 2003 data type: BIGINT)

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 INT type for IDENTITY property rules that also apply to BIGINT type.

BINARY[(n)](SQL 2003 data type: BLOB)

Stores a fixed-length binary value from 1 to 8000 bytes. A BINARY value takes up n + 4 bytes.

BIT (SQL 2003 data type: 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. Columns of type BIT cannot be indexed.

CHAR[(n)], CHARACTER[(n)] (SQL 2003 data type: 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 turned off.) The type is n bytes.

CURSOR (SQL 2003 data type: 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 (SQL 2003 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) (SQL 2003 data type: 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 (SQL 2003 data type: none) Synonym for FLOAT(53).

FLOAT[(n)] (SQL 2003 data type: FLOAT, FLOAT(n))

Stores floating point values in the range -1.79E + 308 to 1.79E + 308. The precision, determined by the parameter n, can vary from 1 to 53. To store 7 digits (and - from 1 to 24) requires 4 bytes. Values greater than 7 digits occupy 8 bytes.

IMAGE (SQL 2003 data type: 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 Microsoft Word documents and Microsoft Excel spreadsheets. Values of type IMAGE cannot be freely manipulated. IMAGE and TO 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 (SQL 2003 data type: 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/ALTERTABLE Statement section.

MONEY (SQL 2003 data type: missing)

Stores currency values in the range -922337203685477.5808 to 922337203685477.5807. The value takes 8 bytes.

NCHAR(n), NATIONAL CHAR(n), NATIONAL CHARACTER) (SQL 2003 data type: NATIONAL CHARACTER(n))

Stores fixed-length UNICODE data of up to 4000 characters. Storage requires n*2 bytes.

NTEXT, NATIONAL TEXT(SQL 2003 data type: 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.

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) (SQL 2003 data type: 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_PADDING system option for NCHAR and NVARCHAR fields is always ON.

REAL, FLOAT(24)(mun data SQL 2003: 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 (SQL 2003 data type: missing)

A unique number stored in the database that is updated whenever a row is updated, called TIMESTAMP in earlier versions.

SMALLDATETIME (SQL 2003 data type: missing)

Stores date and time in the range from "01-01-1900 00:00" to "06-06-2079 23:59" accurate to the minute. (Minutes are rounded down if the seconds value is 29.998 or less, otherwise they are rounded up.) The value occupies 4 bytes.

SMALLINT (SQL 2003 data type: 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 (SQL 2003 data type: missing)

Stores currency values in the range from 214748.3648 to -214748.3647. The values take 4 bytes.

SQL VARIANT (SQL 2003 data type: none)

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 (SQL 2003 data type: none)

A special type that stores the resulting set of data from 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 (SQL 2003 data type: 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 type values can be manipulated using the DATALENGTH, PATINDEX, SUBSTRING functions. TEXTPTR and TECH-TVALID, as well as the READTEXT, SET TEXTSIZE, UPDATETEXT and WR1TETEXT commands.

TIMESTAMP (SQL 2003 data type: 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.

Stores unsigned integers in the range 0 to 255 and occupies 1 byte. See the description of the /L/G type for rules related to the IDENTITY property that also apply to this type.

UNIQUEIDENTIFIER (SQL 2003 data type: missing)

Represents a value that is unique across all databases and all servers. Represented as xxxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxxxxxx, in which each "x" represents a 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)] (SQL 2003 data type: 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 f(n)J (SQL 2003 data type: CHARACTER VARYING [(n)]

Stores fixed-length character data ranging in size from 1 to 8000 characters. The space taken up is equal to the actual size of the entered value in bytes, not the n value.

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.

Date values in Transact-SQL are defined by default 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. A brief description of the properties of binary and bit data types is 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 on the NTFS file 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 a unique identification number that 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 of different types at the same time, 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 TIMESTAMP cells, you can determine the relative time of the last modification of the 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 on the NTFS file system. 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 to work with this data you can use statements to modify the data (SELECT, INSERT, UPDATE and DELETE);

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 would be an alternative way to store and access values in all sparse columns of a table.

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 that 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 (other than TIMESTAMP) is not explicitly specified as NULL or NOT NULL, 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.)