Создание таблиц баз данных

Как правильно выбрать тип данных

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

Выбор подходящего типа данных является довольно понятным процессом. Данные, которые будут вводиться в колонку, должны соответствовать типу данных, заданному для этой колонки. Поэтому вам следует выбрать такой тип данных, который лучше всего охватит диапазон значений, которые могли бы храниться в колонке для всего срока эксплуатации вашего приложения, и в то же время стремясь ограничить ненужный расход места на диске. Ненужный расход места на диске – это место на диске, выделенное для элементов данных, хранящихся в колонке, но не используемое. Например, предположим, у вас есть колонка, в которой нужно хранить одно целое число в диапазоне от 1 до 100. Очевидно, что эти значения могут храниться в типе integer, но каждое целое число типа integer занимает 4 байта. Данные с типом tinyint могут хранить значения от 0 до 255 и для них нужен только 1 байт места. В данном случае tinyint будет наилучшим выбором, потому что это экономит место на диске, необходимое для хранения данных из таблиц.

Затем вы должны определить, какой тип данных следует использовать – с фиксированной либо с переменной длиной. Если все значения данных из колонки будут иметь приблизительно одинаковые размеры, то более эффективным станет применение типа данных с фиксированной длиной, так как обработка данных, имеющих типы переменной длины, вызывает повышенную нагрузку. Вообще говоря, типы данных с переменной длиной следует применять, только если вы предполагаете значительные различия в длине данных, хранимых в данной колонке, и когда данные из колонки обновляются редко. К данным с переменной длиной относятся varchar, nvarchar, varbinary, text, ntext и image.Применение типов данных с переменной длиной может привести к значительной экономии места для хранения данных. Например, если вы зададите тип данных с фиксированной длиной, достаточной для хранения значений в колонке с наибольшей возможной длиной, то для всех значений, занимающих меньше места, истратиться столько же места, как и для этого самого объемного значения. Результатом этого станут огромный ненужный расход места на диске, потому что лишь немногие строки будут расходовать все максимально допустимое место для данных. В большинстве строк будет использовано меньше места, а все неиспользуемое место станет потерянным. А если вы зададите, что будет применяться тип данных с переменной длиной, то короткие значения будут расходовать лишь то место, которое им действительно нужно. Но опять-таки, применение типов данных с переменной длиной повышает нагрузку на процессор. Поэтому, если вам не требуется тип данных с переменной длиной, то применяйте тип данных с фиксированной длиной. Если из соображений расходования места для хранения данных нужно применять тип данных с переменной длиной, то, конечно, применяйте его.

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

Создание таблицы Product_Info с использованием системных типов данных

Прежде чем продолжить наше изложение, давайте рассмотрим T-SQL-оператор CREATE TABLE, которую можно применить для создания таблицы Product_Info, показанной в этой лекции ранее в табл. 10.1. В этом примере мы применим только системные типы данных и колонки с фиксированной длиной. Когда вы создаете таблицу при помощи оператора T-SQL, она создается в базе данных, которой вы пользуетесь в текущий момент. Если вы хотите создать таблицу в какой-либо конкретной базе данных, то надо применить оператор USE имя_базы_данных, как показано в примере кода (в нашем примере база данных имеет имя MyDB). Ключевое слово GO означает, что все предыдущие операторы должны быть теперь выполнены. (Об использовании T-SQL см. лекцию 13.)

USE MyDB 
GO 
CREATE TABLE Product_Info 
( 
Product_ID    smallint,
Product_Name    char(20), 
Description  char(30), 
Price   smallmoney, 
Brand_ID	smallint 
) 
GO

Давайте посмотрим, что произойдет при исполнении этого кода. После оператора CREATE TABLE следует спецификация таблицы с именем Product_Info. Между открывающей и закрывающей скобками заданы имена колонок и их типы данных. Длины для двух типов данных заданы равными 20 и 30, потому что большинство названий продуктов уместятся в 20 символов, а большинство описаний продуктов уместятся в 30 символов. Для Product_ID и Brand_ID применяется тип данных smallint, а не tinyint, потому что мы предполагаем, что количество разновидностей как продуктов, так и торговых марок, превысит 255 (максимальное значение для типа tinyint ), хотя и будет меньше, чем 32767 (максимальное значение для типа smallint ). Так как значения больше, чем 32767, нам не понадобятся, то при использовании типа int мы напрасно тратили бы место на диске.

