SQL Server data type conversion method _mssql

Transformations of different data types are often used in SQL Server's day-to-day functions, stored procedures, and SQL statements. There are two types of data conversions in SQL Server: one is explicit data conversion, and the other is implicit data conversion. The following is a brief description of these two data type conversions:

1 an explicit conversion

A display conversion is an explicit conversion of an expression of a data type to another data type. The cast and CONVERT functions are commonly used.

Cast:cast (expression as data_type)
Convert:convert (data_type[(length)], expression [, style])

The parameter expression is any valid Microsoft SQL Server expression. Data_type the data type provided by the target system, you cannot use a user-defined data type.

2 recessive conversion

Implicit conversions are not visible to the user and are handled automatically by the SQL Server engine. An implicit transformation automatically converts data from one data type to another data type. For example, if a smallint variable is compared to an int variable, the smallint variable is implicitly converted to an int variable before being compared. Some implicit and explicit data-type conversions are not supported when converting from one SQL Server object data type to another. For example, a nchar value cannot be converted to an image value at all. NChar can only be converted explicitly to binary, and implicit conversion to binary is not supported. NChar can be explicitly or implicitly converted into nvarchar.

3 risk of recessive conversions

Implicit conversions are handy when it comes to simplifying SQL scripts, but there are also potential risks that can occur when the script starts running, but after a certain point in time, the program inexplicably appears to be wrong. Here is an example of a real-world project to illustrate. In SQL Server 2008, there is a table you need to pull data from two different data tables, because the two tables belong to different systems, their primary key types are different, one is type int, one is GUIDs, and at first you think that both can be converted to character types for storage. So just create a mixed id column of nvarchar (50) as the primary key in the table. As shown in the following illustration:

The initial pull data does not have the GUID value, is the int type converts the data, therefore the SQL script runs normally, but suddenly at one run time, appears "nvarchar value ' 4c185367-f004-41fe-8a0a-db4e819b1ff2 ' Failed to convert to data type int. "The mistake. As shown in the following illustration:

Navigate to the script and execute the following sql:

SELECT * FROM dbo. Demo where Mix id=305

The data in the primary key has the character type of a GUID conversion, as well as a string of int conversions, as follows:

However, if you execute the following SQL, it is all done correctly:

SELECT * FROM dbo. Demo where mixed id=305 and name = ' INT '

select * FROM dbo. Demo where mix id=305 and ordinal = ' 2 '

select * FROM dbo. The Demo where blends id=305 and ordinal =2

select * FROM dbo. Demo where Mix id= ' 305 ' and name = ' INT '

select * FROM dbo. Demo where Mix id= ' 305 '

The results are as follows:

The result of the above error should be this:

SELECT * FROM dbo. Demo where mixed id=305 when executed, SQL Server converts the nvarchar type's implicit to type int, and if there is no GUID-type character in the data, the conversion is normal, and if so, when a GUID character is converted to an int, The conversion fails.

That's all this article, and I want to help you with SQL Server data type conversions.