SQL語法提示工具SQL Prompt使用教程：為什么應(yīng)該始終指定列是否接受空值

---

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

點(diǎn)擊下載SQL Prompt正式版

在大多數(shù)情況下，需要指定列是否應(yīng)允許空條目。依靠默認(rèn)值并不是一個(gè)好主意，假設(shè)，如果不使用NULL或NOT NULL顯式指定列的可為空性，則該列應(yīng)為可為空。如果您不為給定的數(shù)據(jù)類型選擇該選項(xiàng)，那么控制發(fā)生什么事情的規(guī)則很難解釋，即使您了解這些規(guī)則，您的團(tuán)隊(duì)或后繼者也會(huì)愿意這樣做嗎？

在SQL中，NOT NULL子句是“邏輯約束”，用于確保列永遠(yuǎn)不會(huì)獲得分配給它的空值。相反，NULL子句清楚表明希望該列接受空值。如果您的表規(guī)范不包含這些子句，則從數(shù)據(jù)庫(kù)屬性、連接設(shè)置或數(shù)據(jù)類型的默認(rèn)值確定是否存在空值?；旧?，您并不總是知道，并且可以輕松地得到不允許為空的列。

如果在創(chuàng)建或更改表或聲明表變量時(shí)未能指定列的可為空性，則SQL Prompt的最佳實(shí)踐代碼分析規(guī)則BP014會(huì)警告您（每個(gè)表達(dá)式一次違反一次）。

為什么要關(guān)心列是否為空？

之所以需要指定此名稱，是因?yàn)殛P(guān)系數(shù)據(jù)庫(kù)旨在有效地防止不良數(shù)據(jù)進(jìn)入。約束是實(shí)現(xiàn)此目的的方法。因此，必須對(duì)不能合法包含null的所有列使用NOT NULL。 如果您指定一列為非空值，那么您將定義一個(gè)約束，以確保該列永遠(yuǎn)不會(huì)容納或接受空值，因此您不能意外的將該值保留下來。通過在列中允許空值，除非使用ISNULL（）、IFNULL（）和NULLIF（）之類的函數(shù)來處理空值，否則還使聚合變得棘手，并使WHERE子句產(chǎn)生意外結(jié)果。

如果不指定該列，則不要假設(shè)該列將允許為空

本文旨在證明為什么您應(yīng)該始終指定在任何表中定義的列是否允許空值。不能深入討論NOT NULL約束是否是一件好事，而只是解釋了為什么需要聲明自己的偏好。

您可能會(huì)認(rèn)為，如果您在列的定義中未包含NOT NULL約束，那么該列將可以為空。不，錯(cuò)了。它可以為空，但也可能不是。它取決于數(shù)據(jù)類型、數(shù)據(jù)庫(kù)設(shè)置和連接設(shè)置。除非您能記住所有規(guī)則并保證用于DDL腳本的連接類型，否則接受始終指定列為NULL或NOT NULL的單個(gè)規(guī)則要簡(jiǎn)單得多。

現(xiàn)在，我們將研究各種因素，這些因素可以決定一列是否得到NOT NULL約束（如果您未指定的話）。

您的數(shù)據(jù)庫(kù)指定默認(rèn)值

如果程序員在創(chuàng)建或更改表時(shí)未指定列的可空性，則數(shù)據(jù)庫(kù)設(shè)置（特別是該ANSI_NULL_DEFAULT選項(xiàng)）將確定該列是NULL還是NOT NULL，除非您擁有覆蓋該列的SQL Server連接或其他因素，我會(huì)解釋，將其覆蓋。

ANSI_NULL_DEFAULT選項(xiàng)是sql_option設(shè)置之一，語法為：

ALTER Database SET { database_name | CURRENT } SET ANSI_NULL_DEFAULT { ON | OFF }

如果設(shè)置為ON，則在發(fā)出CREATE TABLE或ALTER TABLE語句時(shí)，允許所有未顯式定義為NOT NULL的用戶定義數(shù)據(jù)類型或列為空值。

