Мартьянова А.Е. Базы данных и знаний. Учебное пособие

Подождите немного. Документ загружается.

Мартьянова А.Е. Базы данных и знаний 201

Если SQL Server был инсталлирован с поддержкой US_English

Language, значения типа datetime по умолчанию выводятся на экран в

следующем порядке: месяц, число, год. С помощью команды set datefor-

mat этот порядок можно изменить.

Дату можно вводить в следующих форматах:

• Sep 23 1949

• SEP 23 1949

• September 23 1949

• sep 1949 23

• 1949 sep 23

• 1949 23 sep

• 23 sep 1949

Числовой формат значений типа datetime позволяет в качестве раз-

делителя между различными блоками данных ставить слэш (/), дефис (-)

или точку (.).

В числовом формате разделитель ставится между месяцем, числом

и годом, которые вводятся в порядке, заданном командой set dateformat.

Если порядок ввода значений типа datetime нарушен, значения месяца,

дня или года будут неправильно интерпретированы и сохранены, а в

случае выхода любого из этих значений за

рамки допустимого диапазона

появится сообщение об ошибке.

Ниже приведены примеры ввода различных значений типа datetime

в числовом формате. Порядок ввода, определенный с помощью команды

set dateformat, следующий: месяц, число, год. SQL Server был инсталли-

рован с поддержкой US_English Language:

• 1 6/24/71

• 06/24/71

• 6-24-1971

• 6.24.1971

• 06.24.71

Последний из возможных форматов ввода даты и времени для типа

данных datetime представляет собой либо непрерывное значение, со-

стоящее из четырех, шести или восьми цифр, либо время без значения

даты. На этот формат команда set date format не влияет.

Если вы вводите непрерывное значение из шести или восьми цифр,

оно всегда интерпретируется как год, месяц, число. Значения месяца и

числа всегда интерпретируются как состоящие

из двух цифр каждое. Не-

прерывное значение из четырех цифр интерпретируется как год. По

Мартьянова А.Е. Базы данных и знаний 202

умолчанию устанавливаются текущий век, первый месяц года и первое

число этого месяца. Ниже приведены примеры возможных интерпрета-

ций введенных символов для типа datetime.

Введенные символы Эквивалентное представление

710624 June 24, 1971

19710624 June 24, 1971

1971 January 1, 1971

“ January 1, 1900 12:00 A.M.

При работе с датой и временем помните, что когда в качестве зна-

чения вы вводите в столбец пустую строку, а столбец определен для дан-

ных типа datetime, вы не получите значение NULL, как можно было бы

ожидать. Когда в качестве значения типа datetime для даты или времени

вы вводите два апострофа без каких-либо символов между ними, SQL

Server всегда сохраняет значение January 1, 1900 12:00 A.M.

Время должно вводиться в следующем порядке: часы, минуты, се-

кунды и миллисекунды. В качестве разделителя между блоками

времени

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

димые цифры как время, а не как дату. Чтобы указать время до или вре-

мя после полудня, нужно ввести соответственно AM или РМ в верхнем

или нижнем регистре. Этого не требуется, если вы используете регио-

нальные установки.

Перед заданием блока миллисекунд можно ставить точку

или

двоеточие, которые влияют на интерпретацию этого блока. Одно число,

идущее после точки, обозначает десятые доли секунды; два числа интер-

претируются как сотые доли секунды; три числа интерпретируются как

тысячные доли секунды. Двоеточие указывает, что последующие числа

должны быть интерпретированы как тысячные доли секунды. Приведем

различные интерпретации введенного значения времени

в значении типа

datetime.

Введенное значение времени Интерпретация

11:21 11 ч 21 мин после полудня

11:21:15:871 11 ч 21 мин 15 с и 871 мс после полудня

11:21:15.8 11 ч 21 мин 15, и 8 с после полудня

6am 6 ч после полудня

7 PM 7 ч до полудня

