Continuing the small research that I have been doing on the matter of the Varchar(1) & Varchar(2) datatypes usage, I am taking this article to consider the situations when the columns are defined as NOT NULL.

Following the same scheme as with the first post of this series, here is the code for creating 2 similar tables with CHAR and VARCHAR datatypes, which are different to the first post that those 3 columns are not null:

CREATE TABLE dbo.CharTestTableNN
(
	id INT IDENTITY NOT NULL PRIMARY KEY,
	client_name CHAR(20) NOT NULL DEFAULT 'SuperClient',
	sex CHAR(1) NOT NULL DEFAULT 'M',
	confirmation CHAR(1) NOT NULL DEFAULT 'N',
	state_code CHAR(2) NOT NULL DEFAULT 'WA',
	some_date DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
	some_amount DECIMAL(9,2) NOT NULL DEFAULT 128.34,
);

CREATE TABLE dbo.VarcharTestTableNN
(
	id INT IDENTITY NOT NULL PRIMARY KEY,
	client_name CHAR(20) NOT NULL DEFAULT 'SuperClient',
	sex VARCHAR(1) NOT NULL DEFAULT 'M',
	confirmation VARCHAR(1) NOT NULL DEFAULT 'N',
	state_code VARCHAR(2) NOT NULL DEFAULT 'WA',
	some_date DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
	some_amount DECIMAL(9,2) NOT NULL DEFAULT 128.34,
);

-- Insert 1.000.000 rows into both tables
SET NOCOUNT ON;
BEGIN TRAN
	DECLARE @i INT;
	SET @i = 0;

	WHILE @i < 1000000
	BEGIN 
		INSERT INTO dbo.CharTestTableNN
			DEFAULT VALUES;

		INSERT INTO dbo.VarcharTestTableNN
			DEFAULT VALUES;
			
		SET @i = @i + 1;
	END
COMMIT;

-- Lets compare the space occupied 
EXEC sp_spaceused 'dbo.CharTestTableNN';
EXEC sp_spaceused 'dbo.VarcharTestTableNN';

-- Drop Those Tables
DROP TABLE dbo.CharTestTableNN;
DROP TABLE dbo.VarcharTestTableNN;

The results speak for themselves: the difference is very noticeable, as in the case of the CHAR columns we have ~50MB occupied and which is exactly the same as when using NULLable columns. From the other side, our table VarcharTestTableNN which is using Varchar datatype for the columns jumped from ~46MB to almost 58MB, thanks to all the additional space occupied by the Varchar Datatype.

-- Consult the row length of the tables
select OBJECT_NAME(object_id) as TableName, min_record_size_in_bytes, max_record_size_in_bytes, alloc_unit_type_desc, index_level, record_count, page_count, avg_page_space_used_in_percent
	from sys.dm_db_index_physical_stats (db_id(), object_id('dbo.CharTestTableNN'), null, null, 'detailed');

select OBJECT_NAME(object_id) as TableName, min_record_size_in_bytes, max_record_size_in_bytes, alloc_unit_type_desc, index_level, record_count, page_count, avg_page_space_used_in_percent
	from sys.dm_db_index_physical_stats (db_id(), object_id('dbo.VarcharTestTableNN'), null, null, 'detailed');

Checking out the internal row length we can see that in this case that while the table with CHAR datatypes continues occupying 48 bytes per row, the table with Varchar datatype columns has jumped to quite fat 56 bytes per row. This naturally confirms that when you know the exact length, you should use the exact datatypes instead of the varying.

So, it looks quite logical to me, that when you have all the textual content filled out – CHAR datatype is definitely in occupying less space, but what about situations when our columns are defined as NOT NULL but they are actually empty or half empty? Lets check it out :

-- Lets create 2 additional tables, one with non null columns but with no content, and other with only half of the columns full
CREATE TABLE dbo.VarcharTestTableNN_Empty
(
	id INT IDENTITY NOT NULL PRIMARY KEY,
	client_name CHAR(20) NOT NULL DEFAULT 'SuperClient',
	sex VARCHAR(1) NOT NULL DEFAULT '',
	confirmation VARCHAR(1) NOT NULL DEFAULT '',
	state_code VARCHAR(2) NOT NULL DEFAULT '',
	some_date DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
	some_amount DECIMAL(9,2) NOT NULL DEFAULT 128.34,
);

