Главная » Microsoft SQL Server, Базы данных » Создание пользовательских столбцов данных

0

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

Таблицы SQL Server могут содержать до 1024 столбцов; в то же время считается, что в таблице хорошо сформированной реляционной базы данных их число не должно превышать двадцати пяти.

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

CREATE TABLE имя_та блицы ( имя_столбца тип_данных атрибуты, имя_столбца тип_данных атрибуты ) ;

Столбцы данных могут добавляться и в существующие таблицы с помощью инструкции ALTER TABLE ADD COLUMN:

ALTER TABLE имя_та блицы

ADD имя_столбца тип_данных атрибуты;

Существующие столбцы могут быть изменены с помощью инструкции ALTER TAQBLE ALTER COLUMN:

ALTER TABLE имя_таблицы ALTER COLUMN имя_столбца

новый_тип_данных а трибуты;

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

Типы данных столбцов

Тип данных столбца служит для двух целей.

?               Он обеспечивает первый уровень целостности данных. Символьные данные не могут быть занесены в столбцы типов даты-времени и числового типа. На практике мне встречались базы данных, все столбцы которых имели тип nvarchar для облегчения ввода данных. Однако такой подход нельзя назвать правильным. Тип данных играет важную роль как инструмент проверки допустимости данных, и его лучше не обходить вниманием.

?               Он определяет объем дискового пространства, выделяемого для столбца.

Символьные типы данных

SQL Server поддерживает несколько символьных типов данных (табл. 17.2).

Типы данных, использующие таблицу Unicode, особенно полезны для хранения текста, написанного на нескольких языках. Однако за это приходится расплачиваться удвоением занимаемого пространства. Некоторые разработчики используют для всех своих символьных столбцов тип nvarchar, в то время как другие не хотят его использовать ни за какие деньги. Лично я советую использовать данные Unicode только для текста на иностранных языках; в остальных случаях лучше использовать типы char, varchar и text.

Таблица 17.2. Символьные типы данных
Тип данных Описание Размер в байтах
Char (п) Символьные данные фиксированной длины. Могут содержать до 8000 символов и использовать принятый по умолчанию порядок и набор символов Заданная длина, умноженная на 1 байт
Nchar(п) Символьные данные фиксированной длины в таблице Unicode Заданная длина, умноженная на 2 байта
Varchar(п) Символьные данные переменной длины. Могут содержать до 8000 символов и использовать принятый по умолчанию порядок и набор символов По 1 байту на символ
varchar(max) Символьные данные переменной длины. Могут содержать до 2 Гбайт информации и использовать принятый по умолчанию порядок и набор символов По 1 байту на символ
nvarchar(n) Символьные данные переменной длины, хранящие до 8000 символов при использовании порядка, принятого по умолчанию По 2 байта на символ
nvarchar(max) Символьные данные переменной длины, хранящие до 2 Гбайт. Используется таблица Unicode и порядок, принятый по умолчанию По 2 байта на символ
text Символьные данные переменной длины, содержащие до 2147483647 символов По 1 байту на символ
ntext Символьные данные в таблице Unicode, содержащие до 1073741823 символов По 2 байта на символ
sysname Пользовательский тип данных, используемый для имен таблиц и столбцов, — эквивалент типа nvarchar (128) По 2 байта на символ

Числовые типы данных

SQL Server поддерживает несколько числовых типов данных (табл. 17.3).

Таблица 17.3. Числовые типы данных

Тип данных Описание Размер в байтах
bit 1 или 0 1 бит
tinyint Целые числа от 0 до 255 1 байт
smallint Целые числа от -32768 до 32767 2 байта
int Целые числа от -2147483648 до 2147483647 4 байта
bigint Целые числа от -2Л63 до 2~63 8 байтов
decimal ИЛИ numeric Числа с фиксированной точностью до 10″38+1 Варьируется в зависимости от длины
money Числа от -2~63 до 2~63 с точностью до одной десятитысячной 8 байтов
Тип данных Описание Размер в байтах
smallmoney Числа от -214748,3648 до 214748,3647 с точностью до одной десятитысячной 4 байта
float Числа с плавающей запятой от -1.79Е+308 4 байта или 8 байтов в зависимости от
до 1.79Е+308 точности
real Числа с плавающей запятой с точностью до 24 знаков 4 байта

При работе с денежными значениями особо тщательно выбирайте тип данных. Использование для них типов float и real может привести к ошибкам округления. Типы данных money и smallmoney имеют фиксированную точность в четыре знака (т.е. до одной сотой копейки). Для некоторых монетарных значений клиенту может понадобиться точность до одной копейки. В этом случае более подходящим окажется тип decimal.

Типы данных даты-времени

