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

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

點擊下載SQL Prompt免費版

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

在數據導入期間使用表值構造函數分配合理的字符串長度

SQL Server可以做得更好，而不是像這樣聳聳肩就放棄。當它需要時，它可能會非常聰明。例如，它可以在像這樣的表值構造函數（ TVC ）語句中計算出數據類型的長度及其可空性。

SELECT name
    FROM (VALUES --one to twenty 
  ('Yan'), --in Lincolnshire dialect
  ('Tyan'),
  ('Tethera'),
  ('Methera'),
  ('Pimp'),
  ('Sethera'),
  ('Lethera'),
  ('Hovera'),
  ('Dovera'),
  ('Dik'),
  ('Yanadik'),
  ('Tyanadik'),
  ('Tetheradik'),
  ('Metheradik'),
  ('Bumfitt'),
  ('Yanabumfit'),
  ('Tyanabumfitt'),
  ('Tetherabumfitt'),
  ('Metherabumfitt'),
  ('Giggot'))f(name)

如果使用此TVC SELECT INTO表，您將能夠看到它創建了一個VARCHAR列，該列的長度為該列中值的最長字符串（在此cas中，“Tetherabumfitt”和“Metherabumfitt”；14個字符）。

我們可以通過帶有臨時表的SELECT INTO輕松地驗證這一點……

SELECT name INTO #MyTemp
    FROM (VALUES --one to twenty 
  ('Yan'), --in Lincolnshire dialect
  ('Tyan'),
   ...etc ...
  ('Giggot'))f(name)

…然后檢查創建的列的寬度…

SELECT system_type_name FROM 
   sys.dm_exec_describe_first_result_set('SELECT * FROM #MyTemp',NULL,1)
   /*system_type_name
     varchar(14)
   */

這表明如果您需要從外部的基于文本的源（其中包含一個包含很多字符串的繁瑣的表）進行導入，那么確保獲得合理長度的字符串數據類型的最佳方法是使用TVC。

在INSERT INTO…VALUES語句中使用時，TVC的行數限制為1000行，如果超出該行，則會看到錯誤10738。 但是，就像上面說明的那樣，在使用VALUES的SELECT INTO語句中使用TVC時，我無法檢測到任何限制。

讓我們嘗試一下。這是業務目錄的首次導入，當然是用SQL Data Generator欺騙的。如果您想一起玩，可以從我的表沒有聚集索引（BP021）的文章中下載構建腳本和.sqlgen文件。為了證明這一點，我們將前1000行放入SSMS的網格視圖中。

SELECT TOP 1000 * FROM bigdirectory

現在，如果您有SQL Prompt，則在閱讀本文時會在椅子上來回擺動，因為您在這里有相當不錯的優勢。單擊網格的左上角正方形以突出顯示整個批次，然后單擊鼠標右鍵并選擇“腳本作為插入”選項。

SQL Prompt在INSERT INTO

在這種情況下，我們將堅持使用1000行，并略微修改此代碼以使其SELECT INTO與TVC中的臨時表配合使用，從而避免使用1000行的限制：

……依此類推，直到……

執行上面的代碼，讓我們再次使用方便的sys.dm_exec_describe_first_result_set DMV，以獲取SELECT * FROM #temptable查詢結果集的元數據：

SELECT name + ' ' + system_type_name
         + CASE WHEN is_nullable = 1 THEN ' NULL' ELSE ' NOT NULL' END
    FROM sys.dm_exec_describe_first_result_set('
  SELECT * FROM #temptable', NULL, 1);

它為每列指定了數據類型、長度和可空性，如下所示（要使其正常工作，示例行中將需要一些長字符串和空值）：

id int NOT NULL
Name varchar(37) NOT NULL
Address1 varchar(34) NULL
Address2 varchar(23) NULL
Town varchar(22) NOT NULL
City varchar(19) NOT NULL
County varchar(18) NOT NULL
Postcode varchar(8) NOT NULL
Region varchar(22) NOT NULL
BusinessType varchar(55) NULL
Leads varchar(17) NULL
Phone varchar(12) NULL
Fax varchar(12) NULL
Website varchar(56) NULL

我已經對此進行了多達10000行的測試，但其他方面做得更多。一旦所有列都有正確的數據類型、長度和可空性，就可以對長度進行一些舍入，以允許出現異常值，創建一個良好的整潔表，然后使用它導入整個數據（在這個實驗中為400萬行）。

CREATE TABLE BusinessDirectory
    (
    id INT NOT NULL,
    Name VARCHAR(40) NOT NULL,
    Address1 VARCHAR(40) NULL,
    Address2 VARCHAR(50) NULL,
    Town VARCHAR(30) NOT NULL,
    City VARCHAR(20) NOT NULL,
    County VARCHAR(20) NOT NULL,
    Postcode VARCHAR(8) NOT NULL,
    Region VARCHAR(30) NOT NULL,
    BusinessType VARCHAR(60) NULL,
    Leads VARCHAR(20) NULL,
    Phone VARCHAR(15) NULL,
    Fax VARCHAR(15) NULL,
    Website VARCHAR(60) NULL
    );
  INSERT INTO BusinessDirectory
    (id, Name, Address1, Address2, Town, City, County, Postcode, Region,
  BusinessType, Leads, Phone, Fax, Website)
    SELECT id, Name, Address1, Address2, Town, City, County, 
  Postcode, Region, BusinessType, Leads, Phone, Fax, Website
      FROM BigDirectory;

如果您不喜歡這種方法，那么在這種情況下，您當然可以輕松地從原始表中獲取最大實際長度。

但是，檢測列是否可為空是比較棘手的。

SELECT Max(Len(Name)), Max(Len(Address1)), Max(Len(Address2)), Max(Len(Town)),
    Max(Len(City)), Max(Len(County)), Max(Len(Postcode)), Max(Len(Region)),
    Max(Len(BusinessType)), Max(Len(Leads)), Max(Len(Phone)), Max(Len(Fax)),
    Max(Len(Website))
    FROM BigDirectory;

從外部基于文本的源中導入時，TVC技術可能會很方便。但是，主要目的是向您展示SQL Server可以很好地檢測字符串數據類型的正確長度和可空性。

結論

SQL Server要求您指定字符串數據類型的長度。您可能會認為，因為在保留長度時它并不反對，所以它會為您自動檢測長度。不，一點都不。如果聲明的列作為CHAR、NCHAR、VARCHAR或者NVARCHAR沒有長度，SQL Server讀取的長度將為1。這是任何可變長度的字符串來說，這都是一個愚蠢的長度，它等于毫無用處的廢話，但我們仍然堅持使用它。

如果您定義的變量字符串沒有長度，它將對您造成更可怕的后果。 它不僅會假定它的長度為1個字符，而且還會默默地謹慎地將分配給它的每個值減小為一個字符。 如果您未指定長度，則SQL Server決定將varchar減少為一個字符時，一定會感到很傻，因為如果在將數據類型轉換為NVARCHAR或VARCHAR時犯同樣的錯誤，它將產生一個帶有更合理的長度為30。

始終為任何基于文本的數據類型指定長度，例如NVARCHAR或VARCHAR。也不要過度使用該MAX規范，因為結果列將無法被索引，并且會帶來性能負擔。

本教程內容到這里就完結啦，感興趣的朋友可以繼續關注我們，我們會不斷更新相關資訊！您也可以下載SQL Prompt試用版進行測評~

相關內容推薦：

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

想要購買SQL Prompt正版授權，或了解更多產品信息請點擊