Shulou(Shulou.com)05/31 Report--

This article introduces the relevant knowledge of "what are the data types of SQL SERVER". In the operation process of actual cases, many people will encounter such difficulties. Next, let Xiaobian lead you to learn how to deal with these situations! I hope you can read carefully and learn something!

1. SQL Server Data Types

A data class is an attribute of data that indicates the type of information the data represents. Every computer language defines its own data types. Of course, different programming languages have different characteristics, and the types and names of the 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])] Float[(n)] Real Int Smallint Tinyint Money Smallmoney Bit Cursor Sysname Timestamp Uniqueidentifier Text Image Ntext

(1)binary data types

Binary data includes Binary, Varbinary, and Image Binary data types that can be either fixed-length (Binary) or variable-length. Binary[(n)] is n-bit fixed binary data. where n ranges from 1 to 8000. The size of the memory is n + 4 bytes. Varbinary[(n)] is n-bit variable length binary data. where n ranges from 1 to 8000. The size of the memory cell is n + 4 bytes, not n bytes. The data stored in the Image data type is stored as a bit string and is not interpreted by SQL Server, but must be interpreted by the application. For example, applications can store data in the Image data type using BMP, TIEF, GIF, and JPEG formats.

(2)character data type

Character data types include Char, Varchar, and Text. Character data is data composed of any arbitrary combination of letters, symbols, and numbers. Varchar is variable-length character data that is no longer than 8KB. Char is fixed-length character data that is up to 8KB in length. ASCII data larger than 8KB can be stored using the Text data type. For example, because Html documents are all ASCII characters and are typically 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 characters defined by a specific character set. During SQL Server installation, you are allowed to select a character set. With Unicode data types, columns can store any character defined by the Unicode standard. The Unicode standard includes all characters defined in various character sets. With Unicode data types, the number of scents conquered is twice the size of scents occupied with non-Unicode data types. In SQL Server, Unicode data is stored as Nchar, Nvarchar, and Ntext data types. Columns stored using this character type can store characters from multiple character sets. When column lengths vary, the Nvarchar character type should be used, which can store up to 4000 characters. When the column length is fixed, the Nchar character type should be used, and again, 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 dates and times. 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 first data type is date before, time after, and the second data type is instant before, date after. In Microsoft SQL Server, when the date and time data types include both Datetime and Smalldatetime, the range of dates stored is from January 1, 1753 to December 31, 9999 (each value requires 8 bytes of storage). When using the Smalldatetime data type, the range of dates stored is from January 1, 1900 to December 31, 2079 (each value requires 4 bytes of storage). The date format can be set. The command to format a date is as follows: DateSet Format {format}| @format _var| Among them, format| @format_var is the order of 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 year month day; when Set DateFormat DMY is executed, the date format is day month year

(5) Digital data types

Digital data contains only numbers. Numeric data types include positive and negative numbers, decimals (floating point numbers), and integers. Integer numbers consist of positive and negative integers, such as 39, 25, 0-2, and 33967. In Micrsoft SQL Server, integers are stored as data types Int, Smallint and Tinyint. The Int data type stores a larger range of data than the Smallint data type, while the Smallint data type stores a larger range of data than the Tinyint data type. The range of data stored using Int data dogs is-2 147 483 648 to 2 147 483 647 (each value requires 4 bytes of storage space). When using Smallint data types, the range of stored data is-32 768 to 32 767 (each value requires 2 bytes of storage space). When using Tinyint data types, the range of stored data is from 0 to 255 (each value requires 1 byte of storage space). The data types in SQL Server are Decimal and Numeric. The storage space occupied by such data is determined according to the number of bits after the number of bits of the data. In SQL Server, the data types for approximate fractional data are Float and Real. For example, one-third of this fraction is credited as. 333333, which can be accurately represented when approximate data types are used. Therefore, the data retrieved from the system may not be exactly the same as the data stored in that column.

(6) Monetary data indicates positive or negative monetary quantities.