05:21:15:500AM 5 ч 21 мин 15 с 500 мс после полудня

202

Мартьянова А.Е. Базы данных и знаний 203

Тип smalldatetime. Это второй тип данных даты и времени, с по-

мощью которого можно определять структуры хранения данных, такие

как столбцы таблицы. В формате этого типа данных вы можете хранить

дату и время, начиная от 1/1/1900 нашей эры и заканчивая 6/6/2079 на-

шей эры.

Общий размер области хранения для типа данных smalldatetime за-

нимает

4 байта. SQL Server использует 2 байта для хранения числа дней

после базовой даты (1 января 1900 года). Время хранится в другой паре

байтов в виде количества минут после полудня. Точность для данных

типа smalldatetime равна одной минуте. В отличие от значений типа

datetime, значения типа smalldatetime имеют меньшие диапазон и точ-

ность.

Использование данных типа smalldatetime вместо datetime при хра

нении значений даты и времени позволяет получить двойную экономию

памяти.

Специальные типы данных

Перечисленные типы данных используются для хранения до 90%

всей информации. Но в некоторых случаях возникает необходимость в

более адаптированных решениях.

Например, вам может понадобиться хранить только такие данные,

которые представлены как «истина» или «ложь», «да» или «нет». По-

скольку это является стандартным бинарным условием, вероятно, имеет

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

пример: хранение набора данных, имеющего размер, превышающий 8

Кбайт – максимально допустимый размер для обыкновенного символь-

ного типа данных.

Поэтому помимо перечисленных основных, Transact-SQL содержит

набор специальных типов данных:

Тип bit. Используется для хранения информации, которая имеет

два возможных состояния: ноль или единица. Данные типа bit хранятся в

одном бите. Если в структуру хранения данных типа bit, такую как стол-

бец таблицы, ввести любое значение, отличное от нуля или единицы, то

сохранится единица. Тип данных bit не допускает использования значе-

ния NULL.

Хотя в документации SQL Server это напрямую не утверждается,

но тип

данных bit соответствует булевому типу в других языках про-

граммирования и баз данных.

С помощью одного байта можно задать до восьми столбцов табли-

цы, определенных для данных типа bit. Пространство, выделяемое для

Мартьянова А.Е. Базы данных и знаний 204

нескольких столбцов в одном байте, не обязательно должно быть непре-

рывным. Если в таблице для данных типа bit вы определите 9 столбцов,

то для всех этих столбцов будет выделено 2 байта.

SQL Server записывает информацию о столбцах, определенных для

данных типа bit, в системную таблицу syscolumns, в которой в столбце

status хранятся смещения этих столбцов. Столбец, определенный для

данных типа bit, не может быть индексом.

Тип timestamp. Если вы определяете столбец таблицы для данных

типа timestamp, то сразу же после вставки новой или обновления сущест-

вующей строки в столбце этого типа автоматически добавляется значе-

ние счетчика. Непосредственно ввести значение в столбец с этим типом

данных невозможно. Увеличивающееся значение счетчика может

быть

вставлено в такой столбец только самой системой SQL Server.

Вставленное системой SQL Server в столбец значение счетчика

идентифицирует последовательность операций, которые выполнил SQL

Server. Значения в столбце типа timestamp хранятся в формате

varbinary(8), а не в формате datetime или smalldatetime. По умолчанию в

столбцах типа timestamp допустимы значения NULL.

Значение типа time-

stamp не является датой или временем, но внутри таблицы или базы дан-

ных оно всегда уникально. В таблице можно определить только один

столбец для данных этого типа.

Тип данных timestamp часто используется для уникальной иденти-

фикации строки. Когда вы обновляете поля в строке, существует общая

практика упоминать столбец, определенный как timestamp,

в предложе-

нии WHERE

инструкции UPDATE. В этом случае гарантируется, что ин-

струкция UPDATE

обновит только одну строку таблицы. Вы можете

