Подробно о типах данных

Правильный выбор типов данных - важный этап проектирования таблиц

В следующих выпусках рубрики "T-SQL для начинающих" будет рассказано о применении синтаксиса T-SQL для построения таблиц. Но прежде чем начинать строить таблицы, необходимо поговорить о типах данных. Для каждого столбца таблицы должен быть определен некоторый тип данных, например, целые (integer) или символьные (character). Это позволяет системе в последующем контролировать данные, которые будут помещаться в этот столбец. В предлагаемой статье автор рассматривает предусмотренные в системе типы данных и рассказывает о том, как создавать собственные типы данных. Подобные дополнительные типы данных следует создать до того как начнется построение таблиц, ведь при создании таблиц могут понадобиться ссылки на эти типы данных. Определяемые пользователями типы данных позволят существенно усилить контроль над данными и повысить их целостность, особенно в проектах, в которых с базами данных параллельно работают несколько разработчиков.

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

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

Большинство столбцов в этой таблице являются текстовыми полями. Номер телефона, обозначение штата и почтовый индекс всегда имеют постоянную длину, поэтому соответствующие им столбцы phone, state и ZIP удобнее объявить символьными (char). Имя автора, его адрес и город, где он живет, могут различаться по длине, поэтому для них предпочтительнее использовать тип символьных данных переменной длины (varchar). Столбец contract с информацией о наличии контракта с автором, в котором данные представляются в форме да/нет, целесообразно отнести к двоичному типу данных bit, у которого имеется два возможных значения - 0 и 1. Теперь остановимся подробнее на системных типах данных.

Типы данных, предусмотренные в системе

В версии SQL Server 6.0 определено 19 типов данных, а в версии SQL Server 7.0 добавлено еще 4 типа для удобства работы с закодированными с помощью Unicode данными, а также для поддержки приложений, основанных на использовании хранилищ данных. Каждый тип данных имеет ряд разновидностей, отличающихся возможным набором значений. Знание этих значений облегчит выбор подходящего типа данных.

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

Символьные данные типа char предсавляют собой один из наиболее распространенных типов данных. К этому типу относятся такие символьные данные, как имена или адреса. В версии SQL Server 6.5 длина любого столбца с символьными данными ограничивалась 255 знаками. Если же информация оказывалась длиннее, к примеру, если в столбец предполагалось вводить свободный комментарий по поводу контракта, то тогда следовало использовать текстовый тип данных (text). В SQL Server 7.0 это ограничение отменено, так что символьное поле может содержать до 8000 байтов. Верхняя граница обусловлена размером страницы памяти, поскольку ни одна запись не может располагаться в памяти на нескольких страницах. В тех случаях, когда для столбца определен тип данных char, данные следует помещать в одинарных кавычках, как показано в следующем примере:

UPDATE.....
SET.....
WHERE.....


Как отмечалось в статье "Работа с символьными данными", помещенной в майском номере журнала, можно выбрать символьный тип данных либо фиксированной длины, char, либо переменной длины, varchar. Фиксированный размер оказывается предпочтительным в тех случаях, когда данные имеют одинаковую или сходную длину, например, при вводе идентификатора автора (часто в этом качестве используют индивидуальный номер системы социальной безопасности, что, кстати, является плохим примером для подражания). В большинстве ситуаций применение переменной длины данных не приводит к сколько-нибудь заметному увеличению времени обработки. В то же время фамилия автора может быть очень длинной, так что использование типа varchar оказывается вполне оправданным. Применительно к подавляющему большинству фамилий фиксированная длина поля означает потерю значительного объема памяти, поэтому лучше использовать тип данных varchar. При выборе того или иного типа данных всегда следует искать компромисс с учетом двух аспектов: с одной стороны, потери полезного объема памяти при использовании данных фиксированной длины, а с другой стороны, увеличения времени обработки в случае применения данных переменной длины.

SQL Server 7.0 поддерживает набор символов Unicode. В связи с этим, чтобы воспользоваться всеми преимуществами, предоставляемыми расширенными возможностями Unicode, необходимо было ввести дополнительный тип данных.Если вам захочется использовать символьные данные Unicode, то следует указать тип данных Nchar или, если это информация переменной длины, то Nvarchar. При вводе данных Unicode их следует заключать в одиночные кавычки, причем непосредственно перед ними необходимо поставить заглавную латинскую букву N. Если в рассмотренном ранее примере имя автора отнесено к типу данных Unicode, то предыдущий оператор обновления примет следующий вид:

UPDATE.....
SET.....
WHERE.....


