SQL Server supported data types for getting started with SQL Server

SQL Server supported data types for getting started with SQL Server

Common data types

1. Integral type data

1) bit, can only store 0, 1, and null values null.

2) tinyint, a positive number without a sign bit, takes 1 bytes, 8 bits, and supports a data range of 0~2^8-1.

3) smallint, with sign bit, occupies 2 bytes, 16 bits, the supported data range is -2^15~2^15-1.

4) int, signed bit, takes 4 bytes, 32 bits, and the supported data range is -2^31~2^31-1.

5) bigint, with sign bit, occupies 8 bytes, 64 bits, the supported data range is -2^63~2^63-1.

2. Floating-point data

1) float (n), floating-point data is approximate , where n Is the number of bits (in scientific notation) used to store the mantissa of a float value, so you can determine the precision and storage size.   

n is specified, it must be a value between 1 and range .

53 . " The precision refers to how many numbers are supported, and if the given data exceeds the precision range, it is truncated directly and will not be rounded.

n                & nbsp;       value	precision	storage sizes
1-24	7-digit number	4 bytes
25-53	15-digit number	8 bytes

2) real, equivalent to float (24).

3) Double, equivalent to float (53).

4) Declmal (m,n), precise numeric type, you must specify the total length of the float data and the number of digits after the decimal point. m corresponds to the total length of the floating-point data, n corresponds to the number of digits after the decimal point, and the number of digits before the decimal point is m-n.

5) numeric (m,n), equivalent to Declmal (m,n).

3. Time-based data

1) datetime, which represents the date and time, this data type stores all date and time data from January 1, 1753 to December 31, 9999, accurate to 1% seconds or 3.33 milliseconds.

2016-04-19 03:57:26.243

2) smalldatetime, used to indicate the date and time from January 1, 1900 to June 6, 2079, accurate to one minute.

2016-04-19 03:59:00

3) Date, which represents a day, does not contain the time part, can be expressed from January 1 to December 31, 9999.

2016-04-19

4) datetime2 (n), n is less than or equal to 7,n can control how many digits after the decimal point, accurate to nanosecond.

2016-04-19 04:07:22.4761175

5) DateTimeOffset (n), n is less than or equal to 7, n can control how many digits after the decimal point, there will be time zone data.

2016-04-19 09:09:58.5000000 +00:00

4. String-type data

1) char (n), fixed length string data, maximum support 8,000 characters. If the number of characters in the input data is less than n, then the system automatically adds a space after it to fill the set space if the input data is too long, it will intercept its excess.

2) varchar (n), variable length string data, maximum support of 8,000 characters. Because the varchar data type is stored for the actual numeric length. If the number of characters in the input data is less than n, then the system does not add spaces to fill the set space.

3) nchar (n), used to store fixed-length uniform encoded character data, unified encoding with a double-byte structure to store each character, instead of single-byte, maximum can store 4,000 characters.

4) nvarchar (n), used as a uniform coded character data for storing variable lengths, unified encoding uses a double-byte structure to store each character, rather than a single byte, which can store up to 4,000 characters.

5) varchar (max), variable length string data, Max indicates the maximum storage size is 2^31-1 bytes (2 GB).

6) nvarchar (max), variable length string data, Max indicates that the maximum storage size is 2^31-1 bytes (2 GB).

If the column data items are of the same size, charis used.

If the size of the column data items varies considerably, varcharis used.

Use varchar (max)if the column data item size varies greatly and the size may exceed 8,000 bytes.

5. Currency type data

1) smallmoney, used to denote money and currency values, this data type can store data from 214748.3648 to 214748.3647, accurate to one out of 10,000 of the currency unit, and automatically rounded if the number of decimal digits is too large.

2) Money, which is used to denote monetary value. This data type can store data from 922 billion to 922 billion, accurate to one out of 10,000 of the currency unit, and automatically rounded if the number of decimal digits is too large.

6. Time-stamped data

Timestamp, the timestamp data type is a special type of data used to create a database-wide unique digital. There can be only one timestamp column in a table. Each time you insert or modify a row, the value of the timestamp column changes automatically. Although it has "time" in its name, the timestamp column is not a date that people can recognize. In a database, the timestamp value is unique.

0x0000000000d43bad

7. Unique Marker type data

uniqueidentifier, which is used to store a globally unique identifier, the GUID. The GUID is truly globally unique. This number has little chance of being rebuilt in another system. You can use the NEWID function to get this type of data.

Select NEWID ()--78c8e94e-dbbf-4c4f-892a-93d04a098a12

8. Binary type data

1) binary (n) for storing binary data up to 8000 bytes long in fixed length.

2) varbinary (n), which is used to store variable-length binary data up to 8000 bytes long.

3) varbinary (max), used to store variable-length binary data up to 2^31-1 bytes long.　

If n is not specified in a data definition or variable declaration statement , the default length is 1. If n is not specified using the CAST function , the default length is 30.

If the column data items are of the same size, binary is used .

If the size of the column data items varies considerably, use varbinary.

Use varbinary (max) when the column data entry exceeds 8,000 bytes .

SQL Server supported data types for getting started with SQL Server