Reviews 

sql table types. SQL Server data types. Date and Time Data Types

The data type determines what value the column can contain: integer data, character data, money data, date and time data, binary strings, and so on.

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 the table is created. 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 basic data types in MySQL: text, number, and date.

Text data types

Type of Description
CHAR(size) Holds a fixed length string (may contain letters, numbers, and Special symbols). The fixed size is given in brackets. Can store up to 255 characters
VARCHAR(size) Holds a variable length string (may contain letters, numbers, and special characters). The maximum size is given in brackets. Can store up to 255 characters. If you set 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
MEDIUM TEXT 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
LONG TEXT 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 me introduce a list possible values. You can list up to 65535 values ​​in the ENUM list. If a value is inserted that is not in the list, an empty value will be added.
Note. The values ​​are sorted in the order you enter them.

You enter possible values ​​in this format: ENUM ("a", "b", "c")
SET Like ENUM, except that SET can contain up to 64 list items and can store multiple choices

Numeric Data Types

Type of 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 fine. 0 to 65535 UNSIGNED*. The maximum number of digits can be specified in brackets
MEDIUMINT(size) -8388608 to 8388607 normal. 0 to 16777215 UNSIGNED*. The maximum number of digits can be specified in brackets
INT(size) -2147483648 to 2147483647 normal. 0 to 4294967295 UNSIGNED*. The maximum number of digits can be specified in brackets
BIGINT(size) -9223372036854775808 to 9223372036854775807 normal. 0 to 18446744073709551615 UNSIGNED *. The maximum number of digits can be specified in brackets
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 the d parameter
DOUBLE(size,d) Large 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 the d parameter
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 the d parameter

UNSIGNED - additional parameter integer type. Usually 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.

Today in the world there are great amount technologies and means intended for information storage. Databases are one of the most common means. To work with them, various systems management. This method of storage assumes that all information is clearly structured and entered in 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 at once that explain the essence of the concept of "data type". Each of them has one common meaning. By convention, a data type can be defined as a group of data characterized by its values—numeric, character, and so on—as well as the operations that can be applied to the values ​​in question. Scope of use various types data is very versatile. Data can be used not only to store information, but also in programming to solve problems. When developing programs, the practice of using your own data types with a certain set of operations has become widespread. 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 appeared a long time ago. It was caused by the need to structure all the volumes of information to ensure the convenience of their processing. Today, all existing data types are based on two basic ones: character and numeric. Based on these data, a modern classification, which includes pointers, integer, boolean, floating point, and string information types. All of the above is fully covered by the SQL classification. However, for modern systems database management there are special add-ons. These include MySQL and Oracle.

Basic data types

Data types that are used when creating standards-compliant table attributes SQL language, 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 represented as characters. In this case, we are talking about special characters and letters, which together will be processed in any SQL query like strings. Below are the data types that form the first group:

CHAR (size) given type 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 the maximum size in bytes - 255.

- VAR CHAR (size) - by analogy with 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 the required amount of memory is allocated here 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, the memory for storing the value will be allocated according to the specified parameter.

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

- BLOB - this type is similar to the TEXT type. It allows you to save textual information in the database, the volume of which can be up to 65,535 characters. However, in practice, this type can be used to store pictures, sound information, electronic documents and others.

- MEDIUM TEXT - this type was developed on the basis of the TEXT type. Due to the size increased to 16777215 characters, this type allows you to store more data.

- MEDIU MBLOB - used to store electronic documents in the database, the size of which does not exceed 16777215 characters.

- LONG TEXT - by functionality similar to the 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 define a list of different values. This type allows you to specify 65535 values. Strings of this type can take only one of all the values ​​specified in the set. In the case when the addition of values ​​that are not in the specified list will be carried out, empty values ​​will be written to the table.

- SET - this data type defines a set allowed values. Unlike the previous type, it is used to contain 64 parameters that can be initialized with multiple elements of the given arguments.

Table with fractional data types

fractional type SQL data used to store floating point numbers. As a rule, in practice, data of this type characterize various financial indicators. Depending on the required accuracy, one of the following types can be used.

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 ​​as strings. It is used in bank calculations, where the accuracy of the fractional part can reach 8 or 10 digits.