быть уверены в уникальности значения типа timestamp, поскольку сервер

поддерживает и обновляет его всякий раз, когда вы вставляете новую

или обновляете существующую строку.

Данные типа timestamp используются как часть предложения

WHERE

для предотвращения одновременного обновления одной и той

же строки двумя пользователями, поскольку при выполнении для строки

инструкции WHERE

значение данных типа timestamp меняется автома-

тически. Вы можете быть уверены, что не обновите чью-либо чужую

информацию. Если кто-то еще изменяет строку, над которой вы сейчас

работаете, то при сохранении им обновленной информации поле time-

stamp изменится и строка не будет больше соответствовать вашей копии.

Когда вы выполните команду UPDATE

для сохранения ваших измене-

ний, предложение WHERE

не даст вам этого сделать, поскольку команда

204

Мартьянова А.Е. Базы данных и знаний 205

не найдет выбранную вами строку. Данные типа timestamp – это пре-

красное средство для поддерживания сервером уникальности каждой

строки.

Если вы присвоили столбцу имя timestamp, но не указали тип дан-

ных, то для него устанавливается тип данных timestamp. Используя гло-

бальную системную переменную @@dbts, можно отобразить текущее

значение поля типа timestamp, которое относится к очередному значению

счетчика

обновляемой или новой строки.

Для ссылки на глобальную переменную @@dbts можно использо-

вать инструкцию SELECT: SELECT @@dbts.

Тип binary(п) можно использовать для хранения битовых образов

(bit patterns) размером не более 8 Кбайт. С помощью указанного в круг-

лых скобках целого числа (n) определяется длина всего битового образа.

Размер поля столбца типа binary (двоичного) не может быть меньше од

ного байта, но при этом допускается хранение битового образа, целиком

состоящего из нулей.

Вводимое шестнадцатеричное значение должно начинаться симво-

лами 0х. Для них используются символы от 0 до 9 и от А до F. Например,

для ввода значения А0, с учетом начальных символов 0х, получим фор-

мат 0хА0. Если вы вводите значение, длина

которого превышает длину,

определенную вами для полей столбца, оно будет усечено. Кроме этого,

значения дополняются нулями после последней значащей цифры.

Тип varbinary(n) можно использовать для хранения битовых обра-

зов размером не более 8 Кбайт. С помощью указанного в круглых скоб-

ках целого числа определяется длина всего битового образа. Размер поля

этого двоичного

столбца не должен быть меньше одного байта, но при

этом допускается хранение битового образа, целиком состоящего из ну-

лей.

В отличие от данных типа binary, размер поля для данных типа

varbinary ограничен только реальной длиной значения. Как и в типе дан-

ных binary, перед двоичным значением ставятся символы 0х. Для бинар-

ных данных

используются символы от 0 до 9 и от А до F. Если вы вводи-

те значение, которое больше определенной вами длины поля столбца,

оно будет усечено.

Тип image позволяет при необходимости хранить большое количе-

ство символьной или двоичной информации (образы). Допускается хра-

нить свыше 8 Кбайт данных этого типа. Предварительное выделение

достаточного количества памяти

для данных типа image расточительно,

Мартьянова А.Е. Базы данных и знаний 206

поэтому заранее выделяется только его часть. Оставшаяся память выде-

ляется динамически.

Тип данных image иногда используют для вставки в строку объек-

тов OLE (Object Linking and Embedding, связывание и внедрение объек-

тов), таких, например, как документы Word, электронные таблицы Excel,

графические файлы GIF и JPEG.

Данные хранятся в виде строк фиксированной длины и на началь-

ном этапе для них выделяется 8 Кбайт

памяти. Дополнительные 8 Кбайт

добавляются динамически по мере необходимости, и данные связывают-

ся вместе так же, как для текстовых столбцов. Страницы данных разме-

ром в 8 Кбайт непрерывны логически, но не обязательно физически.

