Microsoft SQL Server — система управления реляционными базами данных (СУБД), разработанная корпорацией Microsoft. Основной используемый язык запросов — Transact-SQL, создан совместно Microsoft и Sybase. Transact. SQL является реализацией стандарта ANSI/ISO по структурированному языку запросов (SQL) с расширениями. Используется для небольших и средних по размеру баз данных, и в последние 5 лет — для крупных баз данных масштаба предприятия, конкурирует с другими СУБД в этом сегменте рынка. Релизы *1992 — SQL Server 4. 2 *1993 — SQL Server 4. 21 под Windows NT *1995 — SQL Server 6. 0, кодовое название "SQL 95 " *1996 — SQL Server 6. 5, к. н. "Hydra " *1999 — SQL Server 7. 0, к. н. "Sphinx " *1999 — SQL Server 7. 0 OLAP, к. н. "Plato " *2000 — SQL Server 2000 32 -bit, к. н. "Shiloh " (версия 8. 0) *2003 — SQL Server 2000 64 -bit, к. н. "Liberty " *2005 — SQL Server 2005, к. н "Yukon " (версия 9. 0) *2008 — SQL Server 2008, к. н. "Katmai " (версия 10. 0) Версии SQL Server 2005 * Enterprise Edition * Standard Edition * Workgroup Edition * Developer Edition * Express Edition * Mobile Edition * Desktop Ediion
Физическая архитектура базы данных Экземпляр Microsoft SQL Server включает в себя системные базы данных (master, model, msdb, tempdb), содержащие служебную информацию, и пользовательские базы данных. Каждая база данных размещается в отдельных файлах – минимум двух: один для самой базы данных – файл данных (mdf-файл), и один для журнала транзакций (ldf-файл). Первый файл данных (mdf-файл) является основным и кроме самих данных содержит системную информацию, второй и все последующие файлы данных являются вторичными (ndf-файлами) и содержат непосредственно сами данные. Основной единицей хранения данных является страница. SQL Server выполняет чтение и запись данных постранично. Вся база данных логически подразделена на страницы, нумеруемые начиная с 0. Размер страницы составляет 8 Кбайт (128 страниц на один мегабайт) Для более эффективного управления страницами они объединяются в экстенты – по 8 страниц в экстенте. Экстенты могут быть двух типов: • mixed – страницы, входящие в такой экстент могут принадлежать разным объектам; uniform- экстент содержит станицы, принадлежащие одному объекту
Системная информация о странице хранится в заголовке, под который отводится первые 96 байт. Эта информация содержит номер страницы, тип страницы, количество свободного пространства, и ID объекта, владеющего страницей. В конце каждой страницы располагается таблица смещения строк.
SQL Server использует в файлах данных следующие типы страниц: • Data- страница содержит строки всех данных за исключением данных типа text, ntext, image, nvarchar(max), varbinary(max) и xml-данных; • Index - страница содержит информацию о индексах; . • Text/Image страница для хранения: следующих типов LOB-объектов: o text, ntext, image, nvarchar(max), varbinary(max) и xml-данных; столбцов переменной длины, чей размер строки превышает 8 KB: o varchar, nvarchar, varbinary и sql_variant • Global Allocation Map - страница данного типа содержит информацию об используемости экстентов (на одной странице хранятся данные об используемости 64000 экстентов); • Shared Global Allocation Map - страница данного типа содержит информацию об используемости экстентов типа Mixed; • Page Free Space страница содержит информацию количестве о свободного пространства на странице; • Index Allocation Map страница содержит данные, экстенты страницы, какие имеют принадлежащие одному объекту-владельцу; • Bulk Changed Map страница содержит информацию об экстентах, измененных посредством набора операций, выполненных последней операции копирования базы данных (BACKUP LOG); • Differential Changed Map страница содержит информацию об экстентах, измененных с момента последней операции копирования базы данных (BACKUP DATABASE).
Таблицы и индексы хранятся как наборы страниц. Таблица может быть подразделена на одно или несколько разбиений (partitions), содержащих строки. Разбиение таблицы определяет пользователь при ее создании. Таблицы SQL Server 2005 используют для организации их страниц данных в разбиении один из следующих двух методов: • Кластерные таблицы (для которых создан кластерный индекс, требующий физического перестроения данных в соответствии со структурой индекса); • Кучи (Heaps) – таблицы, не имеющие кластерного индекса.
Индексы в SQL Server организованы в виде В-деревьев. Каждая страница в индексном В-дереве называется индексным узлом (index node). В вершине Вдерева расположен корневой узел (root node). В нижней части дерева располагаются индексные узлы, называемые листья (leaf nodes). Между вершиной дерева и листьями располагаются промежуточные уровни (intermediate levels). Каждая строка индекса содержит ключевое значение и указатель или на страницу промежуточного уровня в В-дереве, или на данные, расположенные в нижнем уровне дерева (leaf level).
Доступ к таблицам, не имеющим кластерных индексов, выполняется посредством последовательного просмотра IAM-страниц с целью нахождения экстентов, содержащих станицы, относящиеся к данной куче. Следующий рисунок иллюстрирует процесс извлечения строк данных таблицы машиной баз данных Database Engine.
Некластерные индексы имеют структуру В-дерева подобно кластерным индексам, за исключением следующих различий: • строки данных таблицы являются не упорядоченными и хранятся в порядке, основанном на их некластерном ключе; • leaf- уровень некластерного индекса состоит из индексных страниц, а не страниц данных. Каждая строка индекса в некластерном индексе содержит некластерное ключевое значение и локатор строки (row locator). Если таблица не содержит кластерного индекса, то локатор строки является указателем строки, в противном случае – ключом кластерного индекса для данной строки. Указатель строки (ROWID) содержит ID-файла, номер страницы, номер строки на странице
Компоненты SQL Server 2005 Analysis Services Full-Text Search Replica tion SQL Server Integration Services Relational Database Engine. NET CLR Native HTTP Support Notification Services Reporting Services Service Broker Relational Database Engine – это ядро SQL Server, использующее. NET. Analysis Services – службы для анализа данных, поддерживают OLAP. SQL Server Integration Services (SSIS) – средства для создания решений импорта и экспорта данных и выполнения трансформирования данных при передаче. Notifiations Services – службы оповещения. Reporting Services – службы для создания и публикации отчетов. Service Broker – механизм, основанный на очереди, для связывания различных служб приложений. Native HTTP Support – встроенная поддержка HTTP. Позволяет отвечать на запросы HTTP без IIS. SQL Server Agent – автоматизирует обслуживание БД и управляет задачами, событиями и оповещениями. Replication – ряд средств для копирования и распространения данных и объектов БД из одной БД в другую и синхронизации между БД для поддержания соответствия. Full-Text Search – средство эффективного поиска в БД.
Microsoft SQL Server 2005. Объекты базы данных Логически данные в базе данных хранятся в виде объектов базы данных. Объекты данных хранятся в схеме базы данных. SQL Server предоставляет следующие объекты данных: • таблицы; • представления; • синонимы; • индексы; • хранимые процедуры; • триггеры; • пользовательские типы данных; • функции пользователя; • ключи, обеспечивающие ссылочную целостность; • ограничения целостности; • умолчания • правила (используются для обратной совместимости) К объектам базы данных также относятся схемы, пользователи и роли. В SQL Server введены новые объекты, используемые Service Broker: • типы сообщений (структура сообщения, отправляемого от одного сервиса другому), • контракты (соглашения между двумя сервисами), • очереди (сообщения, направленные сервису), • сервисы (наборы задач, где каждая задача представляется контрактом), сервисные программы.
Новые инструменты для администраторов и разработчиков БД SQL Server Management Studio, позволяет управлять серверами БД, Analysis Services и серверами Reporting Services. SQL Server 2005 предоставляет 4 средства для разработки: • SQL Server Management Studio; • Business Intelligence Development Studio; • sqlcmd; • Дизайнеры Visual Studio. SQL Server Management Studio в разработке может использоваться для графического создания БД или для создания, выполнения и сохранения скриптов. Business Intelligence Development Studio используется для создания решений Analysis Services. Sqlcmd – новая утилита командной строки для замены isql и osql, предоставляет дополнительную функциональность. SQL Server 2005 расширяет среду Visual Studio для облегчения конструирования отчетов.
Безопасность SQL Server 2005 Основные принципы безопасности остались теми же. Имеются пользователи, принципалы (principals), защищаемые объекты (securables) и разрешения или полномочия (permissions), описывающие права принципалов на проведение тех или иных действий над защищаемыми объектами. Безопасность реализована на трех уровнях: операционной системы Windows, SQL Server и базы данных (database). Разделение принципалов и защищаемых объектов по трем уровням безопасности показано на рисунке. Разрешения (GRANT, REVOKE, DENY) и основные действия над объектами также показаны на рисунке
Управление безопасностью с использованием схем Управление схемами осуществляется при помощи трех операторов: CREATE SCHEMA <выражение имени схемы> [<элемент схемы>[, …n]] ALTER SCHEMA <имя схемы> TRANSFER <имя объекта> DROP SCHEMA <имя схемы> для создания, модифицирования и удаления схемы соответственно. В качестве <выражения имени схемы> может быть просто имя схемы, выражение <имя схемы> AUTHORIZATION <имя владельца схемы>, выражение AUTHORIZATION <имя владельца схемы>. В качестве <элемента схемы> можно указать операторы CREATE TABLE, CREATE VIEW или GRANT. В операторе ALTER SCHEMA под <именем объекта> указывается объект, который помещается в схему. -- Создаем логин на сервере CREATE LOGIN Paul WITH PASSWORD ='P@ssw 0 rd' USE Adventure. Works -- Создаем пользователя в БД CREATE USER Paul FOR LOGIN Paul GO -- Создаем схему CREATE SCHEMA my. Schema AUTHORIZATION Paul CREATE TABLE Person. Data (rec. ID int, name nvarchar(60)) Пример 1. Создание схемы с указанием владельца и таблицей Для владельца схемы созданная схема не является схемой по умолчанию. У пользователя может быть несколько схем. Указать схему по умолчанию для пользователя мы можем в операторах CREATE USER и ALTER USER Paul WITH DEFAULT_SCHEMA=my. Schema Пример 2. Задание схемы по умолчанию существующему пользователю Схемы являются такими же объектами БД, как и таблицы, поэтому, к ним также применяются разрешения. DENY SELECT ON SCHEMA: : my. Schema TO public Пример 3. Запрещаем SELECT в схеме my. Schema роли public.
Средства разработки SQL Server 2005 Для разработчиков БД расширен Transact SQL, встроена поддержка XML, появились службы обмена сообщениями, оповещений и отчетов, встроена поддержка HTTP, проведена интеграция с Common Language Runtime (CLR), созданы так называемые объекты управления SQL (SQL Management Objects – SMO), имеется возможность обработки структурированных исключений. В Transact SQL введены новые типы данных, структурированная обработка исключений, общие табличные выражения и функции ранжирования. Можно использовать тип данных xml для хранения в БД данных в формате XML и обработки этих данных как XML (X-Query). CLR интегрирована в ядро SQL Server, что позволяет создавать хранимые процедуры, функции, триггеры и типы данных, написанные на языках. NET. Объекты SMO предоставляют управляемый программный интерфейс для написания скриптов административных задач. Это позволяет разработчикам создавать административные решения, используя основанные на COM и. NET языки программирования.
Усовершенствования языка программирования Transact. SQL Новые и усовершенствованные типы данных Расширены типы данных varchar, nvarbinary. Если раньше их размер составлял не более 8000 байт, то, используя ключевое слово max (varchar(max), nvarbinary(max)), можно увеличить их размер до 2 Гб. Новый тип xml предназначен для хранения данных в формате XML и обработки этих данных, используя возможности формата. CREATE TABLE my. Schema. XMLTable (rec. ID int, xml. Doc xml) Можно также использовать XML-схемы. DECLARE @xml. Var xml (AWSchema. Collection)
Разбиение таблиц на разделы (партиципирование) Разбиение таблицы на разделы позволяет хранить табличные данные в нескольких файлах, что улучшает производительность на мультипроцессорных и многодисковых системах и облегчает управление большими таблицами. Разбиение таблицы осуществляется в три этапа: CREATE PARTITION FUNCTION email. PF (nvarchar(30)) AS RANGE RIGHT FOR VALUES (‘G’, ’N’) создается схема разбиения; CREATE PARTITION SCHEME email. PS AS PARTITION email. PF TO (fg 1, fg 2, fg 3) создается партиципированная таблица. CREATE TABLE Customer. Email (rec. ID int, email nvarchar(30)) ON email. PS (email) Переменные fg 1, fg 2, fg 3 – это файловые группы. Именно по этим трем файлам будет разбита таблица. В первый файл попадут email-адреса, начинающиеся c букв «A-F» , во второй – «G-M» , в третий – «N-Z» . Узнать, в какую часть (партицию) будет записано то или иное значение, можно выполнив следующий запрос: создается функция разбиения; SELECT $partition. <имя функции>(<значение>).
Усовершенствования DML. Ключевое слово OUTPUT Для создания лога, описывающего в таблице 2, скажем, ввод строк в таблицу 1, в SQL Server 2000 приходилось создавать триггеры. Теперь для этого можно использовать ключевое слово OUTPUT. Рассмотрим на примере. DECLARE @Insert. Details TABLE (Product. Model. ID int, Inserted. By sysname) INSERT INTO Production. Product. Model(Name, Modified. Date) OUTPUT inserted. Product. Model. ID, suser_name() INTO @Insert. Details VALUES ('Racing Bike', getdate()) SELECT * FROM @Insert. Details Пример 4. Использование OUTPUT
Операторы PIVOT и UNPIVOT осуществляют переворот значений в столбцы и столбцов в значения. PIVOT переворачивает значения в столбцы. UNPIVOT переворачивает столбцы в значения.
Оператор APPLY позволяет осуществлять в запросе SELECT объединение таблицы с результатом функции, возвращающим таблицу. При этом функция считается для каждой строки объединяемой таблицы. Есть два варианта этого оператора: CROSS APPLY и OUTER APPLY. Их смысл тот же, что и у операторов INNER и OUTER JOIN. Рассмотрим пример. CREATE FUNCTION Sales. Most. Recent. Orders (@Cust. ID AS int) RETURNS TABLE AS RETURN SELECT TOP(3) Sales. Order. ID, Order. Date FROM Sales. Order. Header WHERE Customer. ID = @Cust. ID ORDER BY Order. Date DESC SELECT Name AS Customer, MR. * FROM Sales. Store CROSS APPLY Sales. Most. Recent. Orders(Customer. ID) AS MR Пример 7. Оператор APPLY
Общие табличные выражения (Common Table Expressions) Общее табличное выражение (CTE) – это именованный временный результат, основанный на запросе SELECT. WITH Top. Sales (Sales. Person. ID, Num. Sales) AS (SELECT Sales. Person. ID, Count(*) FROM Sales. Order. Header GROUP BY Sales. Person. Id) SELECT Login. ID, Num. Sales FROM Human. Resources. Employee e INNER JOIN Top. Sales ON Top. Sales. Person. ID = e. Employee. ID ORDER BY Num. Sales DESC Пример 5. Общее табличное выражение
Функции упорядочивания (ranking) Функция Описание Возвращает ранг каждой строки в пределах группы RANK результирующего отношения Возвращает последовательный ранг каждой строки в DENSE_RANK пределах группы результирующего отношения Возвращает порядковый номер для каждой строки в ROW_NUMBER пределах группы результирующего отношения Разделяет строки в каждой группе результата по специальным номерам ранга, основанным на значении NTILE переданного параметра SELECT P. Name Product, P. List. Price, PSC. Name Category, RANK() OVER(PARTITION BY PSC. Name ORDER BY P. List. Price DESC) AS Price. Bank FROM Production. Product P JOIN Production. Product. Sub. Category PSC ON P. Product. Sub. Category. ID=PSC. Product. Sub. Category. ID Пример 8. Функция RANK
Обработка структурированных исключений (structured exception) Обработка структурированных исключений давно присутствует в большинстве объектноориентированных языков. Как и в этих языках, в Transact SQL она реализована в виде конструкции TRY…CATCH. Рассмотрим на примере работу с этим блоком и дополнительными средствами обработки ошибок. CREATE TABLE dbo. Data. Table (Col. A int PRIMARY KEY, Col. B int) CREATE TABLE dbo. Error. Log (Col. A int, Col. B int, error int, datetime) GO EXEC dbo. Add. Data 1, 1 CREATE PROCEDURE dbo. Add. Data @a int, @b int EXEC dbo. Add. Data 2, 2 AS EXEC dbo. Add. Data 1, 3 SET XACT_ABORT ON GO BEGIN TRY SELECT * FROM dbo. Error. Log BEGIN TRAN Пример 9. Обработка структурированных исключений INSERT INTO dbo. Data. Table VALUES (@a, @b) COMMIT TRAN END TRY BEGIN CATCH DECLARE @err int SET @err = @@error ROLLBACK TRAN INSERT INTO dbo. Error. Log VALUES (@a, @b, @err, GETDATE()) END CATCH GO
Скачать презентацию Microsoft SQL Server система управления реляционными базами
ПрИС Лекция 13.ppt