主页 / dba / 问题 / 164698
Accepted
MrVimes
Asked: 2017-02-18 06:58:14 +0800 CST

在 varchar 列中存储 NULL 与存储 ''

  • 772

我意识到这可能被标记为重复，但我特别询问 SQL Server 2005

我在互联网上阅读了相互矛盾的建议，所以我在这里问。特别是在 SQL Server 2005 中，varchar 列中的 NULL 是否与空字符串占用相同的空间？

我在另一个驱动器上构建了一个“保留”表，并用源表中的数据填充它，并且在字段为空白的地方我习惯nullif([field],'')插入空值来代替空白。

然后我建立了一个与保持表结构完全相同的新表，但我没有用 null 替换空白，而是插入了空白，到目前为止它似乎占用了更多空间（我还没有完成填充它和我还不能确定它是否占用了更多数据）

所以在我进一步填充它并最终得到一个比我想象的更大的表格之前，我最好插入空值或空白吗？

编辑：

将数据从holding表迁移到新表后，新表约大4gb。

表大小差异

表设计中只有两个小的区别 - 'serial_number' 字段在保存表中是 char(15)，但在目标表中是 varchar(15)。（序列号的最大长度是 14 并且有很多空值 - 如果我记得的话，我想大约是 3000 万），并且持有表的聚集索引有一个额外的列 - program_name..

抱桌

USE [Temp_holding_EWS]
GO
/****** Object:  Table [dbo].[AmtoteAccountActivity_holding]    
 Script Date: 02/17/2017 20:41:32 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[AmtoteAccountActivity_holding](
    [_Date] [char](8) NULL,[Community] [varchar](10) NULL,
    [AccountNumber] [varchar](50) NULL,
    [Branch] [varchar](10) NULL,
    [Window] [varchar](3) NULL,
    [Time] [char](8) NULL,[Balance_Forward] [varchar](10) NULL,
    [Transaction_Type] [varchar](10) NULL,
    [Program_Name] [varchar](10) NULL,
    [Race] [varchar](10) NULL,[Pool_Type] [varchar](10) NULL,
    [Amount] [money] NULL,[Runners] [varchar](60) NULL,
    [Total_Bet_Amount] [varchar](10) NULL,
    [Debit_Amount] [varchar](10) NULL,
    [Credit_Amount] [varchar](10) NULL,
    [Tx_Date] [char](8) NULL,
    [Check_Clear_Date] [varchar](10) NULL,
    [Refund_Amt] [varchar](10) NULL,
    [Bet_Pool_Modifier] [varchar](5) NULL,
    [RecordID] [int] IDENTITY(1,1) NOT NULL,
    [serial_number] [char](15) NULL,
    [handle]  AS 
       (CONVERT([money],[total_bet_amount],(0))-CONVERT([money],[refund_amt],(0))),
    [txdatetime]  AS (CONVERT([datetime],([tx_date]+' ')+[time],(11))),
    [dbdate]  AS (CONVERT([datetime],[_date],(11))),
    [Audit_Trail] [varchar](20) NULL,
 CONSTRAINT [PK_AmtoteAccountActivity_holding] PRIMARY KEY NONCLUSTERED 
(
    [RecordID] 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
SET ANSI_PADDING OFF

（聚集索引）

USE [Temp_holding_EWS]
GO
/****** Object:  Index [IX_AmtoteAccountActivity_holding] 
    Script Date: 02/17/2017 21:08:44 ******/
CREATE CLUSTERED INDEX [IX_AmtoteAccountActivity_holding] ON 
    [dbo].[AmtoteAccountActivity_holding] 
(
    [AccountNumber] ASC,
    [_Date] ASC,
    [Program_Name] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, 
    SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF,
ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]

目标表

USE [EWS]
GO
/****** Object:  Table [dbo].[AmtoteAccountActivity]    
Script Date: 02/17/2017 20:48:16 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[AmtoteAccountActivity](
    [_Date] [char](8) NULL,     [Community] [varchar](10) NULL,
    [AccountNumber] [varchar](50) NULL,
    [Branch] [varchar](10) NULL,[Window] [varchar](3) NULL,
    [Time] [char](8) NULL,  [Balance_Forward] [varchar](10) NULL,
    [Transaction_Type] [varchar](10) NULL,
    [Program_Name] [varchar](10) NULL,
    [Race] [varchar](10) NULL,
    [Pool_Type] [varchar](10) NULL,
    [Amount] [money] NULL,[Runners] [varchar](60) NULL,
    [Total_Bet_Amount] [varchar](10) NULL,
    [Debit_Amount] [varchar](10) NULL,
    [Credit_Amount] [varchar](10) NULL,
    [Tx_Date] [char](8) NULL,
    [Check_Clear_Date] [varchar](10) NULL,
    [Refund_Amt] [varchar](10) NULL,
    [Bet_Pool_Modifier] [varchar](5) NULL,
    [RecordID] [int] IDENTITY(1,1) NOT NULL,
    [serial_number] [varchar](15) NULL,
    [handle]  AS 
       (CONVERT([money],[total_bet_amount],(0))-CONVERT([money],[refund_amt],(0))),
    [txdatetime]  AS (CONVERT([datetime],([tx_date]+' ')+[time],(11))),
    [dbdate]  AS (CONVERT([datetime],[_date],(11))),
    [Audit_Trail] [varchar](20) NULL,
 CONSTRAINT [PK_AmtoteAccountActivity2] PRIMARY KEY NONCLUSTERED 
(
    [RecordID] 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
SET ANSI_PADDING OFF

（聚集索引）

USE [EWS]
GO
/****** Object:  Index [IX_AmtoteAccountActivity2]  Script Date: 02/17/2017 21:06:29 ******/
CREATE CLUSTERED INDEX [IX_AmtoteAccountActivity2] ON [dbo].[AmtoteAccountActivity] 
(
    [AccountNumber] ASC,
    [_Date] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, 
SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, 
ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]

