17 просмотров
Рейтинг статьи
1 звезда2 звезды3 звезды4 звезды5 звезд
Загрузка...

Ms sql server секционирование таблиц. Создание физической модели базы данных: проектирование производительности

Содержание

Физическое проектирование и реализация базы данных в MS SQL Server;

Сайт СТУДОПЕДИЯ проводит ОПРОС! Прими участие 🙂 — нам важно ваше мнение.

Определение ограничений целостности.

Были определены следующие ограничения целостности:

· book_lending: внешний ключ library_card_id ссылается на library_card (library_card_id) ON UPDATE CASCADE ON DELETE NO ACTION

· book_lending: внешний ключ edition_id ссылается на edition (edition_id) ON UPDATE CASCADE ON DELETE NO ACTION

· edition: внешний ключ edition_genre_id ссылается на genre (genre_id) ON UPDATE CASCADE ON DELETE NO ACTION

· edition: внешний ключ edition_format_id ссылается на edition_format (format_id) ON UPDATE CASCADE ON DELETE NO ACTION

· edition: внешний ключ edition_pubhouse_id ссылается на publishing_house (pubhouse_id) ON UPDATE CASCADE ON DELETE NO ACTION

· library_card: [reader_pass_n] like ‘[0-9][0-9][0-9][0-9][0-9][0-9]’

· library_card: [reader_pass_s] like ‘[0-9][0-9][0-9][0-9]’

Определение состава и структуры таблиц.

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

Ниже представлены описания структуры базы данных для целевой СУБД:

CREATE TABLE [dbo].[book_lending](

[book_lend_id] [int] IDENTITY(1,1) NOT NULL,

[library_card_id] [int] NOT NULL,

[edition_id] [int] NOT NULL,

[lending_date] [datetime] NOT NULL,

[delivery_date_fact] [datetime] NULL,

[delivery_date_plan] [datetime] NULL,

[penalty] [money] NULL,

CONSTRAINT [PK_book_lending] PRIMARY KEY CLUSTERED

)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

CREATE TABLE [dbo].[edition](

[edition_id] [int] IDENTITY(1,1) NOT NULL,

[edition_name] [varchar](50) NOT NULL,

[edition_author] [varchar](50) NULL,

[edition_genre_id] [int] NOT NULL,

[edition_format_id] [int] NOT NULL,

[edition_pubhouse_id] [int] NOT NULL,

[edition_year] [date] NOT NULL,

[edition_count] [int] NOT NULL,

[edition_biblfeatures] [varchar](100) NULL,

[edition_number] [int] NOT NULL,

CONSTRAINT [PK_edition] PRIMARY KEY CLUSTERED

)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY],

CONSTRAINT [IX_edition_number] UNIQUE NONCLUSTERED

)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

CREATE TABLE [dbo].[library_card](

[library_card_id] [int] IDENTITY(1,1) NOT NULL,

[library_card_number] [int] NOT NULL,

[reader_surname] [varchar](30) NOT NULL,

[reader_first_name] [varchar](20) NOT NULL,

[reader_middle] [varchar](20) NOT NULL,

[reader_address] [varchar](30) NULL,

[reader_phone] [char](12) NULL,

[reader_pass_s] [char](4) NOT NULL,

[reader_pass_n] [char](6) NOT NULL,

CONSTRAINT [PK_Library_card] PRIMARY KEY CLUSTERED

)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY],

CONSTRAINT [IX_Library_card] UNIQUE NONCLUSTERED

)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY],

CONSTRAINT [IX_library_card_1] UNIQUE NONCLUSTERED

)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY],

CONSTRAINT [IX_library_card_2] UNIQUE NONCLUSTERED

)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

CREATE TABLE [dbo].[publishing_house](

[pubhouse_id] [int] IDENTITY(1,1) NOT NULL,

[pubhouse_name] [varchar](50) NOT NULL,

CONSTRAINT [PK_publishing_house] PRIMARY KEY CLUSTERED

)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY],

