Storing NULL versus storing '' in a varchar column

Question

I realize this may be marked as duplicate, but I'm specifically asking in relation to SQL Server 2005

I have read conflicting advice on the internet so I'm asking here. Specifically in SQL Server 2005 does a NULL in a varchar column take up the same space as an empty string?

I built a 'holding' table on another drive, and populated it with the data from the source table, and wherever the fields were blank I used nullif([field],'') to insert nulls in place of the blanks.

Then I built a new table with exactly the same structure as the holding table, but instead of replacing blanks with null I just inserted the blanks, and so far it seems to be taking up more space (I'm not yet done populating it and I can't be sure it is taking up more data yet)

So before I fill it further and end up with a table bigger than I thought it would be, am I better off inserting nulls or blanks?

Edit:

After migrating the data from the holding table to the new table, the new table is approximately 4gb larger.

There are only two small differences in the table design - The 'serial_number' field is char(15) in the holding table but varchar(15) in the destination table. (The maximum length of a serial number is 14 and there are many empty values - I think around 30 million if I recall), and the clustered index for the holding table has an extra column - program_name..

Holding table

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

(Clustered index)

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]

Destination table

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

(Clustered index)

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]

(Note: For anyone wondering why it has apparently financial and numerical values stored in character fields: That was the original table design 17 years ago (not by me) and there are now hundreds of sql queries that run on this database, it is less work to keep them as varchar and the queries keep their casting, than it is to change them to money,int, or decimal and change hundreds of queries)

The space difference here will be negligible if any (share DDL). The more important factor is semantics. Does "blank space" mean absence of value? What if an empty string later becomes to mean something other than that? NULL is a useful thing; don't fear it. — Aaron Bertrand, Commented Feb 17, 2017 at 15:02
My problem is it's not negligible on a table with 120 million rows on an old server with limited disk space. Negligible suggests there is a difference. Some articles say null takes up 0 bytes + 2 bytes overhead, others say null value takes up no space. — MrVimes, Commented Feb 17, 2017 at 15:06
@MrVimes - when you say "I built a new table with exactly the same structure" is the column in question defined as NULL or NOT NULL? — Hannah Vernon, Commented Feb 17, 2017 at 15:08
to get a good answer, you need to add the DDL for both tables. — Hannah Vernon, Commented Feb 17, 2017 at 15:10
It seems you are fully capable of running a test to determine which of those articles to believe. So if your concern is only about space, not sure why you're asking. — Aaron Bertrand, Commented Feb 17, 2017 at 15:10

Community · Accepted Answer · 2017-04-13 12:42:56Z

Let's create three tables with a varchar column, two of them allowing NULL, one not.

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);

Populate them with 1,000,000 rows:

;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;

Let's check the size:

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');

Results:

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

So it looks like for 1,000,000 rows, choosing NULL over '' saves a whopping 8 KB (and this isn't even reflected in sp_spaceused, because that one page you saved is still reserved, just not allocated).

Repeated for a heap (again, have to do multiple tests since we're guessing about your actual table structure):

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

So, negligible, as I suggested, even extrapolating over 120,000,000 rows, the biggest possible difference (once more, depending on your schema) would be 960KB on a proper table, and 6.7MB on a heap. If your server is so tight on disk space that 6.7MB is going to drive decisions, you might consider how much an additional disk would cost when compared to the time you're spending investigating this.

IMHO, there are far more important reasons between deciding to use NULLs or not to represent "no data." A good question with lots of opinions and commentary is here:

Why shouldn't we allow NULLs?

Might be worth mentioning how compression does or does not make a difference, especially if he's running out of space on his server. — Joe Obbish, Commented Feb 17, 2017 at 16:51
@Joe Well, they're currently on 2005, which doesn't have compression. But sure, very little impact here. The data in my test tables here does not get a whole lot from compression (about 14-16%). If we can get full schema and data distribution, it might be more realistic, though. Of course the OP can test that on their own, and should, once they're on a non-prehistoric version, as it will be highly dependent on schema + data, and the cost/value of compression will be highly dependent on the workload (we know there isn't a lot of disk headroom, but there may not be much CPU headroom, either). — Aaron Bertrand, Commented Feb 17, 2017 at 17:13
My migration operation finished, and it seems like the table does now take up a significantly higher amount of space with empty strings instead of nulls. — MrVimes, Commented Feb 17, 2017 at 20:51
@MrVimas This could be due to how the data was laid onto pages during the migration, which could be quite different from how the data got into the source naturally, especially if parallel. Could you check it after a rebuild? — Aaron Bertrand, Commented Feb 17, 2017 at 21:10
I'm scared to do a rebuild. There's 30gb of space on the data drive, and in the past I've watched available space gradually fall away when rebuilding the clustered index on a large table. I'm reasonably happy that the table is smaller than it was, even if it's not as small as the 'holding' table. — MrVimes, Commented Feb 18, 2017 at 11:33

CaM · Accepted Answer · 2017-02-17 15:10:25Z

See this article, which explains how SQL stores NULLs.

Basically, a variable width column (varchar) stores a bitmap that indicates null or not null. If it's null, then zero bytes are allocated for the varchar field and the bit gets flipped.

For fixed width columns (char), the entire field is still allocated, with no data stored in it. So a 10-yte char field will allocate 10 bytes, NULL or not.

That article does an insert with data, with NULL, and with empty string. It then polls the page size to see what's going on internally.

For both Null and Empty string, 0 bytes are allocated for varchar fields.

Martin Smith · Accepted Answer · 2017-02-18 12:36:03Z

For at least the standard (FixedVar) record format it does not make any difference to how much space the table consumes (it might make a marginal difference to indexes as discussed later).

Both a null varchar and an empty string are stored in exactly the same way. The only way they are distinguished is whether there was a 1 or 0 in the null bitmap. They both take zero length in the variable length column data section and both can also avoid taking up two bytes in the variable column offset array if they are not followed by any columns that do contain data.

One of the comments says

For the table where NULLs are not allowed, you'd want to define the column as NOT NULL, for a number of reasons, not least of which is that removes the requirement for a null bitmap for that columnn

This is not true for datapages. See Myth #6b: The null bitmap only contains bits for nullable columns.

There is a slight difference for indexes in that if all the columns participating in an index are not nullable the null bitmap is omitted.

However the difference is negligible and you should be choosing the option that gives you the desired semantics.

Stack Exchange Network

Storing NULL versus storing '' in a varchar column

3 Answers 3

Your Answer

Not the answer you're looking for? Browse other questions tagged
sql-server
sql-server-2005
or ask your own question.

Linked

Hot Network Questions

Storing NULL versus storing '' in a varchar column

3 Answers 3

Your Answer

Sign up or log in

Post as a guest

Not the answer you're looking for? Browse other questions tagged sql-serversql-server-2005 or ask your own question.

Linked

Related

Hot Network Questions

Not the answer you're looking for? Browse other questions tagged
sql-server
sql-server-2005
or ask your own question.