Значения, которые хранятся как данные типа image, при использо-

вании инструкции SELECT

отображаются точно так же, как значения

любых других столбцов. Количество отображаемых байтов ограничено

значением глобальной переменной @@Textsize, которое по умолчанию

равно 8 Кбайт. В столбце, определенном для данных типа text или image,

можно разрешить значения NULL,

и для такого значения страница памя-

ти размером 8 Кбайт не выделяется (но только до тех пор, пока для стро-

ки, в которой имеется значение NULL,

не выполнена операция

UPDATE).

Данные типа image при их отображении с помощью инструкции

SELECT

не транслируются автоматически в символы ASCII. Данные

столбца образов не предназначены для прямого вывода на экран. Они

могут быть переданы для обработки перед отображением другой про-

грамме, возможно, запущенной в системе клиента.

Для работы с данными типа image имеется несколько ограничений.

С их помощью можно определять только столбцы таблицы, но нельзя

определять другие

структуры хранения данных, такие как локальные пе-

ременные или параметры.

Слишком большие объемы данных типа image, которые могут хра-

ниться в столбцах таблицы, делают этот тип данных неподходящим для

многих инструкций Transact-SQL. Нельзя упоминать столбец, опреде-

ленный для этого типа данных, в предложении ORDER

BY, GROUP BY

или COMPUTE. SQL Server не будет пытаться сортировать или группи-

ровать строки таблицы, содержащие столбцы, в которых может нахо-

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

количества информации пришлось бы выделять слишком большие объе-

мы памяти.

206

Мартьянова А.Е. Базы данных и знаний 207

Ниже приведены некоторые правила, о которых необходимо пом-

нить при создании запроса.

• Недопустимы столбцы, определенные для данных типа image, в

структуре UNION,

если только это не UNION all.

• Недопустим подзапрос, который возвращает значение типа image.

• Столбцы, определенные для данных типа image, недопустимы в

предложении where или having, если не используется оператор сравнения

like.

• Недопустимо объявление distinct, за которым следует столбец таб-

лицы, определенный для данных типа image.

• Недопустим индекс, первичный или внешний ключ столбцов, оп-

ределенных для данных типа image.

Для хранения

значений денежных единиц в SQL Server реализова-

ны два специальных типа данных:

Тип money позволяет хранить значения денежных единиц. Данные

типа money состоят из целой и дробной частей, хранящихся в виде двух

целых значений, занимающих по 4 байта каждое. Точность значений ти-

па money равна одной десятитысячной денежной единицы. Некоторые

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

денеж-

ных значений, отображают значения типа money округленными до бли-

жайшей сотой доли.

Тип smallmoney предназначен для хранения более ограниченного,

чем в типе данных money, диапазона значений денежных единиц. Значе-

ния, которые можно хранить как данные типа smallmoney, хранятся в 4

байтах в виде целой и дробной частей. Как и значения типа money, зна-

чения типа

smallmoney отображаются при помощи некоторых специаль-

ных инструментов округленными до ближайшей сотой доли.

Хранение денежных значений в виде данных типа smallmoney вме-

сто данных типа money ведет к двойной экономии памяти.

Перед значением, добавляемым в столбцы таблицы, определенные

для данных типа money или smallmoney, необходимо указывать символ

доллара ($) или другой знак, определенный для конкретной денежной

единицы.

Тип sysname совпадает с типом данных varchar(30), однако ис-

пользуется для определения столбцов данных в системных таблицах и не

допускает значений NULL.

С помощью типа данных sysname нельзя оп-

ределять столбцы вашей таблицы – используйте для этого тип данных

varchar (30) или какой-либо ваш собственный.

Мартьянова А.Е. Базы данных и знаний 208

Тип uniqueldentifier. Состоит из 16-байтного шестнадцатеричного

числа, представляющего собой глобальный уникальный идентификатор

(Globally Unique Identification, GUID). Этот тип данных удобно исполь-

