Optimization Tips for MS SQL 6.5: Storage Nullable Fields

Introduction

In this article I want to show why it is important to know the data
pages and the data rows structure and how you can use this knowledge
for optimization purpose in MS SQL 6.5.

You can use the following undocumented command to view the data
pages structure (in comparison with DBCC PAGE, this command will
return information about all data pages for viewed table, not only for
particular number):

DBCC tab (dbname, objname, printopt={ 0 | 1 | 2 })

where dbname - is the database name,
      objname - is the table name,
      printopt - is the type of the output:

      0 - minimum information (only the pages headers, the total
          number of data pages in this table and the total number
          of data rows in this table)

      1 - more information (plus full rows structure)

      2 - as printopt = 1, but without rows separation (full dump)

by default printopt = 0

Optimization tips

1. If you define default constraint for some fields, you should define this fields as NOT NULL.

2. Place all nullable fields at the end of the fields list (after fields with fixed length), so than more often this field will contain NULL value, the closer to the end of the record it should be placed.

Because one byte is placed into the Offset table for each nullable field, (to the exclusion of the field that situated at the end of the row and containing the Null value, or if only fields that contains the Null values are situated after this nullable field) you may economize storage space if you will use this tips. The economy can make from several percents up to two times (look at the examples below).

Examples

1. Incorrect example:

USE pubs
GO
if object_id('dbo.tbTest') is not null drop table tbTest
GO
CREATE TABLE tbTest (
  id int identity primary key,
  field1 char(255) not null,
  field2 char(255) not null,
  field3 char(255) not null,
  field4 char(226) not null,
  field5 char(2) null,
  field6 char(1) null default ('1')
)
GO
INSERT INTO tbTest (field1, field2, field3, field4) VALUES ('1', '1', '1', 
'1')
INSERT INTO tbTest (field1, field2, field3, field4) VALUES ('2', '2', '2', 
'2')
GO
DBCC TRACEON (3604)
GO
DBCC TAB(pubs, tbTest)
GO

These are the results from my computer:

...

DATABASE:5   OBJECT:117575457   PAGE:425 (0x1a9)

Page header for page 0x12fe000
pageno=425 nextpg=0 prevpg=424 objid=117575457 timestamp=0001
00004979
nextrno=1 level=0 indid=0  freeoff=1039 minlen=997
page status bits: 0x100,0x1
The total number of data pages in this table is 2.
Table has 2 data rows.

You can see, that in this example two pages was created for storage two data rows. This is because one byte was placed into the Offset table for field field5 at the end of each data row.

2. Correct example:

USE pubs
GO
if object_id('dbo.tbTest') is not null drop table tbTest
GO
CREATE TABLE tbTest (
  id int identity primary key,
  field1 char(255) not null,
  field2 char(255) not null,
  field3 char(255) not null,
  field4 char(226) not null,
  field6 char(1) null default ('1'),
  field5 char(2) null
)
GO
INSERT INTO tbTest (field1, field2, field3, field4) VALUES ('1', '1', '1', 
'1')
INSERT INTO tbTest (field1, field2, field3, field4) VALUES ('2', '2', '2', 
'2')
GO
DBCC TRACEON (3604)
GO
DBCC TAB(pubs, tbTest)
GO

These are the results from my computer:

...

DATABASE:5   OBJECT:181575685   PAGE:424 (0x1a8)

Page header for page 0x1302000
pageno=424 nextpg=0 prevpg=0 objid=181575685 timestamp=0001 000049e6
nextrno=2 level=0 indid=0  freeoff=2044 minlen=997
page status bits: 0x100,0x1
The total number of data pages in this table is 1.
Table has 2 data rows.

As you can see, that in this example only one page was created for storage two data rows. This is because one byte was not placed into the Offset table for field field5 at the end of each data row.

By the way, if you will create field6 as

  field6 char(1) not null default ('1')

then you will economize one byte in the Offset table too.

MS SQL reads data by entire pages. So for this query:

SELECT * FROM pubs..tbTest

SQL Server will read one page only, and your query will be
two times faster than in the first example.

So the proper fields order in the table definition is very
important thing for storage and query optimization.

Literature

1. SQL Server Books Online.