Detailed data types for SQL Server

Read Catalogue

• One: System data type
• 2. Floating-point data types
• 3. Character data type
• 4. Date and time data types
• 5. Text and graphic data types
• 6. Currency data type
• 7. Bit data type
• 8. Binary data types
• 9. Other data types
• Two: Custom data types

A data type is an attribute that specifies the type of data that an object can hold, and supports multiple data types in SQL Server, including character types, numeric types, and date types. The data type is equivalent to a container, the size of the container determines how many things are loaded, and dividing the data into different types can save disk space and resources.
SQL Server can also automatically limit the range of values for each data type, such as defining a field of type int, and SQL Server automatically converts the type to smallint if the size of the inserted value is within the range of smallint or tinyint when inserting data or tinyint, so that when you store your data, you use only 1/2 or more of the storage space of Int.
The data types in the SQL Server database management system can be divided into two categories: the system default data type and the user-defined data type. The contents of these two categories of data types are described below.

Back to TopOne: System data type

There are a few major categories of system data types provided by SQL Server, a total of 25. SQL Server automatically restricts the range of values for each system data type, and SQL Server will give an error when the value in the inserted database exceeds the range allowed by the data.

1. Integer data type

An integer data type is one of the most commonly used data types and is primarily used to store numeric values that can be directly performed without the use of function conversions.
(1). bigint
Each bigint is stored in 8 bytes, with one bits representing the sign bit, and the other 63 bits representing the length and size, which can represent 2 of all integers in the 63-square range of 63.
(2). int
int or integer, each int is stored in 4 bytes, one of the bits represents the sign bit, the other 31 bits represent the length and size, and can represent all integers in the 31-in-1 range of the 31-time Square of 2.
(3). smallint
Each smallint type of data occupies two bytes of storage space, where one bits represents the sign of an integer value, the other 15 bits represent length and size, and can represent all integers in the 15-in-1 range of 2 of the 15-time square.
(4). tinyint
Each tinyint type of data consumes one byte of storage space and can represent all integers in the 0~255 range.

Back to Top 2. Floating-point data types

