SQL Server data type conversion method, server data type

SQL Server data type conversion method, server data type

Conversions of different data types are often used in SQL Server daily functions, stored procedures, and SQL statements. SQL Server has two types of data conversion: explicit data conversion and implicit data conversion. The conversion of the two data types is briefly described below:

1 explicit conversion

Explicit conversion is to explicitly convert a certain data type expression to another data type. CAST and CONVERT functions are commonly used.

CAST: CAST (expression AS data_type)
CONVERT: CONVERT (data_type [(length)], expression [, style])

The expression parameter is any valid Microsoft SQL Server expression. The data type provided by the target system of data_type. User-defined data types cannot be used.

2 implicit Conversions

Implicit conversions are invisible to users and are automatically processed by the SQL Server engine. Implicit conversions automatically convert data from one data type to another. For example, if a smallint variable is compared with an int variable, the smallint variable is implicitly converted to an int variable before the comparison. Some implicit and explicit data types cannot be converted from the Data Type of one SQL Server object to another. For example, nchar values cannot be converted to image values. Nchar can only be converted to binary explicitly. implicit conversion to binary is not supported. Nchar can be converted to nvarchar explicitly or implicitly.

3 risks of implicit conversion

Implicit conversions are sometimes very convenient and can simplify SQL scripts, but they also pose potential risks, which may be normal at the beginning of the script operation, however, after a certain time point, the program encountered an inexplicable error. The following is an example of a real project. In SQL Server 2008, there is a table that needs to pull data from two different data tables. Because these two data tables belong to different systems, their primary key types are different. One is int type, one is GUID, which can be converted to the character type for storage at first. Therefore, create an nvarchar (50) hybrid ID column in the table as the primary key. As shown in:

The data pulled at the beginning does not have a GUID value, and all the data is converted from the INT type. Therefore, the SQL script runs normally, but suddenly at a run time, an error occurred while converting the nvarchar value '4c185367-F004-41FE-8A0A-DB4E819B1FF2 'to a data type int." . As shown in:

Locate the script and run the following SQL statement:

Select * from dbo. Demo where hybrid ID = 305

The data in the primary key includes the GUID-converted string and INT-converted string. The sample data is as follows:

However, if you execute the following SQL statements, they are all executed normally:

Select * from dbo. demo where mixed ID = 305 and name = 'int' select * from dbo. demo where mixed ID = 305 and serial number = '2' select * from dbo. demo where mixed ID = 305 and serial number = 2 select * from dbo. demo where hybrid ID = '20140901' and name = 'int' select * from dbo. demo where hybrid ID = '20140901'

The result is as follows:

The result of the above error is as follows:

Select * from dbo. Demo whereHybrid ID = 305 during execution, SQL Server implicitly converts nvarchar to int type. If there are no GUID characters in the data, the conversion is normal. If yes, if implicit conversion from GUID to INT is performed, the conversion fails.

The above is all the content in this article. I hope it will be helpful for you to convert the SQL Server data type.

Articles you may be interested in:

• SQL SERVER Data Type
• SQL Server data type conversion experiment
• Common Data Types of SQL server
• SQL SERVER Data Type Details Supplement 2
• SQL Server query data type is ntext is NULL or NULL
• Sqlserver2005 TSql New Function Learning Summary (data type)
• Differences between SQL Server Data Types char, nchar, varchar, and nvarchar
• SQL Server Common Data Types