Ограничение максимальной длины информации при работе с типом данных Unicode составляет 4000 знаков. Это объясняется тем, что для хранения каждого символа Unicode требуется два байта памяти. Поэтому на стандартную страницу памяти размером 8К можно поместить в два раза меньше символов Unicode, чем при использовании обычных символов.

Целые

Базовый тип целых чисел integer охватывает диапазон от -2 147 483 638 до 2 147 483 647. Уменьшенные целые smallint включают числа от -32 768 до 32767. Зачастую, когда точно известно, что диапазон возможных числовых значений данных невелик, лучше применять тип данных smallint. К примеру, в базе данных личной коллекции компакт-дисков при выборе типа данных для первичного ключа целесообразно использовать тип данных smallint. Ведь предположение о том, что число компакт-дисков в такой коллекции превысит 32 676 единиц, кажется неправдоподобным. В этой ситуации не следует для оптимизации памяти использовать тип данных tinyint, поскольку он позволяет обрабатывать только значения от 0 до 255. Такой тип данных можно было бы применить для нумерации депозитных сертификатов, а для коллекции компакт-дисков он может оказаться недостаточным.

Точные числовые данные

Если приложение таково, что необходимо получать из базы данных числовое значение в строго указанном формате, то для этого следует выбрать один из точных числовых типов данных. Существуют два точных числовых типа данных: десятичный (decimal) и числовой (numeric), которые по существу совпадают друг с другом. Для них можно задать требуемые точность p и масштаб s в формате decimal (p,s). Точность представляет собой число значащих символов по обе стороны от десятичной запятой, а масштаб - количество символов справа от нее. Например, число 123,4567 можно хранить в столбце, для которого тип данных задан в виде (7,4). Если число, которое должно быть помещено в столбец с точным числовым типом данных, содержит больше десятичных знаков, чем указано в типе данных, то такое число округляется до требуемой точности. Числовые типы данных могут использовать для хранения значений от 2 до 17 байтов.

Приближенные числовые данные

Некоторые числа нельзя точно представить в десятичном виде с ограниченным числом знаков, например, одну треть или число пи. Для записи таких чисел используются действительный (real) или плавающий (float) типы данных. Данные действительного типа хранятся с точностью от 1 до 7 знаков. Плавающий формат, который иногда называют еще форматом двойной точности, может хранить числа, содержащие от 8 до 15 значащих цифр. Действительный и плавающий типы данных применяются в научных приложениях для хранения чисел, не требующих точного двоичного выражения. Одна-две последние цифры могут не вполне точно сохраняться при преобразованиях в двоичный формат. Поэтому такие числа не следует использовать в операциях точного сравнения, применяемых для формулирования условий оборота WHERE.

Двоичные

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

Денежные

Для представления денежных значений используются два типа данных: денежный (money) и малый денежный (smallmoney). Тип данных денежный применяется для хранения значений от плюс до минус 922 триллионов. Большинство из нас вполне может пользоваться малым денежным типом данных, который перекрывает диапазон значений от -214 748,3648 до +214 748,3647. По существующей договоренности денежный тип данных при хранении имеет четыре десятичных знака после запятой, а для его представления пользователям требуется только два знака . Отметим, что многие финансовые транзакции не используют денежный тип данных. Например, в биржевыех торговых операциях применяются 1/32 доли, для хранения которых необходимо пять десятичных мест.

Даты

В SQL Server и дата и время хранятся в одном столбце, так что если с помощью функции GETDATE() запросить текущую дату, то при этом система сообщит и время. Для дат применяются два типа данных, datetime и smalldatetime. Тип данных smalldatetime охватывает период времени от 1 января 1900 года до 6 июня 2079 года и включает время с точностью до минуты. Такого диапазона достаточно для подавляющего большинства проектов. Тип данных datetime годен для использования до 31 декабря 9999 года (это следует учитывать при решении проблемы 10К года). Начало диапазона этого типа данных датируется 1 января 1753 года. Почему 1753? Это связано с переходом с юлианского на грегорианский календарь. Несмотря на то, что грегорианский календарь был предложен некоторое время назад, процесс его принятия продолжался в течение приблизительно 30 лет. На протяжении этого периода некоторые страны уже приняли грегорианский, а другие еще нет. Поэтому для того, чтобы дата воспринималась однозначно, надо знать географическое положение объекта. Кроме того, год начинался не 1 января, а 1 марта. Поэтому такая дата, как 15 января 1792 года, может интерпретироваться и как середина первого месяца 1492 года, и как середина 11-го месяца 1793 года. Создатели SQL Server решили не рисковать, и поэтому приняли решение не воспринимать даты, относящиеся к периоду времени до начала 1753 года. Следует отметить также, что тип данных datetime показывает тысячные доли секунды, хотя точность гарантируется только до 1/300 части секунды.