您可以通過以下方式查看當(dāng)前數(shù)據(jù)庫(kù)的設(shè)置：

SELECT DATABASEPROPERTYEX(Db_Name(), 'IsAnsiNullDefault');

返回1或0。

您的連接指定默認(rèn)

您可以通過應(yīng)用程序的連接設(shè)置覆蓋數(shù)據(jù)庫(kù)設(shè)置。所有常用的設(shè)置都可以做到這一點(diǎn)。您使用的連接通常會(huì)指定默認(rèn)值應(yīng)為NULL。這樣做可能不是很明智，但它是ANSI標(biāo)準(zhǔn)，這表明如果將更通用的ANSI_DEFAULTS設(shè)置為ON，也會(huì)發(fā)現(xiàn)它還將ANSI_NULL_DFLT_ON設(shè)置為ON。

SSMS允許您指定用于連接到SQL Server的默認(rèn)值，如果需要，可以覆蓋ANSI標(biāo)準(zhǔn)。存在集ANSI_NULL_DFLT_OFF和集ANSI_NULL_DFLT_ON，盡管它們不能同時(shí)設(shè)置為ON。您可以選擇將兩者都關(guān)閉，在這種情況下，您只是選擇繼承數(shù)據(jù)庫(kù)默認(rèn)值，或者可以通過將ANSI_NULL_DFLT_ON設(shè)置為ON來堅(jiān)持默認(rèn)值是NULL。如果您愿意，可以通過將ANSI_NULL_DFLT_OFF設(shè)置為ON來覆蓋數(shù)據(jù)庫(kù)設(shè)置，以使默認(rèn)值為 NOT NULL。

SET ANSI_NULL_DFLT_ON { ON | OFF }
SET ANSI_NULL_DFLT_OFF { ON | OFF }

SQLCMD、BCP和SSMS略有不同，但通常它們是一致的。連接時(shí)，SQL Server的SQL Server本機(jī)客戶端ODBC驅(qū)動(dòng)程序和SQL Server的SQL Server本機(jī)客戶端OLE DB提供程序會(huì)自動(dòng)設(shè)置ANSI_NULL_DFLT_ON為ON。但是，對(duì)于來自遺留的db庫(kù)應(yīng)用程序的連接，SET ANSI_NULL_DFLT_ON的默認(rèn)設(shè)置是關(guān)閉的。

因此，如果我們想查看為連接啟用了哪些設(shè)置，則可以運(yùn)行…

SELECT Setting FROM (VALUES 
(1 , 'DISABLE_DEF_CNST_CHK'),
(2 , 'IMPLICIT_TRANSACTIONS'),
(4 , 'CURSOR_CLOSE_ON_COMMIT'),
(8 , 'ANSI_WARNINGS'),
(16 , 'ANSI_PADDING'),
(32 , 'ANSI_NULLS'),
(64 , 'ARITHABORT'),
(128 , 'ARITHIGNORE'),
(256 , 'QUOTED_IDENTIFIER'),
(512 , 'NOCOUNT'),
(1024 , 'ANSI_NULL_DFLT_ON'),
(2048 , 'ANSI_NULL_DFLT_OFF'),
(4096 , 'CONCAT_NULL_YIELDS_NULL'),
(8192 , 'NUMERIC_ROUNDABORT'),
(16384 , 'XACT_ABORT'))f(Bit,Setting)
WHERE bit & @@Options =bit

返回：

數(shù)據(jù)類型定義為NOT NULL

除非另外指定，否則Microsoft提供的幾個(gè)數(shù)據(jù)類型（timestamp和sysname）都不為空。您可以根據(jù)系統(tǒng)數(shù)據(jù)類型指定自己的別名數(shù)據(jù)類型，并指定它們是否默認(rèn)為可為空。要查看哪些數(shù)據(jù)類型不為空，可以使用如下查詢：…

SELECT name FROM sys.types WHERE is_nullable=0

要使它們可為空或不可為空，請(qǐng)使用以下語法