SQL Server хранит в одном столбце дату и время и использует для этого типы данных date time и smalldatetime (табл. 17.4). Основным раличием между этими двумя типами является точность и учет столетия. Если в столбце должна храниться только дата и при этом не должен учитываться период до XX века, то вполне подойдет тип small date time. Если требуется дополнительно хранить значение времени, точности типа smalldatetime может не хватить.

Таблица 17.4. Типы данных даты-времени
Тип данных Описание Размер в байтах
datetime

smalldatetime

Значения даты и времени от 1 января 1753 года до 31 декабря 9999 года с точностью до трех миллисекунд

Значения даты и времени с 1 января 1900 года до 6 июня 2079 года с точностью до одной минуты

8 байтов 4 байта

Юлианский календарь вступил в силу с 1 января 1753 года. Так как СУБД SQL Server не хочет разбираться, какие нации и религии и как использовали даты до 1753 года, она вообще исключает их из рассмотрения. Хотя в общем случае это не составляет особой проблемы, некоторые исторические и генеалогические базы данных требуют использования и более ранних дат. В качестве обходного маневра я рекомендую использовать для таких данных столбец на основе типа char, применяя при этом триггер или хранимую процедуру для проверки форматирования и допустимости даты при вводе.

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

Прочие типы данных

Прочие типы данных перечислены и описаны в табл. 17.5. Они способны покрыть потребности двоичных объектов и вариантных данных.

Таблица 17.5. Прочие типы данных

Тип данных Описание Размер в байтах
timestamp Уникальное в пределах базы данных случайное значение, генерируемое при каждом обновлении 8 байтов
uniqueidentifier Генерируемое системой 16-байтовое значение 16 байтов
binary(n) Данные фиксированной длины вплоть до 8000 байтов Определяется длиной
binary(max) Данные фиксированной длины вплоть до 8000 байтов Определяется длиной
varbinary Двоичные данные переменной длины вплоть до 8000 Число используемых
байтов байтов
image Двоичные данные переменной длины вплоть до Число используемых
2147483647 байтов байтов
sql_variant Может хранить любые типы данных длиной до 2147483647 байтов

Тип данных timestamp, ранее известный как rowversion, может пригодиться для поиска потерянных обновлений (подробно о нем речь пойдет в главе 51). Тип uniqueidentif ier пригодится для создания первичных ключей, особенно в реплицируемых базах данных. Более подробно об этом типе см. выше, в разделе “Создание первичных ключей”.

Вычисляемые столбцы

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

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

Синтаксис вычисляемых столбцов противоположный присвоению псевдонима: Имя_столбца AS выражение

Таблица OrderDetail учебной базы данных OBXKites содержит вычисляемый столбец для расширенной цены, что и продемонстрировано далее в ее определении:

CREATE TABLE dbo.OrderDetail (

Quantity NUMERIC(7,2) NOT NULL,

UnitPrice MONEY NOT NULL,

ExtendedPrice AS Quantity * UnitPrice Persisted,

)

ON [Primary];

Go

В версии SQL Server 2005 вычисляемые столбцы могут оставаться на диске. Новинка ^ ^то значительно повышает производительность базы данных при выполнении 2005  поиска.

Ограничения и значения столбцов по умолчанию

База данных всегда настолько хороша, насколько хороши ее данные. Ограничения представляют собой высокоскоростную проверку допустимости значений или правил бизнес- логики, выполняемую на уровне ядра базы данных. Кроме проверки типа данных, SQL Server содержит пять типов ограничений.

?               Ограничения первичного ключа. Гарантируют уникальность первичного ключа и отсутствие в нем пустых значений.

?               Ограничения внешнего ключа. Гарантируют указание значения на допустимый ключ.

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

?               Ограничения проверки. Эти булевы ограничения задаются пользователем.

?               Ограничения на уникальность. Гарантируют уникальность значений.

SQL Server также предлагает использование параметров столбцов.

?               Значение по умолчанию. Если инструкция INSERT не вставляет в столбец никакого значения, в него подставляется значение по умолчанию.

Значения столбца, заданные по умолчанию, на одной из страниц Books Online рассматриваются как один из типов ограничений, однако на другой странице в списке ограничений они не упомянуты. Я считаю это свойством столбца, так как на самом деле никаких проверок ввода данных пользователем не проводится; также не форсируется поддержание целостности данных. В то же время значения, заданные по умолчанию, являются ценным параметром столбца.

Допустимость пустых значений

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

Дополнительная О том, как определять, выявлять и обрабатывать пустые значения, см. в главе 8.

информация.

Допустимы ли в столбце пустые значения, определяется с помощью атрибута столбца NULL или NOT NULL.