The first two types in this area cannot be used. Storing financial indicators in the form of strings can greatly facilitate the solution of many practical problems. When conducting operations and deciding financial matters SQL data type conversion is of great importance. Developers must take into account the type of information storage and processing methods so that the data remains unchanged at all times.

Integer type 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) - responsible for storing integer data in the range [-231;231-1].
  • TINYINT (size) - used to store numbers in the range -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 choose the right data type, you can save a lot of memory and at the same time reduce server time when executing the necessary SQL queries. The data types and their range determine the amount of memory required for storage. Developers need to be aware that using large attribute ranges can increase memory costs. You should clearly analyze the tasks to be solved and identify situations in which the approximate range of data is known and the condition for using signed numbers is defined. If there is a small range of used arguments, and all numbers will be positive, then it will be more correct to use the 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. The use of the following types can provide additional benefits in the case of developing systems whose operation is directly dependent on time indicators.

  • 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 character, with the exception of numbers, can be used as a separator.
  • TIME - allows you to enter temporary values ​​in a table cell. Values ​​are specified in the "hh:mm:ss" format.
  • DATE TIME - this type combines the functions of the two previous ones. 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 as the number of seconds that have passed since midnight on 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 data types presented above have been 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 is a special pointer

In some cases, when filling the database, a situation arises when, when adding a record to the table, there is no need to enter information in all columns. To do this, you must use a special null assignment pointer or NULL. It uses the SQL language as an aid. The data types of the columns, which do not have to be populated, are specified with an operator to allow the inclusion of null values ​​when tables are created. Also, the NULL operator, when using the optional NOT prefix, can be used to indicate that all values ​​must be filled. The 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 above data types.

In the modern world, there are a large number of means and technologies intended for information storage. One of the most common methods is databases, for which various management systems are used. This method storage assumes that all data is clearly structured and entered in special tables. They, in turn, consist of attribute columns of a particular data type.

Data type - what is it?

Today, there are several definitions that explain the concept of the term "data type". However, each of them has one common meaning. Therefore, it can be conditionally designated as a group of data, characterized by its values ​​(symbolic, numeric, etc.), as well as operations applied to the values ​​in question.

The scope of data types is multifaceted. They are used not only to store information, but also in programming to solve various problems. When designing programs, it is a common practice to develop and use your own data types with a specific set of operations. However, custom data types are always based on basic data types. The SQL standard is also based on the use of the most common basic types, but with a number of specific additions.

Classification of data types

Grouping data according to their type arose a long time ago and was caused by the need for more convenient processing. Currently, the basis of existing data types is formed by two: character and numeric.

Based on them, a modern classification was developed, which includes pointers, logical, integer, floating point and string data types. SQL - the classification fully covers all of the above. However, for some modern DBMS, there are additional add-ons. These include Oracle and MySQL.

Basic data types

Used when creating table attributes that meet the language standards are divided into 4 classes:

  • string values;
  • fractions;
  • integer values;
  • date and time values.

String data type

The first group of values ​​allows you to store any data represented as characters.

These can be special characters, numbers and letters, which together will be treated as strings in any Data types, the table listing which is presented below, form the first group.

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 set for a string is 255.

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, for a string consisting of 5 characters, 6 bytes of memory are required. In the first case, the memory for the value will be allocated according to the specified parameter.

Used to store string data up to 255 characters.

Used to store textual information, the size of which does not exceed 65,535 characters.

The considered data type is similar to the TEXT type and allows storing textual information in the database, the volume of which can reach 65,535 characters. But in practice it is used to store sound data, drawings, electronic documentation, etc.

It was developed on the basis of the TEXT type, but allows you to store more data due to the increased size of up to 16,777,215 letters or symbols.

Used to save electronic documents in the database, the size of which does not exceed 16,777,215 characters.

Functionally similar to the previous types, but with increased memory capacity up to 4 gigabytes.

Allows you to put large volumes of data (4,294,967,295 characters) into the database.

ENUM (a, b, c, etc.)

A special data type used to specify a list of possible values. Allows you to specify 65535 values. Strings of the considered type can take on a single value from those specified in the set. In the case when there will be an addition of values ​​that are not present in the specified list, empty values ​​will be written to the table.

Specifies a set of valid values. Unlike the previous type, it is used to contain 64 parameters that can be initialized with any or more elements from the given arguments.

