4

What is the penalty in space taken for varchar types in SQL Server 2012 and above? That is, if I have 3 columns of type nvarchar (or varchar for that matter), and they are all empty, how much space is taken in the table?

CREATE TABLE dbo.ReferringUrl 
(
     Id int IDENTITY(1, 1) NOT NULL,
     RequestUrl nvarchar(384) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
     ReferringUrlName nvarchar(384) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
     ReferringIpAddress nvarchar(64) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
)
marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Peter Kellner
  • 14,748
  • 25
  • 102
  • 188
  • @NathanSkerl this question is about empty string, not null. Not sure if the linked answer is right, as storing zero for length would not differentiate for null . Could be that -1 length is null and 0 is empty but that is just a guess, a definitive answer would be good. – Andy Apr 01 '15 at 01:31
  • "both NULL and empty strings both take up zero bytes": [link](http://stackoverflow.com/questions/5618357/sql-server-null-vs-empty-string) – nathan_jr Apr 01 '15 at 03:20
  • In the example you give if all the columns were empty the row would have no variable length section at all. – Martin Smith Apr 01 '15 at 22:08

2 Answers2

5

You can test this out for yourself with DBCC PAGE or SQL Server internals viewer.

Some example cases are below to illustrate the following points.

  1. A varchar column that is null or empty takes up no space at all except 2 bytes in the column offset array (rows 4 and 5).
  2. If a variable length column is null or empty and only followed by other null/empty values none of these trailing empty columns even need the 2 bytes in the column offset array (rows 6 and 7).
  3. If all variable length columns are null or empty the row has no variable length section at all (rows 2 and 3).
INSERT dbo.ReferringUrl
       (Id,
        RequestUrl,
        ReferringUrlName,
        ReferringIpAddress)
VALUES (1,N'AAA','BBB','CCC'),
       (2,NULL,NULL,NULL),
       (3,'','',''),
       (4,NULL,NULL,'CCC'),
       (5,'','','CCC'),
       (6,'AAA',NULL,NULL),
       (7,'AAA','',''),
       (8,N'AAA','BBB','CCC')

1 - All columns have values

enter image description here

The image above is from SQL Server internals viewer - which also helpfully provides a key for the various components in the row shown below.

enter image description here

2 - All varchar columns are NULL

enter image description here

3 - All varchar columns have empty strings

enter image description here

4 - All varchar columns null except final one

enter image description here

5 - All varchar columns empty except final one

enter image description here

6 - All varchar columns null except first one

enter image description here

7 - All varchar columns empty except first one

(due to a bug in the internals viewer this does not get the highlighting but notice it is the same length as the previous row)

enter image description here

Martin Smith
  • 438,706
  • 87
  • 741
  • 845
4

A not-null varchar(n) value require 2 bytes for the length plus 1 byte for each character. An nvarchar(n) requires 2 bytes per character. A zero-length string requires only 2 bytes for both varchar and nvarchar, 2 bytes for the length plus zero bytes for the value. NULL values require no space at all since the corresponding bit in the null bitmap indicates the value is null and no length or value is stored.

Space requirements are different when page compression is enabled. Space needs depend on prefix and dictionary compression in that case.

Dan Guzman
  • 43,250
  • 3
  • 46
  • 71
  • The implication that "NULL values require no space at all" but that zero-length strings do is not accurate. They sometimes require space in the column offset array and sometimes don't but both get treated the same in that respect. – Martin Smith Apr 03 '15 at 19:15
  • @MartinSmith, you are of course right that there are exceptions to what I mentioned in my brief answer. There are some other interesting exceptions too, such as when columns are added in Enterprise (and Developer) editions due to online operations (http://rusanu.com/2011/07/13/online-non-null-with-values-column-add-in-sql-server-11/). Updates do data pages are deferred in some cases. – Dan Guzman Apr 03 '15 at 23:30
  • How is a 2-byte length (0..65535) able to represent the length of strings that are longer than 65,535 characters? – Ian Boyd Dec 13 '17 at 16:42
  • 1
    @IanBoyd, the limit for varchar(n) in SQL Server is 8000 characters. The length is not stored, but rather the offset to the column value within the physical row structure. The varchar(MAX) type is a different beast. Long varchar(MAX) values which are too large to be stored in-row are stored in separate pages. The row has a structure with the length and pointer to the first page lob page in that case. – Dan Guzman Dec 13 '17 at 19:05