We are designing a new database, and need to know if a nvarchar(n) column will use any bytes to store a null value?
If a nvarchar(n) column stores a null value, how many bytes are used?
How many bytes are used to store and empty string in a nvarchar(n)? (i.e."")
The correct answer for SQL Server is, "It Depends".
Here's a test table...
CREATE TABLE dbo.VarcharTest01
(
C01 INT NOT NULL
,C02 VARCHAR(10) NULL
,C03 VARCHAR(10) NULL
,C04 VARCHAR(10) NULL
,C05 VARCHAR(10) NULL
,C06 VARCHAR(10) NULL
,C07 INT NULL
,C08 VARCHAR(10) NULL
,C09 VARCHAR(10) NULL
,C10 VARCHAR(10) NULL
,C11 VARCHAR(10) NULL
,C12 VARCHAR(10) NULL
,CONSTRAINT PK_VarcharTest01
PRIMARY KEY CLUSTERED (c01)
)
;
If we populate C01 and C07, that produces the following map dump when using DBCC Page (I formatted it for easy understanding).
All NULLs, Zero Bytes
--===== Results: Only Int Columns are populated.
-- C01 = 1
-- C07 = -1
Note that all values are "BIG ENDIAN".
Width of Null Bitmap is ColCnt/8+1 (12/8+1 = 2)
Note than NOTHING about the 10 VARCHAR columns has been recorded.
It's because they're all NULL.
Last
Meta Fixd All NULL
Data Byte Col Bit
A B OffS C01 C07 Cnt Map
-- -- ---- -------- -------- ---- ----
1 2 3 4 5 6 7 8 9 101112 1314 1516 Decimal Byte Number
-- -- ---- -------- -------- ---- ----
10 00 0c00 01000000 ffffffff 0c00 be0f
So one of the answers for how many bytes a NULL value is is absolutely ZERO.
Empty Strings
If we populate C02 with an "Empty String", only the bitmap changes to say the column is no longer a NULL but nothing else happens... so one of the answers for how much an "Emptry String" takes is Zero. In all cases, an "Empty String" occupies the same space as a NULL (and that can change as we'll soon see). Only the bitmap is changed to say it's no longer a NULL.
Here are the results from that. Note that ONLY the bitmap changed in valued.
--===== Results: Int Columns are populated.
-- C02 (left-most VARCHAR populated with "Empty String"
Note that all values are "BIG ENDIAN"
Width of Null Bitmap is ColCnt/8+1 (12/8+1 = 2)
Last
Meta Fixd All NULL
Data Byte Col Bit
A B OffS C01 C07 Cnt Map
-- -- ---- -------- -------- ---- ----
1 2 3 4 5 6 7 8 9 101112 1314 1516 Decimal Byte Number
-- -- ---- -------- -------- ---- ----
10 00 0c00 01000000 ffffffff 0c00 bc0f
First Non-Null Variable Width Column - 5 Bytes
If we populate column C02 with an "A", how many bytes do you think it will take? The correct answer is "5"... two for the "Variable Width column count", two for the "1st Variable Width Column Offset" (it does NOT store the length of the column!), and 1 for the single byte entry of "A". Here are the results on the page...
--===== Results: Int Columns are populated.
-- C02 (left-most VARCHAR populated with the letter "A"
Note that all values are "BIG ENDIAN"
Width of Null Bitmap is ColCnt/8+1 (12/8+1 = 2)
Last Var
Meta Fixd All NULL Wdth 1st
Data Byte Col Bit Col VCol C
A B OffS C01 C07 Cnt Map Cnt OffS 02
-- -- ---- -------- -------- ---- ---- ---- ---- --
1 2 3 4 5 6 7 8 9 101112 1314 1516 1718 1920 21 Decimal Byte Number
-- -- ---- -------- -------- ---- ---- ---- ---- --
30 00 0c00 01000000 ffffffff 0c00 bc0f 0100 1500 41
2 Bytes per NULL When...
Let's add the letter "G" to C08. Here are the results on the page... get ready for a shock!
--===== Results: Int Columns are populated.
-- C02 (left-most VARCHAR populated with the letter "A")
-- C08 (the 6th VARCHAR column with the letter "G")
Note that all values are "BIG ENDIAN"
Width of Null Bitmap is ColCnt/8+1 (12/8+1 = 2)
Last Var C02 C03 C04 C05 C06 C08
Meta Fixd All NULL Wdth 1st 2nd 3rd 4th 5th 6th
Data Byte Col Bit Col Col Col Col Col Col Col C C
A B OffS C01 C07 Cnt Map Cnt OffS OffS OffS OffS OffS OffS 02 08
-- -- ---- -------- -------- ---- ---- ---- ---- ---- ---- ---- ---- ---- -- --
1 2 3 4 5 6 7 8 9 101112 1314 1516 1718 1920 2122 2324 2526 2728 2930 31 32 Decimal Byte Number
-- -- ---- -------- -------- ---- ---- ---- ---- ---- ---- ---- ---- ---- -- --
30 00 0c00 01000000 ffffffff 0c00 3c0f 0600 1f00 1f00 1f00 1f00 1f00 2000 41 47
EVERY NULL VARCHAR COLUMN TO THE LEFT OF THE RIGHT-MOST POPULATED COLUMN HAS 2 BYTES ADDED TO THE ROW FOR STARTING POSITIONS THAT DON'T MATTER because the BitMap still says they're NULL.
And notice that nothing has yet shown up for columns C09 through C12. That's because there are no populated variable width columns to the right of of them.
So adding that 1 byte to C08 (the 6th VARCHAR column from the "left") caused 5*2 (Remember, C02 was already populated) or 10 bytes worth of starting positions to materialize plus the 1 byte for the data itself for a total of 11 bytes. Heh... and you thought using VARCHAR(1) was a sin... it is but it doesn't come close to this sin.
My recommendation to everyone that uses a variable width "Created_By" column at the far "right" of the table is to stop doing that! :D Plan your variable width columns to appear from left to right from least sparse to most sparse. That means that your "Created_By" column should probably be the first (reading the columns left to right) of the variable width columns. :D
And, sure... you could use "SPARSE" columns but at a cost of an additional 4 bytes per entry, it might not be worth it and the MS documentation on that subject is worth a heavy study before using "SPARSE" columns.
So, the correct answers are to the original and some of the follow on claims... are...
In SQL Server, "It Depends"...
A NULL VARCHAR (or NVARCHAR) can take 0 or 2 bytes depending on whether or not there are any populated variable width columns to the right of it.
And "Empty Strings" work exactly the same way as NULLs concerning space used. It will also change the corresponding bit in the bitmap from "1" to "0".
Empty string will use 2 bytes. More details at: nchar-and-nvarchar
NULL will use 1 bit in the NULL bitmap.
It is a physical implementation detail, so it could be different for Oracle an for SQL Server. With SQL Server, NULLs a represented with a single bit in the null bitmap (one bit for each nullable column). An empty string, be it varchar or nvarchar, it's two bytes. Hope someone from Oracle side will complement.