CREATE TABLE dbo.VarcharTestTableNN_Avg
(
	id INT IDENTITY NOT NULL PRIMARY KEY,
	client_name CHAR(20) NOT NULL DEFAULT 'SuperClient',
	sex VARCHAR(1) NOT NULL DEFAULT 'M',
	confirmation VARCHAR(1) NOT NULL DEFAULT 'N',
	state_code VARCHAR(2) NOT NULL DEFAULT '',
	some_date DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
	some_amount DECIMAL(9,2) NOT NULL DEFAULT 128.34,
);



-- Insert 1.000.000 rows into both tables
SET NOCOUNT ON;
BEGIN TRAN
	DECLARE @i INT;
	SET @i = 0;

	WHILE @i < 1000000
	BEGIN 
		INSERT INTO dbo.VarcharTestTableNN_Empty
			DEFAULT VALUES;
			
		INSERT INTO dbo.VarcharTestTableNN_Avg
			DEFAULT VALUES;

		SET @i = @i + 1;
	END
COMMIT;

-- Lets compare the space occupied 
EXEC sp_spaceused 'dbo.VarcharTestTableNN_Empty';
EXEC sp_spaceused 'dbo.VarcharTestTableNN_Avg';

The results are quite logical - the empty space filled, NON NULL table VarcharTestTableNN_Empty occupies a 45.7 MB which is a bit more then a NULL table in the previous article (45.6MB). At the same time, the table with just half of the content columns filled (VarcharTestTableNN_Avg) occupies an intermediate amount of space - 53.9MB, which is exactly between the 46MB of the NULL and 58MB of the full ones.

Lets see what has changed in the terms of the row length for this tables:

-- Consult the row length of the tables
select OBJECT_NAME(object_id) as TableName, min_record_size_in_bytes, max_record_size_in_bytes, alloc_unit_type_desc, index_level, record_count, page_count, avg_page_space_used_in_percent
	from sys.dm_db_index_physical_stats (db_id(), object_id('dbo.VarcharTestTableNN_Empty'), null, null, 'detailed');

select OBJECT_NAME(object_id) as TableName, min_record_size_in_bytes, max_record_size_in_bytes, alloc_unit_type_desc, index_level, record_count, page_count, avg_page_space_used_in_percent
	from sys.dm_db_index_physical_stats (db_id(), object_id('dbo.VarcharTestTableNN_Avg'), null, null, 'detailed');

The row length speaks for itself, here with 44 bytes for NON NULL Empty table and with an intermediate result of 52 bytes for the table with only half content columns filled (VarcharTestTableNN_Avg).

These results need just one last comparison - the missing link of the Char datatype column with empty content, so lets check it out:

CREATE TABLE dbo.CharTestTableNN_Empty
(
	id INT IDENTITY NOT NULL PRIMARY KEY,
	client_name CHAR(20) NOT NULL DEFAULT 'SuperClient',
	sex CHAR(1) NOT NULL DEFAULT '',
	confirmation CHAR(1) NOT NULL DEFAULT '',
	state_code CHAR(2) NOT NULL DEFAULT '',
	some_date DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
	some_amount DECIMAL(9,2) NOT NULL DEFAULT 128.34,
);

-- Insert 1.000.000 rows 
SET NOCOUNT ON;
BEGIN TRAN
	DECLARE @i INT;
	SET @i = 0;

	WHILE @i < 1000000
	BEGIN 
		INSERT INTO dbo.CharTestTableNN_Empty
			DEFAULT VALUES;
			
		SET @i = @i + 1;
	END
COMMIT;

-- Checking on the row length
select OBJECT_NAME(object_id) as TableName, min_record_size_in_bytes, max_record_size_in_bytes, alloc_unit_type_desc, index_level, record_count, page_count, avg_page_space_used_in_percent
	from sys.dm_db_index_physical_stats (db_id(), object_id('dbo.CharTestTableNN_Empty'), null, null, 'detailed');

