Редкие столбцы, не показывающие никакой экономии

Question

Я провел тест, сравнивая таблицу с несколькими разреженными столбцами с таблицей без разреженных столбцов, и я вижу нулевую экономию места.

У меня есть две таблицы, обе хранят информацию об адресе в основном в столбцах varchar. в обеих таблицах допускаются значения NULL, в одной из них установлено свойство разреженного столбца.

Я вставляю 1000 строк значений по умолчанию в каждую (значения по умолчанию равны нулю). Разреженные столбцы хранят значения NULL по-разному, поэтому я считаю, что мне нужно сэкономить место. но при запуске sp_spaceUsed я не вижу никакого сохранения. Любые идеи о том, что я делаю неправильно или где мое понимание неверно?

    Create Table SparseColTest_NonSparse
    (
        AddressID int identity(1,1) not null,
        AddressLine1 varchar(500)  null,
        AddressLine2 varchar(500)  null,
        AddressLine3 varchar(500)  null,
        PostalCode varchar(20)  null,
        Country varchar(50) 
    )

    Create Table SparseColTest_Sparse
    (
        AddressID int identity(1,1) not null,
        AddressLine1 varchar(500) sparse null,
        AddressLine2 varchar(500)  sparse null,
        AddressLine3 varchar(500)  sparse null,
        PostalCode varchar(20)  sparse null,
        Country varchar(50) 
    )


    declare @i int
    set @i = 0

    while(@i <= 100000)
    BEGIN

        insert into SparseColTest_NonSparse Default values
        insert into SparseColTest_Sparse default values

        set @i = @i + 1
    END

    exec sp_spaceUsed 'SparseColTest_NonSparse'
    exec sp_spaceUsed 'SparseColTest_Sparse'

    /*
    name                          rows                 reserved           data                       index_size         unused
    ----------------------------- -------------------- ------------------ -------------        ----- ------------------ ------------------
    SparseColTest_NonSparse       210003               2888 KB            2840 KB                    8 KB               40 KB


    name                          rows                 reserved           data                       index_size         unused
    ----------------------------- -------------------- ------------------ -------------        ----- ------------------ ------------------
    SparseColTest_Sparse                210003               2888 KB            2840 KB                    8 KB               40 KB

    ****NOTE - even with 210k rows sparse and non sparse tables are identical in size.
    */

Answer 1

Проблема заключается в хранении на страницах. Это приближение того, что происходит.

Место занято по следующим причинам:

1. AddressID (4 байта)
2. Карта битов NULL (1 байт в обеих таблицах)
3. Список разреженных столбцов (2 байта на пустое поле)
4. Список значений столбцов (не любой, потому что пустые varchars не занимают места)
5. 6 байтов служебных данных для разреженных столбцов (здесь только ссылка)
6. Некоторые другие незначительные накладные расходы

Это может потребовать некоторого объяснения. Обе таблицы имеют AddressID, так что занимает столько же места.

Все не разреженные столбцы находятся в битовой карте NULL. Вы можете подумать, что там будут только NULL-способные, но нет. SQL Server имеет бит-карту для всех из них. Хранится по одному байту за раз. Таким образом, шесть столбцов в не разреженной таблице занимают то же пространство, что и два не разреженных столбца (идентификатор и страна) в разреженной таблице.

Varchars - это данные переменной длины. Когда NULL, они все еще резервируют 2 байта на столбец. Так что это 10 байт в первой записи. И 2 байта во втором.

Разреженные записи имеют служебные данные 6 байтов.

Разреженные столбцы занимают место только тогда, когда они имеют значения. Ни у кого здесь нет ценностей.

Конечный результат заключается в том, что между накладными расходами и, возможно, ограничениями на выравнивание байтов в обеих записях получается одинаковая длина. Значения для соответствующих столбцов в не разреженной таблице занимают некоторое пространство для длин, и это, кажется, соответствует пространству, используемому столбцами. Я также подозреваю, что строки данных на странице выровнены по 2- или 4-байтовым границам. Битовые карты NULL имеют одинаковый размер в обеих таблицах.

Я вижу экономию пространства, когда в разреженной таблице у меня есть как минимум на 8 разреженных столбцов больше, чем в не разреженной таблице. Это определенно дает вам экономию на NULL-битной карте, которая округляется до ближайшего байта.

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

Для вашего примера разреженные столбцы не полезны. С реальными данными они, вероятно, фактически увеличат пространство для хранения.

Ознакомьтесь с документацией здесь.