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

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

點(diǎn)擊下載SQL Prompt免費(fèi)版

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

在數(shù)據(jù)導(dǎo)入期間使用表值構(gòu)造函數(shù)分配合理的字符串長度

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

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表，您將能夠看到它創(chuàng)建了一個VARCHAR列，該列的長度為該列中值的最長字符串（在此cas中，“Tetherabumfitt”和“Metherabumfitt”；14個字符）。

我們可以通過帶有臨時表的SELECT INTO輕松地驗(yàn)證這一點(diǎn)……

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

…然后檢查創(chuàng)建的列的寬度…

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

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

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

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

SELECT TOP 1000 * FROM bigdirectory

現(xiàn)在，如果您有SQL Prompt，則在閱讀本文時會在椅子上來回擺動，因?yàn)槟谶@里有相當(dāng)不錯的優(yōu)勢。單擊網(wǎng)格的左上角正方形以突出顯示整個批次，然后單擊鼠標(biāo)右鍵并選擇“腳本作為插入”選項(xiàng)。

SQL Prompt在INSERT INTO

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

……依此類推，直到……

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

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);

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

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

我已經(jīng)對此進(jìn)行了多達(dá)10000行的測試，但其他方面做得更多。一旦所有列都有正確的數(shù)據(jù)類型、長度和可空性，就可以對長度進(jìn)行一些舍入，以允許出現(xiàn)異常值，創(chuàng)建一個良好的整潔表，然后使用它導(dǎo)入整個數(shù)據(jù)（在這個實(shí)驗(yàn)中為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;

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

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

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;

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

結(jié)論

SQL Server要求您指定字符串?dāng)?shù)據(jù)類型的長度。您可能會認(rèn)為，因?yàn)樵诒Ａ糸L度時它并不反對，所以它會為您自動檢測長度。不，一點(diǎn)都不。如果聲明的列作為CHAR、NCHAR、VARCHAR或者NVARCHAR沒有長度，SQL Server讀取的長度將為1。這是任何可變長度的字符串來說，這都是一個愚蠢的長度，它等于毫無用處的廢話，但我們?nèi)匀粓猿质褂盟?/p>

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

始終為任何基于文本的數(shù)據(jù)類型指定長度，例如NVARCHAR或VARCHAR。也不要過度使用該MAX規(guī)范，因?yàn)榻Y(jié)果列將無法被索引，并且會帶來性能負(fù)擔(dān)。

本教程內(nèi)容到這里就完結(jié)啦，感興趣的朋友可以繼續(xù)關(guān)注我們，我們會不斷更新相關(guān)資訊！您也可以下載SQL Prompt試用版進(jìn)行測評~

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

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

想要購買SQL Prompt正版授權(quán)，或了解更多產(chǎn)品信息請點(diǎn)擊“咨詢在線客服”