SQL Server 2005 奇怪的 varchar 行为

2021-09-10 00:00:00 sql-server-2005 tsql sql-server

此 SQL Server 2005 T-SQL 代码:

This SQL Server 2005 T-SQL code:

DECLARE @Test1 varchar;
SET @Test1 = 'dog';

DECLARE @Test2 varchar(10);
SET @Test2 = 'cat';

SELECT @Test1 AS Result1, @Test2 AS Result2;

产生:

结果 1 = d结果 2 = 猫

我希望要么

  1. 赋值 SET @Test1 ='dog'; 失败，因为没有@Test1
    2. 有足够的空间
  2. 或者 SELECT 在 Result1 列中返回dog".
  1. The assignment SET @Test1 = 'dog'; to fail because there isn't enough room in @Test1
  2. Or the SELECT to return 'dog' in the Result1 column.

@Test1 怎么了?有人可以解释一下这种行为吗?

What is up with @Test1? Could someone please explain this behavior?

推荐答案

让我用 SQL Server 文档中的一些引述来回答.

Let me answer with some quotes from the SQL Server documentation.

char 和 varchar

varchar[(n)]

...

当数据定义或变量声明语句中未指定 n 时，默认长度为 1.

When n is not specified in a data definition or variable declaration statement, the default length is 1.

转换字符数据

当字符表达式转换为不同大小的字符数据类型时，对于新数据类型来说太长的值将被截断.

When character expressions are converted to a character data type of a different size, values that are too long for the new data type are truncated.

因此，您的 varchar 被声明为 varchar(1)，并且您的 SET 语句中的隐式转换(从长度为 3 的字符串文字到 varchar(1)) 将 dog 截断为 d.

So, your varchar is declared as a varchar(1), and the implicit conversion in your SET statement (from a string literal of length 3 to a varchar(1)) truncates dog to d.

相关文章