SQL Server data type _mssql

data types for 1.SQL server

Data class is a property of data that represents the type of information that the data represents. Any computer language defines its own data type. Of course, different programming languages have different characteristics, the types of data defined and the names are more or less different. SQL Server provides 25 types of data:

• Binary [(N)]
• Varbinary [(N)]
• Char [(N)]
• varchar[(N)]
• nchar[(N)]
• nvarchar[(N)]
• Datetime
• smalldatetime
• decimal[(P[,s])]
• numeric[(P[,s])]
• float[(N)]
• Real
• Int
• Smallint
• Tinyint
• Money
• SmallMoney
• Bit
• Cursor
• Sysname
• Timestamp
• uniqueidentifier
• Text
• Image
• Ntext

(1) binary data type

Binary data includes Binary, Varbinary and Image
The Binary data type can be either fixed-length (Binary) or variable-length.
binary[(n)] is n-bit fixed binary data. Where the value of n ranges from 1 to 8000. The size of its storage scenting is n + 4 bytes.
varbinary[(N)] is an n-bit variable-length binary data. Where the value of n ranges from 1 to 8000. The size of its storage scenting is n + 4 bytes, not n bytes.
The data stored in the Image data type is stored in a bit string, not interpreted by SQL Server, and must be interpreted by the application. For example, an application can store data in an Image data type using BMP, Tief, GIF, and JPEG formats.

(2) character data type

The types of character data include Char,varchar and Text
Character data is data that is any combination of letters, symbols, and numbers.
Varchar is a variable length character data that is no longer than 8KB. Char is a fixed-length character data with a maximum length of 8KB. More than 8KB of ASCII data can be stored using the text data type. For example, because Html documents are all ASCII characters and, in general, longer than 8KB, these documents can be stored in SQL Server with the Text data type.

(3) Unicode data type

Unicode data types include Nchar,nvarchar and ntext
In Microsoft SQL Server, traditional non-Unicode data types allow you to use characters defined by a particular character set. Allows you to select a character set during SQL Server Setup. With Unicode data types, you can store any character defined by the Unicode standard in a column. In the Unicode standard, all the characters defined in various character sets are included. Using Unicode data types, the prevailing scenting is twice times the size of the scenting used for non-Unicode data types.
In SQL Server, Unicode data is stored in Nchar, Nvarchar, and Ntext data types. Columns stored with this type of character can store characters from multiple character sets. When the length of a column changes, you should use the nvarchar character type, at which time you can store up to 4,000 characters. When the length of a column is fixed, you should use the Nchar character type, and you can store up to 4,000 characters. When you use the Ntext data type, the column can store more than 4,000 characters.

(4) Date and time data types