Fractional data type table

Fractional SQL data types are used to store floating point numbers. In practice, as a rule, various financial indicators are set. Depending on the required accuracy, one of the three presented is used:

For example, in banking calculations, the accuracy of the fractional part reaches a value of 8 or 10 digits. The first two types cannot be used in this area.

Storing financial figures as strings greatly simplifies many tasks. However, when dealing with financial issues or performing various SQL operations, data type conversion is of great importance. Developers must be sure to consider the type of storage and processing methods so that the data always remains unchanged.

integer data type

Whole numbers - separate group numbers, forming one of the main classes. Integer SQL data types are based on the use of the base one with some extension of its properties.

By choosing the right data type, you can save a lot of memory and server time while running the required SQL queries. Data types, or rather their range, determine the amount of storage space required.

Therefore, it is important for developers to keep in mind that using large attribute ranges incurs an increase in memory overhead. It is necessary to clearly analyze the tasks to be solved and identify cases where the approximate range 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 will be more correct to use the unsigned type formed by the UNSIGNED attribute.

Date and Time Data Types

When learning the basics of SQL, date and time data types are of particular interest.

The use of the following types provides additional advantages when developing systems that depend on time indicators.

The main purpose is to store the date in the format YEAR-MONTH-DAY (“YYYY-MM-DD” or “yyyy-mm-dd”). Normally, values ​​are separated by "-", however, any character other than numbers can be used as a separator.

Allows you to enter temporary values ​​in a table cell. All values ​​are given in the format "hh:mm:ss"

Combines the functions of the previous two types. The storage format is represented as follows: "yyyy-mm-dd hh:mm:ss".

Stores the date and time as the number of seconds elapsed since midnight on 01/01/1970 to the specified value.

Used to store annual values ​​in two or four digit format.

What else do you need to know?

All these types of data are systematized by Microsoft in detail. SQL data types are developed by her in more detail.

For example, the company details how much memory in bytes is allocated when using each type. Having studied the available information, it is easier for developers to design the structure of tables and the entire database based on the hardware capabilities of the server.

Special pointer - NULL

Sometimes, when filling the database, a situation arises when, when adding a record to a table, there is no need to enter information in all columns. For this, a special null pointer is used - NULL, which uses the SQL language as an aid. The data types of columns that do not have to be filled in are specified with an operator when creating tables to allow the inclusion of null values. Otherwise, the operator NULL with additional accessory NOT can be used to indicate that all values ​​must be filled in.

Pointer NULL has no type, but simply points to an empty value in database tables. Therefore, it can be combined with any of the above types.

Last update: 07/12/2017

When you create a table, you must specify a specific data type for all of its columns. The data type determines what values ​​can be stored in the column, how much they will take up memory space.

The T-SQL language provides many different types. Depending on the nature of the values, all of them can be divided into groups.

Numeric Data Types

    BIT : stores the value 0 or 1. In fact, it is an analogue of the boolean type in programming languages. It takes 1 byte.

    TINYINT : stores numbers from 0 to 255. Occupies 1 byte. Good for storing small numbers.

    SMALLINT : stores numbers from -32768 to 32767. Occupies 2 bytes

    INT : stores numbers from -2147483648 to 2147483647. Occupies 4 bytes. The most used type for storing numbers.

    BIGINT : Stores very large numbers from -9 223 372 036 854 775 808 to 9 223 372 036 854 775 807 that take up 8 bytes of memory.

    DECIMAL : stores fixed precision numbers. It takes from 5 to 17 bytes, depending on the number of numbers after the decimal point.

    This type can take two parameters precision and scale: DECIMAL(precision, scale) .

    The precision parameter represents the maximum number of digits that the number can store. This value must be between 1 and 38. The default is 18.

    The scale parameter represents the maximum number of digits that a number after the decimal point can contain. This value must be between 0 and the value of the precision parameter. It defaults to 0.

    NUMERIC : This type is similar to the DECIMAL type.

    SMALLMONEY : stores fractional values ​​from -214 748.3648 to 214 748.3647. Designed to store money. Takes 4 bytes. Equivalent to DECIMAL(10,4) .

    MONEY : Stores fractional values ​​from -922337203685477.5808 to 922337203685477.5807. Represents monetary values ​​and occupies 8 bytes. Equivalent to DECIMAL(19,4) .

    FLOAT : stores numbers from -1.79E+308 to 1.79E+308. It takes from 4 to 8 bytes depending on the fractional part.

    May be defined as FLOAT(n) , where n represents the number of bits that are used to store the decimal part of the number (the mantissa). By default n = 53.

    REAL : stores numbers from -340E+38 to 3.40E+38. Takes 4 bytes. Equivalent to FLOAT(24) .

