SQL Server 中隐式转换和显式转换的区别
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/20967973/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Difference between Implicit Conversion and Explicit Conversion in SQL Server
提问by Vignesh Kumar A
Can you please tell me difference between implicit conversion and explicit conversion in SQL Server?
你能告诉我 SQL Server 中隐式转换和显式转换的区别吗？
I have googled about this but I can't get it.
我已经用谷歌搜索过这个，但我无法理解。
回答by Martin Smith
An explicit conversion occurs when you use the CONVERTor CASTkeywords explicitly in your query.
当您在查询中显式使用CONVERTorCAST关键字时，会发生显式转换。
An implicit conversion arises when you have differing datatypes in an expression and SQL Server casts them automatically according to the rules of datatype precedence.
当表达式中有不同的数据类型并且 SQL Server 根据数据类型优先级规则自动转换它们时，就会发生隐式转换。
For example nvarchar has higher precedence than varchar
例如 nvarchar 的优先级高于 varchar
CREATE TABLE Demo
(
X varchar(50) PRIMARY KEY
)
/*Explicit*/
SELECT *
FROM Demo
WHERE CAST(X AS NVARCHAR(50)) = N'Foo'
/*Implicit*/
SELECT *
FROM Demo
WHERE X = N'Foo' /*<-- The N prefix means nvarchar*/
The second execution plan shows a predicate of
第二个执行计划显示了一个谓词
CONVERT_IMPLICIT(nvarchar(50),[D].[dbo].[Demo].[X],0)=[@1]
Both the explicit and implicit conversions prevent an index seek in this case.
在这种情况下，显式和隐式转换都会阻止索引查找。
回答by Nadeem_MK
Implicit conversionsare not visible to the user. SQL Server automatically converts the data from one data type to another. For example, when a smallintis compared to an int, the smallintis implicitly converted to int before the comparison proceeds.
GETDATE()implicitly converts to date style 0. SYSDATETIME()implicitly converts to date style 21.
隐式转换对用户不可见。SQL Server 自动将数据从一种数据类型转换为另一种数据类型。例如，当 asmallint与 an 进行比较时int，smallint在进行比较之前，将隐式转换为 int。
GETDATE()隐式转换为日期样式 0。SYSDATETIME()隐式转换为日期样式 21。
Explicit conversionsuse the CASTor CONVERTfunctions.
The CAST and CONVERT functions convert a value (a local variable, a column, or another expression) from one data type to another. For example, the following CAST function converts the numeric value of $157.27into a character string of '157.27':
显式转换使用CASTorCONVERT函数。CAST 和 CONVERT 函数将值（局部变量、列或其他表达式）从一种数据类型转换为另一种数据类型。例如，以下 CAST 函数将$157.27的数值转换为字符串'157.27'：
CAST ( 7.27 AS VARCHAR(10) )
回答by Nagaraj S
Implicitmeans that the database engine will convert the data type automatically, a process invisible to the user.
隐式意味着数据库引擎会自动转换数据类型，这是一个用户不可见的过程。
Explicitmeans that you must specify how the data type should be converted. If you don't specify how SQL Server should convert the data types to do what you want (explicitly), it will try to guess your intentions (implicitly).
显式意味着您必须指定应如何转换数据类型。如果您没有指定 SQL Server 应如何转换数据类型以执行您想要的操作（显式），它会尝试（隐式）猜测您的意图。
回答by Md. Mahmudul Hasan
Implicit conversion is a conversion in which you don't have to bother about the conversion. SQL Server automatically converts the data from one data type to another. For example, if a smallint is compared to an int, the smallint is implicitly converted to int before the comparison proceeds. Suppose two column exist such as num1 in smallint and num2 in int. you want to compare them(whether equal). You have to write:
隐式转换是一种您不必担心转换的转换。SQL Server 自动将数据从一种数据类型转换为另一种数据类型。例如，如果将 smallint 与 int 进行比较，则在进行比较之前，smallint 会隐式转换为 int。假设存在两列，例如 smallint 中的 num1 和 int 中的 num2。你想比较它们（是否相等）。你必须写：
Select ..... where num1 = num2
nothing required for the conversion.
转换不需要任何东西。
Explicit conversion is a conversion in which you have to describe the conversion in your hand. Explicit conversions use the CAST or CONVERT functions. For example, a column date1 written in '21.01.2013'. it is in a varchar format according to the provided data/table. you want to compare with another column date2 which is in '21/01/2013' format. It is a date but provided in a varchar format as well. To compare them(whether equal) you have to write:
显式转换是一种转换，您必须在其中描述您手中的转换。显式转换使用 CAST 或 CONVERT 函数。例如，以“21.01.2013”编写的列 date1。根据提供的数据/表，它采用 varchar 格式。您想与“21/01/2013”格式的另一列 date2 进行比较。它是一个日期，但也以 varchar 格式提供。要比较它们（是否相等），您必须编写：
select ....... where cast(date1 as date) =cast(date2 as date)
