Разработка и сопровождение баз данных в MS SQL Server 2000

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

      3.4.3 Создание представлений 

      Представление (View) для пользователей баз данных выглядит как таблица, но при этом оно не содержит данных, а лишь представляет данные, расположенные в одной или нескольких таблицах. Подобно реальным таблицам представления содержат именованные столбцы и строки с данными, которые они динамически выбирают из таблиц и предлагают эти данные пользователю для просмотра. Представления часто применяются для ограничения доступа к конфиденциальным данным в таблицах баз данных. Когда в представление не включается столбец исходной таблицы, то считают, что на таблицу наложен вертикальный фильтр. Если в SQL – запросе установлено одно или несколько условий для выборки строк, то считают, что на таблицу наложен горизонтальный фильтр.

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

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

• представление должно содержать, как минимум, одну таблицу в параметре FROM команды SELECT;

• не разрешается использование функций агрегирования и др.

      Как и для таблиц, для представлений  можно определить следующие права доступа:

• SELECT – просмотр данных;
• INSERT – добавление данных через представления;
• UPDATE – изменение данных в исходных таблицах;
• DELETE –удаление данных в исходных таблицах.

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

      CREATE VIEW [Имя базы данных.] [имя владельца.]

      Имя представления

      [(Имя  колонки [,... n])]

      [WITH{ENCRYPITIONSHEMABINDING

      VIEW_METADATA}

      AS Команда SELECT

      [WITH CHECK OPTION]

      Если  в команде не заданы имена колонок представления, то они определяются по именам выбираемых колонок в команде SELECT. Параметр ENCRYPTION скрывает код создания этого представления, а параметр SHEMABINDING обеспечивает контроль структуры исходных объектов, к которым обращается оператор SELECT. Опция WITH CHEC OPTION не позволяет изменять строки таким образом, чтобы они исчезли при отборе командой SELECT.

      Наиболее  полная информация по созданию и удалению баз данных приводится в [1, 3, 5, 6, 9] 

      3.5 Создание и управление индексами 

      Создание  индекса командами языка Transact – SQL производится следующим

образом:

• автоматически при создании первичного ключа, когда создается кластерный индекс (если не указан параметр NONCLUSTERED);
• автоматически при реализации ограничения целостности UNIQUE, когда создается не кластерный индекс;
• автоматически при создании таблицы, когда для столбца указываются параметры CLUSTERED или NONCLUSTERED;
• с помощью специальной команды CREATE INDEX.

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

      Формат  команды для явного создания индекса  следующий:

      CREATE [UNIQVE] [CLUSTEREDNONCLUSTERED] INDEX

      Имя индекса

      ON {Имя индексаИмя представления}

      (column[ASCDESC] [,…n])

      [WITH [PAD_INDEX]

      [[,] FILLFACTOR = Фактор заполнения]

      [[,] IGNOR_DUP_KEY]

      [[,] DROP_EXISTING]

      [[,] STATISTICS_NORECOMPUTE]

      [[,] SORT_IN_TEMP_DB]

      ]

      [ON Имя группы файлов]

      Если  автоматическое создание кластерного индекса не предполагается, то перед созданием не кластерного индекса надо создать кластерный, так как некластерный индекс всегда ссылается на кластерный. Можно создать 249 некластерных индексов с использованием до 16 столбцов в каждом индекс, при этом общая длина индекса не должна превышать 900 байтов. Столбцы с типами данных text, ntext или image в индексах не допускаются. Порядок столбцов при определении ключа очень важен. Желательно их указывать в порядке возрастания длины данных. Параметры ASC и DESC определяют метод сортировки ключевых элементов – соответственно по возрастанию или по убыванию.

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

      Параметр IGNORE_DUP_KEY не приводит к отказу транзакции при добавлении дублирующих строк, при этом сами дублирующие строки игнорируются, и сервером выдается сообщение об ошибке. Остальные параметры команды используются редко. Созданный тем или иным способом индекс, можно переименовать с помощью системной хранимой процедуры sp_rename, можно его удалить командой DROP INDEX или перестроить для упорядочивания свободного места на индексных страницах, используя команды DROP INDEX и CREATE INDEX или команду DBCC DBREINDEX. Для получения информации об индексах используется системная хранимая процедура:

      sp_helpindex [@objname] ‘name’, где name – имя рассматриваемой таблицы текущей базы данных.

      Для просмотра индивидуальных свойств  конкретного индекса следует применять команду:

      INDEXPROPERTY (table_ID, index, property), в которой table_ID = OBJECT_ID (имя таблицы) – идентификационный номер таблицы, index – имя индекса, а property – рассматриваемое свойство: Index Depth (глубина индекса), Is Clustered (кластерный), Is Unique (уникальный) и др.

      Для сбора и анализа статических  данных при использовании индексов используются следующие команды и процедуры: CREATE STATISTICS, UPDATE STATISTICS, sp_autostats, sp_statistics и др. 

      3.6 Вставка, удаление и изменение данных 

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

      SQL Server 2000 предлагает несколько различных  механизмов управления данными. Например, вставка данных может выполняться не только средствами Transact-SQL, но и с помощью утилиты bср.ехе или служб трансформации данных (DTS, Data Transformation Services).

      Для добавления данных в языке Transact-SQL используются команды INSERT и SELECT INTO, для изменения данных – команда UPDATE и для удаления строк из таблиц – команда DELETE.

      Команда INSERT позволяет вставить в таблицу одну или несколько строк. Упрощенный синтаксис этой команды таков:

      INSERT [INTO] имя модифицируемой таблицы

      [WITH (уровень блокировки запроса)]

      {[(список  колонок модифицируемой таблицы)]

      {VALUES (список значений новой строки)

      команда SELECT}}

      DEFAULT VALUES

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

      SET IDENTITY_INSERT имя модифицируемой таблицы ON

      Если  список столбцов не задан, то сервер будет  вставлять данные последовательно во все столбцы, начиная с первого. Для каждого столбца должен быть указан аргумент, имеющий соответствующий тип. Аргументами могут быть константы, выражения соответствующего типа, значение NULL и значение по умолчанию DEFAULT. В списке столбцов можно не указывать столбцы со свойством IDENTITY, столбцы, допускающие значение NULL и столбцы типа timestamp.

      Если  в команде задан источник данных DEFAULT VALUES, то строка будет содержать только значения по умолчанию или значения NULL.

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

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

      SELECT список выбираемых колонок исходных таблиц

      INTO имя автоматически создаваемой  таблицы

      FROM список исходных таблиц

      [условия  выбора значений из таблиц]

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

      Для любой базы данных использование  команды SELECT… INTO запрещено. Для установки разрешения на ее использование необходимо выполнить команду:

      EXES sp_dboption ‘имя базы данных’, ‘select into/bulkcopy’, ‘on’

      Изменение данных в таблицах или задание значений переменным производится командой UPDATE:

      UPDATE имя таблицы или представления  WITH блокировка

      SET имя колонки или переменной = выражение…

      FROM имена исходных таблиц

      WHERE условия поиска

      Удаление  данных из таблиц производится командой DELETE:

      DELETE FROM имя таблицы или представления

      или

      DELETE FROM имя таблицы

      WHERE условие поиска OPTION (уровни блокировки).