Преобразование типов данных (компонент Database Engine)
Преобразование типов данных происходит в следующих случаях:
При перемещении, сравнении или объединении данных одного объекта с данными другого объекта эти данные могут преобразовываться из одного типа в другой.
При передаче в переменную программы данных из результирующего столбца Transact-SQL, кодов возврата или выходных параметров, эти данные должны преобразовываться из системного типа данных SQL Server в тип данных переменной.
Преобразование типов данных бывает явным и неявным.
Неявное преобразование скрыто от пользователя.
SQL Server автоматически преобразует данные из одного типа в другой. Например, если тип данных smallint сравнивается с типом int, то перед сравнением тип smallint неявно преобразуется в тип int. Обратите внимание, что оптимизатор запросов может сформировать план запросов для выполнения этого преобразования в любое время. Это может привести к ошибкам времени выполнения преобразования — потере точности, попытке преобразования нечисловой строки в число и др. Дополнительные сведения см. в разделе Устранение ошибок и предупреждений по выражениям запроса.
Явное преобразование выполняется с помощью функций CAST и CONVERT.
Функции CAST и CONVERT преобразуют значение (локальную переменную, столбец или выражение) из одного типа данных в другой. Например, приведенная ниже функция CAST преобразует числовое значение $157.27 в строку символов '157.27':
CAST ( $157.27 AS VARCHAR(10) )Если программный код Transact-SQL должен соответствовать требованиям ISO, используйте функцию CAST вместо CONVERT. Использование функции CONVERT вместо CAST дает преимущество в дополнительной функциональности.
Некоторые виды явного и неявного преобразования типов данных не поддерживаются при преобразовании типа данных одного объекта SQL Server в тип данных другого объекта. Например, значение типа nchar нельзя преобразовать в значение типа image. Тип данных nchar можно преобразовать только в тип данных binary, причем только явно. Неявное преобразование в binary не поддерживается. Однако тип данных nchar можно преобразовать в тип nvarchar как явно, так и неявно.
При обработке типа sql_variant SQL Server поддерживает неявное преобразование объектов других типов данных в тип sql_variant. Но SQL Server не поддерживает неявные преобразования типа sql_variant в объекты с другим типом данных.
Дополнительные сведения о поддерживаемых видах преобразования объектов SQL Server см. в разделе Функции CAST и CONVERT (Transact-SQL).
При взаимных преобразованиях переменных приложения и столбцов результирующих наборов SQL Server, кодов возврата, параметров и маркеров параметров поддерживаемые преобразования типов данных определяются API базы данных. Дополнительные сведения см. в разделе Перенос данных в программные переменные.
Поведение преобразования типов данных
В следующих подразделах данного раздела приведено описание процесса преобразования следующих типов данных:
данных binary и varbinary |
данных типа money |
данных типа bit |
данных типа decimal и numeric |
данных типа Character |
типов данных с использованием хранимых процедур OLE-автоматизации |
данных типа date и time |
данных типа integer |
данных типа float и real |
Преобразование типов данных binary и varbinary
При преобразовании данных строкового типа (char, varchar, nchar, nvarchar, binary, varbinary, text, ntext или image) в тип данных binary или varbinary неравной длины SQL Server дополняет или усекает данные справа. При преобразовании других типов данных в тип binary или varbinary данные дополняются или усекаются слева. Дополнение осуществляется шестнадцатеричными нулями.
Если для обмена данными лучше всего подходит тип binary, то другие типы данных удобнее всего будет преобразовать в binary или varbinary. Преобразование любого достаточно большого значения в двоичное и обратно всегда дает первоначальное значение, если оба преобразования выполняются в одной и той же версии SQL Server. Двоичное представление значения может меняться в зависимости от версии SQL Server.
Типы данных int, smallint и tinyint можно преобразовать в тип binary или varbinary, но если преобразовать значение binary обратно в целочисленное, то оно будет отличаться от исходного в случае усечения. Например, в следующей инструкции SELECT показано, что целочисленное значение 123456 обычно хранится как двоичное 0x0001e240:
SELECT CAST( 123456 AS BINARY(4) )
Однако в следующей инструкции SELECT показано, что если целевой тип binary слишком мал для хранения всего значения, то начальные цифры неявно усекаются и то же самое число хранится как 0xe240:
SELECT CAST( 123456 AS BINARY(2) )
В следующем пакете показано, что это необъявленное усечение может повлиять на арифметические операции без возникновения ошибки.
DECLARE @BinaryVariable2 BINARY(2)
SET @BinaryVariable2 = 123456
SET @BinaryVariable2 = @BinaryVariable2 + 1
SELECT CAST( @BinaryVariable2 AS INT)
GO
Окончательный результат — 57921, но не 123457.
.gif "Примечание") Примечание |
|---|
Преобразование любого типа данных в binary может различаться в зависимости от версии SQL Server. |
Преобразование в тип данных bit
Преобразование в тип данных bit увеличивает любое ненулевое значение на 1.
Преобразование в символьные данные
При преобразовании символьного выражения в символьный тип данных другой длины значения, слишком длинные для нового типа данных, усекаются. Тип uniqueidentifier представляет собой символьный тип (чтобы обеспечить возможность преобразования из символьного выражения), поэтому на него распространяются правила усечения при преобразовании в символьный тип. Дополнительные сведения см. в разделе uniqueidentifier (Transact-SQL).
Если символьное выражение преобразуется в символьное выражение другого типа данных или размера, например из char(5) в varchar(5) или из char(20) в char(15), то преобразованному значению присваиваются параметры сортировки входного значения. Если несимвольное выражение преобразуется в символьный тип данных, то преобразованному значению присваиваются параметры сортировки, заданные по умолчанию в текущей базе данных. В любом случае необходимые параметры сортировки можно присвоить с помощью предложения COLLATE.
| Примечание |
|---|
Преобразование кодовых страниц поддерживается для типов данных char и varchar, однако поддержка типа данных text не предусмотрена. Как и в ранних версиях SQL Server, о потере данных во время преобразования кодовых страниц не сообщается. |
Символьные выражения, которые преобразуются в приближенный тип данных numeric, могут содержать необязательную экспоненциальную нотацию (символ e нижнего регистра или E верхнего регистра, за которым следуют необязательный знак плюс (+) или минус (-) и число).
Символьные выражения, преобразуемые в точный тип данных numeric, должны состоять из цифр, десятичного разделителя и необязательного знака плюс (+) или минус (-). Начальные пробелы не учитываются. Разделители в виде запятой запрещены (например, десятичный разделитель в числе 123 456,00).
Кроме того, символьные выражения, преобразуемые в типы данных money или smallmoney, могут содержать необязательный десятичный разделитель и обозначение валюты. Разрешаются разделители в виде запятой, например 123 456,00 руб.
В следующем примере показан способ преобразования данных для вывода. Этот пример перед сравнением строк преобразует продажи в символьные данные и текущую дату в стиль 3, ДД/ММ/ГГ.
USE AdventureWorks2008R2;
GO
SELECT BusinessEntityID,
CAST(SalesYTD AS varchar(12)),
CONVERT(VARCHAR(12), GETDATE(), 3)
FROM Sales.SalesPerson
WHERE CAST(SalesYTD AS varchar(20) ) LIKE '1%';
GO
В этом примере значение uniqueidentifier преобразуется в тип данных char.
DECLARE @myid uniqueidentifier
SET @myid = NEWID()
SELECT CONVERT(char(255), @myid) AS 'char';
GO
В этом примере текущая дата преобразуется в стиль 3, ДД/ММ/ГГ.
SELECT CONVERT(char(12), GETDATE(), 3);
GO
Преобразование данных типа Date и Time
При преобразовании в типы данных date и time SQL Server отбрасывает все значения, которые не удается распознать как дату или время. Обзор всех типов данных и функций даты и времени в языке Transact-SQL см. в разделе Функции даты и времени (Transact-SQL).
В следующем примере значения типа date и datetime2 преобразуются соответственно в типы данных varchar и binary.
DECLARE @mydate date;
SET @mydate = '4/05/98';
SELECT CAST(@mydate AS varchar) AS DATE_VARCHAR;
GO
DECLARE @mydate datetime2;
SET @mydate = '4/05/98';
SELECT CAST(@mydate AS binary) AS DATE_BINARY;
GO
Ниже приводится результирующий набор.
(1 row(s) affected)
DATE_VARCHAR
------------------------------
Apr 5 1998
(1 row(s) affected)
DATE_BINARY
--------------------------------------------------------------
0x0700000000008B210B
(1 row(s) affected)
Преобразование данных типа float и real
Значения типа float усекаются, если они преобразуются в любой целочисленный тип данных.
Если тип данных float или real нужно преобразовать в символьный тип, то, как правило, строковую функцию STR использовать удобнее, чем CAST( ). Это объясняется большими возможностями функции STR в отношении форматирования. Дополнительные сведения см. в разделах STR (Transact-SQL) и Встроенные функции (Transact-SQL).
Преобразование данных типа money
При преобразовании целочисленного типа данных в тип money предполагаются денежные единицы. Например, целочисленное значение 4 преобразуется в значение типа данных money величиной 4 денежные единицы.
В следующем примере значения типа smallmoney и money преобразуются соответственно в типы данных varchar и decimal.
USE AdventureWorks2008R2;
GO
DECLARE @mymoney_sm smallmoney;
SET @mymoney_sm = 3148.29;
SELECT CAST(@mymoney_sm AS varchar) AS 'SM_MONEY varchar';
GO
DECLARE @mymoney money;
SET @mymoney = 3148.29;
SELECT CAST(@mymoney AS decimal) AS 'MONEY DECIMAL';
GO
Ниже приводится результирующий набор.
SM_MONEY VARCHAR
------------------------------
3148.29
(1 row(s) affected)
MONEY DECIMAL
----------------------
3148
(1 row(s) affected)
Преобразование данных типов decimal и numeric
Для типов данных decimal и numeric SQL Server обрабатывает каждое конкретное сочетание точности и масштаба как разные типы данных. Например, значения decimal(5,5) и decimal(5,0) считаются разными типами данных.
В инструкциях Transact-SQL константа с десятичным разделителем автоматически преобразуется в значение типа данных numeric с минимально необходимой точностью и масштабом. Например, константа 12 345 преобразуется в значение numeric с точностью 5 и масштабом 3.
Преобразование типа данных decimal или numeric в тип float или real может привести к потере точности. Преобразование типов данных int, smallint, tinyint, float, real, money или smallmoney в тип decimal или numeric может вызвать переполнение.
По умолчанию SQL Server использует округление с потерей точности и масштаба при преобразовании числа в значение decimal или numeric. Однако при включенном (ON) параметре SET ARITHABORT в случае переполнения SQL Server вызывает ошибку. Для возникновения ошибки недостаточно только потери точности и масштаба.
Преобразование данных типа integer
При неявном преобразовании данных типа integer в данные типа character, если число слишком большое для символьного поля, SQL Server вставляет символ с кодом ASCII 42 — звездочку (*).
Целочисленные константы, превышающие 2 147 483 647, преобразуются в тип данных decimal, а не в bigint. Следующий пример демонстрирует изменение типа результата с int на decimal при превышении порогового значения.
SELECT 2147483647 / 2 AS Result1, 2147483649 / 2 AS Result2 ;
Ниже приводится результирующий набор.
Result1 Result2
1073741823 1073741824.500000
Преобразование типов данных с помощью хранимых процедур OLE-автоматизации
Поскольку SQL Server использует типы данных Transact-SQL, а OLE-автоматизация — типы данных Visual Basic, хранимым процедурам OLE-автоматизации приходится преобразовывать данные, которыми они обмениваются.
В следующей таблице описываются преобразования типов данных SQL Server в типы данных Visual Basic.
Тип данных SQL Server |
Тип данных Visual Basic |
|---|---|
char, varchar, text, nvarchar, ntext |
String |
decimal, numeric |
String |
bit |
Boolean |
binary, varbinary, image |
Одномерный массив Byte() |
int |
Long |
smallint |
Integer |
tinyint |
Byte |
float |
Double |
real |
Single |
money, smallmoney |
Currency |
datetime, smalldatetime |
Date |
Все значения NULL |
Тип данных Variant со значением Null |
Все одиночные значения SQL Server преобразуются в одиночные значения Visual Basic, за исключением binary, varbinary и image. В Visual Basic эти значения преобразуются в одномерные массивы Byte(). Этот массив имеет диапазон Byte(от 0 до length1), где length — число байтов в значениях SQL Server binary, varbinary или значениях image.
Ниже приведена таблица преобразования типов данных Visual Basic в типы данных SQL Server.
Тип данных Visual Basic |
Тип данных SQL Server |
|---|---|
Long, Integer, Byte, Boolean, Object |
int |
Double, Single |
float |
Currency |
money |
Date |
datetime |
String длиной 4000 символов или меньше |
varchar/nvarchar |
String длиной более 4000 символов |
text/ntext |
Одномерный массив Byte() размером 8 000 байт или меньше |
varbinary |
Одномерный массив Byte() размером более 8 000 байт |
image |