По умолчанию в новых столбцах SQL Server предполагает недопустимость пустых значений, однако этот режим работы можно изменить с помощью свойства подключения ansi_ null_df lt_on. Стандарт ANSI по умолчанию предполагает допустимость пустых значений, если, конечно, в определении столбца не было явно указано NOT NULL.

Так как по умолчанию SQL Server и стандарт ANSI предполагают противоположные режимы допустимости пустых значений, лучше не полагаться на случай и при определении столбца явно указывать параметр null или not null.

В следующем примере продемонстрированы режимы работы с пустыми значениями ANSI и SQL Server, принятые по умолчанию. В первом примере демонстрируется работа SQL Server. Вначале общий параметр ansi null устанавливается в ложное значение false, а параметр подключения ansi null dflt off — в значение on:

USE TempDB;

EXEC sp_dboption ‘TempDB’# ANSI_NULL_DEFAULT, ‘false';

SET ANSI_NULL_DFLT_OFF ON;

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

CREATE TABLE NullTest(

PK INT IDENTITY,

One VARCHAR(50)

) ;

Затем мы пытаемся вставить в таблицу пустое значение:

INSERT NullTest(One)

VALUES (NULL);

и в результате получаем ошибку:

Server: Msg 515, Level 16, State 2, Line 1 Cannot insert the value NULL into column ’One1, table ‘TempDB.dbo.NullTest'; column does not allow nulls. INSERT fails.

The statement has been terminated.

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

EXEC sp_dboption ‘TempDB’, ANSI_NULL_DEFAULT, ‘true';

SET ANSI_NULL_DFLT_ON ON;

DROP TABLE NullTest;

CREATE TABLE NullTest(

PK INT IDENTITY,

One VARCHAR(50)

) ;

Теперь мы пытаемся вставить в нее пустое значение:

INSERT NullTest(One)

VALUES (NULL);

и получаем положительный результат:

(1 row(s) affected)

Ограничения на уникальность

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

В утилите Management Studio ограничение на уникальность устанавливается во вкладке Index диалогового окна свойств таблицы. Этот процесс идентичен установке индекса, но с одним отличием: в этом случае вместо индекса выбирается ограничение.

В программном коде ограничение на уникальность можно установить, установив в определении столбца параметр UNIQUE. Приведем пример:

CREATE TABLE Employee (

EmployeeID INT PRIMARY KEY NONCLUSTERED,

EmployeeNumber CHAR(8)UNIQUE,

LastName NVARCHAR(35),

FirstName NVARCHAR(35)

) ;

Insert Employee (EmployeelD, EmployeeNumber, LastName, FirstName)

Values( 1, ‘1’, 1 Wilson1, ‘Bob’);

Insert Employee (EmployeelD, EmployeeNumber, LastName, FirstName); Values( 2, ‘l1, ‘Smith1, ‘Joe’);

В результате выполнения этого пакета инструкций будет получена ошибка:

Server: Msg 2627, Level 14, State 2, Line 1

Violation of UNIQUE KEY constraint ‘UQ______ Employee____ 68487DD7′.

Cannot insert duplicate key in object ‘Employee’.

The statement has been terminated.

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

ALTER TABLE Employee

ADD CONSTRAINT EmpNumUnique UNIQUE (EmployeeNumber);

Ограничения проверки

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

Дополнительная Ограничения проверки могут содержать пользовательские скалярные функции информация (о них речь пойдет в главе 22), которые могут выполнять последовательность _ ,          инструкций Т-SQL. В результате вызов такой функции в ограничении проверки

открывает почти неограниченные возможности, в том числе и возможность сложного поиска. В то же время сложные проверки правил бизнес-логики чаще всего выполняются в триггерах after.

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

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

В следующем примере ограничение обеспечивает превышение табельным номером сотрудника (поле EmployeeNumber) значения 1:

Drop Table Employee CREATE TABLE Employee (

EmployeelD INT PRIMARY KEY NONCLUSTERED,

EmployeeNumber CHAR(8) CHECK (EmployeeNumber > ‘1’),

LastName NVARCHAR(35),

FirstName NVARCHAR(35)

) ;

Insert Employee (EmployeelD, EmployeeNumber, LastName, FirstName)

Values( 2, ‘1’, ‘Smith’, ‘Joe’);

Выполнение этого пакета инструкций приведет к ошибке:

Server: Msg 547, Level 16, State 1, Line 1

INSERT statement conflicted with COLUMN CHECK constraint

‘ CK___ Employee Employ      5FB337D6′ .

The conflict occurred in database ’tempdb1, table ‘Employee1, column 1 EmployeeNumber’ .

The statement has been terminated.