（注意：对于任何想知道为什么它显然将财务和数值存储在字符字段中的人：那是 17 年前的原始表设计（不是我设计的），现在有数百个 sql 查询在这个数据库上运行，它更少努力将它们保持为 varchar 并且查询保持它们的转换，而不是将它们更改为货币、整数或小数并更改数百个查询）

sql-server sql-server-2005

3 个回答

  1. Best Answer

    让我们创建三个带有 varchar 列的表，其中两个允许 NULL，一个不允许。

    CREATE TABLE dbo.x1(id int IDENTITY(1,1) PRIMARY KEY, field varchar(5) null);
CREATE TABLE dbo.x2(id int IDENTITY(1,1) PRIMARY KEY, field varchar(5) null);
CREATE TABLE dbo.x3(id int IDENTITY(1,1) PRIMARY KEY, field varchar(5) not null);

    用 1,000,000 行填充它们：

    ;WITH x(x) AS (SELECT 0 UNION ALL SELECT x+1 FROM x WHERE x < 1000000)
INSERT dbo.x1(field) SELECT NULL FROM x OPTION (MAXRECURSION 0);
INSERT dbo.x2(field) SELECT '' FROM dbo.x1;
INSERT dbo.x3(field) SELECT '' FROM dbo.x1;

    让我们检查一下大小：

    SELECT COUNT(*)*8192/1024. FROM sys.dm_db_database_page_allocations(DB_ID(), 
  OBJECT_ID(N'dbo.x1'), 1, NULL, 'DETAILED');
SELECT COUNT(*)*8192/1024. FROM sys.dm_db_database_page_allocations(DB_ID(), 
  OBJECT_ID(N'dbo.x2'), 1, NULL, 'DETAILED');
SELECT COUNT(*)*8192/1024. FROM sys.dm_db_database_page_allocations(DB_ID(), 
  OBJECT_ID(N'dbo.x3'), 1, NULL, 'DETAILED');

    结果：

    12,928 KB
12,936 KB
12,936 KB

    因此，对于 1,000,000 行，选择NULLover''可以节省高达 8 KB（这甚至没有反映在 中sp_spaceused，因为您保存的那一页仍然是保留的，只是没有分配）。

    重复堆（再次，必须进行多次测试，因为我们正在猜测您的实际表结构）：

    12,872 KB
12,872 KB
12,928 KB

    因此，正如我建议的那样，可以忽略不计，即使推断超过 120,000,000 行，最大可能的差异（再次取决于您的架构）在适当的表上将是 960KB，在堆上是 6.7MB。如果您的服务器磁盘空间如此紧张，以至于 6.7MB 将用于驱动决策，那么您可能会考虑与您花在调查上的时间相比，额外磁盘的成本是多少。

    恕我直言，在决定使用 NULL 或不表示“无数据”之间存在更重要的原因。一个有很多意见和评论的好问题在这里：

    • 9

  2. 请参阅这篇文章，它解释了 SQL 如何存储 NULL。

    基本上，可变宽度列 (varchar) 存储指示 null 或非 null 的位图。如果它为空，则为 varchar 字段分配零字节并且该位被翻转。

    对于固定宽度的列 (char)，仍会分配整个字段，其中不存储任何数据。所以一个 10 字节的 char 字段将分配 10 个字节，无论是否为 NULL。

    那篇文章使用数据、NULL 和空字符串进行了插入。然后它轮询页面大小以查看内部发生的情况。

    对于 Null 和 Empty 字符串，为 varchar 字段分配 0 个字节。

    • 3

  3. 至少对于标准 ( FixedVar ) 记录格式，它对表占用的空间量没有任何影响（它可能对索引产生微小的影响，如后面讨论的那样）。

    nullvarchar和空字符串都以完全相同的方式存储。区分它们的唯一方法是空位图中是否存在1或0。它们在可变长度列数据部分中都采用零长度，并且如果它们后面没有任何包含数据的列，它们也可以避免在可变列偏移数组中占用两个字节。

    其中一条评论说

    对于不允许使用 NULL 的表，出于多种原因，您希望将列定义为 NOT NULL，其中最重要的是消除了对该列的空位图的要求

    这不适用于数据页。请参阅神话 #6b：空位图仅包含可空列的位。

    索引的细微差别在于，如果参与索引的所有列都不可为空，则省略空位图。

    但是，差异可以忽略不计，您应该选择能够为您提供所需语义的选项。

    • 2

相关问题