SQL语法提示工具SQL Prompt教程：使用没有明确长度的可变长度数据类型（上）

SQL Prompt根据数据库的对象名称、语法和代码片段自动进行检索，为用户提供合适的代码选择。自动脚本设置使代码简单易读--当开发者不大熟悉脚本时尤其有用。SQL Prompt安装即可使用，能大幅提高编码效率。此外，用户还可根据需要进行自定义，使之以预想的方式工作。

点击下载SQL Prompt免费版

如果您声明一个可变长度的字符串，或在不指定其长度的情况下强制字符串，则可能会被“静默”字符串截断。一些开发人员诉诸使用(MAX)规范，这也是一个错误。当您导入文本并且根本不知道每个字符串的正确长度时，Phil Factor解释了其中的危险，然后提供了解决该问题的方法。本文是该教程的上半部分内容~

在SQL中，如果您以CHAR、NCHAR、VARCHAR或者NVARCHAR这四种格式中的任何一种格式声明字符串，不指定其长度，字符串被赋予一个字符的长度。 如果使用CAST或CONVERT强制转换字符串，并犯同样的错误，则该字符串的长度为30个字符。 为什么是1和30？ 仅出于历史原因，但其他RDBMS也有类似的反应。

无论哪种情况，您都可能会遇到“静默”字符串截断的麻烦，因此，如果您在可变长字符串声明（BP007）和强制转换期间忘记指定字符串长度，SQL Prompt会提供一些内置的代码分析规则来警告您（BP008）。

一些开发人员认为，如果不考虑长度问题，SQL Server会满足他们的需求。这并不像看起来那样牵强，因为我将在其他情况下继续演示。通常，此错误的上下文是从不知道列的正确字符串长度的数据源导入时发生的，并且需要创建目标表。或者，您可能最终使用它MAX作为长度，这会带来麻烦。

在本文的后面，我将向您展示一个方便的技巧来解决此问题。它使用“提示的脚本作为插入”功能来编写表的前1000行的脚本，然后我们说服SQL Server通过在SELECT INTO语句中，通过表值构造函数（TVC）导入数据来分配正确的数据类型和字符串长度。

忘记字符串长度的问题

通常，但并非总是如此，您的错误会很快显现出来。

CREATE TABLE dbo.Deleteme (MyString VARCHAR NOT null)
  INSERT INTO dbo.Deleteme (MyString) VALUES ('first'),('Second'),('Third')
  /*
  Msg 8152, Level 16, State 14, Line 5
  String or binary data would be truncated.
  The statement has been terminated.
  */
  drop TABLE dbo.Deleteme

如果对变量执行相同的操作，则可能会更加不愉快，因为字符串会被截断为单个字符，但不会出错。

DECLARE @MyString VARCHAR ='this ends badly'
  SELECT @MyString
  --no error. Produces value 't'

函数或过程中的参数也是如此……

CREATE FUNCTION dbo.deleteme (@param1 VARCHAR, @param2 CHAR)
  RETURNS VARCHAR(100)
  AS
    BEGIN
      RETURN @param1 + @param2;
    END;
  Go
  SELECT dbo.deleteme('this is likely to','go very badly')
  --returns 'tg'

并在函数的返回值中：

CREATE FUNCTION dbo.deletemeAlso (@param1 VARCHAR(100), @param2 Varchar(100))
  RETURNS VARCHAR
  AS
    BEGIN
      RETURN @param1 + @param2;
    END;
  GO
  SELECT dbo.deletemeAlso('this is likely to','go very badly')
  --returns 't'

如果对变量执行相同的操作，则可能会更加不愉快，因为字符串会被截断为单个字符，但不会出错。

SELECT Convert(nVARCHAR,'12345678901234567890123456789012345678901234567890' ,113)
  /*
  ------------------------------
  123456789012345678901234567890
  (1 row affected)
  */

当时的传统观点认为，大多数数据库字符串的长度都少于30个字符。即使是少于30个字符的字符串也必须得到处理。

SELECT Convert(CHAR,GetDate() ,113)
  SELECT system_type_name FROM 
   sys.dm_exec_describe_first_result_set(
      'SELECT Convert(VARCHAR,GetDate() ,113)',
       NULL, 1)
  --varchar(30)

我不确定为什么有人会在不指定VARCHAR长度的情况下使用它。 这可能是过程编码所养成的习惯。 对于任何RDBMS，情况都是不同的。您可以确保以经济的方式存储字符串，或者只是挥手示意，并将字符串分配为（MAX）长度规范，这会带来所有的索引编制和性能折衷（这些数据类型不能指定为索引键列）。

使用别名数据类型

如果您需要默认的字符串长度作为设计的一部分，则可以创建别名数据类型。这样更安全：

--create an alias data type
  CREATE TYPE dbo.String  FROM VARCHAR(30) NOT NULL
  GO
  DECLARE @MyString String ='this ends well'
  SELECT @MyString
  ------------------------------
  --this ends well

但是，缺点是，由于某种原因，您不能在CAST或CONVERT操作中使用它，因为它不是已定义的系统类型。

SELECT Convert(dbo.String,GetDate() ,113)
  SELECT Cast(GetDate() AS String)
  /*Msg 243, Level 16, State 2, Line 40
  Type dbo.String is not a defined system type.
  Msg 243, Level 16, State 2, Line 41
  Type String is not a defined system type.*/

本教程内容尚未完结，请点击下方链接查看剩余内容~您可以下载SQL Prompt试用版跟着文章尝试一下~

相关内容推荐：

SQL语法提示工具SQL Prompt教程：使用没有明确长度的可变长度数据类型（下）

想要购买SQL Prompt正版授权，或了解更多产品信息请点击