зовать для обеспечения уникальности строк таблицы.

В SQL Server 2000 появились еще два новых типа данных –

sql_variant и table.

Тип sql_variant. Этот тип данных подобен типу variant, используе-

мому в Microsoft Visual Basic, и позволяет в одном столбце, параметре

или переменной хранить

значения различных типов данных. При его ис-

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

• Объекты типа sql_variant могут использоваться для хранения лю-

бых типов данных кроме text, ntext, image и timestamp. Ну и, естественно,

самого типа sql_variant.

• Столбцы с этим типом данных могут использоваться в индексах и

уникальных ключах.

• Для столбцов с типом данных sql_variant не

поддерживается свой-

ство IDENTITY,

но столбцы могут входить в первичный или внешний

ключ.

• Столбцы с этим типом данных не могут использоваться в рассчи-

тываемых столбцах.

Если вы определили для столбца тип данных sql_variant, то уже не

сможете изменить его на другой тип.

Тип table. Подобен временной таблице и может использоваться

для хранения результирующего набора для

последующей обработки.

Этот тип данных может использоваться только в качестве локальной пе-

ременной или возвращаемого значения в определенных пользователем

функциях. Определение переменной этого типа должно включать опре-

деление столбцов, и типов данных с сопутствующими характеристиками

– все как для обычной таблицы. Область применения этого типа данных

ограничивается операторами SELECT

и INSERT, а также – если он под-

держивается – в операторах UPDATE, DELETE и DECLARE CURSOR.

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

Можно определить собственный тип данных, который затем будет

использоваться для сохраняемых структур, таких как столбцы таблицы

[6]. Тип данных, определенный пользователем (user-defined), или просто

пользовательский, всегда формируется на основе одного из существую-

щих системных типов данных. Если необходимо, пользовательский

тип

данных может включать в себя спецификацию длины и характеристику

NULL.

208

Мартьянова А.Е. Базы данных и знаний 209

Для пользовательского типа данных лучше использовать описа-

тельное имя информацией о системном типе данных, на основе которого

он создается.

Для определения пользовательского типа данных с помощью En-

terprise Manager проделайте следующие действия:

1. Раскройте дерево баз данных щелчком мыши на значке + рядом

с папкой \Databases. Выберите мышью базу данных. Пользовательский

тип данных может быть

только в выбранной базе данных.

2. Выделите папку \User Defined Datatypes и нажмите кнопку New

на панели инструментов.

3. В поле Name (Имя) диалогового окна User-Defined Data Type

Properties (Свойства пользовательского типа данных) введите имя типа

данных.

4. В списке Data type (Тип данных) выберите один из системных

типов данных, а в поле Length (Длина) задайте его длину, если

это поле

не заблокировано. Другими словами, это следует сделать, если выбран

тип данных binary, char, decimal, nchar, numeric, nvarchar, varbinary или

varchar. Вы можете также разрешить ввод значения NULL, применить

предварительно определенное правило и установить значение по умол-

чанию.

5. Для того чтобы завершить процесс создания типа данных, на-

жмите кнопку OK.

На рис. III.12 показано диалоговое окно, которое позволяет

созда-

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

создан

Мартьянова А.Е. Базы данных и знаний 210

Рис. III.12. Диалоговое окно создания пользовательского типа данных

Для удаления пользовательского типа данных необходимо выде-

лить его в окне SQL Server Enterprise Manager и нажать кнопку Delete

(Удалить) на панели правления. В результате на экран будет выведено

диалоговое окно Drop Objects (Удаление объектов) с запросом на удале-

ние (рис. III.13).

Невозможно удалить пользовательский тип данных, если в какой-

либо таблице (или таблицах) есть столбец,

определенный для данных

этого типа. Удалить пользовательский тип данных можно только в том

случае, если он не используется ни в одной таблице. В противном случае

на экран выводится окно с сообщением об ошибке.

210