Типы данных переменной длины и значения null.

Выбор типов данных для столбцов таблицы является чрезвычайно важным решением при проектировании базы данных. Я пропагандирую подход, при котором следует стремиться к тому, чтобы не хранить ничего лишнего. А также как можно реже использовать null, стараясь всегда объявлять столбцы с опцией not null.

Если у вас в базе данных хранятся текстовые данные одинаковой длины, то не следует использовать для их хранения тип varchar. Вместо него следует использовать тип char.

В чем же потенциальные минусы использования типа varchar. Рассмотрим в простейшем приближении схему хранения данных строки таблицы на странице базы данных:

Столбцы постоянной длины всегда хранятся в начале строки. Для доступа к ним не требуется никакой дополнительной информации, так как, зная типы данных и место начала строки, всегда можно сказать в каком месте начинается и оканчивается значение столбца. Для того чтобы прочитать столбец переменной длины, надо знать где он оканчивается. Для этой цели после столбцов постоянной длины хранится байтовый массив, в котором каждые два байта обозначают место, в котором оканчивается очередной столбец переменной длины. Этот массив занимает место, увеличивая общий размер таблицы.
В дополнение к этому, если в таблице есть строки, допускающие пустые значения, то в каждой строке есть битовая маска, в которой зашита информация о том какие столбцы принимают значения null, а какие нет.
Все это в совокупности приводит к тому что таблица может занимать больше места чем могла бы. Как следствие для поиска данных в ней требуется выполнять больше операций чтения, чтобы считать больше страниц. Нужно стремиться к тому чтобы страниц было меньше.
Приведем следующий пример двух таблиц, предназначенных для хранения данных клиентов: зарплатный счет, инн, номер пенсионного свидетельства.

if object_id ( N'dbo.PersData', N'U' ) is null

begin

       create table dbo.PersData

       (

             iRowId       int identity ( 1, 1 )      not null,

             vcAccount    char ( 20 )                not null,

             vcSnils      char ( 14 )                not null,

             vcInn        char ( 12 )                not null,

             constraint PK_PersData_iRowId primary key clustered ( iRowId asc ) on [PRIMARY]

       ) on [PRIMARY]

end

go

if object_id ( N'dbo.PersDataVar', N'U' ) is null

begin

       create table dbo.PersDataVar

       (

             iRowId       int identity ( 1, 1 )      not null,

             vcAccount    varchar ( 20 )             null,

             vcSnils      varchar ( 13 )             null,

             vcInn        varchar ( 12 )             null,

             constraint PK_PersDataVar_iRowId primary key clustered ( iRowId asc ) on [PRIMARY]

       ) on [PRIMARY]

end

go

Вторая таблица имеет неоптимальные типы данных. Ее столбцы с атрибутами допускают пустые значения и имеют переменную длину. В остальном таблицы одинаковы. Наполним их данными одинакового размера, так чтобы в таблицах было одинаковое число строк:

insert into dbo.PersData with ( tablock ) ( vcAccount, vcSnils, vcInn )

       select

             left ( concat

             (

                    abs ( binary_checksum ( newid () ) ),

                    abs ( binary_checksum ( newid () ) ),

                    abs ( binary_checksum ( newid () ) )

             ), 20 ),

             left ( concat ( '', abs ( binary_checksum ( newid () ) ) ), 3 ) + '-' +

             left ( concat ( '', abs ( binary_checksum ( newid () ) ) ), 3 ) + '-' +

             left ( concat ( '', abs ( binary_checksum ( newid () ) ) ), 3 ) + '-' +

             left ( concat ( '', abs ( binary_checksum ( newid () ) ) ), 2 ),

             left ( concat

             (

                    abs ( binary_checksum ( newid () ) ),

                    abs ( binary_checksum ( newid () ) ),

                    abs ( binary_checksum ( newid () ) )

             ), 12 )

       from

             master.dbo.spt_values t1

                    cross join

             master.dbo.spt_values t2

insert into dbo.PersDataVar with ( tablock ) ( vcAccount, vcSnils, vcInn )

       select

             left ( concat

             (

                    abs ( binary_checksum ( newid () ) ),

                    abs ( binary_checksum ( newid () ) ),

                    abs ( binary_checksum ( newid () ) )

             ), 20 ),

             left ( concat ( '', abs ( binary_checksum ( newid () ) ) ), 3 ) + '-' +

             left ( concat ( '', abs ( binary_checksum ( newid () ) ) ), 3 ) + '-' +

             left ( concat ( '', abs ( binary_checksum ( newid () ) ) ), 3 ) + '-' +

             left ( concat ( '', abs ( binary_checksum ( newid () ) ) ), 2 ),

             left ( concat

             (

                    abs ( binary_checksum ( newid () ) ),

                    abs ( binary_checksum ( newid () ) ),

                    abs ( binary_checksum ( newid () ) )

             ), 12 )

       from

             master.dbo.spt_values t1

                    cross join

             master.dbo.spt_values t2

go 20

В таблицах теперь одинаковое число строк. Каждый соответствующий столбец имеет одну и ту же длину. Выполним процедуру sp_spaceused, чтобы посмотреть сколько места используется данными и индексами в каждой из таблиц:

exec sp_spaceused [dbo.PersData]

exec sp_spaceused [dbo.PersDataVar]

Размер данных и индекса для dbo.PersData составляет 7.07 Гб, для таблицы dbo.PersDataVar размер составляет 8.07 Гб. Это на целый гигабайт больше. И этот гигабайт не дает ничего полезного, а наоборот занимает место на диске, вызывает большее число операций чтения, замедляя запросы!
Поэтому при проектировании таблицы следует избегать значений null, а там где надо, использовать типы постоянной длины. Также следует быть экономными при выборе того насколько вместительными будут ваши типы данных. Например, если от столбца с датой требуется хранение только числа, месяца и года, не надо использовать тип данных datetime, достаточно просто типа date, который занимает намного меньше места. Если таблица атрибутов не будет хранить более 64 тысяч строк, то для ее идентификатора достаточно использовать тип smallint, а не int. Так как каждая строка любой таблицы, ссылающейся на такой справочник, будет экономить по два байта.