Тексты и образы

Для символьных данных длиннее, чем 255 знаков, в SQL Server 6.5 следует применять тип данных текст (text). В SQL Server 7.0 граница применимости этого типа данных отодвигается до 8000 знаков. Для больших двоичных объектов (BLOB), таких как цифровые образы, используется тип данных образ (image). ?анные типа текст или образ не хранятся в строках, поэтому к ним не применимо ограничение на размер страницы памяти. В строках хранятся лишь указатели на страницы базы данных, в которых находится информация. Для обновления этих типов данных необходимы специальные процедуры, которые выходят за рамки рассмотрения настоящей статьи. (Более подробно об этом написано в статье Майкла Оути "Нам не страшен огромный BLOB", опубликованной в апрельском номере журнала.) Здесь необходимо отметить только то, что изменения текстовых данных или образов не фиксируются в журнале, а указатели не обновляются. Изменяются только сами поля, содержащие текст или образ.

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

Некоторые типы данных трудно отнести к какой-либо категории. Один из таких типов - битовые данные (bit). Это целое число, которое может принимать только два значения - 0 и 1 (в одном байте можно хранить восемь подобных величин). Битовые значения часто применяются в качестве флагов, принимающих значения истина или ложь. Их можно использовать, например, для хранения сведений о том, заключен ли контракт с автором, или принадлежат ли его книги к бестселлерам, или для чего-либо аналогичного. Одно незначительнгое отличие версии SQL Server 7.0 состоит в том, что для столбцов с битовыми данными теперь разрешены и неопределенные значения, в то время как в версии SQL Server 6.5 допустимы были только значения 0 и 1.

Для внутренних целей в SQL Server используется тип данных метка времени (timestamp). Этот тип данных генерирует уникальное значение, которое обновляется каждый раз, когда модифицируется информация в строке таблицы. Метки времени являются внутренними значениями, поддерживаемыми в SQL Server. Они не соответствуют реальным датам и времени.

В SQL Server 7.0 введены два новых типа данных, которые более подробно будут рассмотрены в последующих публикациях. тип данных уникальный идентификатор (uniqueidentifier) позволяет присвоить столбцу уникальное в глобальном масштабе значение. Глобальная уникальность означает неповторимость не только в рамках конкретной базы данных или в пределах одного компьютера, но вообще везде. Этот тип данных играет важную роль при работе с хранилищами данных, когда информация собирается в него из множества разнообразных источников. Тип данных курсор (cursor) применяется для переменных курсора. Использование курсора - отдельная большая тема, которой автор собирается посвятить целую статью.

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

SQL Server позволяет пользователям определять собственные типы данных, которые являются комбинацией системных типов данных. Как правило, они базируются на системном типе данных определенной длины (имеются в виду числовые и символьные значения), для которого назначаются правила и значения по умолчанию. Например, можно определить тип данных Почтовый индекс как char(10), а тип данных Телефонный номер как char(14). Тогда тип данных Почтовый индекс будет гарантировать целостность форматов почтовых индексов во всех столбцах, для любого клиента, поставщика, служащего или контактного лица где угодно по всему миру.

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

На данный момент добавлять новые типы данных можно с помощью SQL Server Enterprise Manager или из анализатора запросов Query Analyzer (в версии SQL Server 6.5 это производится из окна ISQL/w). Каждый тип данных, который добавляют пользователи, действует только в пределах конкретной базы данных. Если же необходимо создать тип данных для всех баз, то его следует поместить в базу данных моделей Model. С момента создания в этой базе данных новый тип данных будет доступен во всех остальных базах. Существует другой способ решения этой проблемы - написать сценарий или сгенерировать его с помощью утилиты генерации сценариев Generate SQL Script, входящей в состав SQL Server. Тогда этот сценарий можно будет запускать из любой базы данных. В Enterprise Manager следует дважды щелкнуть правой кнопкой мыши на названии базы данных, а затем последовательно выбрать из меню Все задачи (All tasks), Генерировать сценарий SQL (Generate SQL Script) и наконец, выбрать пункт создания сценария для всех типов данных.

В окне Enterprise Manager следует выбрать пункт Базы данных (Databases), выбрать конкретную базу из списка, а затем перейти к пункту Определяемые пользователем типы данных (User-Defined Data Types). Щелкните правой кнопкой мыши и выберите пункт Свойства новых определяемых пользователем типов данных (New User-defined Datatype Properties). После этого приступайте к определению типа данных.

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

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

Выберите правильный тип

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

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

Об авторе

