Все значения в столбце должны быть одного типа данных. (Единственным исключением из этого правила являются значения типа данных SQL_VARIANT.) Используемые в Transact-SQL типы данных можно разбить на следующие категории:
временные типы (даты и/или времени);
прочие типы данных.
Все эти категории данных рассматриваются далее в последующих разделах.
Числовые типы данных
Как и следовало ожидать по их названию, числовые типы данных применяются для представления чисел. Эти типы и их краткое описание приводятся в таблице ниже:
Числовые типы данных T-SQL
Тип данных
Описание
INTEGER
Представляет целочисленные значения длиной в 1 байт в диапазоне от 0 до 255
Используется для представления денежных значений. Значения типа MONEY соответствуют 8-байтовым значениям типа DECIMAL, округленным до четырех разрядов после десятичной точки
Представляет такие же значения, что и тип MONEY, но длиной в 4 байта
Символьные типы данных
Существует два общих вида символьных типов данных. Строки могут представляться однобайтовыми символами или же символами в кодировке Unicode. (В кодировке Unicode для представления одного символа применяется несколько байтов.) Кроме этого, строки могут быть разной длины. В таблице ниже перечислены категории символьных типов данных с их кратким описанием.
Символьные типы данных T-SQL
Тип данных
Описание
CHAR[(n)]
Используется для представления строки однобайтовых символов переменной длины (0 NCHAR[(n)]
Используется для хранения строк переменной длины, состоящих из символов в кодировке Unicode. Для хранения каждого символа строки типа NVARCHAR требуется 2 байта, поэтому строка типа данных NVARCHAR может содержать самое большее 4000 символов.
Тип данных VARCHAR идентичен типу данных CHAR, за исключением одного различия: если содержимое строки CHAR(n) короче, чем n символов, остаток строки заполняется пробелами. А количество байтов, занимаемых строкой типа VARCHAR, всегда равно количеству символов в ней.
Типы данных времени
В языке Transact-SQL поддерживаются следующие временные типы данных:
Если нужно сохранить только составляющую даты или времени, использование значений типа DATETIME или SMALLDATETIME несколько неудобно. По этой причине в SQL Server были введены типы данных DATE и TIME, в которых хранятся только составляющие даты и времени значений типа DATETIME, соответственно. Значения типа DATE занимают 3 байта, представляя диапазон дат от 01/01/0001 до 31/12/9999. Значения типа TIME занимают 3-5 байт и представляют время с точностью до 100 нс.
Тип данных DATETIME2 используется для представления значений дат и времени с высокой точностью. В зависимости от требований, значения этого типа можно определять разной длины, и занимают они от 6 до 8 байтов. Составляющая времени представляет время с точностью до 100 нс. Этот тип данных не поддерживает переход на летнее время.
Все рассмотренные на данный момент временные типы данных не поддерживают часовые пояса. Тип данных DATETIMEOFFSET имеет составляющую для хранения смещения часового пояса. По этой причине значения этого типа занимают от 6 до 8 байтов. Все другие свойства этого типа данных аналогичны соответствующим свойствам типа данных DATETIME2.
Двоичные и битовые типы данных
К двоичным типам данным принадлежат два типа: BINARY и VARBINARY. Эти типы данных описывают объекты данных во внутреннем формате системы и используются для хранения битовых строк. По этой причине значения этих типов вводятся, используя шестнадцатеричные числа.
Значения битового типа bit содержат лишь один бит, вследствие чего в одном байте можно сохранить до восьми значений этого типа. Краткое описание свойств двоичных и битовых типов данных приводится в таблице ниже:
Двоичные и битовые типы данных T-SQL
Тип данных
Описание
BINARY[(n)]
Определяет строку битов фиксированной длины, содержащую ровно n байтов(0 VARBINARY[(n)]
Определяет строку битов переменной длины, содержащую до n байтов (0 BIT
Применяется для хранения логических значений, которые могут иметь три возможных состояния: false, true и null
Тип данных больших объектов
используется для хранения объектов данных размером до 2 Гбайт. Такие объекты обычно применяются для хранения больших объемов текстовых данных и для загрузки подключаемых модулей и аудио- и видеофайлов. В языке Transact-SQL поддерживаются следующие типы данных LOB:
Начиная с версии SQL Server 2005, для обращения к значениям стандартных типов данных и к значениям типов данных LOB применяется одна и та же модель программирования. Иными словами, для работы с объектами LOB можно использовать удобные системные функции и строковые операторы.
В компоненте Database Engine параметр MAX применяется с типами данных VARCHAR, NVARCHAR и VARBINARY для определения значений столбцов переменной длины. Когда вместо явного указания длины значения используется значение длины по умолчанию MAX, система анализирует длину конкретной строки и принимает решение, сохранять ли эту строку как обычное значение или как значение LOB. Параметр MAX указывает, что размер значений столбца может достигать максимального размера LOB данной системы.
Хотя решение о способе хранения объектов LOB принимается системой, настройки по умолчанию можно переопределить, используя системную процедуру sp_tableoption с аргументом LARGE_VALUE_TYPES_OUT_OF_ROW. Если значение этого аргумента равно 1, то данные в столбцах, объявленных с использованием параметра MAX, будут сохраняться отдельно от остальных данных. Если же значение аргумента равно 0, то компонент Database Engine сохраняет все значения размером до 8 060 байт в строке таблицы, как обычные данные, а значения большего размера хранятся вне строки в области хранения объектов LOB.
Тип данных UNIQUEIDENTIFIER
Инициализировать столбец или переменную типа UNIQUEIDENTIFIER можно посредством функции NEWID или NEWSEQUENTIALID, а также с помощью строковой константы особого формата, состоящей из шестнадцатеричных цифр и дефисов. Эти функции рассматриваются в следующей статье.
К столбцу со значениями типа данных UNIQUEIDENTIFIER можно обращаться, используя в запросе ключевое слово ROWGUIDCOL, чтобы указать, что столбец содержит значения идентификаторов. (Это ключевое слово не генерирует никаких значений.) Таблица может содержать несколько столбцов типа UNIQUEIDENTIFIER, но только один из них может иметь ключевое слово ROWGUIDCOL.
Тип данных SQL_VARIANT
Тип данных SQL_VARIANT можно использовать для хранения значений разных типов одновременно, таких как числовые значения, строки и даты. (Исключением являются значения типа TIMESTAMP.) Каждое значение столбца типа SQL_VARIANT состоит из двух частей: собственно значения и информации, описывающей это значение. Эта информация содержит все свойства действительного типа данных значения, такие как длина, масштаб и точность.
Для доступа и отображения информации о значениях столбца типа SQL_VARIANT применяется функция SQL_VARIANT_PROPERTY.
Объявлять тип столбца как SQL_VARIANT следует только в том случае, если это действительно необходимо. Например, если столбец предназначается для хранения значений разных типов данных или если при создании таблицы тип данных, которые будут храниться в данном столбце, неизвестен.
Тип данных HIERARCHYID
Тип данных HIERARCHYID используется для хранения полной иерархии. Например, в значении этого типа можно сохранить иерархию всех сотрудников или иерархию папок. Этот тип реализован в виде определяемого пользователем типа CLR, который охватывает несколько системных функций для создания узлов иерархии и работы с ними. Следующие функции, среди прочих, принадлежат к методам этого типа данных: GetLevel(), GetAncestor(), GetDescendant(), Read() и Write().
Тип данных TIMESTAMP
Само по себе значение, сохраняемое в столбце типа TIMESTAMP, не представляет никакой важности. Этот столбец обычно используется для определения, изменилась ли определенная строка таблицы со времени последнего обращения к ней.
Варианты хранения
Начиная с версии SQL Server 2008, существует два разных варианта хранения, каждый из которых позволяет сохранять объекты LOB и экономить дисковое пространство. Это следующие варианты:
хранение данных типа FILESTREAM;
хранение с использованием разреженных столбцов (sparse columns).
Эти варианты хранения рассматриваются в следующих подразделах.
Хранение данных типа FILESTREAM
Как уже упоминалось ранее, SQL Server поддерживает хранение больших объектов (LOB) посредством типа данных VARBINARY(MAX). Свойство этого типа данных таково, что большие двоичные объекты (BLOB) сохраняются в базе данных. Это обстоятельство может вызвать проблемы с производительностью в случае хранения очень больших файлов, таких как аудио- или видеофайлов. В таких случаях эти данные сохраняются вне базы данных во внешних файлах.
Хранение данных типа FILESTREAM поддерживает управление объектами LOB, которые сохраняются в файловой системе NTFS. Основным преимуществом этого типа хранения является то, что хотя данные хранятся вне базы данных, управляются они базой данных. Таким образом, этот тип хранения имеет следующие свойства:
данные типа FILESTREAM можно сохранять с помощью инструкции CREATE TABLE, а для работы с этими данными можно использовать инструкции для модифицирования данных (SELECT, INSERT, UPDATE и DELETE);
система управления базой данных обеспечивает такой же самый уровень безопасности для данных типа FILESTREAM, как и для данных, хранящихся внутри базы данных.
Разреженные столбцы (sparse columns)
Цель варианта хранения, предоставляемого разреженными столбцами, значительно отличается от цели хранения типа FILESTREAM. Тогда как целью хранения типа FILESTREAM является хранение объектов LOB вне базы данных, целью разреженных столбцов является минимизировать дисковое пространство, занимаемое базой данных.
Столбцы этого типа позволяют оптимизировать хранение столбцов, большинство значений которых равны null. При использовании разреженных столбцов для хранения значений null дисковое пространство не требуется, но, с другой стороны, для хранения значений, отличных от null, требуется дополнительно от 2 до 4 байтов, в зависимости от их типа. По этой причине разработчики Microsoft рекомендуют использовать разреженные столбцы только в тех случаях, когда ожидается, по крайней мере, 20% общей экономии дискового пространства.
Разреженные столбцы определяются таким же образом, как и прочие столбцы таблицы; аналогично осуществляется и обращение к ним. Это означает, что для обращения к разреженным столбцам можно использовать инструкции SELECT, INSERT, UPDATE и DELETE таким же образом, как и при обращении к обычным столбцам. Единственная разница касается создания разреженных столбцов: для определения конкретного столбца разреженным применяется аргумент SPARSE после названия столбца, как это показано в данном примере:
Несколько разреженных столбцов таблицы можно сгруппировать в набор столбцов. Такой набор будет альтернативным способом сохранять значения во всех разреженных столбцах таблицы и обращаться к ним.
Значение NULL
Если выражение содержит операцию сравнения и значение одного или обоих операндов этой операции равно null, результат этой операции также будет null.
Значение null должно отличаться от всех других значений. Для числовых типов данных значение 0 и значение null не являются одинаковыми. То же самое относится и к пустой строке и значению null для символьных типов данных.
Значения null можно сохранять в столбце таблицы только в том случае, если это явно разрешено в определении данного столбца. С другой стороны, значения null не разрешаются для столбца, если в его определении явно указано NOT NULL. Если для столбца с типом данных (за исключением типа TIMESTAMP) не указано явно NULL или NOT NULL, то присваиваются следующие значения:
NULL, если значение параметра ANSI_NULL_DFLT_ON инструкции SET равно on.
NOT NULL, если значение параметра ANSI_NULL_DFLT_OFF инструкции SET равно on.
Если инструкцию set не активировать, то столбец по умолчанию будет содержать значение NOT NULL. (Для столбцов типа TIMESTAMP значения null не разрешаются.)
Типы точных числовых данных, использующие целые значения. Для экономии места в базе данных используйте тип данных наименьшего размера, который гарантирует возможность хранения всех возможных значений. Например, типа tinyint достаточно для хранения возраста людей, так как он не может превышать 255 лет. Однако типа tinyint будет недостаточно для возраста зданий, так как они могут быть старше 255 лет.
Remarks
Тип данных int является основным типом целочисленных данных в SQL Server. Тип данных bigint используется для хранения значений, выходящих за диапазон, поддерживаемый типом данных int.
В таблице приоритетов типов данных тип bigint располагается между smallmoney и int.
Функции возвращают bigint только в случае, если выражение параметра имеет тип bigint. SQL Server не выполняет автоматического продвижения других целочисленных типов данных (tinyint, smallint и int) до bigint.
При использовании таких арифметических операторов, как +, –, *, / или %, для явного или неявного преобразования констант типа int, smallint, tinyint или bigint в значения типа float, real, decimal или numeric в SQL Server используются различные правила определения типов данных и точности результата, зависящие от наличия автоматической параметризации запроса.
Преобразование целочисленных данных
При неявном преобразовании данных типа integer в данные типа character, если число слишком большое для символьного поля, SQL Server вставляет символ с кодом ASCII 42 — звездочку (*).
Целочисленные константы, превышающие 2 147 483 647, преобразуются в тип данных decimal, а не в bigint. В приведенном ниже примере демонстрируется изменение типа данных результата с int на decimal при превышении порогового значения.
Примеры
В приведенном ниже примере создается таблица, в которой используются типы данных bigint, int, smallint и tinyint. Значения вставляются в каждый столбец и возвращаются в инструкции SELECT.
Согласно процедурному расширению языка SQL (Transact-SQL), для работы с MS SQL Server предусмотрены различные типы данных. Конкретный тип данных указывается для любого столбца во время создания таблицы и определяет, какие именно значения можно будет хранить, а также сколько места в памяти компьютера они будут занимать. Эта статья представляет собой обзор существующих типов данных для MS SQL Server, которые, в зависимости от поддерживаемых значений, делятся на группы.
Несколько слов о T-SQL
Transact-SQL является процедурным расширением SQL (structured query language), которое создано корпорацией Microsoft специально для баз данных Microsoft SQL Server и Sybase ASE. Известный язык программирования SQL был расширен рядом дополнительных возможностей:
Сегодня язык Transact-SQL — это, по сути, ключ к применению MS SQL Server. Таким образом, все программные приложения, взаимодействующие с экземпляром MS SQL Server, вне зависимости от реализации и UI, выполняют отправку серверу инструкций Transact-SQL.
Директивами сценария являются специфические команды, используемые лишь в MS SQL. Такие команды позволяют серверу определять правила работы с транзакциями и скриптом.
Типы данных (datatypes)
Как это принято и в других языках программирования, язык T-SQL поддерживает множество разных типов данных, используемых для хранения переменных в MS SQL. Эти типы данных можно разделить на группы:
Давайте рассмотрим некоторые из вышеописанных типов данных подробнее.
Числовые типы данных
К числовым типам данных относят:
Также этот тип данных способен принимать 2 параметра — scale и precision: DECIMAL(precision, scale).
Параметр precision служит для представления наибольшего количества цифр, способных хранить числовое значение. По дефолту оно равняется 18-ти, а вообще должно располагаться в диапазоне 1-38.
Параметр scale служит для представления максимального количества цифр, которые способно содержать число после запятой. Такое значение должно быть в диапазоне от 0 до значения, которое указывается параметром precision. По умолчанию равно нулю;
Этот тип данных способен иметь форму определения в формате FLOAT(n), где n — это количество бит, используемых в целях хранения десятичной части числа (речь идет о мантиссе). По дефолту n равняется 53;
Рассмотрим пример числового столбца с таким типом данных:
Тип данных для времени
Существуют типы данных, предназначенных для отображения даты и времени:
Наиболее распространены следующие форматы дат:
В этом формате 2-значные числа в пределах 00-49 воспринимаются в качестве дат диапазона 2000-2049. А числовые значения 50-99 воспринимаются в качестве диапазона 1950-1999.
Распространены следующие временные форматы:
Строковый тип данных
Какие строковые типы данных бывают:
Число символов, которое способен хранить столбец, передается с помощью скобок. К примеру, для столбца CHAR(10) выделится 10 байт. Когда мы сохраняем в столбце строку размером меньше десяти символов, она дополнится пробелами;
Этот тип данных также не подходит для многих языков и все по той же причине: хранит символы не в Unicode;
Также существуют такие типы данных, как TEXT и NTEXT. Они считаются устаревшими, поэтому их лучше не использовать. Вместо только что упомянутых типов данных применяют VARCHAR и NVARCHAR.
Рассмотрим примеры определения строковых столбцов:
О бинарных типах данных
Пришла очередь и двоичным данным:
Такой бинарный тип, как IMAGE, тоже устарел, поэтому вместо него желательно использовать тип данных VARBINARY.
Специальные типы данных
В этой группе перечислим оставшиеся типы данных:
Примечание: для работы с русскими символами тип данных применяют, используя приставку «n» (nchar, nvarchar, ntext). Такие приставки кодируют символы 2-мя байтами. Если говорить иными словами, то для работы с кодировкой Unicode используют тип данных с «n» (вспоминаем слово national). Так же записываются и строковые константы.
Когда речь идет о типе данных для значения переменной длины, задействуют тип данных, имеющий приставку «var» (character varying). Тип данных без приставки «var» характеризуется фиксированной длиной области памяти, причем неиспользованная часть заполняется пробелами либо нулями.
Типы данных SQL разделяются на три группы: — строковые; — с плавающей точкой (дробные числа); — целые числа, дата и время.
Типы данных MySQL
Типы данных MySQL разделяются на следующие типы:
Типы данных Oracle
Типы данных Oracle разделяются на следующие группы:
ANSI SQL стандарт распознает только текст и число, в то время как большинство коммерческих программ используют другие специальные типы, такие как DATЕ и TIME — фактически почти стандартные типы. Некоторые пакеты также поддерживают такие типы, как, например, MONEY и BINARY. Типы данных, распознаваемые с помощью ANSI, состоят из строк символов и различных типов чисел, которые могут классифицироваться как точные числа и приблизительные числа.
CHARACTER(length) определяет спецификацию строк символов, где length задает длину строк заданного типа. Значения этого типа должны быть заключены в одиночные кавычки. Большинство реализаций поддерживают строки переменной длины для типов данных VARCHAR и LONG VARCHAR (или просто LONG).
В то время, как поле типа CHAR всегда может распределить память для максимального числа символов, которое может сохраняться в поле, поле VARCHAR при любом количестве символов может распределить только определенное количество памяти, чтобы сохранить фактическое содержание поля, хотя SQL может установить некоторое дополнительное пространство памяти, чтобы следить за текущей длиной поля. Поле VARCHAR может быть любой длины, включая реализационно-определяемый максимум. Этот максимум может меняться от 254 до 2048 символов для VARCHAR и до 16000 символов для LONG. LONG обычно используется для текста пояснительного характера или для данных, которые не могут легко сжиматься в простые значения полей; VARCHAR может использоваться для любой текстовой строки, чья длина может меняться.
Извлечение и модифицирование полей VARCHAR — более сложный, и, следовательно, более медленный процесс, чем извлечение и модифицирование полей CHAR. Кроме того, некоторое количество памяти VARCHAR, остается всегда неиспользованной для гарантии вмещения всей длины строки. При использовании таких типов следует предусматривать возможность полей к объединению с другими полями.
Точные числовые типы — это числа, с десятичной точкой или без десятичной точки, которые могут представляться в виде [+|-] [. ] и специфицироваться как:
DECIMAL(precision [, scale]) — аргумент размера имеет две части: точность и масштаб. Масштаб не может превышать точность. Точность указывает сколько значащих цифр имеет число. Масштаб указывает максимальное число цифр справа от десятичной точки. Масштаб = нулю делает поле эквивалентом целого числа.
NUMERIC(precision [, scale]) — такое же как DECIMAL за исключением того, что максимальное десятичное не может превышать аргумента точности
INTEGER — число без десятичной точки. Эквивалентно DECIMAL, но без цифр справа от десятичной точки, т.е. с масштабом равным 0. Аргумент размера не используется (он автоматически устанавливается в реализационно-зависимое значение).
SMALLINT — такое же как INTEGER, за исключением того, что, в зависимости от реализации, размер по умолчанию может ( или не может ) быть меньше чем INTEGER.
Приблизительные числовые типы — это числа в показательной (экспоненциальной по основанию 10) записи, представляемые как Е и специфицирущиеся следующим образом:
FLOAT[(precision)] — число с плавающей запятой. Аргумент размера состоит из одного числа, определяющего минимальную точность.
REAL — такое же как FLOAT, за исключением того, что никакого аргумента размера не используется. Точность устанавливается реализационно-зависимой по умолчанию.
DOUBLE PRECISION — такое же как REAL, за исключением того, что реализационно-определяемая точность для DOUBLE PRECISION должна превышать реализационно-определяемую точность REAL.
Типы данных Access
Типы данных Access разделяются на следующие группы:
Типы данных SQL Server
Типы данных, используемые в SQL Server:
Типы данных PostgreSQL
База данных PostgreSQL поддерживает большинство типов данных SQL2003 плюс огромный набор типов для хранения пространственных и геометрических данных. PostgreSQL может похвастаться богатым набором операторов и функций, специально предназначенных для геометрических типов данных. Сюда входят такие средства, как поворот, поиск пересечений и масштабирование. В PostgreSQL также есть поддержка дополнительных версий существующих типов данных, которые характерны тем, что занимают меньше места на диске, чем соответствующие исходные версии. Например, в PostgreSQL предлагается несколько вариантов типа INTEGER для хранения больших и небольших чисел, соответственно занимающих больше или меньше места.