Работа с пользовательскими типами данных

Пользовательские типы данных (типы данных, задаваемые пользователями, user-defined data types) или, как их еще называют, "псевдонимные", "алиасные" типы данных (alias data types)являются индивидуально настроенными системными типами данных. Индивидуальная настройка типов данных (задание типов данных) полезна, когда вы имеете несколько таблиц, в колонках которых должны храниться данные одинаковых типов, а вы хотите гарантировать точное соответствие колонок каждой из таблиц по типу, длине и возможности применения null-значений. Вы можете создать тип данных с осмысленным именем, что упростит программирование и улучшит согласованность таблиц. Затем вы сможете применять этот тип данных при создании многих других таблиц.

Например, предположим, что у нас имеется еще одна таблица, с именем Brands (торговые марки) в той же базе данных, в которой находится таблица Product_Info. Пусть в таблице Brands имеется колонка Brand_ID, соответствующая колонке Brand_ID таблицы Product_Info. Пусть в таблице Brands хранятся названия торговых марок и другая информация, относящаяся к торговым маркам. Чтобы гарантировать, что колонки Brand_ID в обеих таблицах имеют одинаковый тип данных и не допускают null -значений, вы можете создать пользовательский тип данных и присвоить его обеим колонкам. А теперь представьте, что у вас имеется несколько таблиц с колонками, которые должны иметь одинаковые атрибуты. Вы можете не помнить, применяли ли вы в одной таблице тип smallint, или же tinyint, или было ли позволено применять null -значения. Если создать тип данных с понятными именем, то вам не придется беспокоиться о правильности применяемых атрибутов, и вы получите гарантию согласованности данных для всех таблиц.

Создание пользовательских типов данных при помощи Enterprise Manager

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

Как только вы определитесь с этим, можно будет создать тип данных. Для создания пользовательского типа данных с помощью Enterprise Manager выполните следующие действия.

Удаление пользовательских типов данных при помощи Enterprise Manager

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

Находясь в Enterprise Manager, выберите пользовательский тип данных, который нужно удалить (раскройте группу SQL Server, раскройте сервер, раскройте папку Databases, а затем раскройте базу данных, в которой содержится удаляемый тип данных).

Нажмите на папку User Defined Data Types. В правой панели станут видны пользовательские типы данных, имеющиеся в базе данных (рис. 10.3).

Нажмите правой кнопкой мыши на пользовательский тип данных, который вы хотите удалить, и выберите Delete (Удалить) в появившемся контекстном меню. Появится диалоговое окно Drop Objects (Удалить объекты) (рис. 10.4).

Прежде чем удалить тип данных, нажмите на Show Dependencies (Показать зависимости), в результате чего появится диалоговое окно Dependencies (Зависимости) (рис. 10.5).

В поле-списке в левой части диалогового окна Dependencies показаны объекты базы данных, которые зависят от вашего пользовательского типа данных, а в поле-списке в правой части диалогового окна показаны объекты, от которых зависит ваш пользовательский тип данных. Если этот тип данных используется в каких-либо таблицах или объектах (как тип данных в нашем примере), то вам не будет позволено удалить его – при попытке удаления (если вы перейдете к шагу 5) появится сообщение об ошибке (рис. 10.6).

(рис 10.4) Папка User Defined Data Types (Пользовательские типы данных)(рис 10.3) Диалоговое окно Drop Objects (Удалить объекты)

Если у вашего типа данных не возникает проблем с тем, что он где-то используется, то закройте диалоговое окно Dependencies, а затем нажмите на экранную кнопку Drop All (Удалить все) в диалоговом окне Drop Objects, чтобы удалить этот тип данных. Не волнуйтесь, удалены будут только типы данных, показанные в диалоговом окне Drop Objects, а не все ваши пользовательские типы данных.(рис 10.6) Диалоговое окно Dependencies (Зависимости)(рис 10.5) Сообщение об ошибке, появляющееся при попытке удалить тип данных, находящийся в использовании