CREATE TYPE [ schema_name. ] type_name 
{ 
 [
 FROM base_type 
 [ ( precision [ , scale ] ) ] 
 [ NULL | NOT NULL ]
 ]

您會(huì)看到這對(duì)于處理具有特定維度、含義或用途的數(shù)據(jù)是多么的方便。您可以引用一個(gè)姓氏數(shù)據(jù)類型，并知道它不可能NULL（盡管它可以為'null'），并且它的最大值可以在數(shù)據(jù)庫(kù)中的一個(gè)地方更改，如果您突然發(fā)現(xiàn)它的長(zhǎng)度不夠。

對(duì)于基本類型為數(shù)字或十進(jìn)制的數(shù)字?jǐn)?shù)據(jù)，它也非常方便。這意味著出錯(cuò)和意外截?cái)嘁粋€(gè)值要困難得多。除了精度和規(guī)模之外，您可以指定其默認(rèn)為空。

這意味著用戶別名類型是數(shù)據(jù)類型之一，通常最好不要通過覆蓋可空性規(guī)范（如果存在）來嘗試通過在基于此類型創(chuàng)建列時(shí)顯式指定NULL來覆蓋它。有人已經(jīng)決定類型必須為NULL或NOT NULL，這可能是一個(gè)很好的理由。

列參與主鍵

如果您為主鍵分配一列，則為NOT NULL。讓我們演示一下：

CREATE TABLE TestOutNullability
(
MyTimestamp TIMESTAMP,
ObjectName sysname,
MyInt INT,
MyCode NVARCHAR(120),
CONSTRAINT myPK PRIMARY KEY (MyInt,MyCode)
)

因此，讓我們看看所有未指定可空性的列是否都將允許空值：

SELECT c.name, c.is_nullable FROM sys.tables AS T
INNER JOIN sys.columns AS C
ON C.object_id = T.object_id
WHERE t.name='TestOutNullability'
ORDER BY c.column_id

這些列均不可為空。我們可以通過使SSMS使用生成的構(gòu)建腳本對(duì)表進(jìn)行反向工程來證明這一點(diǎn)

/****** Object: Table [dbo].[TestOutNullability] Script Date: 07/02/2020 19:39:12 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[TestOutNullability](
    [MyTimestamp] [timestamp] NOT NULL,
    [ObjectName] [sysname] NOT NULL,
    [MyInt] [int] NOT NULL,
    [MyCode] [nvarchar](120) NOT NULL,
 CONSTRAINT [myPK] PRIMARY KEY CLUSTERED 
(
    [MyInt] ASC,
    [MyCode] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO

看這里！所有列上的NOT NULL約束，即使您的連接設(shè)置指定允許它們?yōu)槟J(rèn)值也是如此。

結(jié)論

我不打算討論在表的列中使用允許空值是否是一個(gè)好主意。但是，可以肯定地說，通常應(yīng)明確指定一列是否應(yīng)允許它們。我會(huì)說“一般”，因?yàn)槿绻褂玫氖怯脩舳x的別名類型，則有一個(gè)參數(shù)可以忽略該參數(shù)，以便在數(shù)據(jù)庫(kù)中使用該類型是一致的。為了安全起見，您需要確保已在創(chuàng)建別名類型的代碼中指定了它！

您可能會(huì)在CREATE TABLE編寫代碼時(shí)理解該代碼，以及在執(zhí)行DDL代碼時(shí)的連接狀態(tài)，但是它是否可重復(fù)？繼承您的代碼的可憐人或必須閱讀該代碼的團(tuán)隊(duì)成員，會(huì)得到什么啟發(fā)嗎？他們會(huì)想要得到線索嗎？

本文內(nèi)容到這里就完結(jié)了，希望對(duì)您有所幫助~感興趣的朋友可以下載SQL Prompt試用版免費(fèi)體驗(yàn)！或者關(guān)注我們慧都網(wǎng)了解更多產(chǎn)品相關(guān)資訊~

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

SQL Prompt 使用教程>>>

---

想要購(gòu)買SQL Prompt正版授權(quán)，或了解更多產(chǎn)品信息請(qǐng)點(diǎn)擊【咨詢?cè)诰€客服】