Numeric column examples: Salary MONEY, TotalWeight DECIMAL(9,2), Age INT, Surplus FLOAT

Data Types Representing Date and Time

    DATE : Stores dates from 0001-01-01 (January 1, 0001) to 9999-12-31 (December 31, 9999). Takes 3 bytes.

    TIME : stores the time in the range from 00:00:00.0000000 to 23:59:59.9999999. It takes from 3 to 5 bytes.

    May be of the form TIME(n) , where n represents the number of digits from 0 to 7 in fractional seconds.

    DATETIME : stores dates and times from 01/01/1753 to 12/31/9999. Occupies 8 bytes.

    DATETIME2 : Stores dates and times ranging from 01/01/0001 00:00:00.0000000 to 12/31/9999 23:59:59.9999999. It takes from 6 to 8 bytes depending on the time precision.

    May be of the form DATETIME2(n) , where n represents the number of digits from 0 to 7 in fractional seconds.

    SMALLDATETIME : stores dates and times between 01/01/1900 and 06/06/2079, which are the nearest dates. Takes 4 bytes.

    DATETIMEOFFSET : Stores dates and times in the range 0001-01-01 to 9999-12-31. Stores detailed time information accurate to 100 nanoseconds. Takes 10 bytes.

Common date formats:

    yyyy-mm-dd- 2017-07-12

    dd/mm/yyyy - 12/07/2017

    mm-dd-yy - 07-12-17

    In this format, two-digit numbers from 00 to 49 are treated as dates in the range 2000-2049. And the numbers from 50 to 90 as a range of numbers 1950 - 1999.

    Month dd, yyyy - July 12, 2017

Common time formats:

  • hh:mi am/pm - 1:21 pm

    hh:mi:ss - 1:21:34

    hh:mi:ss:mmm - 1:21:34:12

    hh:mi:ss:nnnnnnn - 1:21:34:1234567

String data types

    CHAR : stores a string between 1 and 8,000 characters long. Allocates 1 byte for each character. Not suitable for many languages ​​because it stores non-Unicode characters.

    The number of characters a column can store is passed in parentheses. For example, for a CHAR(10) column, 10 bytes will be allocated. And if we store a string of less than 10 characters in a column, then it will be padded with spaces.

    VARCHAR : stores a string. 1 byte is allocated for each character. You can specify a specific length for a column, from 1 to 8,000 characters, such as VARCHAR(10) . If the string must have more than 8000 characters, then the MAX size is set, and up to 2 GB can be allocated for storing the string: VARCHAR(MAX) .

    Not suitable for many languages ​​because it stores non-Unicode characters.

    Unlike the CHAR type, if a 5-character string is stored in a VARCHAR(10) column, exactly five characters will be stored in the column.

    NCHAR : Stores a Unicode string between 1 and 4,000 characters. 2 bytes are allocated for each character. For example, NCHAR(15)

    NVARCHAR : Stores a Unicode string. 2 bytes are allocated for each character. You can set a specific size from 1 to 4,000 characters: . If the string must have more than 4000 characters, then the MAX size is set, and up to 2 GB can be allocated for storing the string.

The other two types, TEXT and NTEXT, are deprecated and therefore not recommended. Instead, VARCHAR and NVARCHAR are used, respectively.

Examples of defining string columns:

Email VARCHAR(30), Comment NVARCHAR(MAX)

Binary data types

    BINARY : stores binary data as a sequence of 1 to 8,000 bytes.

    VARBINARY : Stores binary data as a sequence of 1 to 8000 bytes, or up to 2^31-1 bytes when using the MAX value (VARBINARY(MAX)).

Another binary type, the IMAGE type, is deprecated and it is recommended to use the VARBINARY type instead.