-- Checking out the space occupied
EXEC sp_spaceused 'dbo.CharTestTableNN_Empty';

The results are presented here on the screenshots with row length being 48 bytes and occupied space being around 50 MB. The Char datatype are consistent through different types of situations and seems to be quite predictable.

Basic results:

Lets compare all the results we have tested so far (I am already including the math of removing from the reserved space the pre-allocated unused space):

There are a couple of things visible to me from this table - first of all is that with CHAR you get the what you request in the terms of the space, independently from the final result you get some constant values. The tables with Varchar(1) or Varchar(2) are the same consistent story of the variable length columns - it can be smaller and it can be bigger, depending on the content and the rules.

The interesting thing is so far being confirmed that even those small datatypes perfectly having their own space in the database design. Also, if your table has a lot of NULL or empty spaces then go for Varchar datatype, it can improve occupied space by the table.

To be continued

While preparing my “Inheriting a database for Developers” session for SQLSaturday#162 in Cambridge, I have had a very interesting finding on the matter of the Varchar datatype usage. There is a more or less common knowledge of the SQL Server internals, that the Varchar datatype internally adds 2 additional bytes to the content of the column. Those 2 bytes are used to control the length of the text content internally, so SQL Server knows and understands where the text actually ends. From this point of view, even when the content of the column is empty, then it takes 2 bytes of space anyway.

With this knowledge, it was for me extremely easy and logical to believe that creating any columns with Varchar(2) or even Varchar(1) datatypes does not make any sense at all.
I know that I did believe in it until a couple of days ago, until I actually started playing with it.

This is a first blog in the series of exploring a little bit deeper this datatype usage.
I have found that the following situations are very interesting to investigate:
– Tables with Varchar(x) NULL;
– Tables with Varchar(x) NOT NULL, having different amounts of data;
– Tables with Varchar(x) NULL, using Sparse columns (SQL Server 2008+);
– Tables with Varchar(x) using different Data Compression methods (SQL Server 2008+);

All of the findings will be compared between CHAR(x) and VARCHAR(x) datatypes on SQL Server Box and Azure SQL Database editions.

Some basic information:
First of all, lets consider tha we should bare in mind that both datatypes Char and Varchar while being similar in purpose of holding a some short text content, are very different in their internal implementation nature. The Char datatype size is always well defined and is placed on the datapage at the location and at the sequence order defined by the table creation, while Varchar, like other variable length datatypes are being moved from their position to the end of the table definition, right after the last fixed size column datatype. This gives a very interesting twist to the final results of this experiment.

1. We shall create 2 similar tables using different datatypes (Char and Varchar) compare the respective occupied space. Both tables will have 7 columns, clustered primary key and the differences between them shall be marked in bold.
2. We shall insert 1.000.000 rows into each of the tables, so that we could have some information to play with. The final results will naturally be much bigger on bigger tables or in more extreme internal layouts, with much wider rows.

I won’t spent more time keeping you waiting, and lets dive into SQL Server 2012 and experiment the following code:

CREATE TABLE dbo.CharTestTable
(
	id INT IDENTITY NOT NULL PRIMARY KEY,
	client_name CHAR(20) NOT NULL DEFAULT 'SuperClient',
	sex CHAR(1) NULL,
	confirmation CHAR(1) NULL,
	state_code CHAR(2) NULL,
	some_date DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
	some_amount DECIMAL(9,2) NOT NULL DEFAULT 128.34,
);

CREATE TABLE dbo.VarcharTestTable
(
	id INT IDENTITY NOT NULL PRIMARY KEY,
	client_name CHAR(20) NOT NULL DEFAULT 'SuperClient',
	sex VARCHAR(1) NULL,
	confirmation VARCHAR(1) NULL,
	state_code VARCHAR(2) NULL,
	some_date DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
	some_amount DECIMAL(9,2) NOT NULL DEFAULT 128.34,
);

