All values in a column must be of the same data type. (The only exceptions to this rule are values of the SQL_VARIANT data type.) The data types used in Transact-SQL can be categorized as follows:
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 short descriptions are shown in the table below:
| Data type | Description |
|---|---|
| INTEGER | Represents 4-byte integer values in the range -2 32 to 2 32 - 1. INT is the shorthand for 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 integer values in the range -2 63 to 2 63 - 1 |
| DECIMAL (p, [s]) | Represents fixed point values. The p (precision) argument specifies the total number of digits, and the s (scale) argument specifies the number of digits to the right of the expected decimal point. Depending on the value of the p argument, decimal values are stored in 5 to 17 bytes. DEC is an abbreviated form for DECIMAL. |
| NUMERIC (p, [s]) | Synonym for DECIMAL. |
| REAL | Used to represent floating point values. The range of positive values is approximately 2.23E -308 to -1.18E -38. A null value can also be provided. |
| FLOAT [(p)] | Like REAL, it represents floating point values [(p)]. The p argument specifies the precision. For p< 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 MONEY, but 4 bytes long |
Character data types
There are two general kinds of character data types. Strings can be represented by 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 a brief description.
| Data type | Description |
|---|---|
| CHAR [(n)] | Used to represent fixed-length strings of n single-byte characters. The maximum value for 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 of Unicode characters. The main difference between the CHAR and NCHAR data types is that each character of an NCHAR string takes 2 bytes, and a CHAR string takes 1 byte. Therefore, a string of data type NCHAR can contain at most 4000 characters. The NCHAR type can be used to store, for example, the characters of the Russian alphabet, since single-byte encodings do not allow this. |
| NVARCHAR [(n)] | Used to store variable length strings of Unicode characters. An NVARCHAR string requires 2 bytes to store each character, so an NVARCHAR string can contain at most 4000 characters. |
The VARCHAR data type is identical to the CHAR data type, with one difference: if the contents of the 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
The following temporary data types are supported in Transact-SQL:
Data types DATETIME and SMALLDATETIME are used to store the date and time as integer values of 4 and 2 bytes, respectively. Values of type DATETIME and SMALLDATETIME are stored internally as two separate numeric values. The date portion of DATETIME values is stored in the range 01/01/1753 to 31/12/9999, and the corresponding portion of SMALLDATETIME values is in the range 01/01/1900 to 06/06/2079. The time component is stored in a second 4-byte (2-byte for SMALLDATETIME values) field as three hundredths of a second (for DATETIME) or minutes (for SMALLDATETIME) since midnight.
If you only want to store the date or time portion, using a DATETIME or SMALLDATETIME value is somewhat inconvenient. For this reason, the data types were introduced in SQL Server DATE and TIME that store only the date and time components of DATETIME values, respectively. DATE values are 3 bytes long, representing the date range from 01/01/0001 to 31/12/9999. TIME values are 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 with an accuracy of 100 ns. This data type does not support daylight saving time.
All of the 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 are 6 to 8 bytes long. All other properties of this data type are the same as the corresponding properties of the DATETIME2 data type.
By default, Transact-SQL date values are defined as strings in 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 constituents can be changed using the instruction SET DATEFORMAT... In addition, the system supports numeric values for the month component and separators / and -.) Similarly, the time value is indicated in a 24-hour format in the form "hh: mm" (for example, "22:24").
Transact-SQL supports various input formats for DATETIME values. As mentioned, each constituent 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 internal format of the system 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 that 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:
Large Object Data Type
LOB (Large OBject) data type used to store data objects up to 2 GB in size. Such objects are typically used for storing large amounts of text data and for loading plug-ins and audio and video files. The following LOB data types are supported in Transact-SQL:
Starting in SQL Server 2005, the same programming model applies to both standard data type values and LOB data type values. In other words, convenient system functions and string operators can be used to work with LOB objects.
In the Database Engine parameter MAX used with VARCHAR, NVARCHAR, and VARBINARY data types to define values for variable-length columns. When you use the default MAX length instead of explicitly specifying the length of a value, the system analyzes the length of a specific string and decides whether to store that string as a regular value or as a LOB value. The MAX parameter specifies that the column values can be as large as the maximum LOB size for this system.
Although the system decides how the LOBs 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 1, then the data in the columns declared using the MAX parameter will be stored separately from the rest of the data. If the argument is 0, the Database Engine stores all values up to 8060 bytes in the table row as normal data, and larger values are stored off-row in the LOB storage area.
Starting in SQL Server 2008, VARBINARY (MAX) columns can use 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 is limited only by the size of the file system volume.
UNIQUEIDENTIFIER data type
As the name suggests, the UNIQUEIDENTIFIER data type is an unambiguous identification number that is stored as a 16-byte binary string. This data type is closely related to the identifier GUID (Globally Unique Identifier), which guarantees unambiguity on a global scale. Thus, this data type allows the unique identification of data and objects in distributed systems.
You can initialize a UNIQUEIDENTIFIER column or variable using the NEWID or NEWSEQUENTIALID function, or using a custom formatted 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 the query ROWGUIDCOL keyword to indicate that the column contains identity 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.
SQL_VARIANT data type
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 value in a SQL_VARIANT column has two parts: the value itself and the information describing the value. This information contains all the properties of the actual data type of the value, such as length, scale, and precision.
The SQL_VARIANT_PROPERTY function is used to access and display information about the values of a column of type SQL_VARIANT.
You should only declare a column type as SQL_VARIANT if you really need to. For example, if a column is intended to store values of different data types, or if, when creating a table, the type of data that will be stored in this column is unknown.
HIERARCHYID data type
The HIERARCHYID data type is used to store the complete hierarchy. For example, this type of value can store the hierarchy of all employees or the hierarchy of folders. This type is implemented as a CLR user-defined type that encompasses 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 VARBINARY (8) or BINARY (8), depending on the column property, to be null. For each database, the system maintains a counter that increments whenever any row containing a TIMESTAMP cell is inserted or updated and assigns that cell to that cell. Thus, using cells of type TIMESTAMP, you can determine the relative time of the last modification of the corresponding rows of the table. ( ROWVERSION is synonymous with TIMESTAMP.)
By itself, the value stored in a TIMESTAMP column is not important. This column is commonly used to determine if a specific row in a table has changed since the last time it was accessed.
Storage options
Starting with SQL Server 2008, there are two different storage options, each of which can save LOBs and save disk space. These are the following options:
storage of FILESTREAM data;
storage using sparse columns.
These storage options are discussed in the following subsections.
Storing FILESTREAM Data
As mentioned earlier, SQL Server supports storage of large objects (LOBs) through the VARBINARY (MAX) data type. A property of this data type is that binary large objects (BLOBs) are stored in the database. This circumstance can 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 LOBs that are stored in 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 data modification statements (SELECT, INSERT, UPDATE, and DELETE) can be used to manipulate the data.
the database management system provides the same level of security for FILESTREAM data as it does for data stored within a database.
Sparse columns
The purpose of the storage option provided by sparse columns is significantly different from the storage purpose of the FILESTREAM type. While the purpose of FILESTREAM storage is to store LOBs outside the database, the purpose of sparse columns is to minimize the disk space that the database takes up.
Columns of this type allow you to optimize the storage of columns with most of their values being null. When using sparse columns, you do not need disk space to store null values, 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 when you expect at least 20% of the total disk space savings.
Sparse columns are defined in the same way as other columns in a table; the address to them is carried out in a similar way. This means that you can use SELECT, INSERT, UPDATE, and DELETE statements to access sparse columns in the same way as you would use ordinary columns. The only difference is about creating sparse columns: to define a specific column, sparse is used SPARSE argument after the column name, as shown in this example:
column_name datatype SPARSE
Multiple sparse columns in a table can be grouped into a set of columns. Such a set would be an alternative way to store and access values in all sparse columns of the table.
NULL value
The null value is a special value that can be assigned to a table cell. This value is usually used when the information in a cell is unknown or not applicable. For example, if you do not know the home phone number of a company employee, we recommend that you set 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 of the operands A and B is null, then the result of the operation of addition, subtraction, multiplication, division and division modulo these operands will also be null.
If the expression contains a comparison operator and the value of one or both operands of this operation is null, the result of this operation will also be null.
The null value must be different from all other values. For numeric data types, the value 0 and null are not the same. The same is true for the empty string and null for character data types.
Null values can only be stored in a table column if explicitly allowed in the column definition. On the other hand, null values are not allowed on a column if NOT NULL is explicitly specified in its definition. If NULL or NOT NULL is explicitly specified for a column with a data type (with the exception of TIMESTAMP type), then the following values are assigned:
NULL if parameter value ANSI_NULL_DFLT_ON SET statement is on.
NOT NULL if the value of 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.)
Today in the world there are a huge number of technologies and means for storing information. Databases are one of the most common tools. Various control systems are used to work with them. This storage method assumes that all information is clearly structured and entered into special tables. Tables, in turn, consist of attribute columns of a certain type of information.
What is a datatype?
Today, there are several definitions that explain the essence of the concept of "data type". Each of them has one general meaning. Conventionally, a data type can be designated as a data group characterized by its values - numeric, symbolic, and so on - as well as operations that can be applied to the values in question. The scope of use of various types of data is very multifaceted. Data can be used not only for storing information, but also in programming to solve assigned tasks. When developing programs, the practice of using your own data types with a specific set of operations has become widespread. User information is always based on basic data types. The SQL standard is based on the use of the most common types of information, but with a number of specific additions.
Data types: classification
Grouping data by type has appeared a long time ago. It was caused by the need to structure all 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 this data, a modern classification was developed, which includes pointers, integer, boolean, numeric floating point and string information type. All of the above is fully covered by the SQL classification. However, there are special add-ons for modern database management systems. These include MySQL and Oracle.
Basic data types
The data types that are used to create table attributes that meet the SQL language standards can be divided into 4 classes: string values, fractions, integer values, time and date values.
String data type
The first type of data allows you to store any information presented in the form of symbols. In this case, we are talking about special characters and letters, which together will be treated as strings in any SQL query. Below are the data types that make up the first group:
- CHAR (size) - this type is used to store strings. The parameter specified in brackets allows you to fix the length of the stored string. For a string, you can set the maximum size in bytes - 255.
- VAR CHAR (size) - by analogy with the previous type, this type allows storing strings, the length of which does not exceed 255 characters. The main difference of this type from CHAR is that here the required amount of memory is allocated to store the 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 in accordance with the specified parameter.
- TINY TEXT - this type is used to store text information, the size of which does not exceed 65535 characters.
- BLOB - this type is similar to the TEXT type. It allows storing 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 more.
- MEDIUM TEXT - this type was developed based on the TEXT type. Due to the increased size of 16777,215 characters, this type allows you to store more data.
- MEDIU MBLOB - used for storing electronic documents in the database, the size of which does not exceed 16,777,215 characters.
- LONG TEXT - similar in functionality to the previous data types, however, it has a memory capacity increased to 4 GB.
- LONG BLOB - allows you to place large data in the database - 4294967295 characters.
- ENUM is a special data type that is used to specify a list of distinct 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 specifies a set of valid values. Unlike the previous type, it is used to hold 64-parameters that can be initialized with multiple elements of the given arguments.
Table with fractional data types
The fractional SQL data type is used to store floating point numbers. As a rule, in practice, this type of data characterizes different financial indicators. Depending on the required accuracy, you can use one of several types presented below.
FLOAT (sized) - used to store fractional numbers of specified precision - d;
- DOUBLE (sized) - used to store fractions with binary precision;
- DECIMAL (size, d) - used to store fractional values as strings. It is used in banking calculations, where the precision of the fractional part can reach 8 or 10 digits.
The first two types cannot be used in this area. Storing financial indicators in the form of strings can greatly facilitate the solution of many practical problems. When conducting transactions and solving financial issues, 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 constantly remains unchanged.
Integer data
Integers are a separate group of numbers that form one of the main classes. Integer data types are based on the basic INTEGER type with some property extensions.
- INT (size) - is 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) - is characterized by a slightly increased range of stored data from -32768 to 32767.
- MEDIUMINT (size) - used to store numbers from -223 to 223-1.
- BIGINT (size) - Covers the range of integer values from -263 to 263-1.
Choosing the right data type can significantly save memory while also reducing server time when executing the required SQL queries. Data types and their range determine the amount of storage required. Developers should be aware that using large ranges for attributes can increase memory overhead. It is necessary to clearly analyze the problems to be solved and identify situations in which the approximate range of data is known and the condition for using signed numbers is determined. If a small range of used arguments is used, and all numbers are positive, then it would 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 that are directly dependent on time.
- 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 - makes it possible to enter temporary values into a table cell. Values are specified in the "hh: mm: ss" format.
- DATE TIME - this type combines the functions of the previous two. The data storage format in this case looks like this "yyyy-mm-dd: hh: mm: ss".
- TIME STAMP - this type stores the date and time, calculated by the number of seconds that have passed since midnight 01/01/1970 to the specified value.
- YEAR (M) - Used to store annual values in two- or four-digit format.
What else should you know? All of the above data types 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- 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 the special null destination or NULL pointer. He uses SQL as an aid. Column data types that are not required to be populated are specified with an operator to include null values when tables are created. Also, the NULL operator when using the additional NOT prefix can be used to indicate the mandatory filling of all values. A NULL pointer has no type. It simply points to a blank value in the database tables. For this reason, it may well be combined with one of the above data types.
SQL data types.
|
Description | ||
|
String types |
Constant length character string |
CHAR (number_ of characters) |
|
Variable length character string |
VARCHAR (num_chars) |
|
|
Large variable length character string | ||
|
Big binary variable object | ||
|
Numeric Exact Types |
Integer | |
|
Integer. The number of bits is greater than or equal to INTEGER | ||
|
Integer. Number of discharges less than or equal to INTEGER | ||
|
Fixed point number (number_digits - total number of digits, scale - decimal places) |
(NUMERIC | DECIMAL | DEC) (number of digits, scale) |
|
|
Numeric imprecise types |
Floating point number | |
|
Floating point number. The number of digits is greater than or equal to REAL |
DOUBLE PRECISION |
|
|
Floating point number |
FLOAT (number of digits) |
|
|
Temporary types |
Date. Contains year, month and day | |
|
Time stamp. Contains year, month, day, hour, minutes and seconds |
TIMESTAMP (scale) |
|
|
Time. Contains hour, minutes and seconds (scale - quantity signs in the fractional part of seconds). Possible time zone indication |
TIME (scale) |
|
|
Period. | ||
|
Boolean type |
Accepts boolean values |
Literals
Literals are the simplest expressions that are values of the corresponding types. Each data type has a way of representing its type literals. The following literals are available in SQL:
character strings (example: "character string"),
binary strings (example: X "1AFFD561"),
numbers (examples: 27 -863 0.173 .8582 -9572.5619 11.54Е7 .94Е194 93Е-12 -29.629Е27),
temporary (DATE "2005-12-07" TIME "HH: MI: SS" TIMESTAMP "YYYY-MM-DD HH: MI: SS"),
logical (TRUE, FALSE, UNKNOWN).
Operators and Expressions
Operators are language constructs that specify operations on data elements and return a new value as a result. Data items used in operators are called operands or arguments. Operators are represented as special characters or keywords. For example, the multiplication operator is represented by an asterisk (*), and the null test operator is represented by the IS NULL keyword.
There are two main types of operators. Unary ones have only one operand and are usually represented in the following format:
operand operator
Binaries operate on two operands and are represented as follows:
operand operator operand
In addition, there are special operators that use more than two operands. The type of an operator is determined by the type of arguments used and the type of the returned result. Operators of the following types are available:
string (||);
arithmetic;
brain teaser;
comparison predicates;
special predicates;
operators over sets (tables).
For each column, it is mandatory to define, in addition to the unique column name within the table, the data type as well. Assigning a data type to each column is one of the first steps taken when designing a table. Data types define the valid data values for each column.
2.3.1 The types char and varchar.
They are fixed or variable length character data types.
char [(n)] is a fixed-length, non-Unicode character data with a length of n bytes. The value for n must be between 1 and 8000. The storage size for this type of data is n bytes. The ISO synonym for char is character.
varchar [(n | max)] - variable length character data, not in Unicode; n can range from 1 to 8,000; max means the maximum storage size is 2 ^ 31-1 bytes. The storage size is equal to the actual data length plus two bytes. The entered data can be 0 characters long. ISO synonyms for varchar are char varying or character varying.
Char or varchar data can be a single character or string with a maximum length of 8000 characters for char data and up to 2 ^ 31 characters for varchar data.
Varchar data types can take two forms. Varchar data can be of the specified maximum length in characters, for example varchar (6) indicates that this data type can store a maximum of six characters, or it can be of the form varchar (max), which increases the maximum number of characters that can be stored in this type data, up to 2 ^ 31.
Character constants must be enclosed in single (") or double quotes ("). Enclosing character constants in single quotes is recommended. Enclosing character constants in double quotes is sometimes not allowed when QUOTED IDENTIFIER is ON.
This Transact-SQL code example assigns a value to a character variable:
DECLARE @MyCharVar CHAR (25)
SET @MyCharVar = "Ricardo Adocicados"
If you are using single quotes to enclose a character constant containing a nested single quote, use two single quotes to represent the nested single quote.
For instance:
SET @MyCharVar = "O" "Leary"
If the data to be stored is longer than the allowed number of characters, it is truncated. For example, if a column is defined as char (10) and the value "This is really a very long character string" is stored in it, then SQL Server truncates the character string to "This is valid."
The char data type becomes a fixed length data type if the NOT NULL clause is specified. If a value shorter than the length of the column is inserted into a char column with the NOT NULL clause, that value is padded to the right with spaces to the size of the column. For example, if a column is defined as char (10) and the value "music" is to be stored in it, SQL Server stores the data as "music ____", where "_" is a space.
2.3.2 The nchar and nvarchar data types.
These are constant-length (nchar) or variable-length (nvarchar) character data types that contain Unicode data and use the UCS-2 character set.
nchar [(n)] - Unicode character data, n characters long. The n argument must be between 1 and 4000. The storage size is twice n bytes. ISO synonyms for nchar are national char and national character.
nvarchar [(n | max)] - variable length Unicode character data. The n argument can range from 1 to 4,000. The max argument specifies that the maximum storage size is 2 ^ 31-1 (2,147,483,647) bytes. The storage size in bytes is twice the number of characters entered + 2 bytes. The entered data can be 0 characters long. The ISO synonyms for nvarchar are national char varying and national character varying.
The list below shows that Microsoft SQL Server supports most of the SQL 2003 data types. Also, SQL Server supports additional data types used to uniquely identify data rows in a table and on many servers, such as UNIQUEIDENTIFIER, which is in line with the "grow in width" hardware philosophy. professed by Microsoft (that is, implementation of the base on many servers on Intel platforms), instead of "growth in height" (that is, implementation on one huge powerful UNIX server or Windows Data Center Server).
An interesting side note on dates on SQL Server: SQL Server supports dates starting in the year 1753. You cannot store earlier dates in any SQL Server database data type. Why? This is because the English-speaking world began using the Gregorian calendar in 1753 (the Julian calendar was used until September 1753), and converting Julian to Gregorian dates 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 the INT type for the IDENTITY property rules that also apply to the BIGINT type.
BINARY [(n)] (SQL 2003 datatype: BLOB)
Stores a binary value of fixed length from 1 to 8000 bytes. A BINARY value is n + 4 bytes long.
BIT (SQL 2003 datatype: BOOLEAN)
Stores the value 1, 0, or NULL, which stands for "unknown". One byte can store up to 8 values from BIT table columns. In one more byte, you can place an additional 8 values of the BIT type. BIT columns cannot be indexed.
CHAR [(n)], CHARACTER [(n)] (SQL 2003 datatype: CHARACTER (n))
Stores character data of a fixed length from 1 to 8000 characters. Any unused space is filled with blanks by default. (You can turn off auto-blanking.) The type is n bytes.
CURSOR (SQL 2003 datatype: none)
A special data type used to describe a cursor in the form of a variable or parameter to an OUTPUT stored procedure. 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 01-01-1753 00:00:00 to 31-12-9999 23:59:59. The storage requires 8 bytes.
DECIMAL (p. S). DEC (p, s), NUMERIC (p, s) (SQL 2003 datatype: DECIMAL (p, s). NUMERIC (p. S))
Stores decimal fractions up to 38 digits long. The p and s values determine the precision and scale, respectively. The default scale is 0. The space occupied by the value is determined by the precision used. For 1-9 precision, 5 bytes are used. For 10-19 precision, 9 bytes are used. With a precision of 20-28, 13 bytes are used. Accuracy 29-39 uses 17 bytes.
See the INT type for the 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 datatype: FLOAT, FLOAT (n))
Stores floating point values in the range from -1.79E + 308 to 1.79E + 308. The precision specified by parameter n can range from 1 to 53. To store 7 digits (and - from 1 to 24), 4 bytes are required. Values greater than 7 digits are 8 bytes.
IMAGE (SQL 2003 datatype: BLOB)
Stores a binary value of variable length up to 2,147,483,647 bytes. This data type is often used to store graphics, sound, and files such as Microsoft Word documents and Microsoft Excel spreadsheets. IMAGE values 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 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. See the section "The CREATE / ALTERTABLE Statement".
MONEY (SQL 2003 datatype: none)
Stores monetary values in the range -922337203685477.5808 to 922337203685477.5807. The value is 8 bytes.
NCHAR (n), NATIONAL CHAR (n), NATIONAL CHARACTER) (SQL 2003 data type: NATIONAL CIARACTER (n))
Stores data in UNICODE format with a fixed length of up to 4000 characters. Storage requires n * 2 bytes.
NTEXT, NATIONAL TEXT (SQL 2003 datatype: 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 apply to the NTEXT type.
A synonym for the 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 datatype: NATIONAL CHARACTER VARYING (n))
Stores UNICODE data of variable length up to 4000 characters. The space occupied is calculated as twice the length of all characters inserted into the field (number of characters * 2). In SQL Server, the SET ANSI_PADDING] system parameter for the NCHAR and NVARCHAR fields is always ON.
REAL, FLOAT (24) (SQL 2003 data mun: REAL)
Stores floating point values in the range -3.40E + 38 to 3.40E + 38. It takes 4 bytes. The REAL type is functionally equivalent to the FLOAT (24) type.
ROWVERSION (SQL 2003 data type: none)
A unique number stored in the database that is updated whenever a row is updated, called TIMESTAMP in earlier versions.
SMALLDATETIME (SQL 2003 datatype: none)
Stores the date and time in the range "01-01-1900 00:00" to "06-06-2079 23:59" with an accuracy of the minute. (Minutes are rounded down if seconds are 29.998 or less, otherwise they are rounded up to a larger value.) The value is 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 datatype: none)
Stores monetary values in the range 214748.3648 to -214748.3647. The values are 4 bytes long.
SQL VARIANT (SQL 2003 data type: none)
Stores values that are 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 values returned by functions and stored procedures.
TABLE (SQL 2003 datatype: none)
A special type that stores the data set resulting from the work of the last process. Used solely for procedural processing and cannot be used in CREATE TABLE statements. This data type reduces the need to create temporary tables in many applications. May reduce the need for recompilation of procedures, thus speeding up the execution of stored procedures and UDFs.
TEXT (SQL 2003 datatype: CLOB)
Stores very large fragments of text up to 2,147,483,647 characters long. TEXTn IMAGE values are often much more difficult to manipulate than, say, VARCHAR values. For example, you cannot create an index on a TEXT or IMAGE column. TEXT values can be manipulated using the DATALENGTH, PATINDEX, SUBSTRING functions. TEXTPTR and TEX-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 different from the ANSI TIMESTAMP data type. TIMESTAMP is 8 bytes in size. ROWVERSION values are now better suited to uniquely identifying rows instead of TIMESTAMP.
Stores unsigned integers in the range 0 to 255 and occupies 1 byte. See the description of type / L / G for rules related to the IDENTITY property that also apply to this type.
UNIQUEIDENTIFIER (SQL 2003 datatype: none)
It is a value that is unique across all databases and all servers. It is represented as xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxxxx, where each x is 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 NULL tests. Constraints and properties can be used on columns of this type, with the exception of the IDENTITY property.
VARBINARY [(n)] (SQL 2003 datatype: BLOB)
Represents a binary value of variable length, 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 datatype: CHARACTER VARYING [(n)]
Stores character data of a fixed length ranging from 1 to 8000 characters. The occupied space is equal to the actual size of the entered value in bytes, and not the value of p.