Other data types

    UNIQUEIDENTIFIER : a unique GUID (essentially a string with a unique value) that takes up 16 bytes.

    TIMESTAMP : some number that stores the version number of the row in the table. Occupies 8 bytes.

    CURSOR : represents a set of rows.

    HIERARCHYID : represents a position in a hierarchy.

    SQL_VARIANT : Can store data of any other T-SQL data type.

    XML : Stores XML documents or fragments of XML documents. Takes up to 2 GB of memory.

    TABLE : represents the definition of a table.

    GEOGRAPHY : stores geographic data such as latitude and longitude.

    GEOMETRY : stores coordinates of a location on a plane.

DEFINITION OF THE DATA STRUCTURE

Questions:

1. SQL data types defined by the standard.. 1

2. Data types used in SQL Server. 3

3. Expressions and variables. 5

4. SQL Control Structures.. 6

5. Basic objects of the SQL server database structure. 7

SQL data types defined by the standard

Data is a collection of information stored in a database as one of several different types. Data types set the basic rules for the data contained in a particular table column, including the amount of memory allocated to them.

The SQL language has six scalar data types defined by the standard. Them short description presented in the table.

Character data

Character data consists of a sequence of characters included in the character set defined by the creators of the DBMS. Since character sets are specific to different dialects of the SQL language, the list of characters that can be part of data values ​​of a character type is also implementation dependent. The most commonly used character sets are ASCII and EBCDIC. The following format is used to define character type data:

<символьный_тип>::=

( CHARACTER [ VARYING][length] | [length])

When defining a column with a character data type, the length parameter is used to specify the maximum number of characters that can be placed in this column (the default value is 1). A character string can be defined as having a fixed or variable (VARYING) length. If a string is defined with a fixed length of values, then when fewer characters are entered, the value is padded to the specified length with spaces added to the right. If a string is defined with a variable length of values, then if you enter fewer characters into it, only the characters entered will be stored in the database, which will allow you to achieve some savings external memory.

bit data

bit type data is used to define bit strings, i.e. sequences of binary digits (bits), each of which can have a value of either 0 or 1 . Bit type data is defined using the following format:

<битовый_тип>::=

BIT [length]

Exact numbers

Precise numeric data type used to determine numbers that have an exact representation, i.e. numbers consist of digits, an optional decimal point, and an optional sign character. Precise numeric type data is determined by the precision and length of the fractional part. The precision specifies the total number of significant decimal digits of a number, which includes the length of both the integer part and the fractional part, but excluding the decimal point itself. The scale indicates the number of fractional decimal places in the number.

<фиксированный_тип>::=

(NUMERIC[precision[,scale]]|(DECIMAL|DEC)

[precision[, scale]]

| (INTEGER|INT)| SMALLINT)

Types NUMERIC and DECIMAL designed to store numbers in decimal format. The default length of the fractional part is zero, and the default precision is implementation dependent. The INTEGER (INT) type is used to store large positive or negative integers. The SMALLINT type is for storing small positive or negative integers; in this case, the consumption of external memory is significantly reduced.

Rounded numbers

Type of rounded numbers used to describe data that cannot be exactly represented by a computer, such as real numbers. Rounded or floating point numbers are represented in scientific notation, in which the number is written using the mantissa multiplied by a certain power of ten (exponent), for example: 10E3, +5.2E6, -0.2E-4 . To define data of a real type, the following format is used:

<вещественный_тип>::=

( FLOAT [precision]| REAL |

DOUBLE PRECISION)

Parameter accuracy specifies the number of significant digits of the mantissa. The precision of the REAL and DOUBLE PRECISION types is implementation specific.

date and time

Data type "date/time" is used to determine points in time with some specified accuracy. The SQL standard supports the following format:

<тип_даты/времени>::=

(DATE | TIME[precision]|

TIMESTAMP[precision])

The DATE data type is used to store calendar dates, which include the fields YEAR (year), MONTH (month), and DAY (day). The TIME data type is for storing timestamps that include the HOUR (hours), MINUTE (minutes), and SECOND (seconds) fields. The TIMESTAMP data type is for storing date and time together. The precision parameter specifies the number of fractional decimal places that determines the precision of storing the value in the SECOND field. If this parameter is omitted, it defaults to zero for TIME columns (i.e., whole seconds are stored), while it defaults to 6 for TIMESTAMP fields. keyword WITH TIME ZONE defines the use of the TIMEZONE HOUR and TIMEZONE MINUTE fields, thereby specifying the hour and minutes of the zone time offset relative to Coordinated Universal Time (GMT).

