Difference between currency type and number type in SQL Server
Publish: 2021-03-29 18:25:26
1.
Currency field types generally include int, float, money / smallmoney, decimal / numeric
select according to the accuracy of the stored data:
int can only store integer money
Themoney / smallmoney data types are accurate to one ten thousandth of the monetary unit they represent
decimal / numberic can customize the decimal places and the data precision that can be stored, so generally more people use this type
float is not suitable for currency which needs precise value
conclusion:
it is best to use money and decimal types in finance
if it is a simple calculation, the float type can be used, but the data fetched by the float type on some platforms is not so accurate, and the accuracy may be lost. Therefore, if the cost is not great in a stable performance platform, it is recommended to use the above two types
extended data:
SQL data type:
1, bit integer
bit data type is integer, its value can only be 0, 1 or null. This data type is used to store data with only two possible values, such as yes or no, true or false, on or off.
note: it is a data type that saves a lot of space. If it can meet the requirements, it should be used as much as possible
The 2, tinyint integer
tinyint data type can store integers from 0 to 255. It's useful when you're only going to store a limited number of values. This data type occupies 1 byte in the database
The 3, smallint integer
smallint data type can store integers from the 15th power (- 32768) of - 2 to the 15th power (32767) of 2. This kind of data type is very useful for storing some numerical data which is often limited in a specific range. This data type takes up two bytes of space in the database
The4, int integer
int data type can store integers from the 31st power of - 2 (- 2147483648) to the 31st power of 2 (2147483647). Almost all numerical data stored in the database can use this data type. This data type takes up four bytes in the database
The5, decimal precise numerical type
decimal data type can be used to store numerical data with fixed precision and range from - 10 to - 10. When using this data type, you must specify the range and precision. The range is the total number of digits that can be stored around the decimal point. Precision is the number of digits stored to the right of the decimal point
Numerical data type is similar to decimal7, smallmoney
smallmoney data type is used to represent money and monetary value. This data type can store data from - 214748.3648 to 214748.3647, accurate to one ten thousandth of the monetary unit
8, money type
money data type is used to express money and monetary value. This data type can store data from - 922 billion to 922 billion, accurate to one ten thousandth of the monetary unit
9, float approximate value type
float data type is an approximate value type for floating-point numbers. Floating point numbers are approximate because not all numbers in their range can be expressed accurately. Floating point numbers can be any number from - 1.79e + 308 to 1.79e + 308
10, real approximate value
real data type is similar to floating-point number, it is approximate value type. It can represent floating-point numbers with values between - 3.40e + 38 and 3.40e + 38
2.
Max in SQL server, you can compare the maximum value of any field. If it's character type, it's character, number, letter, text. If you don't know the specific order of the characters, the number is 0-9, the letter is A-Z, the text depends on how many words there are, and then sort by the beginning of the first word. If the first word is heavy, then press the second, and so on, In the example field, there is * a Z Lin Xia, and his maximum value is Xia
Max in SQL server, you can compare the maximum value of any field. If it's character type, it's character, number, letter, text. If you don't know the specific order of the characters, the number is 0-9, the letter is A-Z, the text depends on how many words there are, and then sort by the beginning of the first word. If the first word is heavy, then press the second, and so on, In the example field, there is * a Z Lin Xia, and his maximum value is Xia
3. The money type is accurate to ten thousandths of the unit of money. The storage size is 8 bytes. The storage form is 12345.67. If your original float has more than three decimal places, it will be rounded to money type.
4. SQL server has many data types, including:
the first category: integer data
bit:bit The data type represents 0, 1 or null, which means true, The storage range is: - 2 ^ 31 to 2 ^ 31-1.
smallint: the storage range is: - 2 ^ 15 to 2 ^ 15-1.
tinyint: it is the smallest integer type with only 1 byte and the range is 0 to ^ 8-1.
the second class: accurate numerical data
numeric: the number can reach 38 bits, The number of bytes used to store data will change with the number of usage bits.
decimal: similar to numeric
the third category: approximate floating-point data
float: use 8 bytes to store data. The maximum number of bytes is 53 bits. The range is: - 1.79e + 308 to 1.79e + 308.
Real: use 4 bytes for 24 bits, Number range: - 3.04e + 38 to 3.04e + 38
the fourth category: date time data
DataTime: time range can be expressed from 1753 / 1 / 1 to 9999 / 12 / 31, time can be expressed to 3.33/1000 seconds, using 8 bytes.
smalldatetime: time range can be expressed from 1900 / 1 / 1 to 2079 / 12 / 31, using 4 bytes.
the fifth category: String data
char: length is set, The minimum length is 1 byte and the maximum length is 8000 bytes. The insufficient length will be filled with blanks.
varchar: the length is also set, the minimum length is 1 byte and the maximum length is 8000 bytes, and the trailing blanks will be removed.
text: the length and width are also set, and the maximum length can store 2G data.
the sixth category: Unicode string data
nchar: the length is set, and the minimum length is 1 byte, The maximum length is 4000 bytes. The insufficient length will be filled with white space. It takes 2 bytes to store a character.
nvarchar: the length is set, the minimum is 1 byte, and the maximum is 4000 bytes. The trailing white space will be removed. It takes 2 bytes to store a character.
ntext: the length is set, the minimum is 1 byte, and the maximum is 2G, It takes two bytes to store a character.
seventh category: currency data type
money: record amount range: - 92233720368577.5808 to 92233720368577.5807. It takes eight bytes.
smallmoney: record amount range: - 214748.3648 to 214748.36487. It takes four bytes.
eighth category: tag data
timestamp: this data type is unique in each table! When a record in a table changes, the timestamp field of the record will be automatically updated.
uniqueidentifier: used to identify the only record of many tables in the database.
the ninth category: binary code string data
binary: fixed length binary code string field, the shortest is 1, the longest is 8000.
varbinary: when the data tail is 00, the difference between the binary and the binary is 0, Varbinary will remove
Image: a variable length binary code string, up to 2G
the first category: integer data
bit:bit The data type represents 0, 1 or null, which means true, The storage range is: - 2 ^ 31 to 2 ^ 31-1.
smallint: the storage range is: - 2 ^ 15 to 2 ^ 15-1.
tinyint: it is the smallest integer type with only 1 byte and the range is 0 to ^ 8-1.
the second class: accurate numerical data
numeric: the number can reach 38 bits, The number of bytes used to store data will change with the number of usage bits.
decimal: similar to numeric
the third category: approximate floating-point data
float: use 8 bytes to store data. The maximum number of bytes is 53 bits. The range is: - 1.79e + 308 to 1.79e + 308.
Real: use 4 bytes for 24 bits, Number range: - 3.04e + 38 to 3.04e + 38
the fourth category: date time data
DataTime: time range can be expressed from 1753 / 1 / 1 to 9999 / 12 / 31, time can be expressed to 3.33/1000 seconds, using 8 bytes.
smalldatetime: time range can be expressed from 1900 / 1 / 1 to 2079 / 12 / 31, using 4 bytes.
the fifth category: String data
char: length is set, The minimum length is 1 byte and the maximum length is 8000 bytes. The insufficient length will be filled with blanks.
varchar: the length is also set, the minimum length is 1 byte and the maximum length is 8000 bytes, and the trailing blanks will be removed.
text: the length and width are also set, and the maximum length can store 2G data.
the sixth category: Unicode string data
nchar: the length is set, and the minimum length is 1 byte, The maximum length is 4000 bytes. The insufficient length will be filled with white space. It takes 2 bytes to store a character.
nvarchar: the length is set, the minimum is 1 byte, and the maximum is 4000 bytes. The trailing white space will be removed. It takes 2 bytes to store a character.
ntext: the length is set, the minimum is 1 byte, and the maximum is 2G, It takes two bytes to store a character.
seventh category: currency data type
money: record amount range: - 92233720368577.5808 to 92233720368577.5807. It takes eight bytes.
smallmoney: record amount range: - 214748.3648 to 214748.36487. It takes four bytes.
eighth category: tag data
timestamp: this data type is unique in each table! When a record in a table changes, the timestamp field of the record will be automatically updated.
uniqueidentifier: used to identify the only record of many tables in the database.
the ninth category: binary code string data
binary: fixed length binary code string field, the shortest is 1, the longest is 8000.
varbinary: when the data tail is 00, the difference between the binary and the binary is 0, Varbinary will remove
Image: a variable length binary code string, up to 2G
5. There are many types of numbers, so the common number types are as follows:
integer, that is, integer: int, bigint, smallint, tinyint, etc.
decimal: float, numeric, decimal
integer, that is, integer: int, bigint, smallint, tinyint, etc.
decimal: float, numeric, decimal
6. The decimal data type can store up to 38 numbers, and all numbers can be placed to the right of the decimal point. The decimal data type stores an accurate digital representation; Approximations of values are not stored
the column, variable and parameter of decimal are defined as follows:
· p the sum of the numbers on the left and right of the decimal point, excluding the decimal point. If 123.45, then p = 5, s = 2
specifies the precision or the number of digits that the object can control
· s
specifies the number of decimal places or numbers that can be placed to the right of the decimal point
P and s must abide by the following rules: 0 & lt= s <= p <= 38
the default maximum precision value for numeric and decimal data types is 38. In transact SQL, numeric and decimal data types are functionally equivalent
when the data value must be accurately stored as specified, the decimal data type with decimal can be used to store the number
float and real data
float and real data types are called approximate data types. In terms of approximate digital data types, the use of float and real data follows IEEE 754 standard
approximate number data types do not store the exact values specified for most numbers, they only store the closest values of these values. In many applications, the small difference between the specified value and the stored value is not obvious. But sometimes these differences also deserve attention. Because of the similarity between float and real data types, these data types are not used when precise numerical status is required, such as in financial applications, in operations that need rounding, or in operations that need equivalence checking. In this case, use integer, decimal, money or smallmoney data types
in the where clause search criteria (especially = and & lt& gt; You should avoid using float or real columns. It's better to restrict the use of float and real columns for & gt; Or & lt; The comparison between the two
the IEEE 754 specification provides four rounding modes: round to the nearest value, round up, round down, and round to zero. Microsoft® SQL Server™ Use rounding up. All values must be accurate to a certain precision, but there will be small floating-point value changes. Because the binary representation of floating-point numbers can adopt any one of many legal rounding rules, it is impossible to quantify a floating-point value reliably
convert decimal and numeric data
for decimal and numeric data types, Microsoft & # 174; SQL Server™ Consider each specific combination of precision and decimal places as a different data type. For example, decimal (5,5) and decimal (5,0) are treated as different data types Therefore, the variables used in the stored procere are real or float instead of decimal type)
in the transact SQL statement, constants with decimal points are automatically converted to numeric data values, and the minimum precision and decimal digits are used. For example, the constant 12.345 is converted to a numeric value with a precision of 5 and a decimal place of 3
converting from decimal or numeric to float or real will result in a loss of accuracy. Converting from int, smallint, tinyint, float, real, money, or smallmoney to decimal or numeric results in an overflow
by default, SQL server uses rounding when converting numbers to decimal or numeric values of lower precision and decimal places. However, if the set alert option is on, SQL server will have an error when an overflow occurs. If only precision and decimal places are lost, no error will be generated.
the column, variable and parameter of decimal are defined as follows:
· p the sum of the numbers on the left and right of the decimal point, excluding the decimal point. If 123.45, then p = 5, s = 2
specifies the precision or the number of digits that the object can control
· s
specifies the number of decimal places or numbers that can be placed to the right of the decimal point
P and s must abide by the following rules: 0 & lt= s <= p <= 38
the default maximum precision value for numeric and decimal data types is 38. In transact SQL, numeric and decimal data types are functionally equivalent
when the data value must be accurately stored as specified, the decimal data type with decimal can be used to store the number
float and real data
float and real data types are called approximate data types. In terms of approximate digital data types, the use of float and real data follows IEEE 754 standard
approximate number data types do not store the exact values specified for most numbers, they only store the closest values of these values. In many applications, the small difference between the specified value and the stored value is not obvious. But sometimes these differences also deserve attention. Because of the similarity between float and real data types, these data types are not used when precise numerical status is required, such as in financial applications, in operations that need rounding, or in operations that need equivalence checking. In this case, use integer, decimal, money or smallmoney data types
in the where clause search criteria (especially = and & lt& gt; You should avoid using float or real columns. It's better to restrict the use of float and real columns for & gt; Or & lt; The comparison between the two
the IEEE 754 specification provides four rounding modes: round to the nearest value, round up, round down, and round to zero. Microsoft® SQL Server™ Use rounding up. All values must be accurate to a certain precision, but there will be small floating-point value changes. Because the binary representation of floating-point numbers can adopt any one of many legal rounding rules, it is impossible to quantify a floating-point value reliably
convert decimal and numeric data
for decimal and numeric data types, Microsoft & # 174; SQL Server™ Consider each specific combination of precision and decimal places as a different data type. For example, decimal (5,5) and decimal (5,0) are treated as different data types Therefore, the variables used in the stored procere are real or float instead of decimal type)
in the transact SQL statement, constants with decimal points are automatically converted to numeric data values, and the minimum precision and decimal digits are used. For example, the constant 12.345 is converted to a numeric value with a precision of 5 and a decimal place of 3
converting from decimal or numeric to float or real will result in a loss of accuracy. Converting from int, smallint, tinyint, float, real, money, or smallmoney to decimal or numeric results in an overflow
by default, SQL server uses rounding when converting numbers to decimal or numeric values of lower precision and decimal places. However, if the set alert option is on, SQL server will have an error when an overflow occurs. If only precision and decimal places are lost, no error will be generated.
7. Hello,
$, ￥ are only displayed automatically in the query analyzer when the field type is currency type. We still take the value in the program, that is, there is no such two symbols. If you want to display it, you can add the $, ￥ sign in front of the value
I hope it will help you and I hope it will be adopted. thank you!
$, ￥ are only displayed automatically in the query analyzer when the field type is currency type. We still take the value in the program, that is, there is no such two symbols. If you want to display it, you can add the $, ￥ sign in front of the value
I hope it will help you and I hope it will be adopted. thank you!
8. Let's take a look at the differences between these formats, as shown in the following table:
Settings
description
examples
regular numbers (default values) display the input numbers as they are. 3456.789
thousand separator is used for currency; For negative number, decimal, currency symbol and decimal point position, follow the settings in Windows control panel. ￥ 3457
the euro uses the euro symbol (#8364;), The currency symbol specified in Windows regional settings is not considered&# 8364; 3456.79
at least one digit will be displayed. For negative numbers, decimals, currency symbols and decimal places, follow the settings in the windows "control panel". 3456.79
the standard uses thousands separator; For negative numbers, decimal symbols, and decimal point positions, follow the settings specified in the windows locale. 3456.79
percent multiplied by 100 plus percent sign (%); For negative number, decimal, currency symbol and decimal point position, follow the settings in Windows control panel. 123.00%
the standard scientific counting method was used. 3.46E+03
Settings
description
examples
regular numbers (default values) display the input numbers as they are. 3456.789
thousand separator is used for currency; For negative number, decimal, currency symbol and decimal point position, follow the settings in Windows control panel. ￥ 3457
the euro uses the euro symbol (#8364;), The currency symbol specified in Windows regional settings is not considered&# 8364; 3456.79
at least one digit will be displayed. For negative numbers, decimals, currency symbols and decimal places, follow the settings in the windows "control panel". 3456.79
the standard uses thousands separator; For negative numbers, decimal symbols, and decimal point positions, follow the settings specified in the windows locale. 3456.79
percent multiplied by 100 plus percent sign (%); For negative number, decimal, currency symbol and decimal point position, follow the settings in Windows control panel. 123.00%
the standard scientific counting method was used. 3.46E+03
9. Bit integer the bit data type is integer and its value can only be 0, 1 or null. This data type is used to store data with only two possible values, such as yes or no, true or false, on or off
int integer. Int data type can store integers from - 231 (- 2147483648) to 231 (2147483647). Almost all numerical data stored in the database can use this data type. This data type takes up four bytes in the database
smallint integer. Smallint data type can store integers from - 215 (- 32768) to 215 (32767). This kind of data type is very useful for storing some numerical data which is often limited in a specific range. This data type takes up 2 bytes of space in the database
tinyint integer. Tinyint data type can store integers from 0 to 255. It's useful when you're only going to store a limited number of values. This kind of data type takes up one byte in the database
numeric precise numeric data type is the same as decimal data type
decimal precise numeric decimal data type can be used to store numerical data with fixed precision and range from - 1038-1 to 1038-1. When using this data type, you must specify the range and precision. The range is the total number of digits that can be stored around the decimal point. Precision is the number of digits stored to the right of the decimal point
money money data type is used to represent money and currency value. This data type can store data from - 922 billion to 922 billion, accurate to one ten thousandth of the monetary unit
smallmoney money data type is used to represent money and monetary value. This data type can store data from - 214748.3648 to 214748.3647, accurate to one ten thousandth of the monetary unit
float approximate numeric type float data type is an approximate numeric type for floating-point numbers. Floating point numbers are approximate because not all numbers in their range can be expressed accurately. Floating point number can be any number from - 1.79e + 308 to 1.79e + 308
Real approximate numerical type. Like floating point number, real data type is approximate numerical type. It can represent floating-point numbers with values between - 3.40e + 38 and 3.40e + 38.
datetime datetime data type is used to represent date and time. This data type stores all the date and time data from January 1, 1753 to December 31, 9999, accurate to one hundredth of a second or 3.33 millisecond
smalldatetime date time type smalldatetime data type is used to represent the date and time from January 1, 1900 to June 6, 2079, A cursor data type is a special data type, which contains a reference to a cursor. This kind of data type is used in stored proceres, and cannot be used when creating tables
timestamp special data type is a special data type, which is used to create a unique number within the scope of a database. There can only be one timestamp column in a table. Each time a row is inserted or modified, the value of the
timestamp column changes. 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
uniqueidentifier special data type. The uniqueidentifier data type is used to store a globally unique identifier, that is, a guid. The GUID is indeed globally unique. This number has little chance to be reconstructed in another system. A column with a unique identifier can be initialized by using the new function or converting a string to a unique identifier. The char data type is used to store fixed length non-uniform coded data of a specified length. When defining a column as this type, you must specify the column length. This data type is useful when you always know the length of the data to be stored. For example, when you store data in the format of postcode plus 4 characters, you know that 10 characters are always needed. The maximum column width of this data type is 8000 characters.
varchar character type varchar data type is used to store non-uniform coded character data just like char type. Unlike char, this data type is variable length. When defining a column as the data type, you need to specify the maximum length of the column. The biggest difference between it and char data type is that the length of storage is not the column length, but the length of data
text character type. Text data type is used to store a large number of non-uniform coded character data. This data type can have at most 23.1-1 or 2 billion characters
nchar unified coding character type nchar data type is used to store fixed length unified coding character type data. Unified encoding uses a double byte structure to store each character instead of a single byte (in the case of plain text). It allows a large number of extended characters. This data type can store 4000 characters and double the byte space used
nvarchar unified encoding character type nvarchar data type is used as variable length unified encoding character type data. This data type can store 4000 characters, and the byte space used is doubled
ntext unified encoding character type. Ntext data type is used to store a large number of unified encoding character type data. This data type can store 230-1 or nearly 1 billion characters, and the byte space used is doubled
binary data type binary data type is used to store fixed length binary data up to 8000 bytes. When the contents of the input table are close to the same length, you should use this data type
varbinary binary data type to store variable length binary data up to 8000 bytes. When the content size of the input table is variable, you should use this data type
image binary data type to store variable length binary data, up to 231-1 or about 2 billion bytes
data type of SQL Server
data class is an attribute of data, which indicates the type of information represented by data. Any computer language defines its own data type. Of course, different programming languages have different characteristics, and the types and names of data types defined are more or less different. SQL server provides 25 data types:
· binary [(n)]
· varbinary [(n)]
· char [(n)]
· varchar [(n)]
· nchar [(n)]
· nvarchar [(n)]
· datetime
· smalldatetime
· decimal [(P [, S])]
· numeric [(P [, S]), s] )]
· float [(n)]
· real
· int
· smallint
· tinyint
· money
· smallmoney
· bit
· cursor
· sysname
· timestamp
· uniqueidentifier
· text
· image
· ntext
(1) binary data types
binary data include binary data, binary data and binary data Varbinary and image
binary data types can be either fixed length (binary) or variable length
binary [(n)] is n-bit fixed binary data. The value range of n is from 1 to 8000. Its storage size is n + 4 bytes
varbinary [(n)] is n-bit variable length binary data. The value range of n is from 1 to 8000. Its storage size is n + 4 bytes, not n bytes
the data stored in the image data type is stored as a bit string, not interpreted by SQL server, but must be interpreted by the application. For example, applications can store data in image data types using BMP, Tief, GIF, and JPEG formats
(2) character data types
character data types include char, varchar and text
character data is any combination of letters, symbols and numbers
varchar is variable length character data, whose length is less than 8KB. Char is fixed length character data, and its maximum length is 8KB. More than 8KB of ASCII data can be stored with text data type. For example, because HTML documents are all ASCII characters, and in general, the length is more than 8KB, these documents can be stored in SQL server as text data type
(3) Unicode data types
Unicode data types include nchar, nvarchar and ntext
in Microsoft SQL server, traditional non Unicode data types allow characters defined by a specific character set. During SQL server installation, you are allowed to select a character set. With the Unicode data type, any character defined by the Unicode standard can be stored in the column. In the Unicode standard, all characters defined in various character sets are included. Using Unicode data types can overcome twice the size of the scents used by non Unicode data types
in SQL server, Unicode data is stored as nchar, nvarchar and ntext data types. Columns stored with this character type can store characters in multiple character sets. When the length of the column changes, the nvarchar character type should be used, which can store up to 4000 characters. When the length of the column is fixed, the nchar character type should be used. Similarly, up to 4000 characters can be stored. When using the ntext data type, the column can store more than 4000 characters
(4) date and time data types
date and time data types include datetime and smalldatetime
date and time data types consist of valid date and time. For example, valid date and time data include "4 / 01 / 98 12:15:00:00 PM" and "1:28:29:15:01am 8 / 17 / 98". The former data type is date before, time after, and the latter is instant before, date after. In Microsoft SQL server, when the date and time data types include datetime and smalldatetime, the date range stored is from January 1, 1753 to December 31, 9999 (each value requires 8 storage bytes). When using the smalldatetime data type, the date range stored is from January 1, 1900 to December 31, 2079 (each value requires 4 storage bytes)
date format can be set. The commands for setting date format are as follows:
set dateformat {format | @ format_ Var |
where format | @ format_ VaR is the order of the dates. Valid parameters include mdy, DMY, YMD, YDM, MyD and Dym. By default, the date format is mdy
for example, when set dateformat YMD is executed, the date format is in the form of month, year and day; When set dateformat DMY is executed, the date format is in the form of day, month and year. Digital data types include positive and negative numbers, decimal (floating-point) numbers, and integers
integers are composed of positive and negative integers, such as 39, 25, 0-2, and 33967. In Microsoft SQL server, the data types of integer storage are int, smallint and tinyint. The range of data stored by int data type is larger than that of data stored by smallint data type,
int integer. Int data type can store integers from - 231 (- 2147483648) to 231 (2147483647). Almost all numerical data stored in the database can use this data type. This data type takes up four bytes in the database
smallint integer. Smallint data type can store integers from - 215 (- 32768) to 215 (32767). This kind of data type is very useful for storing some numerical data which is often limited in a specific range. This data type takes up 2 bytes of space in the database
tinyint integer. Tinyint data type can store integers from 0 to 255. It's useful when you're only going to store a limited number of values. This kind of data type takes up one byte in the database
numeric precise numeric data type is the same as decimal data type
decimal precise numeric decimal data type can be used to store numerical data with fixed precision and range from - 1038-1 to 1038-1. When using this data type, you must specify the range and precision. The range is the total number of digits that can be stored around the decimal point. Precision is the number of digits stored to the right of the decimal point
money money data type is used to represent money and currency value. This data type can store data from - 922 billion to 922 billion, accurate to one ten thousandth of the monetary unit
smallmoney money data type is used to represent money and monetary value. This data type can store data from - 214748.3648 to 214748.3647, accurate to one ten thousandth of the monetary unit
float approximate numeric type float data type is an approximate numeric type for floating-point numbers. Floating point numbers are approximate because not all numbers in their range can be expressed accurately. Floating point number can be any number from - 1.79e + 308 to 1.79e + 308
Real approximate numerical type. Like floating point number, real data type is approximate numerical type. It can represent floating-point numbers with values between - 3.40e + 38 and 3.40e + 38.
datetime datetime data type is used to represent date and time. This data type stores all the date and time data from January 1, 1753 to December 31, 9999, accurate to one hundredth of a second or 3.33 millisecond
smalldatetime date time type smalldatetime data type is used to represent the date and time from January 1, 1900 to June 6, 2079, A cursor data type is a special data type, which contains a reference to a cursor. This kind of data type is used in stored proceres, and cannot be used when creating tables
timestamp special data type is a special data type, which is used to create a unique number within the scope of a database. There can only be one timestamp column in a table. Each time a row is inserted or modified, the value of the
timestamp column changes. 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
uniqueidentifier special data type. The uniqueidentifier data type is used to store a globally unique identifier, that is, a guid. The GUID is indeed globally unique. This number has little chance to be reconstructed in another system. A column with a unique identifier can be initialized by using the new function or converting a string to a unique identifier. The char data type is used to store fixed length non-uniform coded data of a specified length. When defining a column as this type, you must specify the column length. This data type is useful when you always know the length of the data to be stored. For example, when you store data in the format of postcode plus 4 characters, you know that 10 characters are always needed. The maximum column width of this data type is 8000 characters.
varchar character type varchar data type is used to store non-uniform coded character data just like char type. Unlike char, this data type is variable length. When defining a column as the data type, you need to specify the maximum length of the column. The biggest difference between it and char data type is that the length of storage is not the column length, but the length of data
text character type. Text data type is used to store a large number of non-uniform coded character data. This data type can have at most 23.1-1 or 2 billion characters
nchar unified coding character type nchar data type is used to store fixed length unified coding character type data. Unified encoding uses a double byte structure to store each character instead of a single byte (in the case of plain text). It allows a large number of extended characters. This data type can store 4000 characters and double the byte space used
nvarchar unified encoding character type nvarchar data type is used as variable length unified encoding character type data. This data type can store 4000 characters, and the byte space used is doubled
ntext unified encoding character type. Ntext data type is used to store a large number of unified encoding character type data. This data type can store 230-1 or nearly 1 billion characters, and the byte space used is doubled
binary data type binary data type is used to store fixed length binary data up to 8000 bytes. When the contents of the input table are close to the same length, you should use this data type
varbinary binary data type to store variable length binary data up to 8000 bytes. When the content size of the input table is variable, you should use this data type
image binary data type to store variable length binary data, up to 231-1 or about 2 billion bytes
data type of SQL Server
data class is an attribute of data, which indicates the type of information represented by data. Any computer language defines its own data type. Of course, different programming languages have different characteristics, and the types and names of data types defined are more or less different. SQL server provides 25 data types:
· binary [(n)]
· varbinary [(n)]
· char [(n)]
· varchar [(n)]
· nchar [(n)]
· nvarchar [(n)]
· datetime
· smalldatetime
· decimal [(P [, S])]
· numeric [(P [, S]), s] )]
· float [(n)]
· real
· int
· smallint
· tinyint
· money
· smallmoney
· bit
· cursor
· sysname
· timestamp
· uniqueidentifier
· text
· image
· ntext
(1) binary data types
binary data include binary data, binary data and binary data Varbinary and image
binary data types can be either fixed length (binary) or variable length
binary [(n)] is n-bit fixed binary data. The value range of n is from 1 to 8000. Its storage size is n + 4 bytes
varbinary [(n)] is n-bit variable length binary data. The value range of n is from 1 to 8000. Its storage size is n + 4 bytes, not n bytes
the data stored in the image data type is stored as a bit string, not interpreted by SQL server, but must be interpreted by the application. For example, applications can store data in image data types using BMP, Tief, GIF, and JPEG formats
(2) character data types
character data types include char, varchar and text
character data is any combination of letters, symbols and numbers
varchar is variable length character data, whose length is less than 8KB. Char is fixed length character data, and its maximum length is 8KB. More than 8KB of ASCII data can be stored with text data type. For example, because HTML documents are all ASCII characters, and in general, the length is more than 8KB, these documents can be stored in SQL server as text data type
(3) Unicode data types
Unicode data types include nchar, nvarchar and ntext
in Microsoft SQL server, traditional non Unicode data types allow characters defined by a specific character set. During SQL server installation, you are allowed to select a character set. With the Unicode data type, any character defined by the Unicode standard can be stored in the column. In the Unicode standard, all characters defined in various character sets are included. Using Unicode data types can overcome twice the size of the scents used by non Unicode data types
in SQL server, Unicode data is stored as nchar, nvarchar and ntext data types. Columns stored with this character type can store characters in multiple character sets. When the length of the column changes, the nvarchar character type should be used, which can store up to 4000 characters. When the length of the column is fixed, the nchar character type should be used. Similarly, up to 4000 characters can be stored. When using the ntext data type, the column can store more than 4000 characters
(4) date and time data types
date and time data types include datetime and smalldatetime
date and time data types consist of valid date and time. For example, valid date and time data include "4 / 01 / 98 12:15:00:00 PM" and "1:28:29:15:01am 8 / 17 / 98". The former data type is date before, time after, and the latter is instant before, date after. In Microsoft SQL server, when the date and time data types include datetime and smalldatetime, the date range stored is from January 1, 1753 to December 31, 9999 (each value requires 8 storage bytes). When using the smalldatetime data type, the date range stored is from January 1, 1900 to December 31, 2079 (each value requires 4 storage bytes)
date format can be set. The commands for setting date format are as follows:
set dateformat {format | @ format_ Var |
where format | @ format_ VaR is the order of the dates. Valid parameters include mdy, DMY, YMD, YDM, MyD and Dym. By default, the date format is mdy
for example, when set dateformat YMD is executed, the date format is in the form of month, year and day; When set dateformat DMY is executed, the date format is in the form of day, month and year. Digital data types include positive and negative numbers, decimal (floating-point) numbers, and integers
integers are composed of positive and negative integers, such as 39, 25, 0-2, and 33967. In Microsoft SQL server, the data types of integer storage are int, smallint and tinyint. The range of data stored by int data type is larger than that of data stored by smallint data type,
10. 1. Oracle supports multiple operating systems, SQL server only supports windows
2. Oracle gives DBA more flexibility. You can adjust the parameters according to the actual situation to make your application perform best; SQL server, on the contrary, is easy to use
Architecture
the file architecture of Oracle is as follows:
data file. DBF (real data)
log file. RDO
control file. CTL
parameter file. Ora
the file architecture of SQL server is as follows:
. MDF (data dictionary)
. NDF (data file)
. LDF (log file)
www.2cto.com
Oracle storage structure: < br />There are two block parameters pctfree (fill factor) and pctused (reuse factor) in Oracle, which can control the block to determine when there is a block itself and when there is not enough space to accept new information (analysis mechanism of block storage)
this can rece the possibility of data row connection and row migration. The block size can be set (OLTP block and DSS block)
in Oracle, continuous blocks can be divided into regions, and dynamically allocated regions (the allocation of regions can be equal or self growing) can rece the number of space allocation
in Oracle, the table can be divided into multiple segments, and the segments are composed of multiple regions, and each segment can specify which table space to allocate in (the types of segments are divided into data segment, index segment, index segment) Rollback segment, temporary segment and cash segment. Oracle can also partition tables and physically separate data on disk according to user-defined business rules, conditions or specifications
this greatly reces the possibility of disk contention
Oracle has seven basic table spaces:
· system table space (to store the information needed by data dictionary and data management)
· RBS rollback table space
· temp temporary table space
· tools interactive table space
· users default table space
· indx index table space
· dbsys data table space
different data are placed in different table spaces (number table space) According to the dictionary and the real data are stored separately), in Oracle, the base table (storage system parameter information) is encrypted storage, and no one can access it. It can only be viewed through the user's visual view<
sql server storage structure
takes pages as the minimum allocation unit, and each page is 8K (uncontrollable, lack of analysis mechanism for page storage). Eight consecutive pages can be formed into an "extension" to further rece the resource consumption ring allocation In SQL server, data is stored in tables, while tables are stored in databases
sql server has five basic databases: www.2cto.com
· master
· mode
· tempdb
· MSDB
· pubs
the real data is stored with the data dictionary. There is no security mechanism for system parameter information
Oracle login management:
· system / Manager (initial account)
· sys / change_ on_ Install
· install / Oracle (installation account)
· Scott / Tiger (sample database for testing)
there are only three system users in Oracle by default, and Oracle logs in through users
sql server login management:
· SQL server authentication
· windows authentication
in SQL server, the database is connected through windows user account or SQL server authentication
sql is not a language, but a way to transfer instructions to Oracle database
null in SQL is neither a character nor a number, it is the default data. Oracle provides NVL function to solve this problem<
string connection is string1 ‖ string2 in Oracle and string1 + string2 in SQL.
set operation: only Union (Union operation) is available in SQL, while minus (difference operation), intersect (intersection operation) and Union (Union operation) are available in Oracle
index: the index of SQL is divided into clustered index and non clustered index, including full-text index
Oracle indexes include: B + tree index, bitmap index, function index, reverse order index,
primary key index, hash index and local index. www.2cto.com The data types of Oracle are complex, including basic data type, derived data type, column object type, table object type and structure type
the data in SQL is relatively simple, only some simple basic data types can not provide transaction operation
in SQL, if a transaction is wrong from the beginning to the end, it will be rolled back before the beginning
in Oracle, it uses fixed-point rollback, that is, the transaction is functionalized and the error is accurately located, the savepoint is marked with savepoint, and the rollback error is marked with rollback
in SQL, there is only one kind of loop, which is inflexible to use. In Oracle, there are many kinds of loops (loop loop, while loop, for loop)
in SQL, the use of cursors is complex. You can't open more than one cursor at the same time because it has only one global variable @ @ fast_ Moreover, it is troublesome to declare the cursor, and the memory will not be cleared when closing the cursor; Cursors in Oracle are single cursors that are unified to improve the speed. They can be read asynchronously by multiple users. Moreover, the declaration is relatively simple. Just a declare cursor name is select statement is enough
fault tolerance mechanism: the error mechanism in SQL is complex and does not provide an error description; There are three types of fault tolerance in Oracle, one is pre-defined error, one is non pre-defined error, and the other is user-defined error. Among the user-defined errors, two of them are not available in SQL, that is, sqlcode error number and sqlerrm error description
file architecture: in SQL, there are. MDF (main data file),. NDF (extended file, multiple files),
. LDF (log file, multiple files, which are stored in online redo log data). One drawback of the log file here is that if the log file is small and exquisite, SQL will automatically stop running and wait for manual intervention, So we should often monitor the log to ensure the stable operation of the system. Oracle includes. DBF (main data file),. RDO (log file, used for catastrophic data backup),. CTL (control file, which maps the physical files of the database to the logical table space in the data dictionary and the redo log file to ensure the consistency of data),. Ora (parameter file)
SQL can only be used to back up the local database, but it can't be backed up online, and the backup compression is very low, which takes up a lot of space; Oracle provides seven kinds of backup mechanism, which has online backup function and is the backup machine of Zhimen
Oracle has three logs by default, which are written to No.1 log first, then to No.2 log when it is full, and then to No.3 log when No.2 log is full. When No.3 log is full, Oracle will automatically prepare the contents of No.1 log, and then clear it and write new log information. Moreover, Oracle log has multiple reuse functions, so we can create multiple images for the log, Writing the same log information to multiple log files at the same time can prevent data loss caused by disk damage
sql server can only assign one log file to each database. And when the log is full, the log will stop working, waiting for human intervention, and cannot be reused automatically. www.2cto.com
the control file of Oracle records the physical location and validity of the database file. Every time the Oracle system is opened, the data will be automatically verified to check its validity and prevent the loss of data, which reflects the strictness of Oracle's data management
sql server does not have this security mechanism. The system only searches whether the data exists after using the database information
the parameter file init.ora of Oracle is adjustable. We can adjust Oracle's usage of system resources according to the size of the database, so as to achieve the most reasonable resource allocation and the best performance of Oracle
the configuration parameters of SQL server are internally determined and cannot be adjusted. Its allocation of system resources is fixed and not controlled by users, so it can not handle the needs of a large number of users at the same time, which limits it to be a small and medium-sized database
Oracle uses block as the minimum storage unit and area as the unit to allocate space. Users can define the size of the block according to their needs, and the area can be allocated by equal or incremental, which can greatly rece the I / O operation of the system and improve the performance of the database
in SQL server, the space allocation is based on pages or 8 pages, which cannot be intervened manually. When frequent storage or large amount of data is encountered, the system will frequently perform I / O operations, which makes the work inefficient
the SQL statement writing specification in Oracle provides complete functions and data types. Oracle provides a sound error mechanism, users can easily get the error location and error reason to modify. Users can also define the required error types to check for logical errors. The conversion of data type and the definition of cursor in Oracle are also very convenient. Therefore, our code writing and maintenance is much more convenient than SQL server
sql server uses C-like language, which is not easy to maintain and view code. SQL server contains too few data types to define structure, array and object. SQL server can not define the process mechanism, and the type conversion is also very inconvenient. The definition of cursors in SQL server is very complex, and when multiple cursors defined by users are opened at the same time, the system has only one cursor variable, which is a serious defect in SQL server
in Oracle, users can define the fill factor (the percentage of free space) and reuse factor (when the storage space of the block falls below this percentage, the block will be marked as available again) when creating the block according to the data needs. Users can set the appropriate fill factor and idle factor according to the change frequency of records and the size of data
sql server has only fill factor, but its definition is just opposite to Oracle, which defines the percentage of remaining available space. There is no reuse factor in SQL server. When the data in the page is lower than the fill factor, it is impossible to insert new data in the remaining space, but the page is still marked as available. When the system wants to write new data, it needs to access the page and waste time. This kind of page is called waste page, which will waste a lot of time when the system inserts new data to find available pages
when creating a table in Oracle, users can precisely define the table space where the data is stored, and even store a table separately in multiple table spaces. In this way, the tables with large amount of data can be stored separately according to some fields, which will bring high efficiency to the query< br /> www.2cto.com
with the rapid development of information technology, data processing is required not only in quantity but also in quality
the stability of the operating system is very important for the database. On the operational platform of the database, Oracle can run on all the mainstream platforms. Oracle database adopts the open strategy goal, which enables customers to choose a solution most suitable for their specific needs. Customers can use many kinds of third-party applications and tools. It is a great support for developers. However, SQL server can only run on windows
2. Oracle gives DBA more flexibility. You can adjust the parameters according to the actual situation to make your application perform best; SQL server, on the contrary, is easy to use
Architecture
the file architecture of Oracle is as follows:
data file. DBF (real data)
log file. RDO
control file. CTL
parameter file. Ora
the file architecture of SQL server is as follows:
. MDF (data dictionary)
. NDF (data file)
. LDF (log file)
www.2cto.com
Oracle storage structure: < br />There are two block parameters pctfree (fill factor) and pctused (reuse factor) in Oracle, which can control the block to determine when there is a block itself and when there is not enough space to accept new information (analysis mechanism of block storage)
this can rece the possibility of data row connection and row migration. The block size can be set (OLTP block and DSS block)
in Oracle, continuous blocks can be divided into regions, and dynamically allocated regions (the allocation of regions can be equal or self growing) can rece the number of space allocation
in Oracle, the table can be divided into multiple segments, and the segments are composed of multiple regions, and each segment can specify which table space to allocate in (the types of segments are divided into data segment, index segment, index segment) Rollback segment, temporary segment and cash segment. Oracle can also partition tables and physically separate data on disk according to user-defined business rules, conditions or specifications
this greatly reces the possibility of disk contention
Oracle has seven basic table spaces:
· system table space (to store the information needed by data dictionary and data management)
· RBS rollback table space
· temp temporary table space
· tools interactive table space
· users default table space
· indx index table space
· dbsys data table space
different data are placed in different table spaces (number table space) According to the dictionary and the real data are stored separately), in Oracle, the base table (storage system parameter information) is encrypted storage, and no one can access it. It can only be viewed through the user's visual view<
sql server storage structure
takes pages as the minimum allocation unit, and each page is 8K (uncontrollable, lack of analysis mechanism for page storage). Eight consecutive pages can be formed into an "extension" to further rece the resource consumption ring allocation In SQL server, data is stored in tables, while tables are stored in databases
sql server has five basic databases: www.2cto.com
· master
· mode
· tempdb
· MSDB
· pubs
the real data is stored with the data dictionary. There is no security mechanism for system parameter information
Oracle login management:
· system / Manager (initial account)
· sys / change_ on_ Install
· install / Oracle (installation account)
· Scott / Tiger (sample database for testing)
there are only three system users in Oracle by default, and Oracle logs in through users
sql server login management:
· SQL server authentication
· windows authentication
in SQL server, the database is connected through windows user account or SQL server authentication
sql is not a language, but a way to transfer instructions to Oracle database
null in SQL is neither a character nor a number, it is the default data. Oracle provides NVL function to solve this problem<
string connection is string1 ‖ string2 in Oracle and string1 + string2 in SQL.
set operation: only Union (Union operation) is available in SQL, while minus (difference operation), intersect (intersection operation) and Union (Union operation) are available in Oracle
index: the index of SQL is divided into clustered index and non clustered index, including full-text index
Oracle indexes include: B + tree index, bitmap index, function index, reverse order index,
primary key index, hash index and local index. www.2cto.com The data types of Oracle are complex, including basic data type, derived data type, column object type, table object type and structure type
the data in SQL is relatively simple, only some simple basic data types can not provide transaction operation
in SQL, if a transaction is wrong from the beginning to the end, it will be rolled back before the beginning
in Oracle, it uses fixed-point rollback, that is, the transaction is functionalized and the error is accurately located, the savepoint is marked with savepoint, and the rollback error is marked with rollback
in SQL, there is only one kind of loop, which is inflexible to use. In Oracle, there are many kinds of loops (loop loop, while loop, for loop)
in SQL, the use of cursors is complex. You can't open more than one cursor at the same time because it has only one global variable @ @ fast_ Moreover, it is troublesome to declare the cursor, and the memory will not be cleared when closing the cursor; Cursors in Oracle are single cursors that are unified to improve the speed. They can be read asynchronously by multiple users. Moreover, the declaration is relatively simple. Just a declare cursor name is select statement is enough
fault tolerance mechanism: the error mechanism in SQL is complex and does not provide an error description; There are three types of fault tolerance in Oracle, one is pre-defined error, one is non pre-defined error, and the other is user-defined error. Among the user-defined errors, two of them are not available in SQL, that is, sqlcode error number and sqlerrm error description
file architecture: in SQL, there are. MDF (main data file),. NDF (extended file, multiple files),
. LDF (log file, multiple files, which are stored in online redo log data). One drawback of the log file here is that if the log file is small and exquisite, SQL will automatically stop running and wait for manual intervention, So we should often monitor the log to ensure the stable operation of the system. Oracle includes. DBF (main data file),. RDO (log file, used for catastrophic data backup),. CTL (control file, which maps the physical files of the database to the logical table space in the data dictionary and the redo log file to ensure the consistency of data),. Ora (parameter file)
SQL can only be used to back up the local database, but it can't be backed up online, and the backup compression is very low, which takes up a lot of space; Oracle provides seven kinds of backup mechanism, which has online backup function and is the backup machine of Zhimen
Oracle has three logs by default, which are written to No.1 log first, then to No.2 log when it is full, and then to No.3 log when No.2 log is full. When No.3 log is full, Oracle will automatically prepare the contents of No.1 log, and then clear it and write new log information. Moreover, Oracle log has multiple reuse functions, so we can create multiple images for the log, Writing the same log information to multiple log files at the same time can prevent data loss caused by disk damage
sql server can only assign one log file to each database. And when the log is full, the log will stop working, waiting for human intervention, and cannot be reused automatically. www.2cto.com
the control file of Oracle records the physical location and validity of the database file. Every time the Oracle system is opened, the data will be automatically verified to check its validity and prevent the loss of data, which reflects the strictness of Oracle's data management
sql server does not have this security mechanism. The system only searches whether the data exists after using the database information
the parameter file init.ora of Oracle is adjustable. We can adjust Oracle's usage of system resources according to the size of the database, so as to achieve the most reasonable resource allocation and the best performance of Oracle
the configuration parameters of SQL server are internally determined and cannot be adjusted. Its allocation of system resources is fixed and not controlled by users, so it can not handle the needs of a large number of users at the same time, which limits it to be a small and medium-sized database
Oracle uses block as the minimum storage unit and area as the unit to allocate space. Users can define the size of the block according to their needs, and the area can be allocated by equal or incremental, which can greatly rece the I / O operation of the system and improve the performance of the database
in SQL server, the space allocation is based on pages or 8 pages, which cannot be intervened manually. When frequent storage or large amount of data is encountered, the system will frequently perform I / O operations, which makes the work inefficient
the SQL statement writing specification in Oracle provides complete functions and data types. Oracle provides a sound error mechanism, users can easily get the error location and error reason to modify. Users can also define the required error types to check for logical errors. The conversion of data type and the definition of cursor in Oracle are also very convenient. Therefore, our code writing and maintenance is much more convenient than SQL server
sql server uses C-like language, which is not easy to maintain and view code. SQL server contains too few data types to define structure, array and object. SQL server can not define the process mechanism, and the type conversion is also very inconvenient. The definition of cursors in SQL server is very complex, and when multiple cursors defined by users are opened at the same time, the system has only one cursor variable, which is a serious defect in SQL server
in Oracle, users can define the fill factor (the percentage of free space) and reuse factor (when the storage space of the block falls below this percentage, the block will be marked as available again) when creating the block according to the data needs. Users can set the appropriate fill factor and idle factor according to the change frequency of records and the size of data
sql server has only fill factor, but its definition is just opposite to Oracle, which defines the percentage of remaining available space. There is no reuse factor in SQL server. When the data in the page is lower than the fill factor, it is impossible to insert new data in the remaining space, but the page is still marked as available. When the system wants to write new data, it needs to access the page and waste time. This kind of page is called waste page, which will waste a lot of time when the system inserts new data to find available pages
when creating a table in Oracle, users can precisely define the table space where the data is stored, and even store a table separately in multiple table spaces. In this way, the tables with large amount of data can be stored separately according to some fields, which will bring high efficiency to the query< br /> www.2cto.com
with the rapid development of information technology, data processing is required not only in quantity but also in quality
the stability of the operating system is very important for the database. On the operational platform of the database, Oracle can run on all the mainstream platforms. Oracle database adopts the open strategy goal, which enables customers to choose a solution most suitable for their specific needs. Customers can use many kinds of third-party applications and tools. It is a great support for developers. However, SQL server can only run on windows
Hot content