sql - CAST 表达式中 varchar 和 varchar(50) 的区别？

Question

SQL Server 中的 varchar 和 varchar(50) 有什么区别？

SELECT CAST(customer_id AS varchar) AS Expr1 FROM tbl_customer

对比

SELECT CAST(customer_id AS varchar(50)) AS Expr1 FROM tbl_customer

Answer 1

如果没有提供长度，则默认创建长度为 30 的 Varchar。

参见:

declare @test varchar(100)
select @test = '1234567890123456789012345678901234567890'

SELECT CAST(@test AS varchar) as resultDefaultLength, 
    CAST(@test AS varchar(50)) as           resultSpecifiedLength

输出:

resultDefaultLength resultSpecifiedLength
123456789012345678901234567890  1234567890123456789012345678901234567890

只要您的 customer_id 不超过 30 个字符，就没有区别。

编辑:

正如 jpw 指出的那样:

This is true in the context of CAST/CONVERT, but otherwise the default length is 1 char. Docs: When n is not specified in a data definition or variable declaration statement, the default length is 1. When n is not specified when using the CAST and CONVERT functions, the default length is 30.

例如当声明一个 varchar(可能还有其他东西？)时，如果没有给出长度，它的长度将为 1。以上面的例子为例并稍作修改:

declare @test varchar
select @test = '1234567890123456789012345678901234567890'

SELECT CAST(@test AS varchar) as resultDefaultLength, 
    CAST(@test AS varchar(50)) as           resultSpecifiedLength

输出:

resultDefaultLength resultSpecifiedLength
1   1