Что имеется в каждой таблице sql

Таблицы

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

Число таблиц в базе данных ограничено только числом объектов доступных в базе данных (2 147 483 647). Стандартная определяемая пользователем таблица может содержать до 1 024 столбцов. Число строк и общий размер таблицы ограничиваются только пространством для хранения на сервере.
Можно также устанавливать свойства для таблицы и каждого столбца в таблице для управления допустимыми данными и другими свойствами. Например, можно задать ограничения на столбец, чтобы в нем не допускались значения NULL, или указать значение по умолчанию, если оно не задано. Также можно присвоить ограничения ключа на таблицу, который обеспечивает уникальность, или установить связи между таблицами.
Данные в таблице могут быть сжаты либо по строкам, либо по страницам. Сжатие данных может позволить отображать больше строк на странице. Дополнительные сведения см. в разделе Data Compression.

Типы таблиц

Помимо стандартной роли базовых определяемых пользователем таблиц, SQL Server предоставляет следующие типы таблиц, которые служат для специальных целей в базе данных.

Секционированные таблицы

Секционированные таблицы — это таблицы, данные которых горизонтально разделены на блоки, которые могут быть распределены между несколькими файловыми группами в базе данных. Секционирование делает большие таблицы и индексы более управляемыми, позволяет быстро и эффективно получать доступ к наборам данных и управлять ими, при этом сохраняя целостность всей коллекции. По умолчанию SQL Server поддерживает до 15 000 секций. Дополнительные сведения см. в разделе Partitioned Tables and Indexes.

Временные таблицы