CONSTRAINT [IX_publishing_house] UNIQUE NONCLUSTERED

)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

REATE TABLE [dbo].[edition_format](

[format_id] [int] IDENTITY(1,1) NOT NULL,

[format_name] [varchar](20) NOT NULL,

[format_penalty] [money] NOT NULL,

CONSTRAINT [PK_edition_format] PRIMARY KEY CLUSTERED

)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY],

CONSTRAINT [IX_edition_format] UNIQUE NONCLUSTERED

)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

CREATE TABLE [dbo].[genre](

[genre_id] [int] IDENTITY(1,1) NOT NULL,

[genre_name] [varchar](20) NOT NULL,

CONSTRAINT [PK_Genre] PRIMARY KEY CLUSTERED

)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY],

CONSTRAINT [IX_genre] UNIQUE NONCLUSTERED

)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

Создание физической модели базы данных: проектирование производительности

Секционирование представлений

В СУБД Oracle есть возможность секционировать представления. Основная идея секционирования представлений проста. Пусть физическая таблица разбита на несколько таблиц (необязательно с помощью методов секционирования таблиц) в соответствии с критерием разбиения, который делает обработку запроса более производительной. Критерий разбиения будем называть предикатом секционирования. Тогда можно создать и настроить представления таким образом, чтобы с их помощью обращение к данным этих таблиц было проще для пользователя. Секция представления определяется в соответствии с диапазоном значений ключа секционирования . Запросы, которые используют диапазон значений для выборки данных из секций представления, будут получать доступ только к тем секциям, которые соответствуют диапазонам значений ключа секционирования .

Секции представления могут быть определены предикатами секционирования , заданными либо при помощи ограничения CHECK , либо с использованием предложения WHERE . Покажем, как могут быть применены оба приема, на примере несколько модифицированной таблицы «Продажи» (Sales), которую мы рассматривали в предыдущем разделе. Допустим, что данные о продажах для календарного года размещаются в четырех отдельных таблицах, каждая из которых соответствует кварталу года — Q1_Sales, Q2_Sales, Q3_Sales и Q4_Sales.

Секционирование представлений с помощью ограничения CHECK . С помощью команды ALTER TABLE можно добавить ограничения на колонку «Дата продажи» (s_date) каждой таблицы, чтобы ее строки соответствовали одному из кварталов года. Созданное затем представление sales дает возможность обращаться к этим таблицам, как к одной, так и ко всем вместе.

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

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

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

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

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

  • Секционирование представлений позволяет операциям DML, таким как загрузка данных, создание индексов и удаление данных, работать на уровне секции, а не целой базовой таблицы.
  • Доступ к одной из секций не оказывает никакого действия на данные в других секциях.
  • СУБД Oracle обладает необходимыми встроенными возможностями для распознавания секционированных представлений .
  • Секционирование представлений очень полезно при работе с таблицами, содержащими большое количество исторических данных.

Секционирование таблиц в СУБД семейства MS SQL Server

Создание секционированных таблиц

В СУБД семейства MS SQL Server также поддерживается секционирование таблиц, индексов и представлений. Однако, в отличие от СУБД семейства Oracle, секционирование в СУБД семейства MS SQL Server выполняется по унифицированной схеме.

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

Читать еще:  Nubia EP-NX008 - Наушники громкости высокой точности. Непредсказуемые наушники NUBIA HP1001

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

Для создания секционированной таблицы в СУБД MS SQL Server используются следующие объекты БД: функции секционирования и схемы секционирования . Эти объекты позволяют разделять данные на конкретные сегменты и управлять их местоположением в БД или ХД. Например, можно распределить данные по нескольким дисковым массивам в зависимости от даты поступления данных или других отличительных признаков. Следует отметить, что таблицу можно секционировать по одному из ее столбцов, и каждая секция должна содержать данные, которые не могут храниться в других секциях.

Функции секционирования

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