Майкл Д. Рейли ( Этот e-mail адрес защищен от спам-ботов, для его просмотра у Вас должен быть включен Javascript ) является одним из основателей и вице президентом компании Mount Vernon Data Systems, которая занимается консалтингом по Windows NT и SMS, а также разработкой приложений,широко использующих базы данных. Он обладает сертификатами MCSE и MCT по Windows NT, SQL Server и SMS.

Листинг 1. Создание таблицы авторов книг Authors с использованием только системных типов данных.

au_id varchar(11) NOT NULL ,
aulname varchar (40) NOT NULL ,
au_fname varchar (20) NOT NULL ,
phone char (12) NOT NULL ,
address varchar (40) NULL ,
city varchar (20) NULL ,
state char (2) NULL ,
zip char (5) NULL ,
contract bit NOT NULL)


Листинг 2. Определение типов данных и последующее создание таблицы авторов книг Authors.

SP_ADDTYPE id, `char(11)`, `not null`
GO
SP_ADDTYPE phonenumber, `char(12)`, `not null`
GO
SP_ADDTYPE statecode, `char(2)`, `null`
GO
SP_ADDTYPE zipcode, `char(10)`, `null`
GO

CREATE TABLE dbo. authors (
au_id id NOT NULL ,
au_lname varchar (40) NOT NULL ,
au_fname varchar (20) NOT NULL ,
phone phonenumber(12) ,
address varchar (40) NULL ,
city varchar (20) NULL ,
state statecode,
zip zipcode,
contract bit NOT NULL )

Статьи по теме

Оптимизация и тюнинг производительности MariaDB MySQL сервера внутри Docker

Содержание: Установка и знакомство с MySQLTuner Установка MySQL Tuning Primer Script Первичная оптимизация MySQL сервера ...

2023-11-08

Работа с базами данных MySQL в ISPManager 5 + Docker

В ISPManager 5 добавлена контейнерная виртуализация Docker для использования альтернативных версий MySQL. Цитата из документации ISPManager 5: ...

2023-10-27

«Облачные» хранилища данных: в ожидании доверия

Информационные технологии активно мигрируют в сторону «облаков». Эксперты прогнозируют, что одним из главных приоритетов для CIO в ближ...

2011-11-05

ГОСТ 34.602-89. на Техническое задание по разработке web проекта

ТЕХНИЧЕСКОЕ ЗАДАНИЕ НА СОЗДАНИЕ АВТОМАТИЗИРОВАННОЙ СИСТЕМЫ Information technology. Set of standards for automated systems. Technical directions ...

2010-12-28

Orange color: теория и практика

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

2010-12-27

FAQ по MS SQL 7.0

11. Как запускать-останавливать SQL Server через командную строку? Мне это нужно для использования в скриптах.Остановка:NET STOP MSSQLSERVERили если вы...

2010-09-17

Виртуальная реализация SQL Server 2005

Ранее в материале «Разбираем “айсберг” sql server 2005» читателям предлагалось воспользоваться опубликованной в internet серией информационных...

2010-09-17

SQL Server в вопросах и ответах

В: У меня на разных компьютерах установлено два sql server 7.0. Для обмена данными между ними я использовал data transformation services (dts), чтобы импортиро�...

2010-09-17

Один из вариантов соглашения об именах объектов MS SQL Server

Помните, что имена объектов sql сервера не могут превышать 30 символов. Не используйте зарезервированные слова, специальные символы, и не н...

2010-09-17

Создание и удаление БД в MS SQL Server

В sql server'е базы данных хранятся в виде обычных файлов на диске. Как минимум на одну БД приходится таких файлов 2: *.mdf и *.ldf. В первом хранятся...

2010-09-17

Создание и удаление таблиц в MS SQL Server

Все данные в БД sql server хранятся в таблицах.Таблицы состоят из колонок, объединяющих значения одного типа, и строк - записей в таблице. В од�...

2010-09-17

Единая аутентификация Windows NT/2000 и Oracle

При использовании Windows NT/2000 как платформы для работы Oracle, и на стороне сервера и на стороне клиентов, имеется возможность использовать ед...

2010-09-17

Oracle / PHP FAQ

Что такое PHP и что необходимо иметь для работы с Oracle?PHP - рекурсивный акроним "PHP Hypertext Preprocessor". Это интерпретируемый язык программирования ...

2010-09-17

Oracle и PHP - это очень просто

ВведениеСУБД Oracle, когда против желания фирмы-изготовителя, а когда согласно – в зависимости от направления политических ветров в конкр�...

2010-09-17

Сжатие данных в целях экономии места и ускорения работы Oracle

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

2010-09-17