Data of type INTERVAL is used to represent periods of time.

The concept of a domain

Domain is a set of valid values ​​for one or more attributes. If a database table or several tables have columns that have the same characteristics, you can describe the type of such a column and its behavior through a domain, and then map each of the identical columns to a domain name. The domain defines all potential values ​​that can be assigned to an attribute.

The SQL standard allows you to define a domain with the following statement:

<определение_домена>::=

CREATE DOMAIN domain_name

datatype

[DEFAULT value]

[ CHECK(valid_values)]

Each domain that you create is given a name, a data type, a default value, and a set of valid values. It should be noted that the above operator format is incomplete. Now, when creating a table, you can specify a domain name instead of a data type.

Removing domains from the database is done using the operator:

DROP DOMAIN domainname [ RESTRICT |

If you specify the CASCADE keyword, any table columns created using the domain being dropped will be automatically modified and described as containing data of the type specified in the definition of the domain being dropped.

An alternative to domains in the environment SQL Server are user-defined data types.

Data Types Used in SQL Server

System data types

One of the highlights of the table creation process is defining the data types for its fields. The data type of a table field determines the type of information that will be placed in this field. The concept of a data type in SQL Server is fully adequate to the concept of a data type in modern programming languages. SQL Server supports a large number of different data types: text, numeric, binary (see table).

Let's bring short review SQL Server data types.

used to store character information. character data types, which include CHAR (length), VARCHAR (length), NCHAR (length), NVARCHAR (length). The last two are for storing Unicode characters. The maximum length value is limited to 8000 characters (4000 for Unicode characters).

Storage of large character data (up to 2 GB) is carried out using the text data types TEXT and NTEXT.

To integer data types include INT (INTEGER), SMALLINT, TINYINT, BIGINT. To store data of an integer type, 4 bytes (range from -231 to 231-1), 2 bytes (range from -215 to 215-1), 1 byte (range from 0 to 255) or 8 bytes (range from -263) are used, respectively. to 263-1). Objects and expressions of integer type can be used in any mathematical operations.

Numbers that include a decimal point are called non-integer numbers. Non-integer data divided into two types - decimal and approximate.

To decimal data types include DECIMAL [(precision[,scale])] or DEC and NUMERIC [(precision[,scale])]. The DECIMAL and NUMERIC data types allow you to define the floating-point precision format yourself. The precision parameter specifies the maximum number of digits of this type of input (before and after the decimal point in the sum), and the scale parameter specifies the maximum number of digits after the decimal point. In normal mode, the server allows you to enter a maximum of 28 digits used in the DECIMAL and NUMERIC types (from 2 to 17 bytes).

To approximate data types include FLOAT (15 digit precision, 8 bytes) and REAL (7 digit precision, 4 bytes). These types represent data in floating point format, i.e. numbers are represented using the mantissa and exponent, which ensures the same precision of calculations no matter how small or large the value is.

To store information about date and time data types such as DATETIME and SMALLDATETIME are intended, which use 8 and 4 bytes to represent dates and times, respectively.

MONEY and SMALLMONEY data types make it possible to store money-type information; they provide precision to 4 decimal places and use 8 and 4 bytes, respectively.

BIT data type allows you to store one bit, which takes the value 0 or 1.

The SQL Server environment implements a number of special data types.

Data type TIMESTAMP used as an indicator of a row version change within the database.

Data type UNIQUEIDENTIFIER used to store globally unique identification numbers.

Data type SYSNAME is for object identifiers.

Data type SQL_VARIANT allows you to store values ​​of any of the data types supported by SQL Server except TEXT, NTEXT, IMAGE, and TIMESTAMP.

Data type TABLE, like temporary tables, provides storage for a set of rows intended for further processing. The TABLE data type can only be used to define local variables and return values ​​from user-defined functions.

Data type CURSOR needed to work with objects such as cursors, and can only be required for variables and parameters of stored procedures. SQL Cursors Server is a mechanism for exchanging data between a server and a client. The cursor allows client applications to operate on only one or more rows rather than the full set of data.