　　 floating-point data types Store decimal decimals, which represent approximate numeric data types for floating-point numeric data. Floating-point data is approximate; the data for floating-point numbers is stored in SQL Server in a way that is not used, that is, if and only if the number to be rounded is a non-zero, add 1 to the least significant bit of its reserved numeric portion, and make the necessary proximity.
(1). Real
you can store positive or negative decimal values, which are stored from -3.40e+38~-1.18e-38, 0, and 1.18e-38~3.40e+38. The data for each real type consumes 4 bytes of storage space.
(2) float[(n)]
This is the number of bits (represented in scientific notation) used to store the mantissa of the float value, so you can determine the precision and storage size. If n is specified, it must be a value between 1 and 53. The default value for n is.
it ranges from -1.79e+308~-2.23e-308, 0 and 2.23e+308~1.79e-308. If you do not specify the length of the data type float, it consumes 8 bytes of storage space. The float data type can be written in the form of float (n), where n is the precision of the specified float data, and n is the integer value between 1~53. When n takes 1~24, it actually defines a real type of data, and the system stores it with 4 of its own. When N takes 25~53, the system considers it to be a float type and stores it in 8 bytes.
(3). decimal[(P[,s]) and numeric[(P[,s])
numeric data type with fixed precision and decimal place. When using maximum precision, valid values are from -10^38+1~10^38-1. Numeric is functionally equivalent to decimal.
p (Precision) specifies the total number of digits that can be stored in decimal digits, including the left and right digits of the decimal point, which must be a value from 1 to the maximum precision of 38, with a default precision of.
S (scale) specifies the maximum number of decimal digits that can be stored to the right of the decimal point, the number of decimals must be a value from 0 to P, and the number of decimal digits can be specified only after the specified precision. The default number of decimal digits is 0; therefore, 0<=s<=p. The maximum storage size varies based on precision. For example: Decimal (10,5) represents a total of 10 digits, with an integer of 5 bits and a decimal 5 bits.

Back to Top 3. Character data type

The character data class is also one of the most commonly used data types in SQL Server to store various characters, number symbols, and special symbols. When you use a character data type, you need to precede it with either a single quotation mark or a double quotation mark.
(1) char (n)
When data is stored with a char data type, each character and symbol occupies one byte of storage space, and n represents the storage space for all characters, and n is the value of 1~8000. If you do not specify the value of N, the value of system default n is 1. If the string length of the input data is less than n, the system automatically adds space after it to fill the set space, and if the input data is too long, it will truncate its excess.
(2). Varhcar (N|max)
n is the maximum length of the stored character, the range is 1~8000, but the storage space can be changed based on the number of characters actually stored, and Max indicates that the maximum storage size is 2 31 square-1 bytes. The storage size is the actual length of the input data plus 2 bytes. The length of the input data can be 0 characters. such as Varchcar (20), the corresponding variable can only store 20 characters, not enough 20 characters by the actual storage.
(3). NCHAR (n)
a fixed-length Unicode character data of n characters. The n value must be between 1~4000 (inclusive), and if no data is defined or a variable declaration statement is specified in N, the default length is 1. This data type is in the Unicode character set, so each storage unit occupies two bytes, which can be included in all the world's text (except, of course, some uncommon characters).
(4) nvarchar (N|max)
similar to varchar, stores variable-length Unicode character data. The n value must be between 1~4000 (inclusive), and if no data is defined or a variable declaration statement is specified in N, the default length is 1. Max refers to 31 square-1 bytes with a maximum storage size of 2. The storage size is twice times the number of input characters + 2 bytes. The length of the data entered can be 0 characters.

Back to Top 4. Date and time data types

(1). Date
stores date data represented by a string, which can represent any date value between the 0001-01-01~9999-12-31 (January 1 to A.D. December 31, 9999). The data format is "YYYY-MM-DD":
YYYY: A four-digit number representing the year, with a range of 0001~9999;
MM: Represents the two digits of the month in the specified year, the range is 01~12;
DD: A two-digit number that represents a day in the specified month, with a range of 01~31 (the highest value depends on the month)
This data type occupies 3 bytes of space.
(2). Time
records a time of day as a string, with a value range of 00:00:00.0000000~23:59:59.9999999 and a data format of "hh:mm:ss[.nnnnnnn":
hh: Represents the two digits of the hour, the range is 0~23.
mm: Represents the two digits of the minute, the range is 0~59.
SS: A two-digit number representing the second, with a range of 0~59.
N is the 0~7 number, and the range is 0~9999999, which represents a small fraction of a second.
the time value consumes 5 bytes of space when it is stored.
(3). DateTime
used to store time and date data, from January 1, 1753 to December 31, 9999, the default value is 1900-01-01 00:00:00, which is enclosed in single or double quotation marks when inserting data or using it elsewhere. You can use "/", "-" and "." As a separator character. This type of data occupies 8 bytes of space.
(4). DateTime2
The extended type of datetime, which has a larger data range, the default minimum precision is highest, and has an optional user-defined precision. The default format is: Yyyy-mm-dd hh:mm:ss[.fractional seconds], the date of Access is 0001-01-01~9999-12-31 (A.D. January 1 to December 31, 9999).
(5). smalldatetime
The smalldatetime type is similar to the DateTime type, except that its storage range is from January 1, 1900 to June 6, 2079, and when the date-time precision is small, engraving uses smalldatetime, which consumes 4 bytes of storage space.
(6). DateTimeOffset
used to define a time zone that is combined with a date and a 24-hour system to identify the timezone. The default format is: "Yyyy-mm-dd hh:mm:ss[.nnnnnnn][{+|-}hh:mm]":
hh: Double digit, range is -14~14
mm: Double digit, range is 00~59;
here hh is the time zone offset, which is the world standard Time (UTC) value that is stored in this type of data, eg: to store Beijing time November 11, 2011 12 O'Clock Full, this value will be 2011-11-11 12:00:00+08:00, because Beijing is in the East eight district, 8 hours earlier than UTC. By default, the data type data is stored in a fixed storage space of 10 bytes in size.

Back to Top 5. Text and graphic data types

(1). Text
Used to store text data, a variable length non-Unicode data in a server code page with a maximum length of 2 31-1 (2147 483 647) characters. When the server code page uses double-byte characters, the storage is still 2147 483 647 bytes.
(2) ntext
The same as the text type, for non-Unicode data of variable length, with a maximum length of 2 of 30 square-1 (1073 741 283) characters. The storage size is twice times the number of characters entered (in bytes).
(3). Image
Variable-length binary data in the range: 0~2 31-1 bytes. Used to store photos, catalog pictures, or drawings, the capacity is 2147 483 647 bytes, the system automatically allocates space based on the length of the data, and the data that stores the field cannot be entered directly using the INSERT statement.

Back to Top 6. Currency data type

(1). Money
Used to store currency values, with a value range of plus or minus 922 337 213 685 477.580 8. The integer portion of the Money data type contains 19 digits, the fractional part contains 4 digits, so the Money data type has a precision of 19 and a storage space of 8 bytes.
(2). smallmoney
Similar to the money type, the value range is 214 748.346 8, and the smallmoney storage occupies 4 bytes of storage space. Enter the data with a currency symbol in front of it, such as the renminbi as ￥ or another defined currency symbol.

Back to Top 7. Bit data type

Bits are called bit data types, and only 0 or 1 is the value, and the length is 1 bytes. The bit value is often used as a logical value to determine true (1) or False (0), and the system replaces it with 1 when entering a value other than 0.

Back to Top 8. Binary Data Types

(1) binary (n)
The length is n bytes of fixed-length binary data, where n is the value from 1~8000. The storage size is n bytes. When you enter binary values, you must have 0x in front, you can use 0XAA5 to represent AA5, and if the input data length is greater than the length that is fixed, the part that is exceeded is truncated.
(2). varbinary (N|max)
Variable-length binary data. where n is the value from 1~8000, Max indicates that the storage size is 2 of 31 square-1 bytes. The storage size is the actual length of the input data + 2 bytes.
Within the defined range, regardless of the length of the input, the binary type of data consumes the same storage space, that is, the defined time space, and for the varbinary type of data, the length of the actual value is stored when the storage space is used.

Back to Top 9. Other data Types

(1). rowversion
Each data has a counter that is incremented when an INSERT or update operation is performed on a table that contains rowversion columns in the database. This counter is a database row version. A table can have only one rowversion column. Each time you modify or insert a row that contains a rowversion column, the incremental database row version value is inserted in the rowversion column.
exposes the data type of a unique binary number that is automatically generated in the database. RowVersion are typically used as a mechanism for adding version stamps to table rows. The storage size is 8 bytes. The rowversion data type is just an incremented number and does not hold the date or time.
(2) Timestamp
Timestamp data type, the timestamp data type is a synonym for the rowversion data type, provides a unique database-wide value that reflects the unique order of data modifications, is a monotonically rising counter, and the value of this column is automatically updated.
You do not have to specify a column name for the timestamp data type in the CREATE TABLE or ALTER TABLE statement.
eg:create table testtable (id int primary key,timestamp);
The SQL Server database engine generates timestamp column names, but RowVersion does not, and you must specify a column name when using rowversion.
(3). uniqueidentifier
The 16-byte GUID (globally unique Identifier, Global unique identifier) is a unique number generated by SQL Server based on the network adapter address and host CPU clock, each of which is a hexadecimal number in the range of 0~9 or a~f. For example: 6F9619FF-8B86-D011-B42D-00C04FC964FF, this number can be obtained through the NEWID () function, and computers around the world are not the same number generated by the secondary function.
(4). Cursor
the cursor data type, which resembles the data table, contains row and column values in its saved data, but has no index, and the cursor is used to set up a data set for each row of data.
(5). sql_variant
for storing any legitimate SQL Server data except text, graphic data, and timestamp data, it is convenient for SQL Server development to work.
(6). Table
used to store the result set after processing the table or view. This new data type allows variables to store a table, making it easier and quicker for a function or procedure to return query results.
(7). XML
the data type that stores the XML data. You can store an XML instance in a column or in a variable of XML type. The stored XML data type indicates that the instance size cannot exceed 2GB.

Back to TopTwo: Custom data types

SQL Server allows users to customize the data type, which is based on the SQL Server system data type, and the custom data type enables database developers to define the data types that meet their development needs as needed. Custom data types are easy to use, but require a lot of performance overhead, so use caution. When a user defines a data type, it is necessary to specify the name of the type, the system data type on which it is based, and whether it is allowed to be empty. SQL Server provides two ways for users to create custom data types. These two methods for defining data types are described below.
(1). Create a user-defined data type using Object Explorer
Create a database test--> the programmability-to-type node, right-click the user-defined data type node, and in the box that pops up, follow the instructions. Very simple, do not repeat it.
(2). Create user-defined data types using stored procedures
accidents using the graphical interface to create custom data types, system stored procedures in SQL Server sp_addtype can also provide a way for users to create custom data types using T_sql statements. Its syntax is as follows:
sp_addtype [@typename =] type,
[@phystyle =] System_data_type
[, [@nulltype =] ' null_type ']
among them, the meanings of each parameter are as follows:
Type: Specifies the name of the user-defined data type.
system_data_type: Used to specify the name and definition of the corresponding system-supplied data type. Note: You cannot use the timestamp data type, and you need to enclose it in quotation marks when the system data type you are using has additional instructions.
Null_type: A null property that specifies a user-defined data type whose value can be "null", "NOT NULL", or "notnull". The default is the same as the default Null property of the system. The name of the user-defined data type should be unique in the database.
eg:sp_addtype homeaddress ' varchar ', ' NOT NULL '
To Delete a user-defined data type:
A. Use the graphical interface to delete, do not repeat.
B. Use the system stored procedure sp_droptype to delete the. Eg:sp_droptype homeaddress, where homeaddress is the user-defined data type name.
Note: The database is using a user-defined data type that cannot be deleted.

Transferred from: http://www.cnblogs.com/selene/

Detailed data types for SQL Server