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

This article is to share with you about the SQL SERVER data type, the editor thinks it is very practical, so I share it with you to learn. I hope you can get something after reading this article.

Data type of 1.SQL SERVER

A data type is an attribute 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, and the types and names of the defined data types are more or less different. SQLServer 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 type

Binary data includes Binary, Varbinary, and Image

The Binary data type can be either 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 blocks is n + 4 bytes.

Varbinary [(n)] is n-bit variable length binary data. Where the value of n ranges 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, is not interpreted by SQL Server, and must be interpreted by the application. For example, an application can store data in the 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 made up of any combination of letters, symbols, and numbers.

Varchar is variable-length character data whose length does not exceed 8KB. Char is fixed-length character data with a maximum length of 8KB. ASCII data that exceeds 8KB can be stored using the Text data type. For example, because Html documents are all ASCII characters and are generally longer than 8KB, these documents can be stored in SQL Server with Text data types.

(3) Unicode data type

Unicode data types include Nchar,Nvarchar and Ntext

In Microsoft SQL Server, traditional non-Unicode data types allow the use of characters defined by a specific character set. During SQL Server installation, you are allowed to select a character set. Using 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 the Unicode data type, you can overcome twice the size of the garbage occupied by using a non-Unicode data type.

In SQL Server, Unicode data is stored as Nchar, Nvarchar, and Ntext data types. Columns stored using this character type can store characters in multiple character sets. When the length of a column changes, you should use the Nvarchar character type, which can store up to 4000 characters. The Nchar character type should be used when the length of the column is fixed, and again, a maximum of 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

The date and time data type consists of valid dates and times. For example, valid date and time data include "4-01-98 12 PM 1500 00 PM" and "1:28:29:15:01AM 8-17-98". The first data type is the date, the time is the last, the data type is immediately before, and the date is after. In Microsoft SQL Server, when the date and time data types include Datetime and Smalldatetime, the stored date range begins on January 1, 1753, and ends on December 31, 9999 (each value requires 8 storage bytes). When using the Smalldatetime data type, the stored date range is from January 1, 1900 to December 31, 2079 (each value requires 4 storage bytes).

The format of the date can be set. The command to format the date is as follows:

Set DateFormat {format | @ format _ var |

Where 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 is in the form of year, month, and day; when Set DateFormat DMY is executed, the date is in the form of day, month and year.

(5) Digital data type

Digital data contains only numbers. Numeric data types include positive and negative numbers, decimals (floating point numbers), and integers

Integers consist of positive and negative integers, such as 39, 25, 0-2, and 33967. In Microsoft SQL Server, the data types stored in integers are Int,Smallint and Tinyint. The range of data stored by the Int data type is greater than that of the Smallint data type, while the range of data stored by the Smallint data type is greater than that of the Tinyint data type. The range of data stored using the Int data type is from-2 147 483 648 to 2 147 483 647 (each value requires 4 bytes of storage). When using the Smallint data type, the range of stored data ranges from-32 768 to 32 767 (each value requires 2 bytes of storage). When using the Tinyint data type, the range of stored data is from 0 to 255 (each value requires 1 byte of storage space).

The data types of exact decimal data in SQL Server are Decimal and Numeric. The storage space occupied by such data is determined according to the number of digits after the number of digits of the data.

In SQL Server, the data types for approximate decimal data are Float and Real. For example, the score of 1/3 is recorded as. 3333333, which can be accurately represented when using approximate data types. Therefore, the data retrieved from the system may not be exactly the same as the data stored in the column.

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

In Microsoft SQL Server, the data types of currency data are Money and Smallmoney

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

(7) Special data types

Special data types include data types that have not been mentioned earlier. There are three special data types, namely Timestamp, Bit, and Uniqueidentifier.

Timestamp is used to represent the sequence of SQL Server activities, expressed in a binary projection format. Timestamp data has nothing to do with inserting data or date and time.

Bit consists of 1 or 0. Use the Bit data type when representing true or false, ON, or OFF. For example, ask if a client request for each visit can be stored in a column of this data type.

Uniqueidentifier consists of 16 bytes of hexadecimal digits that represent a globally unique. GUID is very useful when the record rows of a table are required to be unique. For example, using this data type in the customer identification number column can distinguish different customers.

two。 User-defined data type

User-defined data types are based on the 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 emptiness. For example, you can define a data type called 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

You can use Transact-SQL statements to create user-defined data types. The system stored procedure 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 is the data type 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 (11)', 'Not Null'

Create a user-defined data type ssn that is based on a system data type with a variable length of 11 characters, and null is not allowed.

Example:

Use cust

Exec sp_addtype birthday,datetime,'Null'

Create a user-defined data type birthday based on the system data type DateTime, which allows null.

Example:

Use master

Exec sp_addtype telephone,'varchar (24)', 'Not Null'

Eexc sp_addtype fax,'varchar (24)', 'Null'

Create two data types, telephone and fax

(2) Delete user-defined data types [1]

Can be deleted when a 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: a user-defined data type cannot be deleted when a column in the table is still using a user-defined data type, or when there are default or rules bound to it.

The difference between 3.varchar and nvarchar

Varchar (n)

Variable-length character data that is n bytes long and is not Unicode. N must be a number between 1 and 8000. The storage size is the actual length of the bytes of the input data, rather than n bytes (such as varchar (6), when the field is qqq, the actual footprint is 3 bytes instead of 6 bytes, but only 6 bytes are allowed to be stored, usually two bytes in Chinese).

Nvarchar (n)

Variable-length Unicode character data containing n characters. The value of n must be between 1 and 4000. The storage size of bytes is twice the number of characters entered.

The two fields have field values: "me and coffee". Then the varchar field accounts for 2 × 2 bytes of storage space, while the nvarchar field accounts for 8 × 2 bytes of storage space. If the field value is only in English, you can choose varchar, and use nvarchar when there are more double-byte (Chinese, Korean, etc.) characters in the field value.

How to choose varchar and nvarchar in SQL Server?

Varchar uses a single byte to store data in SQL Server, and nvarchar uses Unico to store data. Chinese characters stored in SQL Server will be saved as two bytes (usually encoded by Unico), English characters will be saved to the database, if the field type is varchar, it will only occupy one byte, and if the field type is nvarchar, it will occupy two bytes.

Under normal circumstances, we can also store Chinese characters using varchar, but if the operating system is an English operating system and the support for Chinese fonts is not comprehensive, there will be garbled codes when storing Chinese characters as varchar in SQL Server. And under normal circumstances, the host will support a Chinese environment, so if you use varchar to store data, it will not be found in the development stage. in most cases There will be no problem when deploying.

But! If the deployed host is an English operating system and does not support the Chinese environment, the problem arises. All varchar fields will become garbled when storing Chinese (shown as?). And normally you won't know that this is because you use the wrong data type to store, you will try to install the Chinese font Try to set up the operating system's locale... none of these can solve the problem, the only solution is to personalize the type of database field to nvarchar (or nchar). Friends who are familiar with project management should know that it is a terrible thing to modify the database at the deployment stage.

Another great advantage of using nvarchar is that you don't have to consider the difference between Chinese and English characters when judging strings.

Of course, using nvarchar to store English characters will double the storage space. But when the storage cost is already very low, giving priority to compatibility will bring you more benefits.

Therefore, you should try to use nvarchar to store data in Design. Use varchar only when you make sure that the field does not save Chinese.

1 、 CHAR . CHAR is very convenient to store fixed-length data, and indexing on CHAR fields is highly efficient, such as defining char (10), so it takes up 10 bytes of space regardless of whether the data you store reaches 10 bytes or not. 2 、 VARCHAR . Store variable-length data, but not as efficiently as CHAR. If the possible value of a field is not fixed length, we only know that it cannot exceed 10 characters, and it is most cost-effective to define it as VARCHAR (10). The actual length of the VARCHAR type is the actual length of its value + 1. Why "+ 1"? This byte is used to keep the actual length used.

From the perspective of space, it is appropriate to use varchar; from the perspective of efficiency, it is appropriate to use char. The key is to find the tradeoff point according to the actual situation. 3 、 TEXT . Text stores variable length non-Unicode data with a maximum length of 2 ^ 31-1 (2147483647) characters.

4 、 NCHAR 、 NVARCHAR 、 NTEXT . These three are more "N" than the first three in terms of name. It means that characters of the Unicode data type are stored. We know that among the characters, only one byte is enough to store English characters, but there are many Chinese characters that need two bytes of storage. It is easy to cause confusion when English and Chinese characters exist at the same time. The Unicode character set is created to solve the incompatible problem of the character set. All its characters are represented by two bytes, that is, English characters are also represented by two bytes. The length of nchar and nvarchar is between 1 and 4000. Compared with char and varchar, nchar and nvarchar can store up to 4000 characters, whether in English or Chinese characters, while char and varchar can store up to 8000 English characters and 4000 Chinese characters. You can see that when using nchar and nvarchar data types, you don't have to worry about whether the input characters are English or Chinese characters, which is more convenient, but there is some loss in the quantity when storing English.

So generally speaking, if it contains Chinese characters, use nchar/nvarchar, if pure English and numbers, use char/varchar.

The above is what the SQL SERVER data type is, and the editor believes that there are some knowledge points that we may see or use in our daily work. I hope you can learn more from this article. For more details, please follow the industry information channel.

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.