Строки данных могут сегментироваться по колонке любого типа, кроме следующих: text, ntext, image, xml, timestamp, varchar(max), nvarchar(max), varbinary(max), псевдонимы типов данных и пользовательские типы данных среды CLR. Однако функция секционирования должна распределять каждую строку данных только в одну секцию таблицы ; иными словами, в результате применения функции одна и та же строка не может принадлежать нескольким секциям одновременно.

Чтобы секционировать таблицу, в ней необходимо создать или выбрать колонку секционирования ( ключ секционирования ). Ключ секционирования можно создать в схеме таблицы в момент создания таблицы либо добавить позднее путем модификации таблицы. Столбец может принимать значения NULL, но все строки, содержащие значения NULL, будут по умолчанию помещаться в самую левую секцию таблицы . Этого можно избежать, указав при создании функции секционирования , что значения NULL должны помещаться в самую правую секцию таблицы . Выбор левой или правой секций – важное решение проектирования, проявляющееся при изменении схемы секционирования , добавлении дополнительных секций или удалении существующих.

При создании функции секционирования можно выбрать функции LEFT или RIGHT . Разница между секциями LEFT и RIGHT состоит в размещении данных по секциям. Функция LEFT распределяет данные по принципу от самого низкого значения до самой высокой величины (то есть по возрастанию). Функция RIGHT распределяет данные по принципу от самого высокого значения до самого низкого (то есть по убыванию). Рассмотрим пример.

Возьмем следующие примеры определения функций секционирования с использованием LEFT и RIGHT :

В первой функции ( Left_Partition ) значения 1, 10 и 100 размещаются соответственно в первой, второй и третьей секциях. Во второй функции ( Right_Partition ) эти значения размещаются во второй, третьей и четвертой секциях.

При создании секционированной таблицы важно, чтобы секции получились сбалансированными по кардинальности. Это позволяет оценить, сколько дискового пространства потребуется для каждой секции. Использование параметров LEFT и RIGHT определяет, куда будут размещаться данные, что, в свою очередь, задает размер секции и размеры индексов , созданных на ней.

Определить номер секции, в которую попадут те или иные данные, можно с помощью функции $PARTITION , как показано ниже:

Первая команда SELECT возвращает значение 2, вторая – значение 3.

Схемы секционирования

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

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

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

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

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

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

Рассмотрим в качестве примера схему типа «звезда» с таблицей фактов «Продажи» (SALES), как показано на рис. 20.6. Создадим секционированную таблицу «Продажи» (SALES).

Сначала мы должны создать функцию секционирования :

MyPartitionFunctionLeft — это название функции разделения, datetime — тип данных ключа секционирования , а RANGE LEFT указывает, как делить значения данных, которые связаны с датами FOR VALUES .

Ключ секционирования имеет тип данных date , т.е. это колонка «Дата события» (Date_of_Event). В команде, приведенной выше, деление строк на непересекающиеся группы построено по принципу разбиения их на двухлетние группы. Разделение на секции RANGE LEFT делит данные в диапазонах значений, показанных на рис. 20.7.

Каждая область значений в секции имеет границы, которые определены в операторе FOR VALUES . Если дата продажи была 23 июня 2006 года, то строка будет храниться в секции 2 (P2).

Теперь создадим схему секционирования . Схема секционирования отображает секции на различные файловые группы (с именами MyFilegroup1, MyFilegroup2, MyFilegroup3, MyFilegroup4 ) , как показано в следующей команде:

MyPartitionScheme – это имя схемы секционирования , а имя MyPartitionFunction определяет функцию секционирования . Эта команда отображает данные в секции, которые связаны с одной или несколькими файловыми группами. Строки с данными со значениями колонки «Дата продажи» (Date_of_Event date) до 1/01/05 связаны с MyFilegroup1 . Строки этой колонки со значениями, большими или равными 1/01/05 и до 1/01/07, назначены MyFilegroup2 . Строки со значениями, большими или равными 1/01/07 и до момента 1/01/09, связаны с MyFilegroup3 . Все остальные строки со значениями, большими или равными 1/01/09, связаны с MyFilegroup4 .

