Преобразование типов данных (компонент 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_variantSQL 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.
Примечание |
---|
Преобразование любого типа данных в binary может различаться в зависимости от версии SQL Server. |
Преобразование в тип данных bit
Преобразование в тип данных bit увеличивает любое ненулевое значение на 1.
Преобразование в символьные данные
При преобразовании символьного выражения в символьный тип данных другой длины значения, слишком длинные для нового типа данных, усекаются.
Если символьное выражение преобразуется в символьное выражение другого типа данных или размера, например из 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 AdventureWorks;
GO
SELECT SalesPersonID,
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 и timeSQL Server отбрасывает все значения, которые не удается распознать как дату или время. Обзор всех типов данных и функций даты и времени в языке Transact-SQL см. в разделе Функции даты и времени (построитель отчетов 1.0).
В следующем примере значения типов 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 AdventureWorks;
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 и numericSQL 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 |