In Microsoft SQL Server, the data types for monetary data are Money and Smallmoney

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

(7) Special data types

Special data types include data types not previously mentioned. There are three special data types, namely Timestamp, Bit and Uniqueidentifier. Timestamp is used to represent the sequence of SQL Server activities, expressed in binary projection format. Timestamp data has nothing to do with insert data or date and time. Bit consists of 1 or 0. Bit data type is used when indicating true or false, ON or OFF. For example, client requests asking if it is every access can be stored in columns of this data type. Uniqueidentifier consists of a 16-byte hexadecimal number representing a globally unique. GUID is useful when the record rows of a table require uniqueness. For example, using this data type in the Customer Identification Number column can distinguish between different customers.

2. user-defined data types

User-defined data types are based on data types provided in Microsoft SQL Server. User-defined data types can be used when the same data type must be stored in several tables, and to ensure that the columns have the same data type, length, and nullability. For example, you can define a class called The data type of postal_code, which is based on the Char data type. When creating 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) Create user-defined data types

User-defined data types can be created using Transact-SQL statements. The system stored procedure sp_addtype can be used to create user-defined data types. The syntax is: sp_addtype {type},[,system_data_type][,'null_type'] where type is the name of the user-defined data type. system_data_type is the data type provided by the system, such as Decimal, Int, Char Wait a minute. 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(11)','Not Null' Creates a user-defined data type ssn based on a system data type of variable length 11 characters, which does not allow nulls. Example: Use dust Exec sp_addtype birthday,datetime,'Null' creates a user-defined datatype birthday based on the system datatype DateTime, null allowed. Example: Use master Exec sp_addtype telephone,'varchar(24),'Not Null' Eexc sp_addtype fax,'varchar(24)','Null'Create two data types, i.e. telephone and fax

(2) Delete user-defined data types

User-defined data types can be deleted when they are not needed. The command to delete user-defined data types is sp_droptype {'type'}. Example: Use master Exec sp_droptype 'ssn' Note: User-defined data types cannot be deleted when columns in a table are still using them, or when defaults or rules are bound to them.

SQL SERVER Field Type Description

The following is a description of the field types for SQL Server versions 7.0 and above. SQL SERVER 6.5 field type description please refer to SQL SERVER provided description.

field type description bit0 or integer number int from-2^31 (-2,147,483,648) to 2^31 integer of (2,147,483,647) smallint integer from-2^15(-32,768) to 2^15(32,767) tinyint integer from 0 to 255 decimal from-10^38 to 10^38 -1 fixed precision synonym for numeric decimal with significant digits money from-2^63 (-922,337,203,685,477.5808) to 2^63-1 Currency data for (922,337,203,685,477.5807), smallest currency unit ten thousandths smallmoney Currency data from-214,748.3648 to 214,748.3647, smallest monetary unit ten thousandths float from-1.79E+308 to 1.79E +308 variable-precision number real from-3.04E+38 to 3.04E +38 variable-precision number datetime date and time data from January 1, 1753 to December 31, 9999 smalldatetime Date and time data from January 1, 1900, to June 6, 2079, with a minimum time unit of three hundredths or 3.33 milliseconds timestamp timestamp, a unique number of database widths uniqueidentifier GUIDchar Fixed length nonUnicode character data, maximum length 8000varchar Variable length nonUnicode character data, Maximum length 8000text variable length non-Unicode character-type data, maximum length 2^31-1(2G)nchar fixed length Unicode character-type data, maximum length 8000nvarchar variable length Unicode character-type data, maximum length 8000ntext variable length Unicode character-type data, maximum length 2^31-1(2G)binary fixed length binary data, maximum length 8000varbinary variable length binary data, Maximum length 8000image Variable length binary data, maximum length 2^31-1(2G)

"SQL SERVER data types what" content is introduced here, thank you for reading. If you want to know more about industry-related knowledge, you can pay attention to the website. Xiaobian will output more high-quality practical articles for everyone!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.