Для каждого набора граничных значений (которые задаются условием FOR VALUES функции секционирования ) количество секций будет равно «Количество граничных значений» + 1 секция. Предыдущее предложение CREATE PARTITION SCHEME включает три ограничения и четыре секции. Независимо от того, созданы ли секции с RANGE RIGHT или RANGE LEFT , количество секций всегда будет равно «Количество граничных значений» + 1, вплоть до 1000 секций на таблицу.

Теперь мы можем создать секционированную таблицу фактов «Продажи» (SALES). Создание секционированной таблицы мало чем отличается от создания обычной таблицы, нужно только сослаться на имя схемы секционирования в условии ON , как показано в команде ниже.

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

Можно объединять только две смежные секции. Чтобы слить две секции, выполните команду:

Здесь секция 1 (P1) объединится с секцией P2. Это означает, что секция P2 будет содержать все строки со значением колонки «Дата продажи» (Date_of_Event) до значения даты 1/01/07. В системной таблице sys.partitions секции будут перенумерованы, начиная с единицы (не с нуля). Секции P1 и P2 станут P1, секция P3 станет P2 и P4 станет P3.

Секционирование индексов в СУБД семейства MS SQL Server

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

Читать еще:  Утилиты внутренней памяти highscreen alpha rage. Получение Root Highscreen Alpha Rage

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

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

Создадим секционированный некластеризованный индекс на секционированной таблице «Продажи» (SALES) из предыдущего примера 20.18. Некластеризованный индекс выравнивается с таблицей; в качестве ключа некластеризованного индекса используется ключ секционирования таблицы.

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

Затем разместим все секции индекса в одной файловой группе с именем PRIMARY , выполним команду

Теперь выполним команду создания индекса , как показано ниже.

В этом некластеризованном индексе в качестве ключа индекса используется колонка «Идентификатор покупателя» (Cust_ID), которая не является ключом секционирования таблицы «Продажи» (SALES).

Решения о секционировании индексов принимаются проектировщиком ХД на стадии проектирования или администратором ХД на стадии эксплуатации ХД. Целью секционирования индексов является либо обеспечение производительности запросов, либо упрощение процедур сопровождения индекса .

m_i_kuznetsov

Размышления о разработке программного обеспечения и информационных систем

То, что действительно важно, но чему нигде не учат

Самому мне писать влом 🙂

Posts from This Journal by “базы данных” Tag

Повышение квалификации: Проектирование высоконагруженных систем

Александр Быков говорит о том же, о чём не устаю говорить я своим коллегам: качество реализуемых систем имеет многоуровневые зависимости от…

Ссылка: Руководство по MongoDB

Руководство по MongoDB MongoDB это кросс-платформенная, документно-ориентированная СУБД, которая обеспечивает высокую производительность и лёгкую…

Тринадцатый семинар для молодых аналитиков

Очень тяжёлая тема для понимания — диаграмма классов. И тяжёлая не только с точки зрения понимания самой диаграммы, но и с точки зрения её…

Анализ хранилищ данных

