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

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

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

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

SQL Server可以做得更好，而不是像這樣聳聳肩就放棄。當(dāng)它需要時(shí)，它可能會(huì)非常聰明。例如，它可以在像這樣的表值構(gòu)造函數(shù)（ TVC ）語句中計(jì)算出數(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)建了一個(gè)VARCHAR列，該列的長度為該列中值的最長字符串（在此cas中，“Tetherabumfitt”和“Metherabumfitt”；14個(gè)字符）。

我們可以通過帶有臨時(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)入，那么確保獲得合理長度的字符串?dāng)?shù)據(jù)類型的最佳方法是使用TVC。

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

讓我們嘗試一下。這是業(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，則在閱讀本文時(shí)會(huì)在椅子上來回?cái)[動(dòng)，因?yàn)槟谶@里有相當(dāng)不錯(cuò)的優(yōu)勢。單擊網(wǎng)格的左上角正方形以突出顯示整個(gè)批次，然后單擊鼠標(biāo)右鍵并選擇“腳本作為插入”選項(xiàng)。

SQL Prompt在INSERT INTO

在這種情況下，我們將堅(jiān)持使用1000行，并略微修改此代碼以使其SELECT INTO與TVC中的臨時(shí)表配合使用，從而避免使用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)建一個(gè)良好的整潔表，然后使用它導(dǎo)入整個(gè)數(shù)據(jù)（在這個(gè)實(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)入時(shí)，TVC技術(shù)可能會(huì)很方便。但是，主要目的是向您展示SQL Server可以很好地檢測字符串?dāng)?shù)據(jù)類型的正確長度和可空性。

結(jié)論

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

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

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

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

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

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

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