SQL語(yǔ)法提示工具SQL Prompt教程：使用沒(méi)有明確長(zhǎng)度的可變長(zhǎng)度數(shù)據(jù)類(lèi)型（下）

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

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

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

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

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

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)建了一個(gè)VARCHAR列，該列的長(zhǎng)度為該列中值的最長(zhǎng)字符串（在此cas中，“Tetherabumfitt”和“Metherabumfitt”；14個(gè)字符）。

我們可以通過(guò)帶有臨時(shí)表的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)
   */

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

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

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

SELECT TOP 1000 * FROM bigdirectory

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

SQL Prompt在INSERT INTO

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

……依此類(lèi)推，直到……

執(zhí)行上面的代碼，讓我們?cè)俅问褂梅奖愕膕ys.dm_exec_describe_first_result_set DMV，以獲取SELECT * FROM #temptable查詢(xún)結(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ù)類(lèi)型、長(zhǎng)度和可空性，如下所示（要使其正常工作，示例行中將需要一些長(zhǎng)字符串和空值）：

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

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í)際長(zhǎng)度。

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

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)入時(shí)，TVC技術(shù)可能會(huì)很方便。但是，主要目的是向您展示SQL Server可以很好地檢測(cè)字符串?dāng)?shù)據(jù)類(lèi)型的正確長(zhǎng)度和可空性。

結(jié)論

SQL Server要求您指定字符串?dāng)?shù)據(jù)類(lèi)型的長(zhǎng)度。您可能會(huì)認(rèn)為，因?yàn)樵诒Ａ糸L(zhǎng)度時(shí)它并不反對(duì)，所以它會(huì)為您自動(dòng)檢測(cè)長(zhǎng)度。不，一點(diǎn)都不。如果聲明的列作為CHAR、NCHAR、VARCHAR或者NVARCHAR沒(méi)有長(zhǎng)度，SQL Server讀取的長(zhǎng)度將為1。這是任何可變長(zhǎng)度的字符串來(lái)說(shuō)，這都是一個(gè)愚蠢的長(zhǎng)度，它等于毫無(wú)用處的廢話(huà)，但我們?nèi)匀粓?jiān)持使用它。

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

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

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

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

SQL語(yǔ)法提示工具SQL Prompt教程：使用沒(méi)有明確長(zhǎng)度的可變長(zhǎng)度數(shù)據(jù)類(lèi)型（上）

想要購(gòu)買(mǎi)SQL Prompt正版授權(quán)，或了解更多產(chǎn)品信息請(qǐng)點(diǎn)擊