Создание и удаление пользовательских типов данных при помощи T-SQL

Системная хранимая процедура sp_addtype является командой T-SQL, применяемой для создания пользовательских типов данных. При помощи запуска этой команды в то время, когда вы работаете с модельной базой данных (базой данных model), можно организовать применение нового типа данных во всех вновь создаваемых пользовательских базах данных, потому что эти базы данных создаются с такими же атрибутами, как и у базы данных model. Запуск этой команды при работе с пользовательской базой данных позволит применять новый тип данных только в этой базе данных. (Помните, что для работы с конкретной базой данных вы должны запускать команду USE имя_базы_данных.) Ниже дан пример команд T-SQL, создающих пользовательский тип данных brand_type в базе данных model:

USE model 
GO 
sp_addtype brand_type, 'smallint', 'NOT NULL' 
GO

Три параметра у sp_addtype – это имя пользовательского типа данных, системный тип данных, на котором основывается новый тип данных и возможность использования null -значений в новом типе данных. Новый тип данных, brand_type, появится во всех вновь создаваемых пользовательских базах данных. Если вы создадите пользовательский тип данных в пользовательской базе данных и захотите посмотреть новый тип данных через Enterprise Manager, то выберите команду Refresh в меню Action Enterprise Manager’а.

Чтобы удалить ненужный пользовательский тип данных, запустите команду sp_droptype в базе данных, в которой этот тип данных был определен. Ниже дан пример команд T-SQL, удаляющих пользовательский тип данных brand_type из базы данных model. (Напомним, что если вы создали этот тип данных в пользовательской базе данных и хотите, чтобы в Enterprise Manager было видно, что этот тип данных был удален, то выберите в Enterprise Manager команду Refresh в меню Action.)

USE model 
GO 
sp_droptype brand_type 
GO

Создание таблиц Product_Info и Brands с применением пользовательских типов данных

Давайте вернемся к нашему примеру базы данных. Мы создадим заново таблицу Product_Info, пользуясь новым пользовательским типом brand_type, а затем создадим таблицу Brands. Эта таблица, как и таблица Product_Info, будет иметь колонку Brand_ID и мы применим для этих колонок одинаковый пользовательский тип данных brand_type. Сначала нам надо будет удалить старую таблицу Product_Info, чтобы мы смогли бы создать ее снова. Ниже показан код, который выполнит все необходимые действия:

USE MyDB 
GO
DROP TABLE Product_Info
GO 
CREATE TABLE Product_Info 
( 
Product_ID     		smallint,
Product_Name   	char(20), 
Description    		char(30), 
Price          			smallmoney, 
Brand_ID       		brand_type 
) 
GO
CREATE TABLE Brands
(
Brand_ID       		brand_type,
Brand_Name     		char(30),
Supplier_ID    		smallint
)
GO

Задав одинаковый тип данных brand_type для колонок Brand_ID в обеих таблицах, мы гарантируем одинаковость атрибутов обеих колонок. Нам не надо помнить об особенностях типа данных, на котором основывается тип данных brand_type, мы можем просто применять новый тип данных для всех колонок Brand_ID.

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

Создание таблиц в заданной группе файлов

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

Создание таблицы Product_Info в заданной группе файлов

Предположим, что в созданной нами базе данных MyDB имеется группа файлов с именем product_group, содержащая один вторичный файл данных, который размещен на другом диске (E), чем диск, на котором размещена первичная группа файлов (С). Применяя эту методику, вы можете физически отделить свои таблицы данных от системных таблиц SQL Server. Еще мы создадим на отдельном диске (F) файл журнала, чтобы отделить ввод-вывод журнала. (Про создание баз данных и применение групп файлов см.лекцию 9.) Необходимые команды могут выглядеть так:

USE master 
GO 
CREATE DATABASE MyDB 
ON PRIMARY                         		-- Явное задание первичной 
                                   		-- группы файлов (не обязательно) 
