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

---

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

點擊下載SQL Prompt正式版

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

在SQL中，NOT NULL子句是“邏輯約束”，用于確保列永遠不會獲得分配給它的空值。相反，NULL子句清楚表明希望該列接受空值。如果您的表規范不包含這些子句，則從數據庫屬性、連接設置或數據類型的默認值確定是否存在空值。基本上，您并不總是知道，并且可以輕松地得到不允許為空的列。

如果在創建或更改表或聲明表變量時未能指定列的可為空性，則SQL Prompt的最佳實踐代碼分析規則BP014會警告您（每個表達式一次違反一次）。

為什么要關心列是否為空？

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

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

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

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

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

您的數據庫指定默認值

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

ANSI_NULL_DEFAULT選項是sql_option設置之一，語法為：

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

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

您可以通過以下方式查看當前數據庫的設置：

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

返回1或0。

您的連接指定默認

您可以通過應用程序的連接設置覆蓋數據庫設置。所有常用的設置都可以做到這一點。您使用的連接通常會指定默認值應為NULL。這樣做可能不是很明智，但它是ANSI標準，這表明如果將更通用的ANSI_DEFAULTS設置為ON，也會發現它還將ANSI_NULL_DFLT_ON設置為ON。

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

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

SQLCMD、BCP和SSMS略有不同，但通常它們是一致的。連接時，SQL Server的SQL Server本機客戶端ODBC驅動程序和SQL Server的SQL Server本機客戶端OLE DB提供程序會自動設置ANSI_NULL_DFLT_ON為ON。但是，對于來自遺留的db庫應用程序的連接，SET ANSI_NULL_DFLT_ON的默認設置是關閉的。

因此，如果我們想查看為連接啟用了哪些設置，則可以運行…

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

返回：

數據類型定義為NOT NULL

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

SELECT name FROM sys.types WHERE is_nullable=0

要使它們可為空或不可為空，請使用以下語法

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

您會看到這對于處理具有特定維度、含義或用途的數據是多么的方便。您可以引用一個姓氏數據類型，并知道它不可能NULL（盡管它可以為'null'），并且它的最大值可以在數據庫中的一個地方更改，如果您突然發現它的長度不夠。

對于基本類型為數字或十進制的數字數據，它也非常方便。這意味著出錯和意外截斷一個值要困難得多。除了精度和規模之外，您可以指定其默認為空。

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

列參與主鍵

如果您為主鍵分配一列，則為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使用生成的構建腳本對表進行反向工程來證明這一點

/****** 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約束，即使您的連接設置指定允許它們為默認值也是如此。

結論

我不打算討論在表的列中使用允許空值是否是一個好主意。但是，可以肯定地說，通常應明確指定一列是否應允許它們。我會說“一般”，因為如果您使用的是用戶定義的別名類型，則有一個參數可以忽略該參數，以便在數據庫中使用該類型是一致的。為了安全起見，您需要確保已在創建別名類型的代碼中指定了它！

您可能會在CREATE TABLE編寫代碼時理解該代碼，以及在執行DDL代碼時的連接狀態，但是它是否可重復？繼承您的代碼的可憐人或必須閱讀該代碼的團隊成員，會得到什么啟發嗎？他們會想要得到線索嗎？

本文內容到這里就完結了，希望對您有所幫助~感興趣的朋友可以下載SQL Prompt試用版免費體驗！或者關注我們慧都網了解更多產品相關資訊~

相關內容推薦：

SQL Prompt 使用教程>>>

---

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