SQL Server data types

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.

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, and the system stores it in 4 bytes;
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.
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: Starting SQL Server with maximum data precision 38
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./*

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.
Can hold up to 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 the coding conflict.
3. VARCHAR
The varchar data type is defined in the form Varcharn). 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 be stored from 0 o'clock A.D. January 1, 1753 to December 31, 9999 23:59 59 seconds

Original link: SQL Server data Type Introduction

The correspondence between database SQL Sserver and data types in C #

Database	C # programs
Int	Int32
Text	String
bigint	Int64
Binary	System.byte[]
Bit	Boolean
Char	String
Datetime	System.DateTime
Decimal	System.Decimal
Float	System.Double
Image	System.byte[]
Money	System.Decimal
NChar	String
ntext	String
Numeric	System.Decimal
nvarchar	String
Real	System.Single
smalldatetime	System.DateTime
smallint	Int16
SmallMoney	System.Decimal
Timestamp	System.DateTime
tinyint	System.Byte
varbinary	System.byte[]
varchar	String
Variant	Object
Unique identifier	System.Guid

Can be written as a method to convert

<summary>////data types in the database and C + +//</summary>//<param name= "type" ></param>///<returns ></returns> private String Changetocsharptype (String type) {string reval=string. Empty; Switch (type.  ToLower ()) {case "int": reval= "Int32", Break, Case "text": reval= "String", break, Case "bigint": reval= "Int64"; Case "binary": reval= "system.byte[]"; Break Case "bit": reval= "Boolean"; Break Case "char": reval= "String"; Break Case "datetime": reval= "System.DateTime"; Break Case "decimal": reval= "System.Decimal"; Break Case "float": reval= "System.Double"; Break Case "image": reval= "system.byte[]"; Break Case "Money": reval= "System.Decimal"; Break Case "nchar": reval= "String"; Break Case "ntext": reval= "String"; Break Case "Numeric": reval= "System.Decimal"; Break case "nvarchar": reval= "String"; Break Case "real": reval= "System.Single"; Break Case "smalldatetime": reval= "System.DateTime"; Break Case "smallint": reval= "Int16"; Break Case "smallmoney": reval= "System.Decimal"; Break Case "timestamp": reval= "System.DateTime"; Break Case "tinyint": reval= "System.Byte"; Break Case "uniqueidentifier": reval= "System.Guid"; Break Case "varbinary": reval= "system.byte[]"; Break Case "varchar": reval= "String"; Break Case "Variant": reval= "Object"; Break Default:reval= "String"; Break } return Reval;}

　　

SQL Server data types