Date and time data types include Datetime and smalldatetimeTwo types of
Date and time data types are made up of valid dates and times. For example, valid date and time data include "4/01/98 12:15:00:00:00 PM" and "1:28:29:15:01am 8/17/98". The previous data type is the date before, the time in the latter one data type is a moment ago, the date after. In Microsoft SQL Server, date and time data types include datetime and smalldatetime two types when the date range is stored starting from January 1, 1753 and ending on December 31, 9999 (each value requires 8 Storage bytes). When using the smalldatetime data type, the date range stored is January 1, 1900, ending on December 31, 2079 (4 storage bytes per value is required).
The format of the date can be set. The commands for setting date formats are as follows:
Set DateFormat {format | @format _var|
of which, 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 the set DateFormat YMD is executed, the format of the date is the month-day form, and when the set DateFormat dmy is executed, the date is in the form of the sun and the moon.

(5) numeric data types

Numeric data contains only numbers. Numeric data types include positive and negative numbers, decimals (floating-point numbers), and integers
integers consist of positive integers and negative integers, such as 39, 25, 0-2, and 33967. In Micrsoft SQL Server, the data type of an integer store is Int,smallint and Tinyint。 The INT data type stores data in a range greater than the range in which the Smallint data type stores the data, whereas the Smallint type stores the data in a range larger than the tinyint data type stores the data. Using Int data The dog Shake stores data from 2 147 483 648 to 2 147 483 647 (each value requires 4 bytes of storage space). When using the Smallint data type, the range of storage data ranges from 32 768 to 32 767 (each value requires 2 bytes of storage). When you use the tinyint data type, the range of stored data is from 0 to 255 (1 bytes of storage is required for each value).
The data types for precise Louboutin data in SQL Server are Decimal and Numeric. The storage space that this data occupies is determined by the number of digits after the number of digits in the data.
In SQL Server, the data type of approximate decimal data is Float and real. For example, the One-third score is recorded. 3333333, when using approximate data types can be accurately expressed. Therefore, the data retrieved from the system may not be exactly the same as the data stored in the column.

(6) Currency data indicates positive or negative amount of money.

In Microsoft SQL Server, the data type of the currency data is Money and SmallMoney

The Money data type requires 8 storage bytes, and the SmallMoney data type requires 4 bytes of storage.

(7) Special data types

Special data types include data types that have not been previously mentioned. There are 3 kinds of special data types, namely Timestamp, Bit and uniqueidentifier。
Timestamp is used to indicate the sequence of SQL Server activity, expressed in binary projection format. Timestamp data is not related to inserting data or date and time.
The Bit is made up of 1 or 0. The Bit data type is used when representing true or false, on, or off. For example, ask whether a client request that is accessed every time can be stored in a column of that data type.
The uniqueidentifier consists of 16-byte hexadecimal digits, representing a globally unique. GUIDs are useful when the record row for a table requires unique. For example, using this data type in a customer Identification number column can differentiate between different customers.

2. User-defined data types

User-defined data types are based on the type of data that is provided in Microsoft SQL Server. You can use user-defined data types when you must store the same data type in several tables, and to ensure that the columns have the same data type, length, and nullability. For example, you can define a data type called Postal_Code, which is based on the Char data type.
When you create a user-defined data type, you must provide three numbers: the name of the data type, the system data type on which it is based, and the nullability of the data type.

(1) Creating user-defined data types

You can use Transact-SQL statements to create user-defined data types. System stored procedures sp_addtype can be used to create user-defined data types. Its grammatical form is as follows:
sp_addtype {type},[,system_data_bype][, ' null_type ']
Where type is the name of the user-defined data type. System_data_type are data types provided by the system, such as Decimal, Int, Char, and so on. Null_type indicates how the data type handles null values and must be enclosed in single quotes, such as ' null ', ' NOT null ', or ' nonull '.
Example:
Use Cust
Exec sp_addtype ssn, ' Varchar ', ' not Null '
Creates a user-defined data type SSN that is based on a system data type that is 11 characters long and is not allowed to be empty.
Example:
Use Cust
Exec sp_addtype birthday,datetime, ' Null '
Creates a user-defined data type birthday that is based on a DateTime and allows nulls.
Example:
Use master
Exec sp_addtype telephone, ' varchar ', ' not Null '
Eexc sp_addtype Fax, ' varchar ', ' Null '
Create two data types, that is, telephone and fax

(2) Delete user-defined data types

Can be deleted when the user-defined data type is not needed. The command to delete a user-defined data type is sp_droptype {' type '}.
Example:
Use master
Exec sp_droptype ' ssn '
Note: This user-defined data type cannot be deleted when the column in the table is also using a user-defined data type, or if the default or rule is also bound above it.

Description of the field type for SQL Server

The following is a description of the field type for the above version of SQL SERVER7.0. SQL SERVER6.5 field type description refer to the instructions provided by SQL Server.

Field type	Describe
Bit	Integer digits of 0 or 1
Int	Integer number from -2^31 (-2,147,483,648) to 2^31 (2,147,483,647)
smallint	Integer number from -2^15 (-32,768) to 2^15 (32,767)
tinyint	Integer numbers from 0 to 255
Decimal	Number of fixed and significant digits from -10^38 to 10^38-1
Numeric	Synonyms for decimal
Money	Currency data from -2^63 ( -922,337,203,685,477.5808) to 2^63-1 (922,337,203,685,477.5807), minimum currency unit 10 per thousand
SmallMoney	Currency data from -214,748.3648 to 214,748.3647, minimum currency unit 10 per thousand
Float	Numbers from -1.79E+308 to 1.79E+308 variable precision
Real	Numbers from -3.04E+38 to 3.04E+38 variable precision
Datetime	Date and time data from January 1, 1753 to 9999 12th 31, with a minimum time unit of 3% seconds or 3.33 milliseconds
smalldatetime	Date and time data from January 1, 1900 to June 6, 2079, minimum unit of time in minutes
Timestamp	Timestamp, a unique number of database widths
uniqueidentifier	Global Unique identifier GUID
Char	Fixed-length non-Unicode character data with a maximum length 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 with a maximum length of 8000
ntext	Variable-length Unicode character data with a maximum length of 2^31-1 (2G)
Binary	Fixed-length binary data with a maximum length of 8000
varbinary	Variable-length binary data with a maximum length of 8000
image	variable-length binary data with a maximum length of 2^ 31-1 (2G)