SQL Server Custom data types

SQL Server provides 25 basic data types:

· binary [(n)] binaries can be either fixed-length (binary) or variable-length. where n is the range of values from 1 to 8000. The size of its storage scenting is n + 4 bytes.

· Binary Data Varbinary [(n)] binary data n-bit variable length. where n is the range of values from 1 to 8000. The size of its storage scenting is n + 4 bytes.

· char [(n)] character data type (any combination of letters, symbols, and numbers) fixed long character data with a length of up to 8KB

· varchar[(n)] variable long character data length up to 8KB

· nchar[(n)] Unicode data type (a column can store characters in multiple character sets) column length is fixed up to 4,000 characters

· nvarchar[(n)] Unicode data Type column length changes can store up to 4,000 characters

· The datetime date and time data type stores a date range that starts January 1, 1753 and ends on December 31, 9999 (each value requires 8 bytes of storage). Accurate to 1% seconds or 3.33 milliseconds

· smalldatetime date and time data type starts January 1, 1900 and ends on December 31, 2079 (each value requires 4 bytes of storage). Accurate to a minute

· decimal[(P[,s])] 17 bytes are accommodated from 1028-1 to-1028-1. The exact numeric data type of the value. You can define the precision (1-28) and the symbol (0-Define the precision). The default precision and symbols are 18 and 0, respectively.

· numeric[(P[,s])] data type same as decimal type

· float[(n)] floating-point numbers can be any number from -1.79E+308 to 1.79E+308

· Real approximate numeric type. It can represent a floating-point number between -3.40E+38 and 3.40E+38.

· The INT data type stores data in ranges from 2 147 483 648 to 2 147 483 647 (each value requires 4 bytes of storage space).

· Smallint stores data in ranges from 32 768 to 32 767 (each value requires 2 bytes of storage space).

· Tinyint stores data from 0 to 255 (each value requires 1 bytes of storage).

· Money currency data indicates positive or negative monetary quantity the Money data type requires 8 storage bytes to store data from 922 billion to 922 billion, accurate to one out of 10,000 of the currency unit

· SmallMoney Currency data indicates a positive or negative currency number smallmoney The data type requires 4 bytes of storage. can store data from 214748.3648 to 214748.3647, accurate to one out of 10,000 of currency units

· The Bit is made up of 1 or 0. When representing true or false, on, or off, use the Bit data type

· The cursor special data type, which contains a reference to a cursor. This data type is used in stored procedures and cannot be used when creating tables

· The Sysname system provides a user-defined data type that is functionally equivalent to nvarchar (128) for referencing database object names.

· The Timestamp is used to represent the sequence of SQL Server activities in a binary projection format. Timestamp data is not related to inserting data or date and time

· The uniqueidentifier consists of 16-byte hexadecimal digits that represent a globally unique. GUIDs are useful when the table's record rows require uniqueness

· Text is used to store a large number of non-uniform encoded character data. This data type can have a maximum of 231-1 or 2 billion characters

· The Image binary data store data is stored as a bit string, not interpreted by SQL Server, and must be interpreted by the application. For example, applications canstore data in the Image data type using BMP, Tief, GIF, and JPEG formats.

· Ntext Unicode data type This column can store more than 4,000 characters

User-defined data types

User-defined data types are based on the data types that are available in Microsoft SQL Server. You can use a user-defined data type 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.

--Build custom data type--exec sp_addtype ssn, ' Varchar (one) ', ' not Null '--delete custom data type--exec sp_droptype ' ssn '--View user custom data type--select * From systypes where xtype<>xusertype; (SQL Server #)--select * from Sys.types where is_user_defined=1

　　

SQL Server Custom data types