At present in the reconstruction of the computer room charge system, this would like to summarize the errors encountered, but some errors, oneself is still in a state of chaos, so, then summed up something else.
In refactoring we often deal with databases, especially data types, and here are some of the data types in SQL Server.
One, 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.
Second, 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.
Three or two 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.
Iv. Logical data types
The bit: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).
Five, 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.
Vi. 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
Vii. 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. For example, DATETIME time is 14:38:30.283 smalldatetime is considered to be 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.
Viii. Currency data types
Currency data types are 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 of the Money data type 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 63)-1 (+922,337,203,685,477.5807), The data precision is one out of 10,000 currency units. The Money data type uses 8 bytes of storage.
2 smallmoney
The smallmoney data type is similar to the money type, but it stores a range of currency values that are smaller than the monetary data type, with a value from -214,748.3648 to +214,748.3647 and a storage space of 4 bytes.
Ix. Specific data types
SQL Server contains a number of special data types for data storage.
1 TIMESTAMP
The timestamp data type provides a database-scoped unique value of this type equivalent to BINARY8 or varbinary (8), but when the column it defines is updated or inserted into the data row, the values of this column are 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.
X. 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.
Description in tabular form:
Field type description
Integer digits of bit 0 or 1
int integer number from -2^31 (-2,147,483,648) to 2^31 (2,147,483,647)
smallint integer numbers from -2^15 (-32,768) to 2^15 (32,767)
tinyint integer numbers from 0 to 255
Number of fixed and significant digits from -10^38 to 10^38-1 decimal
Synonyms for numeric decimal
Money data from -2^63 ( -922,337,203,685,477.5808) to 2^63-1 (922,337,203,685,477.5807), Min. currency Unit 10 per thousand
SmallMoney Currency data from -214,748.3648 to 214,748.3647, Min. currency Unit 10 per thousand
Float variable-precision numbers from -1.79E+308 to 1.79E+308
Real variable-precision numbers from -3.04E+38 to 3.04E+38
DateTime date and time data from January 1, 1753 to 9999 12th 31, the minimum time unit is 3% seconds or 3.33 milliseconds
smalldatetime date and time data from January 1, 1900 to June 6, 2079, Min. time unit is minutes
Timestamp timestamp, unique number of a database width
uniqueidentifier Global Unique identifier GUID
Char fixed-length non-Unicode character data up to a maximum of 8000
varchar variable length non-Unicode character data with a maximum length of 8000
Text variable length non-Unicode character data with a maximum length of 2^31-1 (2G)
NChar fixed-length Unicode character data with a maximum length of 8000
nvarchar variable-length Unicode character data up to a maximum of 8000
ntext variable-length Unicode character data, Max. 2^31-1 (2G)
Binary fixed-length binary data with a maximum length of 8000
varbinary variable-length binary data up to a maximum of 8000
Image variable length binary data with a maximum length of 2^31-1 (2G)
SQL data type
