Transact-SQL - создание различных объектов баз данных

Работа с базами данных в .NET Framework --- SQL Server 2012 --- Создание различных объектов баз данных

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

Представление на основе одной или нескольких существующих таблиц баз данных (или представлений) создается с помощью инструкции CREATE VIEW и инструкции SELECT, которая является неотъемлемой частью инструкции CREATE VIEW. Поскольку создание представления всегда содержит запрос, инструкция CREATE VIEW принадлежит к языку манипуляции данными (DML), а не к языку описания данных (DDL). По этой причине создание и удаление представлений рассматривается в одной из следующих статей, после представления инструкций Transact-SQL для модификации данных.

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

Еще одним дополнительным объектом базы являются хранимые процедуры (stored procedure), которые создаются посредством инструкции CREATE PROCEDURE. Хранимая процедура - это последовательность инструкций Transact-SQL, созданная посредством языка SQL и процедурных расширений.

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

В Database Engine применяется такое же понятие схемы, как и в стандарте ANSI SQL. В стандарте SQL схема определяется как коллекция объектов базы данных, имеющая одного владельца и формирующая одно пространство имен. Пространство имен (namespace) - это набор объектов с однозначными именами. Например, две таблицы могут иметь одно и то же имя только в том случае, если они находятся в разных схемах. Схема является очень важным концептом в модели безопасности компонента Database Engine.

Триггером (trigger) называется объект базы данных, который задает определенное действие в ответ на определенное событие. Это означает, что когда для предопределенной таблицы происходит определенное событие (модификации, вставка или удаление данных), компонент Database Engine автоматически запускает одно или несколько дополнительных действий. Работу с триггерами, схемами, хранимыми процедурами и индексами мы рассмотрим позже.

Синоним (synonym) - это локальный объект базы данных, который предоставляет связь между самим собой и другим объектом, управляемым одним и тем же или связанным сервером баз данных. Синонимы объектов создаются посредством инструкции CREATE SYNONYM, применение которой показано в примере ниже:

USE SampleDb;

CREATE SYNONYM tc
	FOR TestCustomer.Production.CustomerSet;

В этом примере создается синоним таблицы CustomerSet в схеме Production базы данных TestCustomer. Этот синоним можно потом использовать в инструкциях языка DML, таких как SELECT, INSERT, UPDATE и DELETE.

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

Ограничения для обеспечения целостности и домены

Домен (domain) - это набор всех возможных разрешенных значений, которые могут содержать столбцы таблицы. Почти во всех системах управления базами данных для определения таких возможных значений столбца используются такие типы данных, как INT, CHAR и DATE. Такого метода принудительного обеспечения "целостности домена" недостаточно, как можно увидеть в следующем примере.

Допустим в таблице person есть столбец zip, в котором указывается индекс города, в котором проживает данное лицо. Тип данных этого столбца можно определить как SMALLINT или CHAR(5). Определение типа данных столбца как SMALLINT будет неточным, потому что этот тип данных содержит все положительные и отрицательные целые числа в диапазоне от -2¹⁵ до 2¹⁵-1. Объявление с использованием типа данных CHAR(5) будет еще менее точным, поскольку в таком случае можно будет использовать все буквенно-цифровые и специальные символы. Поэтому для точного определения данных столбца индексов требуется диапазон положительных значений от 00601 и 99950.

Более точно целостность домена можно принудительно обеспечить с помощью ограничений CHECK (определяемые в инструкции CREATE TABLE или ALTER TABLE), благодаря их гибкости и тому, что они всегда принудительно применяются при вставке или модифицировании данных столбца.

Язык Transact-SQL поддерживает домены посредством создания псевдонимов типов данных с помощью инструкции CREATE TYPE. Рассмотрению псевдонимов типов данных и типов данных среды CLR посвящены следующие два раздела.

Псевдонимы типов данных

Псевдоним типа данных (alias data type) - это специальный вид типа данных, который определяется пользователем при использовании существующих базовых типов данных. Такой тип данных можно использовать в инструкции CREATE TABLE для определения одного или большего количества столбцов таблицы.

Для создания псевдонимного типа данных обычно применяется инструкция CREATE TYPE. Далее показан синтаксис этой инструкции для определения псевдонима типа данных:

CREATE TYPE [type_schema_name.] type_name
	{ [ FROM base_type [ ( precision [, scale] ) ] [ NULL | NOT NULL ] ]
	| [ EXTERNAL NAME assembly_name [ .class_name ] ]}


Соглашения по синтаксису

Использование инструкции CREATE TYPE для создания типа данных показано в примере ниже:

USE SampleDb;

CREATE TYPE Zip
	FROM SMALLINT NOT NULL;

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

USE SampleDb;

CREATE TABLE Customer (
	Id INT NOT NULL,
	Name VARCHAR(30) NOT NULL,
	/* ... */

	ZipCode Zip,
	CHECK (ZipCode BETWEEN 600 AND 99950)
);

Здесь тип данных столбца ZipCode таблицы Customer определяется псевдонимом типа данных Zip. Допустимые значения этого столбца требуется ограничить диапазоном целочисленных значений от 600 до 99950. Как можно видеть в примере, это ограничение можно наложить с помощью предложения CHECK.

Обычно компонент Database Engine неявно преобразовывает разные типы данных совместимых столбцов. Это также относится и к псевдонимным типам данных. Начиная с версии SQL Server 2008, стали поддерживаться определяемые пользователем табличные типы. В примере ниже показано создание такого типа с помощью инструкции CREATE TYPE.

USE SampleDb;

CREATE TYPE User_table_t AS TABLE (
	name VARCHAR(30), 
	salary DECIMAL(6,3)
);

Определяемый пользователем табличный тип данных User_table_t имеет два столбца: name и salary. Основное синтаксическое отличие табличных типов от псевдонимных состоит в наличии предложения AS TABLE, как это можно видеть в примере. Определяемые пользователем табличные типы обычно применяются с возвращающими табличные значения параметрами.

Типы данных CLR

Инструкцию CREATE TYPE можно также применить для создания определяемых пользователем типов данных с использованием .NET. В этом случае, реализация определяемого пользователем типа определяется в классе сборки в среде CLR. Это означает, что для реализации нового типа данных можно использовать один из языков .NET, такой как C# или Visual Basic.