为什么从 int 转换/转换返回星号 [英] Why cast/convert from int returns an asterisk

问题描述

最近有人问我这个问题，我想我应该把它发布到 Stack Overflow 上以获得一些意见.

Someone recently asked me this question and I thought I'd post it on Stack Overflow to get some input.

现在显然以下两种情况都应该失败.

Now obviously both of the following scenarios are supposed to fail.

#1:

DECLARE @x BIGINT
SET @x = 100
SELECT CAST(@x AS VARCHAR(2))

明显错误:

消息 8115，级别 16，状态 2，第 3 行
将表达式转换为数据类型 varchar 时出现算术溢出错误.

Msg 8115, Level 16, State 2, Line 3
Arithmetic overflow error converting expression to data type varchar.

#2:

DECLARE @x INT
SET @x = 100
SELECT CAST(@x AS VARCHAR(2))

不明显，它返回一个 *(人们会认为这也是算术溢出???)

Not obvious, it returns a * (One would expect this to be an arithmetic overflow as well???)

现在我真正的问题是，为什么???这仅仅是设计使然还是有历史什么的这背后有什么险恶?

Now my real question is, why??? Is this merely by design or is there history or something sinister behind this?

我看了几个网站，没有得到满意的答案.

I looked at a few sites and couldn't get a satisfactory answer.

例如http://beyondrelational.com/quiz/sqlserver/tsql/2011/questions/Why-does-CAST-function-return-an-asterik--star.aspx

http://msdn.microsoft.com/en-us/图书馆/aa226054(v=sql.80).aspx

请注意，我知道/理解，当一个整数太大而无法转换为特定大小的字符串时，它将被转换"为星号，这是显而易见的答案，我希望我能给每个人投票不断给出这个答案.我想知道为什么使用星号而不是抛出异常，例如历史原因等??

推荐答案

为了更有趣，试试这个:

For even more fun, try this one:

DECLARE @i INT
SET @i = 100
SELECT CAST(@i AS VARCHAR(2)) -- result: '*'
go

DECLARE @i INT
SET @i = 100
SELECT CAST(@i AS NVARCHAR(2)) -- result: Arithmetic overflow error

:)

您查询的答案是:历史原因"

The answer to your query is: "Historical reasons"

数据类型 INT 和 VARCHAR 比 BIGINT 和 NVARCHAR 更旧.很多.事实上，它们属于原始 SQL 规范.同样古老的是用星号替换输出的异常抑制方法.

The datatypes INT and VARCHAR are older than BIGINT and NVARCHAR. Much older. In fact they're in the original SQL specs. Also older is the exception-suppressing approach of replacing the output with asterisks.

后来，SQL 人员决定抛出错误比替换虚假(通常令人困惑)的输出字符串更好/更一致等.然而，为了一致性起见，他们保留了预先存在的数据类型组合的先前行为(以免破坏现有代码).

Later on, the SQL folks decided that throwing an error was better/more consistent, etc. than substituting bogus (and usually confusing) output strings. However for consistencies sake they retained the prior behavior for the pre-existing combinations of data-types (so as not to break existing code).

因此(很久之后)添加 BIGINT 和 NVARCHAR 数据类型时，它们获得了新的(呃)行为，因为它们没有被上述祖父覆盖.

So (much) later when BIGINT and NVARCHAR datatypes were added, they got the new(er) behavior because they were not covered by the grandfathering mentioned above.

这篇关于为什么从 int 转换/转换返回星号的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！