Предлагаем вспомнить (а при необходимости и изучить) полезную информацию о Microsoft SQL таблицах, их особенностях и характеристиках. Они создаются с помощью различных инструментов и могут быть представлены в нескольких видах. Рассмотрим подробнее.
Основные характеристики
Таблицы – это логически организованные хранилища данных (объектов), представленные в виде строк и столбцов. Классическим примером может служить таблица, в которой собрана информацию о работниках компании. Так, в ней для каждого отдельного работника выделена строка, а сведения, касающиеся каждого работника представлены в столбцах (ФИО, занимаемая должность, по какому адресу прописка, номера телефонов и т.д.).
Количество таблиц в базе данных (БД) ограничивается лишь числом объектов, которые доступны в ней (2 147 483 647). Наибольшее количество столбцов в стандартной таблице, определяемой пользователем – 1024. Общий размер таблицы (SIZE TABLE), как и количество строк, ограничивается лишь объемом пространства, требуемого для хранения информации на сервере.
Чтобы управлять данными и характеристики таблицы, для нее и каждого столбца в отдельности, определяются свойства. Также реализована возможность:
- присвоения ограничений ключу, обеспечивающему уникальность;
- установки связей между различными таблицами.
Чтобы на странице отображалось максимальное количество строк, данные могу сжиматься по строкам либо страницам.
С таблицами можно выполнять различные операции: изменять, добавлять, удалять, получать данные.
Типы
В SQL Server предусмотрено разбиение таблиц на типы. Каждый тип предназначен для достижения определенных целей в БД. Кроме стандартных, существуют такие типы таблиц:
Секционированные
Такими таблицами называю те, в которых информация поделена секциями (блоками) горизонтально. Блоки могут распределяться между разными группами файлов в БД. Разбиение на секции облегчает управление индексами и таблицами огромных размеров.
Разделение на секции позволяет мгновенно иметь доступ к наборам данных, а также управлять ими максимально эффективно, соблюдая целостность общей коллекции. В SQL Server по умолчанию поддерживается до 15000 секций.
Временные
Такие таблицы сохраняются в базе данных tempdb, по умолчанию содержащуюся в MS SQL Server и также называют темпоральными. Они полезны для хранения табличных данных как части сложного комплексного скрипта. Срок существования – сессия БД. Потому временные таблицы удобно использовать для промежуточных, временных данных.
При создании временной таблицы с помощью CREATE TABLE в SSMS (QUERY Editor), ее срок существования будет ограничен тем временем, пока открыт данный редактор запросов. Потому, к темпоральной таблице можно обращаться из разных скриптов внутри редактора SSMS.
В том случае, когда нужно удалить таблицу еще до окончания сессии, используется команда DROP TABLE.
У всех темпоральных таблиц – первый символ #. Они делятся на два вида:
- локальные – доступные лишь текущему соединению пользователя. В начале таблицы находится символ (#). После отключения пользователем от экземпляра SQL Server сразу удаляются;
- глобальные – доступные всем пользователям. Начинаются с двойного символа (##).Будут удалены после того, как отключатся от SQL Server все пользователи, ссылавшиеся на них.
SQL Server 2019 (15.x) на всех уровнях совместимости структур БД с помощью временных таблиц уменьшает количество повторных компиляций.
SQL Server дополнительно производит простые проверки во избежание неоправданных перекомпиляций:
- на совпадение модуля внешней области, использованного для формирования временной таблицы при компиляции, с тем, который будет использоваться для следующих операций;
- проводит отслеживание каждого изменения языка DDL определения данных, полученные при первичной компиляции, а также сравнение их с операциями DDL при следующих запусках.
В итоге уменьшается количество ненужных перекомпиляций, а также понижается нагрузка на ЦП.
Производные
В отличие от предыдущих, производные таблицы более эффективны в плане производительности. Их также можно создавать в MS SQL Server задав ключевым словом WITH.
Отличаются от предыдущих еще и тем, что хранятся в оперативной памяти. Они будут существовать лишь во время первого выполнения запросов (QUERIES), представляющих данную таблицу.
Системные
Системным называют специальный набор таблиц, в котором SQL Server сохраняет информацию, определяющую конфигурацию сервера вместе со всеми его таблицами. Всем пользователям не доступно выполнение прямого запроса либо обновления таких таблиц.
Информация из системных таблиц может быть доступной лишь через представления системы.
Широкие
В таких таблицах применяются разреженные списки. Это дает возможность расширить в них общее количество столбцов и статистик до 30000, а индексов – до 1000.
Разреженные столбцы делают возможным уменьшение пространства, в котором хранится значение NULL. Но возрастает стоимость принятия значений, не равных NULL. Они определяют набор столбцов, представляющий нетипичное XML-представление, где все разреженные столбцы консолидированы в структурированную выходную информацию.
В широких таблицах размеры строк – до 8019 байт. Не меняется максимально допустимое количество неразряженных и вычисляемых столбцов (равно 1024).
Они оказывают влияние на эффективность:
- Повышаются расходы на обслуживание табличных индексов. Потому специалисты советуют сократить в ней количество индексов, оставив лишь необходимые. С ростом индексов соответственно растут требования не только к памяти, но и времени компиляции DML. Должны быть отфильтрованы, применимы к подмножествам данных некластеризованные индексы.
- Динамическое добавление или удаление столбцов возможно через приложение. Скомпилированные планы запросов становятся также недействительными. При разработке приложений должна учитываться предполагаемая рабочая нагрузка. Это позволит минимизировать риск изменений в схеме.
- На производительность оказывает влияние как удаление, так и добавление информации. Это также нужно учитывать при разработке приложений, стараясь свести к минимуму изменения в данных.
- В широких таблицах необходимо ограничить реализацию инструкций DML, которые обновляют строки ключа кластеризации. Причина – может понадобиться большой объем ресурсов для выполнения и компиляции вышеуказанной инструкции.
- Для выполнения операций по переключению секций может понадобиться большой объем памяти, а значит, и продолжительный промежуток времени на выполнение.
- Для оптимизации работы курсоров обновления (отвечающих за обновление нужных строк) требуется явное перечисление этих столбцов в FOR UPDATE.
Создание сводных таблиц с помощью SQL
Сводные таблицы относятся к базовым видам аналитики. Их можно создать с помощью SQL (вопреки мнению большинства). И хоть это не так быстро, как в MS Excel, решения в SQL достаточно просты.
1. Оператор CASE и его аналоги
Наиболее очевидный, а значит и простой метод создания сводных таблиц – это hardcode с применением оператора CASE. Его можно заменить какой-либо функцией из vendor-specific, но оно того не стоит.
К примеру, заменой может стать оператор FILTER (доступный в SQLite и PostgreSQL). Но у него определенная специфика. Несмотря на то, что он принадлежит стандарту (SQL: 2003), но по факту получает поддержку только в вышеперечисленных SQLite и PostgreSQL.
Иные СУБД также обладают аналогами CASE, которые не предусмотрены стандартами:
- SQL Server 2012+ – IIF;
- в MySQL – IF;
- в Oracle – DECODE.
Чаще всего использование аналогов не оправдано, т.к. преимуществ они не дают, а поддержку кода в будущем значительно усложняют.
2. PIVOT (SQL Server)
Использование оператора CASE вместе с аналогами, описанное выше, не очень впечатляет. Гораздо удобнее использовать разворот таблицы (PIVOT TABLE) в SQL Server. Несмотря на то, что данный оператор не предусмотрен стандартами SQL, СУБД предлагает аналогичный синтаксис.
3. CTE
Оператор PIVOT не единственный, который может «повернуть». С помощью стандартного синтаксиса запрос можно переписать. Для этого следует воспользоваться комбинацией CTE (Common Table Expression) и возможными соединениями.
4. Функция CROSSTAB
В PostgreSQL существует функция, приблизительно идентичная PIVOT в Microsoft SQL — CROSSTAB. Для начала работы понадобится tablefunc.
Особенности использования CROSSTAB:
- основным аргументом воспринимает запрос в виде text sql. Он идентичен тому, что и для PIVOT, но с обязательной сортировкой;
- от PIVOT отличается тем, что для разворота таблицы понадобится прописывать не только наименования столбцов, но и типы данных;
- заполнение строк происходит слева направо, пропуская значения NULL. Т.е. строки, в которых будут значения NULL, «съедут» влево. В результате, корректными будут лишь те строки, в которых были заполнены все значения. Значит, если они были отличны от NULL, то запрос был корректным и вернет нужный результат. Чтобы избежать подобного поведения функции CROSSTAB необходимо применять вариант с двумя аргументами. Второй из них должен включать запрос, который будет в итоге выводить перечень столбцов.
5. Динамический SQL
Запросы, включающие PIVOT или CROSSTAB более функциональны, чем те, которые содержали CASE (либо CTE), однако некоторые значения все еще приходится заносить руками. Когда их огромное количество либо список регулярно обновляется, то идеально выбирать значения автоматически из словаря (если он имеется) либо select distinct supplier from test supply.
Для этого понадобится динамический SQL. Чтобы получить такую строку в SQL Server можно воспользоваться STUFF, а затем добавить ее в окончательный запрос. Однако для любого использования динамического SQL понадобится углубиться в специфику конкретной СУБД вместе с соответствующим ей процедурным решением SQL.
Как создать таблицу с Transact-SQL (Table)
Transact-SQL – это набор данных специального назначения, предназначенный для хранения конечного набора. Чаще всего применяется для хранения набора строк в течение короткого временного отрезка, которые возвращаются в качестве конечного набора функций с соответствующим значением из таблицы.
Функциям и их переменным может соответствовать тип table.
CREATE TABLE
Данная инструкция генерирует новую MS SQL таблицу, в которой присутствуют все нужные столбцы необходимого типа данных. Из одной БД может быть создано ограниченное число таблиц. В ней не может быть свыше 2 млрд. объектов (TABLES, ограничений, триггеров, представлений, хранимых процедур).
В CREATE TABLE используется синтаксис:
- table_name – имя базовой таблицы;
- col_name1 и т.д. – наименования столбцов;
- type1 и т.д. – типы данных.
Наименование объекта БД формируется из важных составляющих:
[server_name.[db_name.[schema_name.]]]object_name
В формуле использованы следующие обозначения:
- server_name – наименование сервера, к которому относится объект БД;
- db_name – наименование БД, к которой относится таблица;
- schema_name – название схемы, к которой относится таблица;
- object_name – наименование таблицы.
Если в столбце не разрешены значения NULL (NOT NULL), то наличие такого значения становится невозможным. При любой попытке вставки в столбец значения NULL, система будет возвращать сообщения о наличии ошибки.
Генерация таблицы всегда происходит в схеме БД. Ее генерация пользователем возможна лишь в той схеме, на которую юзер получил разрешения для реализации инструкции ALTER. Юзеры с ролями sysadmin, db_ddladmin, db_owner могут работать над созданием таблиц в любой схеме.
Используются аргументы table_type_definition и collation_definition.
Используемые предложения
В инструкциях CREATE TABLE или ALTER TABLE используются предложения:
- UNIQUE – определение потенциального ключа (столбец или их группы, имеющие уникальные значения, которые можно применять как первичный ключ);
- PRIMARY KEY – определение первичного ключа (столбца либо их группы, имеющих различные значения в строках);
- CHECK – постановка ограничений для проверок, которые определяют условия для данных, подставляемых в столбец;
- FOREIGN KEY – определение внешнего ключа (столбец либо их группа, которые содержат значения, равные со значением первичного ключа в той либо иной таблицах).
Опции ON DELETE и ON UPDATE используются при удалении или модификации первичного ключа.
Как вывести информацию о полях
Информация о полях, их размере и типах окажется очень полезной, когда понадобится анализ структуры базы данных или необходимости ее документирования. Для ее составления понадобятся две системные таблицы:
- syscolumns – сохраняет список столбцов таблиц базы данных;
- systypes – сохраняет список типов данных.
Также данные о табличных столбцах, включая поля, можно получить, обратившись к хранимой процедуре sp_help. Это гораздо простой способ, но обладает меньшей функциональностью. Это связано с тем, что у процедуры sp_help нет выходных параметров. А потому, произвести обработку набора данных, отображаемого ею, невозможно.
Следовательно, описанный способ сможет подойти администратору баз данных лишь в качестве специфического отчета о структуре таблицы, но не больше.
Изучайте или вспоминайте материал, применяйте на практике. При возникновении вопросов – обязательно задавайте их.
Также приглашаем на специальный курс по MS SQL в Otus.