(NAME = MyDBroot,               			-- Первичный файл данных
FILENAME = 'c:\mssql2k\MSSQL\data\mydbroot.mdf', 
SIZE = 8MB, 
MAXSIZE = 10MB, 
FILEGROWTH = 1MB),
FILEGROUP product_group        		-- Группа файлов для следующего файла
(NAME = MyDBdata1,                 		-- Вторичный файл данных
FILENAME = 'e:\mssql2k\MSSQL\data\mydbdata1.ndf', 
SIZE = 1000MB, 
MAXSIZE = 1500MB, 
FILEGROWTH = 100MB) 
LOG ON 
(NAME = Logdata1,                     			-- Файл журнала
FILENAME = 'f:\log_files\logdata1.ldf', 
SIZE = 1000MB, 
MAXSIZE = 1500MB,
FILEGROWTH = 100MB)
GO

А теперь мы можем создать таблицу Product_Info в группе файлов product_group, пользуясь командой CREATE TABLE, как показано ниже.

USE MyDB 
GO 
CREATE TABLE Product_Info 
( 
Product_ID     		smallint,
Product_Name   	char(20), 
Description    		char(30), 
Price          			smallmoney, 
Brand_ID       		brand_type 
) 
on product_group 
GO

Таблица и все данные, которые будут вставлены в таблицу, разместятся на диске E, диске, на котором была задана группа файлов product_group. Поэтому данные из таблицы Product_Info будут иметь свой дисковый накопитель, предназначенный для их ввода-вывода до тех пор, пока в этой же группе файлов не будут созданы другие таблицы.

Применение null-значений

Null-значение (null value) – это неизвестное значение, для которого применяется обозначение NULL. Способность хранить null-значения (nullability) – это свойство, благодаря которому колонка способна либо хранить null -значения, либо отвергать их. Null -значение в колонке обычно означает, что для данной строки этой колонки нет данных, потому что значение неизвестно, либо не имеет смысла, либо не задано или будет задано в будущем. Null -значения – это не пустые значения и не значения числа 0, их настоящие значения неизвестны (unknown), поэтому никакие два null -значения не являются равными.

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

Как правило, не следует применять null -значения. Из-за них запросы и обновления становятся более сложными, кроме того, к колонкам, способным хранить null -значения, нельзя применять некоторые настройки, такие как первичные ключи и свойство IDENTITY.

Прекрасной альтернативой применению null -значений в колонке является задание значения по умолчанию (default value) для этой колонки. Если значение не было задано при вводе строки, то в колонку записывается значение по умолчанию. (Более подробно о применении значений по умолчанию написано в лекции 16.) Если вы определите эту колонку, как способную хранить null -значения, т.е. два случая, когда колонка получит значение NULL:

Создание таблицы Product_Info с применением null-значений

Давайте вернемся к нашему примеру с таблицей Product_Info и зададим для каждой колонки возможность хранить null -значения. Если вы хотите, чтобы в колонке разрешалось хранить null -значения, то после типа данных надо добавить слово NULL. Если вы не хотите, чтобы разрешалось хранить null -значения, то после типа данных надо добавить слово NOT NULL. Мы рекомендуем всегда указывать, разрешается ли хранить в колонке null -значения, за исключением лишь случаев, когда применяются пользовательские типы данных (они уже заданы NULL или с NOT NULL ). Это поможет вам выработать привычку обращать внимание на необходимость способности к хранению null -значений в тех или иных колонках.

Пускай в нашем примере с таблицей Product_Info null -значения будет разрешено применять только в колонке для описаний продуктов. Мы не указали способность к хранению null-значений для типа данных brand_type, так как его способность к хранению null -значений уже была задана (как NOT NULL ) при создании этого пользовательского типа данных. Новый оператор CREATE TABLE будет выглядеть так:

USE MyDB 
GO
DROP TABLE Product_Info
GO 
CREATE TABLE Product_Info 
( 
Product_ID         		smallint NOT NULL,
Product_Name   	char(20) NOT NULL, 
Description         		char(30) NULL, 
Price                   smallmoney NOT NULL, 
Brand_ID            		brand_type 

) 
GO