-- Insert 1.000.000 rows into both of the tables
SET NOCOUNT ON;
BEGIN TRAN
	DECLARE @i INT;
	SET @i = 0;

	WHILE @i < 1000000
	BEGIN 
		INSERT INTO dbo.CharTestTable
			DEFAULT VALUES;

		INSERT INTO dbo.VarcharTestTable
			DEFAULT VALUES;
			
		SET @i = @i + 1;
	END
COMMIT;

-- Lets compare the space occupied 
EXEC sp_spaceused 'dbo.CharTestTable';
EXEC sp_spaceused 'dbo.VarcharTestTable';

These are results of what those queries, with Varchar clearly occupying much less space. The difference is around 4MB which represents in this case around 10% of the total space, which is pretty amazing to say at least. What has just happened here? Why on planet Earth do Varchar is occupying much less space, when it was clearly stated, that it has 2 extra bytes, so it should be at least equal to the table with Char datatypes.

I was lucky enough to remember a great article on the matters of NULLable records when using variable datatypes, written by Kimberly Tripp. I don’t want to spoil all the fun of reading that article, but I shall just refer that the Varchar Nullable columns shall not occupy a lot of space when having a NULL value inserted. This actually means that the records of the second table (VarcharTestTable) are actually smaller. We can confirm this by consulting the internal information of the both tables:

-- Consult the internal data of our tables
select OBJECT_NAME(object_id) as TableName, min_record_size_in_bytes, max_record_size_in_bytes, alloc_unit_type_desc, index_level, record_count, page_count, avg_page_space_used_in_percent
	from sys.dm_db_index_physical_stats (db_id(), object_id('dbo.CharTestTable'), null, null, 'detailed');

select OBJECT_NAME(object_id) as TableName, min_record_size_in_bytes, max_record_size_in_bytes, alloc_unit_type_desc, index_level, record_count, page_count, avg_page_space_used_in_percent
	from sys.dm_db_index_physical_stats (db_id(), object_id('dbo.VarcharTestTable'), null, null, 'detailed');

Consulting the results brings some very interesting information: the minimum and the maximum row length of the CharTestTable is 48 bytes, while the rows at the VarcharTestTable occupy just 44 bytes. Also, at the last index level the number of pages (8K) differs between 6212 for CharTestTable and 5862 for VarcharTestTable.

This actually means that in the cases where almost every row of our table is NULL, the Varchar(1) or Varchar(2) datatype actually can be quite useful in the terms of space. In the second part of this post, we shall take this matters a little bit deeper and from more different angles.

-- Drop the tables if not needed anymore
DROP TABLE dbo.CharTestTable;
DROP TABLE dbo.VarcharTestTable;

I have successfully tested almost the same script on Windows Azure with a couple of exceptions, which shall be revealed in the next post. I just reveal that after inserting the required registers I have rebuild both clustered indexes with this commands:

Alter Table dbo.CharTestTable
rebuild;

GO

Alter Table dbo.VarcharTestTable
rebuild;

Since at the moment of the writing, there is no support for the so much beloved sp_spaceused procedure, I have used the following script for consulting the created table sizes

select    
      obj.name as TableName, sum(reserved_page_count) * 8.0 / 1024 as SizeInMb
from sys.dm_db_partition_stats part
   join sys.objects obj
   on part.object_id = obj.object_id
group by obj.name

My results were 48,76 MB for CharTestTable and 44,64 MB for the VarcharTestTable, which are in par with the results obtained on the SQL Server Sunshine edition.

I have used then the very same script to consult the internal values of the row length for both tables and they were perfectly the same as on the Box version of SQL Server.

select OBJECT_NAME(object_id) as TableName, min_record_size_in_bytes, max_record_size_in_bytes, alloc_unit_type_desc, index_level, record_count, page_count, avg_page_space_used_in_percent
 from sys.dm_db_index_physical_stats (db_id(), object_id('dbo.CharTestTable'), null, null, 'detailed')
union all
select OBJECT_NAME(object_id) as TableName, min_record_size_in_bytes, max_record_size_in_bytes, alloc_unit_type_desc, index_level, record_count, page_count, avg_page_space_used_in_percent
 from sys.dm_db_index_physical_stats (db_id(), object_id('dbo.VarcharTestTable'), null, null, 'detailed');