There are two characteristics of data in a computer: type and length. The so-called data types are the types of data that are divided by the way data is represented and stored.
Each variable, parameter, expression, and so on in SQL Server has a data type. The data types provided by the system are divided into several categories, as shown in table 4-2.
Of these, BIGINT, sql_variant, and table are the newly added 3 types of data in SQL Server 2000. The following categories describe the various data types.
4.3.1
Integer data type
An integer data type is one of the most commonly used data types.
1
,
INT
(
INTEGER
)
The INT (or integer) data type stores all positive and negative integers between the 31-square (-2, 147, 483, 648) to 2 of 31-1 (2, 147, 483,647) from 2. data for each int type is stored in 4 bytes, where 1 bits represent the sign of the integer value, and the other 31 bits represent the length and size of the integer value.
2
,
SMALLINT
The SMALLINT data type stores all positive and negative integers from 2 to 15 (-32, 768) to 2-15 (1, 32). The data for each smallint type consumes 2 bytes of storage space, where 1 bits represent the sign of the integer value, and the other 15 bits represent the length and size of the integer value.
3
,
TINYINT
The tinyint data type stores all positive integers from 0 to 255. Each tinyint type of data consumes 1 bytes of storage space.
4
,
BIGINT
The BIGINT data type stores all positive and negative integers from -2^63 (-9, 223, 372, 036, 854, 775, 807) to 2^63-1 (9, 223, 372, 036, 854, 775, 807). Each bigint type of data consumes 8 bytes of storage space.
4.3.2
floating-point data types
Floating-point data types are used to store decimal decimals. The data for floating-point numbers is stored in SQL Server in a way that is rounded up (Round up or called only in). The so-called rounding means that when (and only if) the number to be rounded is a non-zero, the value on the least significant bit of its reserved number portion is added 1, and the necessary rounding is carried out. If a number is a rounding number, its absolute value is not reduced. For example: 2-bit and 12-bit rounding for 3.14159265358979, with 3.15 and 3.141592653590 results.
1
,
REAL
Data Type
The real data type is accurate to the 7th decimal place, ranging from -3.40E-38 to 3.40E +38. Each real type of data consumes 4 bytes of storage space.
2
,
FLOAT
The float data type is accurate to the 15th decimal place, ranging from -1.79E-308 to 1.79E +308. Each float type of data consumes 8 bytes of storage space. The float data type can be written as a float[n] form. n Specifies the precision of the float data. n is an integer value between 1 and 15. When n takes 1 to 7 o'clock, it actually defines a real type of data, the system stores it in 4 bytes, and when N takes 8 to 15 o'clock, the system considers it to be a float type and stores it in 8 bytes.
3
,
DECIMAL
The decimal data type can provide the actual storage space required for decimals, but there are limits, and you can use 2 to 17 bytes to store values from 1 to 10 of the 38-square-38-1 of-10. It can be written as decimal[P [s]], and P and s determine the exact proportions and digits. where p represents the total number of digits (excluding decimal points) of the value to be stored, the default value is 0, and s represents the number of digits after the decimal point. For example: Decimal (15 5), which represents a total of 15 digits, where the integer is 10 bits, and the decimal number is 5. Bit table 4-3 lists the relationships between the number of bytes required for each precision.
4
,
NUMERIC
The numeric data type is exactly the same as the decimal data type.
Note:SQL Server supports the default maximum of 28 bits for data precision in conjunction with the front-end development tools. However, you can change the default precision by using commands to execute the Sqlserver.exe program to start SQL Server. The command syntax is as follows: sqlservr[/d master_device_path][/p precisim_leve1]
Example
4-4:
with maximum data accuracy
-
Start
SQL Server
SQLSERVR/D c \ Mssql2000\data\master.dat/p38
/* After using the/p parameter, if no specific precision value is specified thereafter, the default is 38 bits./*
4.3.3
Binary data Types
1
,
BINARY
Binary data types are used to store binary data. It is defined as binary (n) and n is the length of the data, with a value of 1 to 8000. When used, you must specify the size of the binary type data, which should be at least 1 bytes. BINARY type data consumes n+4 bytes of storage space. When you enter data, you must precede the data with the character "0X" as the binary identifier, such as: to enter "ABC" You should enter "0XABC". If the input data is too long, it will intercept the excess part. If you enter an odd number of data bits, a 0 is added after the starting symbol "0X", as the above "0XABC" is automatically changed to "0X0ABC" by the system.
2
,
VARBINARY
The varbinary data type is defined in the form varbinary (n). It is similar to binary type, and the value of n is also 1 to 8000, and if the input data is too long, it will intercept its excess. The difference is that the varbinary data type has a variable length attribute because the varbinary data type has a storage length of actual numeric length + 4 bytes. When a binary data type allows null values, it is treated as a varbinary data type.
In general, because of the fixed length of the binary data type, it is faster to handle than the varbinary type.
4.3.4
Logical data Types
BIT
:The bit data type occupies 1 bytes of storage space, with a value of 0 or 1. If you enter a value other than 0 or 1, it will be treated as 1. The BIT type cannot be defined as a null value (so-called null values are null values or meaningless values).
4.3.5
character data type
The character data type is the most used data type. It can be used to store a variety of letters, number symbols, special symbols. In general, use the character type data with a single quote ' or double quote ' before and after it.
1 CHAR
The CHAR data type is defined in the form char[(n)]. Each character and symbol stored as a char type occupies one byte of storage space. n represents the storage space occupied by all characters, and the value of N is 1 to 8000, which can accommodate 8,000 ANSI characters. If you do not specify an n value, the system default value is 1. 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, the excess will be truncated.
2
,
NCHAR
The nchar data type is defined as nchar[(n)]. It is similar to the char type. The difference is that the value of the nchar data type N is 1 to 4000. Because the nchar type uses the Unicode standard character set (CharacterSet). The UNICODE standard specifies that each character occupies two bytes of storage space, so it consumes one more storage space than the non-Unicode standard data type. The advantage of using the Unicode standard is that it uses two bytes to do the storage unit, the capacity of one storage unit is greatly increased, can be included in the language of the world, in one data column can appear in Chinese, English, French, German, etc., without coding conflicts.
3
,
VARCHAR
The varchar data type is defined in the form varchar [(n)]. It is similar to the char type, the value of n is also 1 to 8000, if the input data is too long, it will be truncated out of its part. What is different is that the varchar data type has a variable length attribute because the varchar data type has a storage length of actual numeric length, and if the input data has fewer than n characters, then the system does not add spaces after that to fill the set space.
In general, because of the fixed length of the char data type, it is faster than varchar type processing.
4
,
NVARCHAR
The nvarchar data type is defined as nvarchar[(n)]. It is similar to the varchar type. The difference is that the nvarchar data type uses the Unicode standard character set (Character set) and the value of N is 1 to 4000.
4.3.6
text and graphic data types
This type of data is used to store large numbers of characters or binary data.
1
,
TEXT
The text data type is used to store a large amount of text data, whose capacity is theoretically 1 to 2 of 31 square-1 (2, 147, 483, 647) bytes, depending on the storage space of the hard disk in the actual application.
In previous versions of SQL Server 2000, a text object in the database was actually stored as a pointer to a data page in 8KB (8,192 bytes). These data pages are dynamically added and logically linked together. In SQL Server 2000, data for text and image types is stored directly in the data row of the table, rather than on different data pages. This reduces the amount of space used to store the text and Ima-ge types, and correspondingly reduces the number of I/O for this type of disk processing data.
2 NTEXT
The ntext data type is similar to the text. Type, where the ntext type uses the Unicode standard character set (Character set) and therefore has a theoretical capacity of 230-1 (1, 073, 741, 823) bytes.
3 IMAGE
The image data type is used to store a large number of binary data binaries. Its theoretical capacity is 2 of the 31-Time Square-1 (2,147,483,647) bytes. The mode in which the data is stored is the same as the text data type. Typically used to store objects such as graphics such as OLE Object linking and embedding, objects connected and embedded). As with binary data types when entering data, the character "0X" must precede the data as a binary identifier
4.3.7
Date and time data types
1 DATETIME
The datetime data type is used to store a combination of date and time. It can store all dates and times from January 1, 1753 to 0 o'clock A.D. December 31, 9999 and 23:59 59 seconds, with an accuracy of up to 1% seconds, or 3.33 milliseconds. The DATETIME data type consumes 8 bytes of storage space. The first 4 bytes are used to store days before or after January 1, 1900, values are positive or negative, positive numbers represent dates after this date, and negative numbers represent dates before this date. The latter 4 bytes are used to store the number of milliseconds elapsed over the time specified from 0 o'clock on this day. If the time portion is omitted when the data is entered, the system 12:00:00:000am as the time default: If the date part is omitted, the system will use January 1, 1900 as the default value for the date.
2 smalldatetime
The smalldatetime data type is similar to the datetime data type, but has a smaller date-time range from January 1, 1900 to June 2079 6: The daily precision is low, can only be accurate to the minute, its minute digit is rounded according to the number of seconds, that is, 30 seconds for the boundary rounding. such as: DATETIME time is 14:38:30.283
When smalldatetime thinks it is 14:39:00 smalldatetime data type uses 4 bytes to store data. The first 2 bytes store the number of days since the base date January 1, 1900, and the last two bytes store the number of minutes elapsed from 0 o'clock this day.
The following describes the date and time input format
Date input Format
Many of the input formats for dates can be broadly divided into three categories:
- English + numeric format
The months in this format are available in English full names or abbreviations, are not case sensitive, and are not comma-free between the year and month;
Year can be 4 Bit or 2 bit, when it is two bits, if the value is less than 50 is considered 20xx years, if greater than or equal to 50
is considered 19xx years, if the day part is omitted, then the month is considered 1th. The following formats are the correct date formats:
June Oct 1 1999 January February
1 1 Sep June July xx
- number + delimiter format
allows you to use a slash (/), a connector (-), and a decimal point (.) as the division
separator between the year, month, and day in numbers. Example:
ymd:2000/6/22 2000-6-22 2000.6.22
mdy:3/5/2000 3-5-2000 3.5.2000
dmy:31/12/1999 31-12-1999 31.12.2000
- pure number format
The pure number format is a continuous 4-bit 6, bit, or 8-digit number representing the date. If you enter a 6-bit or 8-bit
number, the system is identified by year, month, day, and the month and date are represented by a two-digit number:
If the number entered is 4 digits, the system considers the 4 digits to represent the year, with the month and day defaults for January
1st for this year. For example:
20000601---June 1, 2000 991212---December 12, 1999 1998---1998????
must be entered in the order of hours, minutes, seconds, milliseconds when the time is entered. In the meantime separated by a colon ":". However, the millisecond portion can be divided by the decimal point ".", followed by the first digit for one-tenth seconds, the second digit for 1% seconds, and the third digit for 1 per thousand seconds. Use AM when using the 12-hour system. Am and PM (PM) Specify time is noon or afternoon, if not specified, the system defaults to AM. AM and PM are not case sensitive. Example:
3:5:7.2pm---3:5 P.M. 7 seconds 200 milliseconds
10:23:5.123am---10:23 A.M. 5 Seconds 123 milliseconds
You can use the SET DATEFORMAT command to set the system default date-time format.
4.3.8
Currency data type
Currency data type is used to store currency values. When you use a currency data type, you should precede the data with a currency symbol before the system can identify which currency it is, and the default is "￥" if the currency symbol is not added. Each currency symbol is shown in 4-2.
1 Money
The data type of the Money data is a decimal value with 4 decimal places, which takes a value from 2 to 63 square (-922,337,203, 685,477.5808 to 2 of 63 square-1 (+922,337,203,685,477.5807), with a data precision of one out of 10,000 currency units. The Money data type uses 8 bytes of storage.
2 smallmoney
smallmoney data type is similar to Money type, but its stored currency value range is smaller than the data type , which takes a value from -214,748.3648 to +214,748.3647 and a storage space of 4 bytes.
4.3.9
specific data types
SQL Server contains some special data types for data storage.
1 TIMESTAMP
TIMESTAMP data type provides a database-scoped unique this type equivalent to BINARY8 or varbinary (8) , but when the column it defines is updated or inserted into the data row, the value of this column is automatically updated, and a count value is automatically added to this timestamp data column. There can be only one timestamp data column in each database table. If a column named "TIMESTAMP" is established, the type of the column is automatically set to the TIMESTAMP data type.
2 uniqueidentifier
The uniqueidentifier data type stores a 16 bit binary number. This number is called (guidglobally unique Identifier, the global Unique identification number). This number is the only global encoding generated by SQL Server's NEWID function, and the numbers generated by this function are not the same in computers around the world.
4.3.10
user-defined data types
The SYSNAME SYSNAME data type is provided to the user by the system for user-definable data types. It is defined as nvarchar (128), that is, it can store 128 Unicode characters or 256 general characters. Refer to the "Custom data Types" section in Chapter 7th, "Managing database Tables" for specific usage.
4.3.11
new data Types
3 data types were added in SQL Server 2000: BIGINT, sql_variant, and table. Where the bigint data type is described in the integer type, the remaining two are described below:
1 sql_variant
The sql_variant data type can store any legitimate SQL Server data except text, graphic data (text, NTEXT, IMAGE), and timestamp type data. This data type greatly facilitates the development of SQL Server.
2 TABLE
Table data types are used to store result sets that are processed against a table or view. This new type allows a variable to store a table, which makes it easier for a function or procedure to return query results, as well as to use see Chapter 13th, "Cursors, views, and custom functions". reprinted from http://blog.chinaunix.net/uid-20508279-id-1660641.html