Теперь, если описание продукта (Description) не будет задано, а значения остальных четырех полей – заданы, то в таблицу будет введена новая строка со значением NULL для элемента данных, находящегося в колонке Description. Вы должны будете ввести значения для четырех остальных колонок, не допускающих ввода значений NULL (колонок Product_ID, Product_Name, Price и Brand_ID ). Если данные для какой-либо из этих колонок не ввести, то попытка ввести новую строку будет неуспешной.

Добавление свойства IDENTITY

Когда вы создаете таблицу, вы можете задать одну из колонок как идентифицирующую колонку (identity column), добавив к определению колонки свойство IDENTITY. Если колонка создается со свойством IDENTITY, то SQL Server автоматически генерирует для этой колонки значение строки, рассчитываемое по начальному значению (seed value) и значению приращения (increment value). Начальное значение (seed) является значением идентификации для первой строки, вставленной в таблицу. Приращение (increment) – это величина, на которую SQL Server увеличивает значение идентификации для последовательно вводимых строк. Каждый раз при вводе строки SQL Server присваивает текущее значение идентификации элементу данных в колонке идентификации, вводимому в новую строку. Следующая введенная строка получит значение идентификации, большее, чем текущее максимальное значение идентификации на величину приращения. Таким образом, каждая вводимая строка получит уникальное значение идентификации. Свойство идентификации полезно для создания колонок, в которых каждая строка должна иметь уникальный идентификатор, например, для колонки Product_ID. Если вы разрешите SQL Server генерировать идентифицирующие значения для вводимых строк, то это окажется проще, чем следить за правильностью ввода последовательных значений. Идентифицирующие колонки обычно применяются в ограничениях первичного ключа в таблицах, благодаря которым возможна уникальная идентификация строк. (Про ограничения первичного ключа см. лекции 16.)

Например, если вы зададите IDENTITY(0, 10), то значение идентифицирующей колонки для первой введенной строки будет равно 0, для второй строки будет равно 10, для третьей строки – 20, и т.д. Если начальное значение или приращение не задать, то для них будут применяться значения по умолчанию, равные 1 и 1. Вы можете задать как оба этих параметра, так и один из них. Идентифицирующие колонки не могут содержать значения по умолчанию и для них не разрешено применение null -значений. В каждой из таблиц может иметься только одна идентифицирующая колонка.

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

SET IDENTITY_INSERT имя_таблицы ON

При помощи этого оператора можно вставить строку и назначить нужное вам значение идентифицирующей колонки. Закончив ввод строки, нужно отменить возможность вставки в идентифицирующую колонку при помощи такого оператора:

SET IDENTITY_INSERT имя_таблицы OFF

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

Добавление свойства IDENTITY для таблицы Product_Info

Давайте добавим свойство IDENTITY в таблицу Product_Info. Вместо того, чтобы вводить данные в колонку Product_ID , мы сделаем ее идентифицирующей колонкой, и пусть SQL Server автоматически генерирует ее значения, обеспечивая их уникальность. Ниже показан код T-SQL, который создаст такую таблицу:

USE MyDB 
GO 
DROP TABLE Product_Info
GO 
CREATE TABLE Product_Info 
( 
Product_ID         		smallint IDENTITY(1, 1) NOT NULL,
Product_Name   	char(20) NOT NULL, 
Description         		char(30) NULL, 
Price                   	smallmoney NOT NULL, 
Brand_ID            		brand_type 
 
) 
GO

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

Создание таблиц с помощью Enterprise Manager

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

Заключение

В этой лекции вы изучили основы создания таблиц, научились применять и задавать типы данных, размещать таблицы в группах файлов, применять null -значения и добавлять свойство IDENTITY. Желательно привести таблицы в их окончательную форму до того, как вы будете заполнять их данными. В лекции 15 показаны несколько способов для изменения таблиц при помощи T-SQL. В нескольких следующих лекциях вы узнаете об инсталляции и настройке сети и о Microsoft Cluster Server. Эти лекции помогут вам выполнить завершающие стадии настройки SQL Server.