Для добавления ограничения проверки в существующую таблицу используют инструкцию ALTER TABLE:

ALTER TABLE Employee

ADD CONSTRAINT NoHireSmith CHECK (Lastname <> ‘SMITH’);

Значения по умолчанию

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

?               Допустимое статическое числовое или символьное значение, такое как 123 или local.

О Скалярная системная функция, такая как GetDate () или NewID ().

?               Скалярная функция, определенная пользователем.

?               Пустое значение.

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

Если таблица создается в Management Studio, то значение по умолчанию определяется как одно из свойств столбца.

В программном коде значение по умолчанию добавляется как один из параметров определения столбца при создании таблицы или уже впоследствии, с помощью инструкции ALTER TABLE CREATE CONSTRAINT.

Следующий пример (с сокращениями) взят из определения таблицы Product учебной базы данных OBXKites. Значением столбца по умолчанию ActiveDate является текущая дата: CREATE TABLE dbo.Product (

ActiveDate DATETIME NOT NULL DEFAULT GETDATE(),

* * ) ;

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

Результат будет следующим: constraint_type    constraint_name

DEFAULT on column ActiveDate

DF___________________ Product___ ActiveD__ 7F6 0ED5 9

Теперь инструкция ALTER TABLE удаляет существующее значение по умолчанию:

ALTER TABLE Product

DROP CONSTRAINT DF Product ActiveD 7F60ED59

после чего мы снова устанавливаем его:

ALTER TABLE Product

ADD CONSTRAINT ActiveDefault DEFAULT GetDateO FOR ActiveDate;

Каталог данных

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

?               Определены тип данных и длина.

?               Определена допустимость пустых значений.

?               Предопределены правила, которые могут быть применены к данному типу данных.

?               Предопределены значения по умолчанию, применимые к типу данных.

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

Правила, определяемые пользователем

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

В следующем примере продемонстрировано создание правила, которое проверяет дни рождения и гарантирует, что все они в прошлом:

— Определяемое пользователем правило

CREATE RULE BirthdateRule AS @Birthdate <= Getdate();

Чтобы применить это правило к столбцу таблицы или пользовательскому типу данных, используется хранимая процедура sp_bindrule. Первым аргументом этой процедуры является имя правила, а вторым — объект, к которому оно применяется. В следующем примере вышеописанное правило BirthdayRule применяется к столбцу BirthDate таблицы Person:

EXEC sp_bindrule

@rulename = 1BirthdateRule’,

@objname = 1 Person.Birthdate';

Правила рассматриваются как средство обратной совместимости версий SQL Server. Их использование не рекомендовано компанией Microsoft. На смену им пришли ограничения проверки, помещаемые непосредственно в столбцы. Правила можно рассматривать скорее с точки зрения практики программирования, а не использования. Если в вашей базе данных пока еще остались правила, то замените их ограничениями проверки.

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

Значения по умолчанию, определяемые пользователем

Значения по умолчанию легко создать непосредственно в определении таблицы, хотя, подобно правилам, они продолжают существование исключительно из соображений обратной совместимости. Объекты значений по умолчанию представляют собой именованные значения, которые могут быть применены ко множеству таблиц. Значения по умолчанию могут создаваться и применяться к столбцам в Management Studio в узле Defaults каждой из баз данных.

В следующем примере создается значение по умолчанию, равное текущей дате, после чего оно применяется к столбцу Hi redate:

CREATE DEFAULT HireDefault AS GetdateО go

sp_bindefault ‘HireDefault1, ‘Contact.Hiredate';

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

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

Тип данных SysName разработан компанией Microsoft специально для хранения системных имен (таблиц, столбцов) в столбцах.

В утилите Management Studio пользовательские типы данных создаются в узле User-Defined Data Туре каждой из баз данных. Для их создания также может использоваться системная хранимая процедура sp_addtype, которой в качестве аргументов передаются имя, тип данных и параметр допустимости пустых значений. В следующем примере создается пользовательский тип данных, к которому применяются значение по умолчанию и правило, а затем этот тип данных используется при создании таблицы:

EXEC sp_addtype

@typename = Birthdate,

@phystype = SmallDateTime,

@nulltype = ‘NOT NULL';

go

EXEC sp_bindefault

@defname = ‘BirthdateDefault’,

@objname = ‘Birthdate’,

@futureonly = ‘futureonly';

EXEC sp_bindrule

@rulename = ‘BirthdateRule’,

@objname = ‘Person.Birthdate';

Источник: Нильсен, Пол. Microsoft SQL Server 2005. Библия пользователя. : Пер. с англ. — М. : ООО “И.Д. Вильямс”, 2008. — 1232 с. : ил. — Парал. тит. англ.

По теме:

  • Комментарии