SQL語法提示工具SQL Prompt教程：使用沒有明確長度的可變長度數(shù)據(jù)類型（上）

SQL Prompt根據(jù)數(shù)據(jù)庫的對象名稱、語法和代碼片段自動進(jìn)行檢索，為用戶提供合適的代碼選擇。自動腳本設(shè)置使代碼簡單易讀--當(dāng)開發(fā)者不大熟悉腳本時尤其有用。SQL Prompt安裝即可使用，能大幅提高編碼效率。此外，用戶還可根據(jù)需要進(jìn)行自定義，使之以預(yù)想的方式工作。

點擊下載SQL Prompt免費版

如果您聲明一個可變長度的字符串，或在不指定其長度的情況下強(qiáng)制字符串，則可能會被“靜默”字符串截斷。一些開發(fā)人員訴諸使用(MAX)規(guī)范，這也是一個錯誤。當(dāng)您導(dǎo)入文本并且根本不知道每個字符串的正確長度時，Phil Factor解釋了其中的危險，然后提供了解決該問題的方法。本文是該教程的上半部分內(nèi)容~

在SQL中，如果您以CHAR、NCHAR、VARCHAR或者NVARCHAR這四種格式中的任何一種格式聲明字符串，不指定其長度，字符串被賦予一個字符的長度。 如果使用CAST或CONVERT強(qiáng)制轉(zhuǎn)換字符串，并犯同樣的錯誤，則該字符串的長度為30個字符。 為什么是1和30？ 僅出于歷史原因，但其他RDBMS也有類似的反應(yīng)。

無論哪種情況，您都可能會遇到“靜默”字符串截斷的麻煩，因此，如果您在可變長字符串聲明（BP007）和強(qiáng)制轉(zhuǎn)換期間忘記指定字符串長度，SQL Prompt會提供一些內(nèi)置的代碼分析規(guī)則來警告您（BP008）。

一些開發(fā)人員認(rèn)為，如果不考慮長度問題，SQL Server會滿足他們的需求。這并不像看起來那樣牽強(qiáng)，因為我將在其他情況下繼續(xù)演示。通常，此錯誤的上下文是從不知道列的正確字符串長度的數(shù)據(jù)源導(dǎo)入時發(fā)生的，并且需要創(chuàng)建目標(biāo)表。或者，您可能最終使用它MAX作為長度，這會帶來麻煩。

在本文的后面，我將向您展示一個方便的技巧來解決此問題。它使用“提示的腳本作為插入”功能來編寫表的前1000行的腳本，然后我們說服SQL Server通過在SELECT INTO語句中，通過表值構(gòu)造函數(shù)（TVC）導(dǎo)入數(shù)據(jù)來分配正確的數(shù)據(jù)類型和字符串長度。

忘記字符串長度的問題

通常，但并非總是如此，您的錯誤會很快顯現(xiàn)出來。

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

如果對變量執(zhí)行相同的操作，則可能會更加不愉快，因為字符串會被截斷為單個字符，但不會出錯。

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

函數(shù)或過程中的參數(shù)也是如此……

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'

并在函數(shù)的返回值中：

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'

如果對變量執(zhí)行相同的操作，則可能會更加不愉快，因為字符串會被截斷為單個字符，但不會出錯。

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

當(dāng)時的傳統(tǒng)觀點認(rèn)為，大多數(shù)數(shù)據(jù)庫字符串的長度都少于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長度的情況下使用它。 這可能是過程編碼所養(yǎng)成的習(xí)慣。 對于任何RDBMS，情況都是不同的。您可以確保以經(jīng)濟(jì)的方式存儲字符串，或者只是揮手示意，并將字符串分配為（MAX）長度規(guī)范，這會帶來所有的索引編制和性能折衷（這些數(shù)據(jù)類型不能指定為索引鍵列）。

使用別名數(shù)據(jù)類型

如果您需要默認(rèn)的字符串長度作為設(shè)計的一部分，則可以創(chuàng)建別名數(shù)據(jù)類型。這樣更安全：

--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操作中使用它，因為它不是已定義的系統(tǒng)類型。

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.*/

本教程內(nèi)容尚未完結(jié)，請點擊下方鏈接查看剩余內(nèi)容~您可以下載SQL Prompt試用版跟著文章嘗試一下~

相關(guān)內(nèi)容推薦：

SQL語法提示工具SQL Prompt教程：使用沒有明確長度的可變長度數(shù)據(jù)類型（下）

想要購買SQL Prompt正版授權(quán)，或了解更多產(chǎn)品信息請點擊