SQL变量数据类型和表列数据类型之间的差异

What is the difference between SQL variable datatype and Table column datatype.

SQL变量数据类型和表列数据类型的区别是什么?

Consider the below example:

考虑下面的例子:

DECLARE @variable CHAR(1)

SET @variable = 'quarter'

SELECT @variable --works

Result: Q

结果:问

But when I do the same in Table am getting error

但是当我在表中做同样的事情时，我得到了错误

DECLARE @table TABLE
  (
     col CHAR(1)
  )

INSERT INTO @table
VALUES      ('quarter') --Fails

Msg 8152, Level 16, State 14, Line 9 String or binary data would be truncated.

Msg 8152, 16级，状态14，第9行字符串或二进制数据将被截断。

I went through MSDN about DECLARE @local_variable still didn't get any answer. Can anyone tell me what is the reason.

我查了MSDN关于DECLARE @local_variable仍然没有得到任何答案。谁能告诉我是什么原因。

1 个解决方案

#1

the main difference is how warnings in SET vs INSERT work, here is SET ANSI_WARNINGS documentation

主要区别在于SET和INSERT工作中的警告是如何设置的，这里设置了ANSI_WARNINGS文档。

When set to ON, the divide-by-zero and arithmetic overflow errors cause the statement to be rolled back and an error message is generated. When set to OFF, the divide-by-zero and arithmetic overflow errors cause null values to be returned. The behavior in which a divide-by-zero or arithmetic overflow error causes null values to be returned occurs if an INSERT or UPDATE is tried on a character, Unicode, or binary column in which the length of a new value exceeds the maximum size of the column. If SET ANSI_WARNINGS is ON, the INSERT or UPDATE is canceled as specified by the ISO standard. Trailing blanks are ignored for character columns and trailing nulls are ignored for binary columns. When OFF, data is truncated to the size of the column and the statement succeeds.

当设置为ON时，将分划-by- 0和算术溢出错误导致回滚语句并生成错误消息。当设置为OFF时，除零和算术溢出错误导致返回空值。如果在一个字符、Unicode或二进制列上尝试插入或更新，其中新值的长度超过列的最大大小，则会发生按0或算术溢出错误导致返回空值的行为。如果设置ansi_warning是ON的，则按照ISO标准指定的方式取消INSERT或UPDATE。字符列忽略尾空，二进制列忽略尾空。当关闭时，数据被截断到列的大小，语句成功。

but, it does not affect SET statements:

但不影响SET语句:

ANSI_WARNINGS is not honored when passing parameters in a stored procedure, user-defined function, or when declaring and setting variables in a batch statement. For example, if a variable is defined as char(3), and then set to a value larger than three characters, the data is truncated to the defined size and the INSERT or UPDATE statement succeeds.

当在存储过程、用户定义函数中传递参数，或者在批处理语句中声明和设置变量时，ansi_warning是不受重视的。例如，如果一个变量定义为char(3)，然后将其设置为大于3个字符的值，则数据被截断为已定义的大小，插入或更新语句成功。

so, you could disable ansi warnings and your insert query will work. But I'd prefer to have warnings on, but do data validation/truncation on app side.

因此，您可以禁用ansi警告，您的insert查询将会工作。但我更喜欢在应用程序端进行数据验证/截断。

#1