Дополнительные таблицы. Numbers

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

Существует множество способов создания такой таблицы:

• master..spt_values
• row_number() с любой таблицей, например sys.all_objects
• CTE
• Рекурсивный CTE
• Цикл

Загружаем OpenStreetMap в MS SQL Server

На днях подкинули задачку по геокодированию объектов, а именно:

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

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

• Яндекс.Карты Геокодирование (25 000 запросов/сутки)
• Google Geocoding(2 500 запросов/сутки)
• Nominatim OpenStreetMap (политика использования 1 запрос/секунда)

Каждый сервис реализует собственные алгоритмы и предоставляет определенный уровень достоверности результатов. Однако, предполагается, что объектов, геокодирование которых необходимо производить, будет больше, чем количество допустимых запросов и обработку необходимо производить в пакетном режиме.

В качестве возможного варианта решения, я решил попробовать реализовать собственный поиск, используя для этого открытую базу данных Open Street Map (OSM). В качестве инструмента для первоначальной реализации я выбрал MS SQL Server 2012. В последнем поддерживаются пространственные типы данных, а также имеется возможность создания пространственного индекса.

SQL Server. Storage Engine. Страницы данных

В данной статье я хочу описать внутренние механизмы движка MS SQL Server по хранению данных и механику данного процесса. Это очень полезно для изучения особенностей работы SQL Server и для собственной проверки гипотез и утверждений о том, как работает SQL Server. Как говорится, доверяй, но проверяй.

Структура таблицы

Рассмотрим структуру таблицы в SQL Server.
Каждая таблица или индекс хранит свою информацию в партициях (partition, до 15.000 для sql server 2012). Каждая партиция может содержать один объект  B-дерева (b-tree, если быть точным в виде B+ дерева) или кучу(heap), отсюда, данные структуры называют общим именем hobt (heap or b-tree). Между партицией и hobt-структурой существует отношение один к одному. В зависимости от типа данных колонок таблицы или индекса, hobt может состоять из трех наборов страниц (allocation unit), каждая из которых образует цепочку страниц (iam chain):

• IN_ROW_DATA (практически все типы данных, кроме описанных ниже)
• ROW_OVERFLOW_DATA (типы переменной длины, вместе с которыми запись не помещается на страницу)
• LOB_DATA ( nvarchar(max), filestream, xml, varbinary)