Темпоральные таблицы хранятся в базе данных tempdb. Существует два вида временных таблиц: локальные и глобальные. Они отличаются друг от друга именами, видимостью и доступностью. В качестве первого символа имен локальных временных таблиц в качестве первого символа в именах имеется единый знак чисел (#); Они видны только текущему подключению для пользователя и удаляются, когда пользователь отключается от экземпляра SQL Server. Глобальные временные таблицы имеют два числовых знака (##) в качестве первых символов их имен; Они видны любому пользователю после создания и удаляются, когда все пользователи, ссылающиеся на таблицу, отключаются от экземпляра SQL Server.

Сокращение повторных компиляций для рабочих нагрузок с использованием темпоральных таблиц в нескольких областях.

SQL Server 2019 (15.x) при всех уровнях совместимости базы данных сокращает количество перекомпиляций для рабочих нагрузок с использованием временных таблиц в нескольких областях. Эта функция также включена в базе данных SQL Azure на уровне совместимости базы данных 150 для всех моделей развертывания. До этого выпуска при ссылке на временную таблицу в инструкции на языке обработки данных DML ( SELECT , INSERT , UPDATE , DELETE ), если таблица была создана пакетом во внешней области, происходила повторная компиляция инструкции DML при каждом выполнении. В рамках этого улучшения SQL Server выполняет дополнительные упрощенные проверки, чтобы избежать ненужных перекомпиляций:

Проверяет, совпадает ли модуль внешней области, использованный для создания темпоральной таблицы во время компиляции, с используемым для последующих выполнений.
Отслеживает все изменения языка DDL определения данных, сделанные при первичной компиляции, и сравнивает их с операциями DDL в последующих запусках.

Конечным результатом является снижение числа лишних перекомпиляций и нагрузки на ЦП.

Системные таблицы

SQL Server хранит данные, определяющие конфигурацию сервера и всех его таблиц, в специальном наборе таблиц, известном как системные таблицы. Пользователи не могут напрямую выполнить запрос или обновление системных таблиц. Данные из системных таблиц доступны через системные представления. Дополнительные сведения см. в разделе Системные представления (Transact-SQL).

Широкие таблицы

В широких таблицах используются разреженные столбцы , что позволяет увеличить общее количество столбцов в таблице до 30 000. Разреженные столбцы — это обычные столбцы, имеющие оптимизированное хранилище для значений NULL. Разреженные столбцы уменьшают пространство, необходимое для хранения значений NULL, однако увеличивается стоимость получения значений, отличных от NULL. В широкой таблице определен набор столбцов, который представляет собой нетипизированное XML-представление, в котором все разреженные столбцы таблицы объединены в структурированные выходные данные. Количество индексов и статистик также увеличивается до 1000 и 30 000 соответственно. Максимальный размер строки широкой таблицы — 8 019 байт. Таким образом, большинство данных в любой строке должны составлять значения NULL. Максимальным числом неразряженных и вычисляемых столбцов в широкой таблице остается 1024.

Широкие таблицы влияют на производительность следующим образом.

Широкие таблицы увеличивают затраты на обслуживание индексов для таблицы. Рекомендуется ограничить количество индексов широкой таблицы только теми, которые необходимы для бизнес-логики. С ростом числа индексов возрастает время компиляции DML и требования к памяти. Некластеризованные индексы должны быть отфильтрованными индексами, применяющимися к подмножествам данных. Дополнительные сведения см. в разделе Create Filtered Indexes.
Приложения могут динамически добавлять или удалять столбцы из широких таблиц. При добавлении или удалении столбцов скомпилированные планы запросов также становятся недействительными. Рекомендуется разрабатывать приложение с учетом предполагаемой рабочей нагрузки, чтобы изменения в схеме были сведены к минимуму.
Добавление или удаление данных из широкой таблицы может влиять на производительность. Приложения следует разрабатывать с учетом предполагаемой рабочей нагрузки, чтобы изменения в табличных данных были сведены к минимуму.
В широкой таблице следует ограничить выполнение инструкций DML, обновляющих несколько строк ключа кластеризации. Для компиляции и выполнения этих инструкций может потребоваться значительный объем ресурсов.
Операции по переключению секций в широких таблицах могут выполняться медленно. Для их выполнения может потребоваться значительный объем памяти. Требования к производительности и памяти пропорциональны общему количеству столбцов в исходной и целевой секциях.
Курсоры обновления, обновляющие определенные столбцы в широкой таблице, должны явно перечислять столбцы в предложении FOR UPDATE. Это поможет оптимизировать производительность при использовании курсоров.

Обобщенные табличные задачи

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

Табличные задачи	Раздел
Описание процесса создания таблицы.	Создание таблиц (компонент Database Engine)
Описание процесса удаления таблицы.	Удаление таблиц (компонент Database Engine)
Описание способов создания новой таблицы, которая содержит несколько или все столбцы существующей таблицы.	Дублирование таблиц
Описывает процесс переименования таблицы.	Переименование таблиц (компонент Database Engine)
Описание процесса просмотра свойств таблицы.	Просмотр определения таблицы
Описывает, как определить, находятся ли в зависимости от таблицы другие объекты, например представление или хранимая процедура.	Просмотр зависимостей таблицы

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

Задачи столбца	Раздел
Описывает добавление столбцов в существующую таблицу.	Добавление столбцов в таблицу (компонент Database Engine)
Описывает процесс удаления столбца из таблицы.	Удаление столбцов из таблицы
Описывает изменение имени столбца.	Переименование столбцов (компонент Database Engine)
Описывает копирование столбцов из одной таблицы в другую, когда копируется либо только определение столбца, либо определение и данные.	Копирование столбцов из одной таблицы в другую (компонент Database Engine)
Описывает изменение описания столбца путем изменения типа данных или другого свойства.	Изменение столбцов (компонент Database Engine)
Описывает изменение порядка, в котором отображаются столбцы.	Изменение порядка столбцов в таблице
Описывает создание вычисляемого столбца в таблице.	Указание вычисляемых столбцов в таблице
Описывает, как установить значение по умолчанию для столбца. Это значение используется, если другое значение не предоставлено.	Указание значений по умолчанию для столбцов

Таблица как визуализация — Продвинутая аналитика на SQL

Таблицы — необходимый компонент баз данных. Чтобы построить аналитику, мы часто агрегируем данные из таблиц и строим графики. Но иногда анализ самой таблицы без графиков оказывается удобнее.

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

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

Таблица

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

Таблица содержит информация о разных пиццериях. В ней есть следующие столбцы:

Название — это название пиццерии
Доставка — есть у пиццерии доставка или нет
Начало работы — с какого времени открыта пиццерия

Вся информация в таблице — это параметры разных пиццерий, то есть смысл этой таблицы. Смысл называется сущностью — это то, о чем хранится информация в таблице.

Для таблиц как сущностей есть такое правило: одна таблица — одна сущность. Например, в таблице Users хранится только информация о пользователях, в таблице Payments — о платежах, а в таблице выше — о пиццериях.

Таблицы в базах данных могут быть связаны друг с другом. Такие базы называются реляционными.

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

Например, таблица Users хранит информацию о пользователе — его ФИО, адрес электронной почты и другие его параметры. Но каждый пользователь совершает какой-то платеж, и эти платежи хранятся в таблице Payments .

Таблицы Users и Payments связаны. Такие связи называются отношениями между сущностями. Язык SQL — это основной инструмент работы с реляционными базами данных.

У таблиц в реляционных базах данных есть разные компоненты. Их использование является одним из необходимых условий реляционной модели, потому что они позволяют связывать разные сущности. Рассмотрим их подробнее.

Компоненты таблицы

Обязательные компоненты таблиц такие:

Столбец в таблицах еще называют колонкой, полем или атрибутом. Атрибут таблицы выражает какой-то один тип информации о сущности. Например, в таблице Users может храниться поле Full name , и оно содержит информацию о ФИО пользователя. Поле User address будет хранить адрес пользователя. В колонке Название в таблице пиццерий мы видим название конкретной пиццерии.

Строки мы еще зовем записью или кортежем. Одна строка — это одна единица сущности. В таблице Users одна запись — это один пользователь, в Payments — это один платеж, в Пиццерии — одна пиццерия.

При создании таблицы в базе данных мы обязательно указываем тип данных информации в поле. Домен поля — это и есть тип данных столбца. К примеру, поле Full name в таблице Users может содержать строку и ничего кроме строки.

Вот примеры доменов полей:

Строка
Целое число
Число с плавающей точкой и прочее

Мы уже упомянули, что таблицы в реляционных базах данных связаны. Чтобы связать две таблицы, мы используем специальное поле, которое называется ключ. В таблице Users и Payments такой ключ — это колонка UserID . Мы связываем эти две таблицы с помощью оператора join .

Ключ, который является уникальным для всей таблицы, — это первичный ключ. В таблице Users каждый пользователь имеет свой уникальный UserID , и два пользователя не могут иметь один UserID . Но в таблице Payments один пользователь может совершить несколько платежей, поэтому бывают строки с одинаковыми UserID .

В то же время первичный ключ PaymentID для этой таблицы бывает только уникальным, поскольку одна строка — это один платеж, и они не могут повторяться.

Таблицы в реляционных базах данных связаны друг с другом, и сейчас мы рассмотрим разные типы отношений между таблицами.

Отношения таблиц

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

Один к одному
Один ко многим
Многие ко многим

Рассмотрим каждый из этих типов.

Один к одному

Представим, что у нас есть база данных пользователей интернет-магазина. Посмотрим на таблицу Users , в которой есть информация о пользователях:

Глава 24. КАК ДАННЫЕ SQL ХРАНЯТСЯ В УПОРЯДОЧЕННОМ ВИДЕ?

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

КАТАЛОГ СИСТЕМЫ

Чтобы функционировать как БД SQL, ваша компьютерная система должна следить за многими вещами: таблицами, представлениями, индексами, синонимами, привилегиями, пользователями и т.д. Имеются различные способы делать это, но ясно, что наиболее логичный, эффективный и согласованный способ делать это в реляционной среде состоит в том, чтобы сохранять эту информацию в таблицах. Это даёт возможность компьютеру размещать информацию и управлять ею, используя те же самые процедуры, которые он использует чтобы размещать и управлять данными, которые он хранит для вас.

Хотя это — вопрос конкретной программы, а не стандарта ANSI, большинство БД SQL используют набор SQL-таблиц, хранящих служебную информацию, для своих внутренних потребностей. Этот набор называется в различных публикациях системный каталог, словарь данных или просто системные таблицы (Термин «словарь данных» может также относится к общему архиву данных, включая информацию о физических параметрах БД, — данных, которые хранятся вне SQL. Следовательно, имеются программы баз данных, которые имеют и системный каталог, и словарь данных.)

Таблицы системного каталога напоминают обычные SQL-таблицы: те же строки и столбцы данных. Например, одна таблица каталога обычно содержит информацию о таблицах, существующих в БД, по одной строке на каждую таблицу базы данных; другая содержит информацию о различных столбцах таблиц, по одной строке на столбец, и так далее.

Таблицы каталога создаются и заполняются с помощью самой БД и идентифицируются с помощью специальных имён, таких, например, как SYSTEM.

База данных создает эти таблицы и модифицирует их автоматически; таблицы каталога не могут быть непосредственно подвергнуты действию команды модификации. Если это случится, это значительно запутает всю систему и сделает её неработоспособной. Однако в большинстве систем каталог может быть запрошен пользователем. Это очень полезно, потому что это даёт возможность узнать кое-что о БД, которую вы используете. Конечно, не вся информация всегда доступна всем пользователям. Подобно другим таблицам, доступ к каталогу ограничен для пользователей без соответствующих привилегий. Так как каталог принадлежит самой системе, имеется некоторая неясность относительно того, кто имеет привилегии и кто может предоставить привилегии в этом каталоге. Обычно привилегии каталога предоставляет суперпользователь, например, администратор системы, зарегистрированный как SYSTEM или DBA. Кроме того, некоторые привилегии могут предоставляться пользователям автоматически.

ТИПИЧНЫЙ КАТАЛОГ СИСТЕМЫ

Давайте рассмотрим некоторые таблицы, которые мы могли бы найти в типовом каталоге системы:

Таблицы Содержание ------------- ------------------------------------------- SYSTEMCATALOG Таблицы (базовые и представления) SYSTEMCOLUMNS Столбцы таблицы SYSTEMTABLES Каталог представления в SYSTEMCATALOG SYSTEMINDEXES Индексы в таблице SYSTEMUSERAUTH Пользователи базы данных SYSTEMTABAUTH Объектные привилегии пользователей SYSTEMCOLAUTH Столбцовые привилегии пользователей SYSTEMSYNONS Синонимы таблиц

Теперь, если наш DBA предоставит пользователю Stephen право просматривать SYSTEMCATALOG такой командой

GRANT SELECT ON SYSTEMCATALOG TO Stephen;

то Stephen сможет увидеть некоторую информацию обо всех таблицах в БД (мы имеем здесь пользователя DBA, пользователя Chris — владельца трёх наших типовых таблиц, а также Adrian — владельца представления Londoncust).

SELECT tname, owner, numcolumns, type, CO FROM SYSTEMCATALOG; =============== SQL Execution Log =============== | | | SELECT tname, owner, numcolumns, type, CO | | FROM SYSTEMCATALOG; | | | | ================================================ | | tname owner numcolumns type CO | | ------------- ------- ---------- ---- --- | | SYSTEMCATALOG SYSTEM 4 B | | Salespeople Chris 4 B | | Customers Chris 5 B | | Londoncust Adrian 5 V Y | | Orders Chris 5 B | | | ================================================== Рисунок 24.1 Содержание таблицы SYSTEMCATALOG

Как видите, каждая строка описывает свою таблицу. Первый столбец — имя; второй — имя пользователя, который владеет ею; третий — число столбцов таблицы; четвертый — код из одного символа: это или B (для базовой таблицы), или V (для представления). Последний столбец имеет пустые (NULL) значения, если его тип не равен V, и этот столбец указывает, определена или нет возможность проверки.

Обратите внимание что SYSTEMCATALOG (СИСТЕМНЫЙ КАТАЛОГ) представлен как одна из таблиц в вышесказанном списке. Для простоты мы исключили остальные каталоги системы из вывода. Таблицы системного каталога обычно показываются в SYSTEMCATALOG.

ИСПОЛЬЗОВАНИЕ ПРЕДСТАВЛЕНИЙ В ТАБЛИЦАХ КАТАЛОГА

Поскольку SYSTEMCATALOG это таблица, вы можете использовать её в представлении. Фактически можно считать, что имеется такое представление с именем SYSTEMTABLES.

Это представление SYSTEMCATALOG содержит только те таблицы, которые входят в системный каталог; это обычно таблицы базы данных, типа таблицы Продавцов, которые показаны в SYSTEMCATALOG, но не в SYSTEMTABLES.

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

CREATE VIEW Datatables AS SELECT * FROM SYSTEMCATALOG WHERE owner < >'SYSTEM'; РАЗРЕШИТЬ ПОЛЬЗОВАТЕЛЯМ ВИДЕТЬ (ТОЛЬКО) ИХ СОБСТВЕННЫЕ ОБЪЕКТЫ

Кроме того, возможно другое использование представлений каталога. Предположим, вам нужно, чтобы каждый пользователь был способен сделать запрос каталога для получения информации только из таблиц, которыми он владеет. Поскольку значение USER в команде SQL постоянно для ID доступа пользователя, выдающего команду, оно может всегда быть использовано, чтобы давать доступ пользователям только к их собственным таблицам.

Вы можете, для начала, создать следующее представление:

CREATE VIEW Owntables AS SELECT * FROM SYSTEMCATALOG WHERE Owner = USER;

Теперь вы можете предоставить всем пользователям доступ к этому представлению:

GRANT SELECT ON Owntables TO PUBLIC;

Каждый пользователь теперь способен выбирать (SELECT) только те строки из SYSTEMCATALOG, владельцем которых он сам является.

Читать:

Термоэтикетки для яндекс маркета какой размер

ПРЕДСТАВЛЕНИЕ SYSTEMCOLUMNS

Одно небольшое добавление к этому позволит каждому пользователю просматривать таблицу SYSTEMCOLUMNS для столбцов из его собственных таблиц.

Сначала давайте рассмотрим ту часть таблицы SYSTEMCOLUMNS, которая описывает наши типовые таблицы (другими словами, исключим сам каталог):

tname cname datatype cnumber tabowner ----------- ----- -------- ------- -------- Salespeople snum integer 1 Diane Salespeople sname char 2 Diane Salespeople city char 3 Diane Salespeople comm decimal 4 Diane Customers cnum integer 1 Claire Customers cname char 2 Claire Customers city char 3 Claire Customers rating integer 4 Claire Customers snum integer 5 Claire Orders onum integer 1 Diane Orders odate date 2 Diane Orders amt decimal 3 Diane Orders cnum integer 4 Diane Orders snum integer 5 Diane

Как видите, каждая строка этой таблицы показывает столбец таблицы в БД. Все столбцы данной таблицы должны иметь разные имена, так же как каждая таблица должна иметь данного пользователя, и, наконец, все комбинации пользователей, таблиц и имён столбцов должны различаться между собой.

Следовательно, табличные столбцы: tname (имя таблицы), tabowner (владелец таблицы) и cname (имя столбца) вместе составляют первичный ключ этой таблицы. Столбец datatype (тип данных) говорит сам за себя. Столбец cnumber (номер столбца) указывает на местоположение этого столбца внутри таблицы. Для упрощения мы опустили параметры длины столбца, точности и масштаба.

Для справки показана строка из SYSTFMCATALOG, которая ссылается на эту таблицу:

tname owner numcolumns type CO ------------- ------ ----------- ----- --- SYSTEMCOLUMNS System 8 B

Некоторые SQL-реализации будут обеспечивать вас б́ольшим количеством данных, чем показано в этих столбцах, но показанное является основой любых реализаций.

Для иллюстрации процедуры, предложенной в начале этого раздела, имеется способ, позволяющий каждому пользователю видеть информацию SYSTEMCOLUMNS только в принадлежащих ему таблицах:

CREATE VIEW Owncolumns AS SELECT * FROM SYSTEMCOLUMNS WHERE tabowner = USER; GRANT SELECT ON Owncolumns TO PUBLIC;

КОММЕНТАРИЙ В СОДЕРЖАНИИ КАТАЛОГА

Большинство версий SQL, позволяют помещать комментарии (ремарки) в специальные столбцы пояснений таблиц каталогов SYSTEMCATALOG и SYSTEMCOLUMNS, что удобно, так как эти таблицы не всегда могут объяснить свое содержание. Для простоты мы пока исключали этот столбец из наших иллюстраций. Можно использовать команду COMMENT ON со строкой текста, чтобы пояснить любую строку в одной из этих таблиц. Состояние TABLE — для комментирования в SYSTEMCATALOG, а текст COLUMN — для SYSTEMCOLUMNS. Например:

COMMENT ON TABLE Chris.Orders IS 'Current Customer Orders';

Текст будет помещен в столбец пояснений SYSTEMCATALOG. Обычно максимальная длина таких пояснений — 254 символа.

Сам комментарий указывается для конкретной строки, один с tname=Orders, а другой owner=Chris. Мы увидим этот комментарий в строке таблицы Заказов в SYSTEMCATALOG:

SELECT tname, remarks FROM SYSTEMCATALOG WHERE tname = 'Orders' AND owner = 'Chris';

Вывод для этого запроса показан на Рисунке 24.2.

SYSTEMCOLUMNS работает точно так же. Сначала мы создаём комментарий

COMMENT ON COLUMN Orders.onum IS 'Order #';

затем выбираем эту строку из SYSTEMCOLUMNS:

SELECT cnumber, datatype, cname, remarks FROM SYSTEMCOLUMNS WHERE tname = 'Orders' AND tabowner = 'Chris' AND cname = onum;

Вывод для этого запроса показан на Рисунке 24.3.

Чтобы изменить комментарий, вы можете просто ввести новую команду COMMENT ON для той же строки. Новый комментарий будет записан поверх старого. Если вы хотите удалить комментарий, напишите поверх него пустой комментарий:

COMMENT ON COLUMN Orders.onum IS '';

и этот пустой комментарий затрёт предыдущий.

=============== SQL Execution Log =============== | | | SELECT tname, remarks | | FROM SYSTEMCATALOG | | WHERE tname = 'Orders' | | AND owner = 'Chris' | | ; | | ================================================ | | tname remarks | | ------------- ----------------------- | | Orders Current Customers Orders | | | ================================================== Рисунок 24.2 Комментарий в SYSTEMCATALOG =============== SQL Execution Log =============== | | | SELECT cnumber, datatype, cname, remarks | | FROM SYSTEMCOLUMNS | | WHERE tname = 'Orders' | | AND tabowner = 'Chris' | | AND cname = 'onum' | | ; | | ================================================ | | cnumber datatype cname remarks | | ---------- --------- ------ ------------ | | 1 integer onum Orders # | | | ================================================== Рисунок 24.3 Комментарий в SYSTEMCOLUMNS

ОСТАЛЬНОЕ СОДЕРЖИМОЕ КАТАЛОГА

Здесь показаны определения остальных ваших системных таблиц с типовым запросом для каждого:

SYSTEMINDEXES — ИНДЕКСАЦИЯ В БАЗЕ ДАННЫХ

Имена столбцов в таблице SYSTEMINDEXES и их описания:

СТОЛБЕЦ ОПИСАНИЕ ------------- ----------------------------------------------- iname Имя индекса (используемое для его исключения) iowner Имя пользователя, создавшего индекс tname Имя таблицы, которая содержит индекс cnumber Номер столбца в таблице tabowner Пользователь, который владеет таблицей, содержащей индекс numcolumns Число столбцов в индексе cposition Позиция текущего столбца среди набора индексов isunique Уникален ли индекс (Y или N)

ТИПОВОЙ ЗАПРОС Индекс считается неуникальным, если он вызывает продавца в snum-столбце таблицы Заказчиков:

SELECT iname, iowner, tname, cnumber, isunique FROM SYSTEMINDEXES WHERE iname = 'salesperson';

Вывод для этого запроса показан на Рисунке 24.4.

=============== SQL Execution Log ================ | | | SELECT iname, iowner, tname, cnumber, isunique | | FROM SYSTEMINDEXES | | WHERE iname = 'salespeople' | | ; | | ================================================= | | iname iowner tname cnumber isunique | | ----------- ------ ---------- ------- -------- | | salesperson Stephan Customers 5 N | | | =================================================== Рисунок 24.4 Строка из таблицы SYSTEMINDEXES

SYSTEMUSERAUTH — ПОЛЬЗОВАТЕЛЬСКИЕ И СИСТЕМНЫЕ ПРИВИЛЕГИИ В БАЗЕ ДАННЫХ

Имена столбцов для SYSTEMUSERAUTH и их описания:

СТОЛБЕЦ ОПИСАНИЕ -------------- ----------------------------------------------- username Идентификатор (ID) доступа пользователя password Пароль пользователя, вводимый при регистрации resource Где пользователь имеет права RESOURCE dba Где пользователь имеет права DBA

Мы будем использовать простую схему системных привилегий из Главы 22, где были представлены три системные привилегии: CONNECT (ПОДКЛЮЧИТЬ), RESOURCE (РЕСУРСЫ) и DBA.

Все пользователи получают CONNECT по умолчанию при регистрации, поэтому она не описана в таблице выше. Возможные состояния столбцов resource и dba: Y (Да, пользователь имеет привилегии) или No (Нет, пользователь не имеет привилегий).

Пароли (password) доступны только высокопривилегированным пользователям, если таковые существуют. Следовательно, запрос этой таблицы можно вообще делать только для получения информации относительно привилегий системы и пользователей.

ТИПОВОЙ ЗАПРОС Чтобы найти всех пользователей, которые имеют привилегию RESOURCE, и увидеть, какие из них — DBA, вы можете ввести следующее условие:

SELECT username, dba FROM SYSTEMUSERAUTH WHERE resource = 'Y';

Вывод для этого запроса показан на Рисунке 24.5.

SYSTEMTABAUTH — ПРИВИЛЕГИИ ОБЪЕКТА, КОТОРЫЕ НЕ ОПРЕДЕЛЯЮТ АВТОРИЗАЦИЮ СТОЛБЦА

Здесь показаны имена столбцов в таблице SYSTEMTABAUTH и их описания:

СТОЛБЕЦ ОПИСАНИЕ ------------ --------------------------------------------- username Пользователь, который имеет привилегии grantor Пользователь, который передает привилегии по имени пользователя tname Имя таблицы, в которой существуют привилегии owner Владелец tname selauth Имеет ли пользователь привилегию SELECT insauth Имеет ли пользователь привилегию INSERT delauth Имеет ли пользователь привилегию DELETE

Возможные значения каждой из перечисленных привилегий объекта (имена столбцов которых оканчиваются на auth): Y, N и G.

G указывает, что пользователь имеет привилегию с возможностью передачи привилегий. В каждой строке по крайней мере один из этих столбцов должен иметь состояние, отличное от N (другими словами, иметь хоть какую-то привилегию).

=============== SQL Execution Log ================ | | | SELECT username, dba | | FROM SYSTEMUSERAUTH | | WHERE resource = 'Y' | | ; | | ================================================= | | username dba | | ----------- ------ | | Diane N | | Adrian Y | | | =================================================== Рисунок 24.5 Пользователи, имеющие привилегию RESOURCE

Первые четыре столбца этой таблицы составляют первичный ключ. Это означает что каждая комбинация из tname, владелец-пользователь (не забудьте, что две различные таблицы с различными владельцами могут иметь одно и тоже имя), пользователь и пользователь, передающий права (грантор), должна быть уникальной. Каждая строка этой таблицы содержит все привилегии (которые не являются определённым столбцом), предоставляемые одним определённым пользователем другому определённому пользователю в конкретном объекте.

UPDATE и REFERENCES, являются привилегиями, которые могут быть определёнными столбцами и находиться в различных таблицах каталога. Если пользователь получает привилегии в таблице от более чем одного пользователя, такие привилегии могут быть отдельными строками, созданными в этой таблице. Это необходимо для каскадного отслеживания при вызове привилегий.

ТИПОВОЙ ЗАПРОС Чтобы найти все привилегии SELECT, INSERT и DELETE, которые Adrian предоставляет пользователям в таблице Заказчиков, вы можете ввести следующее (вывод показан на Рисунке 24.6):

SELECT username, selauth, insauth, delauth FROM SYSTEMTABAUTH WHERE grantor = 'Adrian' ANDtname = 'Customers'; =============== SQL Execution Log ================ | | | SELECT username, selauth, insauth, delauth | | FROM SYSTEMTABAUTH | | WHERE grantor = 'Adrian' | | AND tname = 'Customers' | | ; | | ================================================= | | username selauth insauth delauth | | ----------- ------- -------- -------- | | Claire G Y N | | Norman Y Y Y | | | =================================================== Рисунок 24.6 Пользователи, получившие привилегии от Adrian

Выше показано, что Adrian предоставил Claire привилегии INSERT и SELECT в таблице Заказчиков, позднее предоставив ей права на передачу привилегий. Пользователю Norman он предоставил привилегии SELECT, INSERT и DELETE, но не дал возможность передачи ни одной из них. Если Claire имела привилегию DELETE в таблице Заказчиков от какого-то другого источника, в этом запросе это показано не будет.

SYSTEMCOLAUTH — ПРИВИЛЕГИИ ОБЪЕКТА, КОТОРЫЕ ОПРЕДЕЛЯЮТ АВТОРИЗАЦИЮ СТОЛБЦА

СТОЛБЕЦ ОПИСАНИЕ ------------- ------------------------------------------- username Пользователь, который имеет привилегии grantor Пользователь, который предоставляет привилегии другому пользователю tname Имя таблицы, в которой существуют привилегии cname Имя столбца, в котором существуют привилегии owner Владелец tname updauth Имеет ли пользователь привилегию UPDATE в этом столбце refauth Имеет ли пользователь привилегию REFERENCES в этом столбце

Столбцы updauth и refauth могут быть в состоянии Y, N или G, но не могут быть одновременно в состоянии N в одной и той же строке. Это первые пять столбцов таблицы, которые не составляют первичный ключ. Он отличается от первичного ключа SYSTEMTABAUTH, в котором содержится поле cname, указывающее на определенный столбец обсуждаемой таблицы, для которой применяются одна или обе привилегии. Отдельная строка в этой таблице может существовать для каждого столбца в любой данной таблице, в которой одному пользователю передаются привилегии определенного столбца с помощью другого пользователя.

Как и в случае с SYSTEMTABAUTH, та же привилегия может быть описана в более чем одной строке этой таблицы, если она была передана более чем одним пользователем.

ТИПОВОЙ ЗАПРОС Чтобы выяснить, в каких столбцах какой таблицы вы имеете привилегию REFERENCES, вы можете ввести следующий запрос (вывод показан на Рисунке 24.7):

SELECT owner, tname, cname FROM SYSTEMCOLAUTH WHERE refauth IN ('Y', 'G') AND username = USE ORDER BY 1, 2;

который показывает, что эти две таблицы, имеющие различных владельцев, но одинаковые имена, в действительности — совершенно разные таблицы (т.е. это не два синонима для одной таблицы).

=============== SQL Execution Log ================ | | | SELECT OWNER, TNAME, CNAME | | FROM SYSTEMCOLAUTH | | WHERE refaulth IN ('Y' , 'G' ) | | AND username = USER | | ORDER BY 1, 2 | | ; | | ================================================= | | owner tname cname | | ----------- ----------- ------- | | Diane Customers cnum | | Diane Salespeople sname | | Diane Salespeople sname | | Gillan Customers cnum | =================================================== Рисунок 24.7 Столбцы, в которых пользователь имеет привилегию INSERT

SYSTEMSYNONS — СИНОНИМЫ ДЛЯ ТАБЛИЦ В БАЗЕ ДАННЫХ

Это имена столбцов в таблице SYSTEMSYNONS и их описания:

СТОЛБЕЦ ОПИСАНИЕ ------------- -------------------------------------------- synonym Имя синонима synowner Пользователь, который является владельцем синонима (может быть PUBLIC (ОБЩИЙ)) tname Имя таблицы, используемой владельцем tabowner Имя пользователя, который является владельцем таблицы

ТИПОВОЙ ЗАПРОС Предположим, что Adrian имеет синоним Clients для таблицы Заказчиков, принадлежащей Diane, и что имеется общий синоним Customers для этой же таблицы. Вы делаете запрос таблицы для всех синонимов в таблице Заказчиков (вывод показан на Рисунке 24.8):

SELECT * FROM SYSTEMSYNONS WHERE tname = 'Customers' =============== SQL Execution Log ================ | | | SELECT * | | FROM SYSTEMSYNONS | | WHERE tname = 'Customers' | | ; | | ================================================= | | synonym synowner tname tabowner | | ----------- ----------- ---------- ---------- | | Clients Adrian Customers Diane | | Customers PUBLIC Customers Diane | | | =================================================== Рисунок 24.8 Синонимы для таблицы Заказчиков

ДРУГОЕ ИСПОЛЬЗОВАНИЕ КАТАЛОГА

Конечно, вы можете выполнять более сложные запросы в системном каталоге. Объединения, например, могут быть очень удобны.
Эта команда позволит вам увидеть столбцы таблиц и базовые индексы, установленные для каждого (вывод показан на Рисунке 24.9):

SELECT a.tname, a.cname, iname, cposition FROM SYSTEMCOLUMNS a, SYSTEMINDEXES b WHERE a.tabowner = b. tabowner AND a.tname = b.tname AND a.cnumber = b.cnumber ORDER BY 3 DESC, 2;

Она показывает два индекса: один — для таблицы Заказчиков и один — для таблицы Продавцов. Последний из них это одностолбцовый индекс с именем salesno в поле snum; он был помещён первым из-за сортировки по убыванию (в обратном алфавитном порядке) в столбце iname. Другой индекс, custsale, используется продавцами, чтобы отыскивать своих заказчиков. Он основывается на комбинации полей snum и cnum внутри таблицы Заказчиков с полем snum, приходящим в индексе первым, как это и показано с помощью поля cposition.

=============== SQL Execution Log ================ | | | SELECT a.tname, a.cname, iname, cposition | | FROM SYSTEMCOLUMNS a, SYSTEMINDEXES b | | WHERE a.tabowner = b.tabowner | | AND a.tname = b.tname | | AND a.cnumber = b.cnumber | | ORDER BY 3 DESC, 2; | | | | ================================================= | | tname cname iname cposition | | ----------- ------- -------- ------------ | | Salespeople sname salesno 1 | | Customers cnum custsale 2 | | Customers snum custsale 1 | | | =================================================== Рисунок 24.9 Столбцы и их индексы

Подзапросы также могут быть использованы. Имеется способ увидеть данные столбца только для столбцов из таблиц каталога:

SELECT * FROM SYSTEMCOLUMNS WHERE tname IN (SELECT tname FROM SYSTEMCATALOG);

Для упрощения мы не будем показывать вывод этой команды, которая состоит из одного входа для каждого столбца каждой таблицы каталога. Вы могли бы поместить этот запрос в представление, назвав его, например, SYSTEMTABCOLS, для представления SYSTEMTABLES.

РЕЗЮМЕ

Итак, система SQL использует набор таблиц, называемый системным каталогом, в структуре базы данных. Эти таблицы могут запрашиваться, но не модифицироваться. Кроме того, вы можете добавлять комментарии столбцов в (и удалять их из) таблицы SYSTEMCATALOG и SYSTEMCOLUMNS.

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

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

Таблицы всех пользователей (1000) в одной базе или разных?

Я думаю с подобным вопросом уже сталкивались хостинги. У меня вопрос следующего содержания:
Есть хостинг услуг для пользователей (их будет порядка 10 тысяч), по хранению и обработке информации на MSSQL2017 или PostgreSQL. Есть 1000 пользователей на сервер, у каждого пользователя есть по 4 своих таблицы (1000пользователей*4таблицы=4000таблиц). Количество записей в каждой таблице порядка 100 000. «Таблица 1» — (id-int, Data-nvarcha(max),
«Таблица 2» — (id-int, Data-int),
«Таблица 3» — (id-int, Data-Float),
«Tаблица 4» — (id-int, Data-bool). В базу будут добавляться данные и удаляться наиболее старшие записи. Вопрос по производительности: Лучше Создать каждому пользователю по 4 таблице в каждой базе, или по 4 таблице каждому пользователю в единой базе?

Отслеживать
28.9k 5 5 золотых знаков 28 28 серебряных знаков 55 55 бронзовых знаков
задан 1 июл 2019 в 13:36
21 5 5 бронзовых знаков

А каким образом пользователи получают доступ к таблицам, подключаясь непосредственно к БД или всегда через какие нибудь ваши программные механизмы (вроде api или пользовательского web интерфейса). А то может и не делать вообще отдельные таблицы .

1 июл 2019 в 13:43
Вопрос в скорости работы или в безопасности?
1 июл 2019 в 13:45

Пользователи будут получать доступ через API, в MSSQL будут готовые процедуры. Вопрос скорее по производительности БД. Если есть варианты по смене БД, то вполне могу сменить. Еще все на стадии разработки.

1 июл 2019 в 13:55

Пока что из описания получается одна или две таблицы на всех пользователей: таблица юзеров и таблица данных с тремя колонками (плюс колонки ключа и т. п.) Опишите подробнее, какая именно информацию будет храниться.

1 июл 2019 в 16:07

Как я понял, 10 тысяч — это про клиентов хостинга. Где хранить пользовательские данные — конечно давать им создавать свои базы. Если речь о данных по учёту ваших клиентов — это отдельное приложение(вполне рядовое) в одной базе. Не нужно создавать отдельные множества таблиц, а тем более баз каждому клиенту. Храните все данные в общем датасете. В общем одна таблица клиентов с аутентификационными данными, а остальные таблицы(услуги, домены и т.п.) ссылаются на pk таблицы клиентов.

2 июл 2019 в 18:40

2 ответа 2

Сортировка: Сброс на вариант по умолчанию

Хранение в виде отдельных таблиц и отдельных баз несет огромные накладные расходы. Прежде всего, на кэширование планов выполнения. SELECT написанный для одного пользователя не будет разделять план выполнения с точно такми же SELECT -ом для другого. Будет тормозить, особенно на холодном старте.

К тому же отдельные базы или таблицы будут тупо выдавливать друг друга из кэша, и у вас будет состояние постоянного холодного старта 🙂

Оба ваши варианта, к сожалению, плохи.

То, что вы разрабатываете, называется multi-tenant приложение.

Структура таблиц для каждого пользователя одинаковая (поля те же, типы данных те же)
Количество данных для каждого пользователя примерно одинаковое (нет жестких перекосов вида пара строк для одного — миллионы для другого)

то нет никакого смысла хранить данные пользователей в раздельных таблицах или раздельных базах. Добавьте в каждую таблицу поле UserID (а лучше — TenantID, это стандартная терминология):

"Таблица 1" - (tenantid-int, id-int, Data-nvarchar(max)), "Таблица 2" - (tenantid-int, id-int, Data-int), "Таблица 3" - (tenantid-int, id-int, Data-Float), "Tаблица 4" - (tenantid-int, id-int, Data-bool).

. и добавьте во все запросы фильтры WHERE TenantID = @CurrentUserID .

Если нужен более строгий и надежный фильтр — используйте Row Level Security вместо ручного WHERE .

И все будет работать без тысяч таблиц и тысяч отдельных баз, на 4 обычных таблицах.

Если перекос по данным есть — то выносите отдельных проблемных (крупных) клиентов в отдельные базы, и роутите туда SQL запросы, с сохранением всей структуры.