Всякий раз после выхода в свет новой версии SQL Server среди специалистов начинается дискуссия о важных новых средствах, реализованных в пакете. Но эта статья посвящена не принципиальным нововведениям, которые появились в версии SQL Server 2008, а незначительным изменениям, которые, возможно, ускользнули от вашего внимания. Я затрону в ней такие темы, как усовершенствованное предложение VALUES, средства поддержки системы расчета порядкового номера недель ISO, а также новые функции преобразования двоичных и символьных типов.
Предложение VALUES
SQL Server 2008 обеспечивает совместимость с конструктором строчных значений (известным также как конструктор табличных значений table value constructor). Теперь с помощью предложения VALUES можно представлять более чем одну строку. Скорее всего, потребность в таком решении возникнет в ситуации, когда нужно будет вставить в таблицу несколько строк; разумеется, удобнее делать это с помощью одной инструкции INSERT, не повторяя ее несколько раз. Чтобы реализовать такую возможность, сначала выполните код в листинге 1; при этом в базе данных tempdb будет создана таблица Orders (для целей тестирования).
Следующий фрагмент кода показывает, как использовать усовершенствованное предложение VALUES с одной инструкцией INSERT для вставки шести строк:
INSERT INTO dbo.Orders
(orderid, orderdate, empid, custid)
VALUES
(10001, '20090212', 3, 'A'),
(10002, '20090212', 5, 'B'),
(10003, '20090213', 4, 'B'),
(10004, '20090214', 1, 'A'),
(10005, '20090213', 1, 'C'),
(10006, ‘20090215’, 3, ‘C’);
Как мы можем убедиться, каждая пара скобок содержит одну строку, при этом строки отделяются друг от друга запятыми. Очевидно, что в данном случае код становится компактнее, но, кроме этого, такая запись имеет еще одно достоинство: вся операция рассматривается как неделимая. Если какая-либо из строк по той или иной причине не попадет в целевую таблицу, вся инструкция INSERT не будет выполнена.
До выпуска SQL Server 2008 аналогичный результат можно было получить, выполнив операцию UNION ALL между несколькими инструкциями SELECT на основе констант следующим образом:
INSERT INTO dbo.Orders
(orderid, orderdate, empid, custid)
SELECT 10001, '20090212', 3, 'A'
UNION ALL SELECT 10002, '20090212', 5, 'B'
UNION ALL SELECT 10003, '20090213', 4, 'B'
UNION ALL SELECT 10004, '20090214', 1, 'A'
UNION ALL SELECT 10005, '20090213', 1, 'C'
UNION ALL SELECT 10006, '20090215', 3, 'C';
Отметим, что в современной реализации системы ее внутренние механизмы обрабатывают усовершенствованное предложение VALUES так же, как представленное выше решение на базе операции UNION ALL, поэтому не ожидайте какого-либо повышения быстродействия. Одно из преимуществ нового подхода состоит в том, что предложение VALUES является стандартным, тогда как решение на основе операции UNION ALL не считается таковым, поскольку запросы в нем не имеют предложений FROM. Можно лишь надеяться, что в одной из следующих версий SQL Server предложение VALUES будет обеспечивать более высокую производительность по сравнению с нынешним способом обработки.
Обратите внимание на то, что конструктор табличных значений можно использовать не только с инструкцией INSERT. Этот инструмент также применяется для создания производных таблиц, запросы к которым формируются в предложении FROM внешнего запроса, следующим образом:
SELECT *
FROM (VALUES
(10001, '20090212', 3, 'A'),
(10002, '20090212', 5, 'B'),
(10003, '20090213', 4, 'B'),
(10004, '20090214', 1, 'A'),
(10005, '20090213', 1, 'C'),
(10006, '20090215', 3, 'C'))
AS O (orderid, orderdate, empid, custid);
В табл. 1 представлены результаты выполнения данного запроса.
Я использую эту возможность всякий раз, когда приходится создавать небольшую вспомогательную таблицу из констант для целей запроса. Теперь для создания такой таблицы нет необходимости формировать временную таблицу или создавать табличную переменную либо прибегать к использованию нестандартного приема UNION ALL. Но средства определения общих табличных выражений (Сommon Table Expression, CTE) на основе конструктора табличных значений все еще не реализованы. На сегодня описанный прием можно использовать только применительно к производным таблицам.
ISO week
ISO week — это система расчета порядковых номеров недель, применяемая главным образом в европейских странах. Первый день недели всегда понедельник. Всем дням одной недели присваивается один и тот же номер недели, даже если неделя захватывает часть одного года и часть другого (т. е. начинается в декабре и заканчивается в январе). Если неделя переходит на другой год, ей присваивается либо последний номер недели по прошлому году (т. е. это будет неделя номер 52 или 53), либо первый номер в следующем году; это зависит от того, на какой год выпадает четверг данной недели. Возьмем, к примеру, неделю, которая начинается в понедельник 29 декабря 2008 года и заканчивается в воскресенье 4 января 2009 года. Четверг этой недели приходится на 2009 год (1 января), поэтому всем дням данной недели присваивается номер недели 1.
В системе SQL Server 2008 реализован простой и эффективный способ расчета номера недели ISO. В функции DATEPART появился новый компонент ISO_WEEK. В следующем фрагменте кода показан пример расчета номера недели ISO с помощью функции DATEPART для набора введенных дат:
SELECT
CAST (dt AS DATE) AS dt,
DATEPART (ISO_WEEK, dt) AS weeknum,
DATENAME (weekday, dt) AS weekday
FROM (VALUES
('20081228'),
('20081229'),
('20081230'),
('20081231'),
('20090101'),
('20090102'),
('20090103'),
('20090104'),
(‘20090105’)) AS D (dt);
В табл. 2 представлены результаты выполнения этого кода.
До выхода в свет SQL Server 2008 для определения номера недели ISO по заданной дате пользователю приходилось проводить собственные манипуляции. Так, в листинге 2 представлено определение предназначенной для расчета номера недели ISO скалярной пользовательской функции UDF, взятой из раздела CREATE FUNCTION электронной документации Books Online системы SQL Server 2005.
Данное определение функции весьма запутанное. Кроме того, при выполнении скалярных функций UDF, когда они запускаются по строкам таблицы, резко снижается производительность системы. Следующий фрагмент кода показывает, как эта функция применяется к набору дат:
SET DATEFIRST 1;
SELECT
AST (dt AS DATETIME) AS dt,
dbo. ISOweek (dt) AS weeknum,
DATENAME (weekday, dt) AS weekday
FROM ( SELECT '20081228'
UNION ALL SELECT '20081229'
UNION ALL SELECT '20081230'
UNION ALL SELECT '20081231'
UNION ALL SELECT '20090101'
UNION ALL SELECT '20090102'
UNION ALL SELECT '20090103'
UNION ALL SELECT '20090104'
UNION ALL SELECT '20090105' )
AS D (dt);
Преобразование двоичных значений в символьные
Еще одно реализованное в SQL Server 2008 непринципиальное усовершенствование касается преобразования двоичных значений в символьные. В случае использования функций CONVERT или CAST для преобразования двоичных данных в символы и обратно исходные и целевые значения выражаются неодинаковыми символами. Пример: в результате преобразования в двоичный формат символьной строки ‘1A‘ обычно получается двоичное представление этих символов: 0x3141. Подобным же образом в результате преобразования двоичной строки 0x3141 в символьный формат образуются символы, представляемые упомянутыми двоичными данными, а именно ‘1A’.
В определенных случаях возникает необходимость преобразования двоичных данных в символьные или символьных в двоичные таким образом, чтобы каждая цифра исходного значения была представлена той же цифрой и в целевом значении. Например, строка ‘1A’ (или ‘0x1A’ с префиксом 0x) преобразуется в двоичное значение с этими шестнадцатеричными цифрами: 0x1A а 0x1A будет преобразовываться в строку ‘1A’ (или ‘0x1A’ с префиксом 0x). Эта возможность иногда бывает полезной при импорте данных.
SQL Server 2008 реализует простое и эффективное решение в форме новых чисел с использованием стилей в функции CONVERT. Style 0 представляет применяемую по умолчанию реакцию SQL Server 2008. Style 1 следует использовать, когда символьная строка имеет префикс 0x, а style 2 — когда строка не имеет такого префикса. Рассмотрим в качестве примера следующий фрагмент кода, иллюстрирующий преобразование двоичного значения в строку символов с помощью обоих стилей:
SELECT
CONVERT (VARCHAR (20), 0x4775696E6E657373, 1) AS
bin_to_char_with_0x_prefix,
CONVERT (VARCHAR (20), 0x4775696E6E657373, 2) AS
bin_to_char_no_prefix;
В табл. 3 представлен результат выполнения этого кода.
Следующий фрагмент иллюстрирует преобразование символьных строк в двоичные значения с использованием обоих стилей:
SELECT
CONVERT (VARBINARY (10), '0x4775696E6E657373', 1)
AS char_with_0x_prefix_to_bin,
CONVERT (VARBINARY (10), '4775696E6E657373', 2)
AS char_no_prefix_to_bin;
В табл. 4 представлен результат выполнения данного кода.
Подобные результаты можно было получать и до выпуска SQL Server 2008, но тогда приходилось задействовать скалярные функции UDF, так что на решение уходило гораздо больше времени. В системах SQL Server 2005 и SQL Server 2008 реализована встроенная UDF с именем fn_varbintohexstr, которая осуществляет преобразование двоичных данных в символьные. Вот пример использования упомянутой функции:
SELECT sys.fn_varbintohexstr (0 x4775696 E6 E657373);
Внутренний механизм решения таков. Упомянутая функция вызывает более универсальную функцию fn_varbintohexsubstring. Последняя принимает четыре аргумента. Первый из них указывает, следует (1) или не следует (0) включать в выходные данные префикс 0x. Второй аргумент представляет собой двоичное значение. Третий аргумент определяет, на каком байте нужно начинать извлечение подстроки (для указания на начало используйте 1), а четвертый определяет, сколько байтов нужно принимать во внимание (0 означает все). Таким образом, приведенный выше вызов функции fn_varbintohexstr эквивалентен следующему вызову функции fn_varbintohexsubstring:
SELECT sys.fn_varbintohexsubstring (1, 0x4775696E6E657373, 1, 0);
Если вас интересует определение функции на языке T-SQL, выполните следующий фрагмент кода:
SELECT OBJECT_DEFINITION (OBJECT_ID ('sys.fn_varbintohexsubstring'));
Как мы видим, функция довольно длинная и запутанная. Если вам приходится совершать подобные преобразования, вы сумеете по достоинству оценить простоту, изящество и эффективность нового решения с помощью функции CONVERT. Системы SQL Server, выпускавшиеся до выхода в свет версии 2008, не предоставляли возможности для преобразования в противоположном направлении, а именно — из символьного формата в двоичный. Если у вас не возникает необходимости реализовывать эту логику в функции и вам приходится выполнять операцию на одном значении, можете использовать замечательный трюк, который мне показал Рон Толмадж. Нужно просто сформировать динамический пакет, в котором следует объединить в цепочку символьное представление двоичного значения как часть кода в виде присвоения бинарного значения параметру:
Char to Bin pre-2008
Using dynamic SQL
DECLARE
@char AS NVARCHAR (20),
@bin AS VARBINARY (10),
@sql AS NVARCHAR (500);
SET @char = N'0 x4775696 E6 E657373';
SET @sql = N'SET @result = ' + @char + N';'
EXEC sp_executesql
@stmt = @sql,
@params = N'@result AS VARBINARY (10) OUTPUT',
@result = @bin OUTPUT;
SELECT @bin;
Но если вы хотите иметь возможность выполнять преобразование в запросе к таблице, придется реализовать его в виде UDF. Для этой цели можно использовать функцию fn_chartobin, представленную в листинге 3.
Функция извлекает из входной строки по паре цифр за один раз (поскольку каждая пара представляет байт), формирует соответствующий двоичный байт и присоединяет его к результирующей двоичной строке. Функция может принимать эти входные данные с префиксом 0 x или без такового. Для тестирования данной функции выполните следующий код:
SELECT dbo.fn_chartobin (‘0x4775696E6E657373’);
Результатом будет двоичное значение 0x4775696E6E657373.
Не проходите мимо
Некоторые усовершенствования языка T-SQL, реализованные в SQL Server 2008, могут остаться незамеченными — прежде всего потому, что их «раскрутке» Microsoft не уделяет особого внимания. Три непринципиальных усовершенствования, которые могут оказаться полезными, включают усовершенствованное предложение VALUES, доработанную функцию для расчета порядкового номера недель ISO, а также функцию преобразования символьных значений в двоичные и обратно при сохранении шестнадцатеричных цифр. В следующих статьях я расскажу о ряде других функций SQL Server 2008, которые тоже могут пригодиться.
Ицик Бен-Ган (itzik@solidqualitylearning.com) — преподаватель в Solid Quality Learning. Читает лекции и консультирует на международном уровне. Является управляющим израильской группы пользователей SQL Server. MVP по SQL Server
Код для создания определяемой пользователем функции fn_chartobin