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

Tsql теория > Создание пользовательских столбцов
26.02.2013 11:37:57



Статья:

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

Таблицы 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 ().

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

Вычисляемые столбцы имеют в таблицах особую ценность, поскольку содержат результат предопределенного выражения так же, как и представления (т.е. сохраненные инструкции 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 'TempDB1, ANSI_NULL_DEFAULT, 'false1;

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;