Существуют разные типы хранилищ данных. Наиболее распространённые хранилища корпоративных данных – файловые и реляционные (на основе…

SQL: что тормозит удаление записей

В реляционных БД есть 8 основных причин тормозов на DELETE (не считая кривых запросов): большое количество индексов в таблице, из которой…

Список СУБД NoSQL

База знаний о нереляционных системах управления данными: http://nosql-database.org

DB-Engines — база знаний о реляционных и нереляционных СУБД

Открыл для себя ресурс DB-Engines — базу знаний о различных СУБД. Характеристики, сторонние инструменты, кривая популярности, источники…

Закон Мэрфи применительно к базам данных

«Если вы проектируете базу данных так, что в неё могут быть помещены неправильные данные, то в конечном счёте так и…

Конкатенация строк с NULL в SQL Server

Ещё одна вещь в SQL, которая почему-то вызывает вопросы. Причём вполне себе стандартная. Вот пример. Сделаем три запроса к простенькой таблице:…

m_i_kuznetsov

Размышления о разработке программного обеспечения и информационных систем

То, что действительно важно, но чему нигде не учат

Самому мне писать влом 🙂

Posts from This Journal by “базы данных” Tag

Повышение квалификации: Проектирование высоконагруженных систем

Александр Быков говорит о том же, о чём не устаю говорить я своим коллегам: качество реализуемых систем имеет многоуровневые зависимости от…

Ссылка: Руководство по MongoDB

Руководство по MongoDB MongoDB это кросс-платформенная, документно-ориентированная СУБД, которая обеспечивает высокую производительность и лёгкую…

Тринадцатый семинар для молодых аналитиков

Очень тяжёлая тема для понимания — диаграмма классов. И тяжёлая не только с точки зрения понимания самой диаграммы, но и с точки зрения её…

Анализ хранилищ данных

Существуют разные типы хранилищ данных. Наиболее распространённые хранилища корпоративных данных – файловые и реляционные (на основе…

SQL: что тормозит удаление записей

В реляционных БД есть 8 основных причин тормозов на DELETE (не считая кривых запросов): большое количество индексов в таблице, из которой…

Список СУБД NoSQL

База знаний о нереляционных системах управления данными: http://nosql-database.org

DB-Engines — база знаний о реляционных и нереляционных СУБД

Открыл для себя ресурс DB-Engines — базу знаний о различных СУБД. Характеристики, сторонние инструменты, кривая популярности, источники…

Закон Мэрфи применительно к базам данных

«Если вы проектируете базу данных так, что в неё могут быть помещены неправильные данные, то в конечном счёте так и…

Конкатенация строк с NULL в SQL Server

Ещё одна вещь в SQL, которая почему-то вызывает вопросы. Причём вполне себе стандартная. Вот пример. Сделаем три запроса к простенькой таблице:…

Ms sql server секционирование таблиц. Создание физической модели базы данных: проектирование производительности

Главными объектами всякой СУБД являются базы данных (databases). В старых СУБД (например, FoxPro ранних версий под MS-DOS) класс объектов «база данных» не был введен явно. Теперь практически в любой СУБД имеется такой класс объектов. База данных является контейнером и пространством имён для всех использующихся в ней объектов.

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

Внутри всякой базы данных имеются следующие классы объектов: таблица (table), представление (view), хранимая процедура (stored procedure), индекс (index), связь (relationship), триггер (trigger), умолчание (default). Также в ряде коммерческих СУБД есть такой полезный класс объектов, как диаграмма (diagram).

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

Представления по сути своей являются результатами запросов к базе данных. Работать с представлением в большинстве случаев можно как с таблицей. Механизм работы таков: пользователь обращается к представлению, как к таблице, СУБД, принимая это обращение, исполняет запрос, записанный в представлении, и возвращает пользователю сформированную по результатам запроса таблицу. Для SQL Server 2000 и некоторых других СУБД в ряде случаев можно изменять данные прямо в таблице запроса, и эти изменения будут отражены в исходных таблицах, по которым был построен запрос. Подробнее мы к этому вопросу ещё вернёмся, когда будем изучать язык манипулирования данными.

Хранимая процедура является набором инструкций (часто в скомпилированном виде), хранящимся на стороне СУБД. Набор инструкций обычно пишется как последовательность SQL-команд. В SQL Server под компиляцией понимается подготовка плана исполнения процедуры в момент её создания или обновления. Подробнее о планах исполнения мы поговорим, когда будем изучать язык манипулирования данными. В хранимых процедурах можно реализовывать сложные алгоритмы обработки данных внутри базы. Использование хранимых процедур часто значительно сокращает объём кода клиентского приложения и во многих случаях позволяет чётко отделять алгоритмы логики программы от алгоритмов обработки данных. О многоуровневых приложениях скажем несколько слов где-нибудь в следующих статьях.

Индексом называется специальный объект, хранящий упорядоченный справочник значений некоторого атрибута (или группы атрибутов) таблицы. Благодаря индексам можно значительно (иногда на порядки) ускорить операции поиска данных. Без индекса поиск осуществляется последовательным перебором кортежей. Наличие индекса позволяет СУБД использовать более быстрые алгоритмы поиска, нежели последовательный перебор.

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

Связью (или отношением) называется особый объект, позволяющий установить зависимость между таблицами и сопоставить этой зависимости ряд проверяющих триггеров. В подавляющем большинстве СУБД реализовано лишь один из возможных видов связи: «один ко многим» («one to many»). В такой связи участвуют две таблицы, в одной из которых определяется первичный ключ (primary key), а в другой внешний ключ (foreign key). Всегда первичный ключ — это «один», внешний ключ — «много». Соответственно, таблица, содержащая первичный ключ, называется главной (master table), а таблица с внешним ключом — подчинённой (detail table). Разумеется, имеется возможность связать таблицу с самой собой.

Читать еще:  Скайрим 5 клык вирма храм прохождение. Wyrmstooth - файл readme

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

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

Ms sql server секционирование таблиц. Создание физической модели базы данных: проектирование производительности

Лабораторная работа №4. Визуальная разработка информационной модели и БД (Microsoft SQL Server)

1. Назначение Microsoft SQL Server

Microsoft SQL Server – высокопроизводительная система управления реляционными базами данных (СУБД), разработанная корпорацией Microsoft. Основной используемый язык запросов — Transact-SQL, представляющий собой диалект стандарта ANSI/ISO по структурированному языку запросов (SQL). Помимо основных функций стандартной СУБД (создание, модификация и администрирование БД), в Microsoft SQL Server поддерживаются возможности визуальной разработки БД, характерные для CASE-средств (прямое и обратное проектирование БД, синхронизация схемы БД с самой БД, генерация DDL-скриптов).

Ниже рассматривается некоторые аспекты визуальной разработки БД с использованием стандартного менеджера СУБД Microsoft SQL Server версии 2012. Скачать облегченную версию (Express Edition) можно по адресу http://www.microsoft.com/sqlserver/ru/ru/default.aspx.

2. Общие сведения об интерфейсе Microsoft SQL Server

Внешний вид главного окна Microsoft SQL Server Management Studio представлен на следующем рисунке.

Рис. 1. Интегрированная среда Microsoft SQL Server Management Studio

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

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

— конструктор (дизайнер) схем баз данных (отображение структуры базы данных или ее части; просмотр связей между таблицами базы данных; изменение структуры базы данных);

— конструктор таблиц (создание новых таблиц; открытие таблиц для редактирования; редактирование свойств столбцов; создание или изменение связей; установка первичных и уникальных ключей; создание или изменение индексов, включая XML-индексы и полнотекстовые индексы; создание или изменение ограничений; создание скриптов изменения);

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

При работе с конструктором схем баз данных в окне свойств объектов отображаются и возможна модификация параметров столбцов, таблиц и связей между ними

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

3. Создание БД и диаграммы с помощью DDL-скрипта

Для создания БД с помощью DDL-скрипта необходимо выбрать пункт меню «Файл / Создать / Запрос в текущем соединении». В рабочей области менеджера появиться пустое окно запроса, в которое необходимо скопировать DDL-скрипт создания БД (см. лабораторную работу № 3 «Разработка информационной модели (методология IDEF1X)»).

Рис. 2. Редактор запросов

Перед выполнением скрипта необходимо создать БД или в начало скрипта добавить SQL-оператор (CREATE DATABASE ). Для выполнения скрипта следует выбрать пункт меню «Запрос / Выполнить». При успешном создании таблиц БД, они появятся в обозревателе объектов БД.

Для создания диаграммы необходимо в обозревателе объектов БД подвести указатель мыши на узел «Диаграммы баз данных», вызвать контекстное меню и в нем выбрать пункт «Создать диаграмму базы данных».

Рис. 3. Контекстное меню диаграмм баз данных

Далее в обозревателе объектов БД необходимо выделить требуемые таблицы (удерживая клавишу Ctrl) и переместить их на диаграмму. Диаграмма примет вид, показанный на рис.1.

Изменить степень детализации отображаемой информации по таблицам можно через контекстное меню, вызываемое при нажатии правой кнопки мыши на любой таблице.

Рис. 4. Контекстное меню таблицы

4. Редактирование структуры БД и диаграмм

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

Создание таблицы. Для создания новой таблицы и отображения ее на диаграмме необходимо выбрать пункт меню «Диаграмма базы данных / Создать таблицу» и в появившемся диалоговом окне задать ее имя. В окне с диаграммой появится пустая таблица, в которую необходимо добавить и указать имена столбцов, их тип и возможность хранения неопределенных значений (NULL). Изменить наименование таблицы, ее описание (примечания) и принадлежность к схеме можно в окне свойств объектов.

Рис. 5. Свойства таблицы

Изменение параметров столбцов. При выборе на диаграмме столбца таблицы в окне свойств объектов появится следующий список параметров.

Рис. 6. Свойства столбца

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

Задание первичного ключа. Для включения или удаления столбца из первичного ключа таблицы необходимо на диаграмме выбрать требуемый столбец, вызвать контекстное меню таблицы (см. рис.4) и выбрать соответствующий пункт меню «Задать первичный ключ» («Удалить первичный ключ»).

Создание связи между таблицами. Для задания связи между таблицами следует выполнить следующую последовательность действий.

1. Выделить дочернюю таблицу.

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

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

Рис. 7. Задание таблиц и столбцов по связи

4. Нажатие на кнопку «OK» приведет к появлению следующего окна, в котором задаются дополнительные параметры связи.

Рис. 8. Задание дополнительных параметров

В частности, в данном окне можно задать триггеры. Триггеры со стороны родительской таблицы указываются в разделе «Спецификация INSERT и UPDATE» (правильнее было назвать «Спецификация DELETE и UPDATE»). При этом возможны следующие варианты типов триггеров:

— Нет действия (RESTRICT или NO ACTION);

— Присвоить NULL (SET NULL);

— Присвоить значение по умолчанию (SET DEFAULT).

Триггеры на вставку (INSERT) и обновление (UPDATE) со стороны дочерней таблицы задаются опосредованно (см. п. 7.10.7 «Особенности генерации DDL-скриптов на основе ERD»). При указании в окне на рис. 8 для параметра «Включить использование ограничения внешнего ключа» значения «Да», данные триггеры будут работать по типу RESTRICT.

Параметры, отображенные на рис.8, можно посмотреть или изменить в окне свойств объектов БД при выборе связи на диаграмме.

Рис. 9. Задание дополнительных параметров в окне свойств объектов БД

Для синхронизации созданной (измененной) схемы БД, изображенной на диаграмме, с БД на диске необходимо выбрать пункт меню «Файл / Сохранить ».

5. Генерация DDL-скрипта для отдельной таблицы

Для генерации DDL-скрипта таблицы необходимо в обозревателе объектов БД подвести указатель мыши на узел с наименованием таблицы, вызвать контекстное меню и в нем выбрать пункт «Создать скрипт для таблицы / Используя CREATE / Новое окно редактора».

Рис. 10. Создание DDL-скрипта генерации таблицы

После выбора соответствующего пункта меню в рабочей области менеджера появится окно со скриптом.

Рис. 11. DDL-скрипт генерации таблицы

6. Задание на выполнение лабораторной работы

1) Изучить и закрепить основы разработки информационных моделей (см. Тема 7. Разработка информационной модели).

2) Освоить стандартный менеджер Microsoft SQL Server Management Studio в части визуальной разработки БД.

4) Оформить и защитить отчет. В отчете должны быть приведены:

Ссылка на основную публикацию
Статьи c упоминанием слов:
Adblock
detector