21 сентября 2022 г. Itzik Ben-Gan
В практике использования T-SQL не характерна манипуляция данными на битовом уровне, но иногда приходится сталкиваться с такой необходимостью. Можно встретить решения, в которых набор флагов (да/нет, вкл/выкл, истина/ложь) хранятся в одной целочисленной или двоичной колонке, где каждый бит представляет отдельный флаг. Одним из примеров является использование битового представления набора разрешений пользователя/роли. Другой пример — использование битового представления набора настроек, включенных или отключенных в данной среде. Даже SQL Server хранит некоторые данные на основе флагов, используя битовое представление. Пример:
Кэширование атрибут плана set_options и required_cursor_options, которые извлекаются с помощью sys.dm_exec_plan_attributes.
Запрос текущего состояния параметров сеанса с помощью функции @@OPTIONS
Настройка параметра конфигурации сервера «user options» с использованием битового представления.
Функция, связанная с триггером COLUMNS_UPDATED , которая указывает, какие столбцы были вставлены или обновлены.
Функция GROUPING_ID, которая создает битовое представление текущего набора группировок.
Манипуляции с битами могут включать в себя проверку того, включен или выключен определенный флаг, установка или снятие определенного флага, проверка того, какие флаги включены, подсчет количества включенных флагов, сдвиг битов влево или вправо и многое другое.
До SQL Server 2022 в T-SQL была минимальная поддержка манипулирования битами. Имелась поддержка следующих операторов:
Некоторые из вышеупомянутых задач манипуляции битами можно решить путём комбинации этих операторов и математических выражений. Но подобные манипуляции ограничены и часто приводят к усложнению логике.
SQL Server 2022 представляет набор новых функций и операторов для работы с битами, предназначенных для расширения этих возможностей, а также упрощает их и делает более интуитивно понятными. Ниже перечислены новые функции и операторы:
Далее будет приведено их краткое описание. Для получения полной технической информации представлены ссылки на официальную документацию. В этой статье мы сосредоточимся на том, как эти функции улучшают и упрощают задачи, которые раньше было труднее решить.
GET_BIT и SET_BIT
Функция GET_BIT извлекает бит из переданного ей значения с заданным битовым смещением. Эта функция имеет следующий синтаксис:
GET_BIT (входное значение, битовое смещение)Входное значение может быть целым числом (integer) или двоичным типом (binary, не LOB). Битовое смещение начинается с 0 и представляет собой смещение бит справа налево. Таким образом, вы указываете 0 для первого (самого правого) бита, 1 для второго, …, 31 для тридцать второго и так далее. Возвращаемое значение имеет тип BIT. Это 1, если запрошенный бит установлен, и 0, если он не установлен.
Например, параметр сеанса ARITHABORT представлен смещением бит по битовой маске равным 6, и может быть получен с помощью функции @@options. Используйте следующий ниже сценарий, чтобы проверить статус этой опции в вашем сеансе:
SELECT GET_BIT( @@OPTIONS, 6 );Если параметр ARITHABORT в сеансе был включен, получится следующий вывод:
-----
1Функция SET_BIT возвращает обработанное входное значение, где бит в указанном смещении устанавливается в 1 по умолчанию или в указанное битовое состояние (1 или 0), если указано. Эта функция имеет следующий синтаксис:
SET_BIT (входное значение, битовое смещение [, битовое состояние])Поддерживаемые типы такие же как и для функции GET_BIT. Ввод битового состояния может быть битового или целочисленного типа (но он тогда должен быть 1 или 0).
Например, следующий код возвращает как текущее состояние @@OPTIONS, так и измененное состояние с отключенным параметром ARITHABORT:
SELECT @@OPTIONS AS cursession, SET_BIT( @@OPTIONS, 6, 0 ) AS cursession_ARITHABORT_off;Результат выглядит так:
cursession cursession_ARITHABORT_off
----------- -------------------------
5496 5432Поскольку это неочевидно, важно отметить что использование функции SET_BIT никоим образом не изменяет входное значение, скорее функция просто возвращает измененное значение на основе введённых параметров.
BIT_COUNT
Функция BIT_COUNT подсчитывает количество бит, установленных во входном значении. Эта функция имеет следующий синтаксис:
BIT_COUNT(входное значение)Входное значение может быть целым числом или двоичным типом, не LOB. Результат имеет тип BIGINT.
Например, следующий запрос подсчитывает количество параметров сеанса, установленных в текущем сеансе:
SELECT BIT_COUNT ( @@OPTIONS ) ;Этот сценарий возвращает следующую информацию сеансе:
--------------------
7LEFT_SHIFT и RIGHT_SHIFT
Функция LEFT_SHIFT, также как оператор <<, сдвигает биты входного значения на указанное количество битов влево, заполняя свободные биты нулями. Функция и оператор имеют следующий синтаксис:
LEFT_SHIFT(входное значение, числовые значения)
входное значение << числовое значениеКак и в предыдущих функция, входное значение двоичного типа, не должно быть LOB. Тип выводимого значения такой же, как и на входе. Количество битовых сдвигов задаётся целым числом.
Функция RIGHT_SHIFT, также как оператор >> , осуществляет побитный сдвиг вправо для входного значения на указанное число бит, заполняя свободные биты нулями. Обратите внимание на то, что тут имеется ввиду логический сдвиг (logical shift) вправо , а не арифметический сдвиг (arithmetic shift) вправо, при котором свободные биты получают исходное значение старшего бита (MSB). T-SQL не поддерживает арифметический сдвиг вправо. Функция и оператор имеют следующий синтаксис:
RIGHT_SHIFT(входное значение, числовое значение)
числовое значение >> числовое значениеВ обеих функциях отбрасываются биты, которые переполняются (исходно, крайние левые биты при сдвиге влево или крайние правые биты при сдвиге вправо). Если вы укажете отрицательное целое число для сдвига, SQL Server преобразует его в противоположную функцию с положительным значением. Таким образом, x >> -1 преобразуется в x << 1.
В следующем примере биты числового значения 10 сдвигаются на один бит вправо и на один бит влево:
SELECT 10 >> 1 AS rightshift, 10 << 1 AS leftshift;Этот сценарий выдаст следующий результат:
rightshift leftshift
----------- -----------
5 20Представление INT-значения 10 в виде битовой маски выглядит следующим образом:
0000 0000 0000 0000 0000 0000 0000 1010Сдвиг на один бит вправо приводит к следующему:
0000 0000 0000 0000 0000 0000 0000 0101Это значение целого числа 5.
Сдвиг на один бит влево приводит к следующему:
0000 0000 0000 0000 0000 0000 0001 0100Это значение целого числа 20.
Логический битовый сдвиг используется иногда как эффективный низкоуровневый способ деления или умножения целых чисел без знака на степени двойки. Например, сдвиг на величину, заданную значением val вправо на n бит дает val/2^n, а сдвиг для val влево на n бит дает val*2^n.
Скорее всего, для таких целей вы не будете использовать в T-SQL подобную экзотику с побитовыми сдвигами.
Более вероятно использование сдвига в существующих битовых масках из-за введения новых флагов и необходимости использовать крайние правые биты для новых флагов. Например, предположим, что вы храните информацию о разрешениях пользователя и роли в виде последовательности битов в столбце с именем perms BINARY(25) в таблице dbo.UserRolePerms. Столбец может хранить до 200 разрешений, но в настоящее время 80 битов справа (смещения от 0 до 79) используются для представления 80 разрешений. В какой-то момент жизни приложения добавляются 10 новых разрешений. По какой-то необъяснимой причине принимается решение для новых разрешений использовать 10 самых правых бит (смещения от 0 до 9). Это означает, что вам придётся сдвинуть существующую информацию о разрешениях на 10 бит влево.
UPDATE dbo.UserRolePerms
SET perms = perms << 10;Решение задач манипуляции битами теперь проще и интуитивно понятнее
Чтобы по-настоящему оценить преимущества новых функций управления битами, достаточно попробовать выполнить аналогичные задачи без них.
Получение состояния бита по заданному смещению или его установка
Давайте вначале обойдёмся без новых функций и составим выражение, извлекающее состояния указанного бита, используя заданное значение @val и битовое смещение @offset. Техника, которую обычно используют для решения этой задачи, зависит от типа данных @val и от того, предоставляются ли определенные гарантии, о которых будет сказано ниже.
Тип данных TINYINT в SQL Server представляет собой целое число без знака, а SMALLINT, INT и BIGINT представляют собой целые числа со знаком, представленные с использованием нотации «дополнением двоек» (two’s complement). Если @val имеет тип TINYINT, SMALLINT, INT или BIGINT с гарантией, что крайний левый бит не используется — вы можете получить значение бит (значение только с указанным битовым смещением, установленным в то же состояние как и на входе @val) с помощью выражения @val & POWER(2, @offset), а состояние бита (1 или 0) с помощью выражения SIGN(@val & POWER(2, @offset)).
Вот пример:
DECLARE @val AS INT = 5496, @offset AS INT = 6;
SELECT
@val & POWER(2, @offset) AS bitval,
SIGN(@val & POWER(2, @offset)) AS bitstate;Этот код возвращает следующее:
bitval bitstate
----------- -----------
64 1Но что если мы имеем дело с целочисленным типом со знаком для @val и самого левого бита? Оба следующих вычисления просто некорректны для крайнего левого бита, не говоря уже о том, что код завершается с ошибкой переполнения:
DECLARE @val AS INT = 5496, @offset AS INT = 31;
SELECT
@val & POWER(2, @offset) AS bitval,
SIGN(@val & POWER(2, @offset)) AS bitstate;Msg 232, Level 16, State 3, Line 3
Arithmetic overflow error for type int, value = 2147483648.000000.
Конечно можно добавить логику для обработки особого случая по-другому, но это усложнит код.
А что, если @val имеет бинарный тип BINARY(25)? Придумать обходное решение для подобной проблемы будет гораздо сложнее.
Зато с использованием новых функции всё становиться просто. Чтобы получить битовое состояние, используйте функцию GET_BIT с @val на входе и смещение @offset без каких-либо специальных манипуляций. Вот пример со значением типа INT в качестве входных данных:
DECLARE @val AS INT = 5496, @offset AS INT = 31;
SELECT GET_BIT( @val, @offset );Этот запрос генерирует вывод, указывающий, что бит по указанному смещению выключен:
-----
0Вот пример с BINARY(25):
DECLARE @val AS BINARY(25) = 0x9B3B9D5AD3084AB1A522082491B8519C008AB4AACB7064B037,
@offset AS INT = 100;
SELECT GET_BIT( @val, @offset );Результат исполнения этого запроса говорит, что бит по заданному смещению включен:
-----
1Чтобы получить значение бита, вы можете использовать функцию SET_BIT, при этом первым параметром будет значение со всеми обнуленными битами, второй параметр — @@offset, а третий — состояние, возвращаемое GET_BIT. Вот пример с целым числом:
DECLARE @val AS INT = 5496, @offset AS INT = 6;
SELECT SET_BIT(0, @offset, GET_BIT( @val, @offset ));Этот запрос возвращает следующий результат:
-----------
64А вот пример с вводом BINARY(25):
DECLARE @val AS BINARY(25) = 0x9B3B9D5AD3084AB1A522082491B8519C008AB4AACB7064B037,
@offset AS INT = 100;
SELECT SET_BIT(CAST(0x AS BINARY(25)), @offset, GET_BIT( @val, @offset ));В результате получим:
-----------------------------------------------------
0x000000000000000000000000100000000000000000000000000Естественно, вы столкнетесь с подобными сложностями и при необходимости установить бит в нужное значение. Попробуйте сделать это с особыми случаями и бинарными типами, подобными примерам выше. Всё будет легче начиная с SQL Server 2022, где можно просто использовать функцию SET_BIT.
Подсчет бит
Давайте теперь попробуем посчитать количества бит, установленных в указанном значении. Опять же, если это целое число со знаком, и вы уверены, что крайний левый бит не используется, вы можете использовать таблицу чисел, умеющий работать с битами оператор AND (&) и агрегат COUNT.
Вот скрипт создания функции GetNums, которая возвращает последовательность целых чисел в запрошенном диапазоне:
CREATE FUNCTION dbo.GetNums(@low AS BIGINT = 1, @high AS BIGINT)
RETURNS TABLE
AS
RETURN
WITH
L0 AS ( SELECT 1 AS c
FROM (VALUES(1),(1),(1),(1),(1),(1),(1),(1),
(1),(1),(1),(1),(1),(1),(1),(1)) AS D(c) ),
L1 AS ( SELECT 1 AS c FROM L0 AS A CROSS JOIN L0 AS B ),
L2 AS ( SELECT 1 AS c FROM L1 AS A CROSS JOIN L1 AS B ),
L3 AS ( SELECT 1 AS c FROM L2 AS A CROSS JOIN L2 AS B ),
Nums AS ( SELECT ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) AS rownum
FROM L3 )
SELECT TOP(@high - @low + 1)
rownum AS rn,
@high + 1 - rownum AS op,
@low - 1 + rownum AS n
FROM Nums
ORDER BY rownum;
GOА вот сценарий для подсчета бит, установленных для входного значения @val с типом INT, при условии, что крайний левый бит не используется:
DECLARE @val AS INT = 5496;
SELECT COUNT(*) AS bitcount
FROM dbo.GetNums(0, 30) AS N
WHERE @val & POWER(2, N.n) <> 0;Получаем:
bitcount
-----------
7Понятно, что подобные вычисления обойдутся не дешево. Более того, если вы используете крайний левый бит или переменная на входе будет двоичного типа, решение становится ещё более сложным. Благодаря новым функциям в SQL Server 2022 вы просто используете функцию BIT_COUNT:
DECLARE @intval AS INT = 5496 ,
@binval AS BINARY ( 25 ) = 0x9B3B9D5AD3084AB1A522082491B8519C008AB4AACB7064B037 ;
SELECT BIT_COUNTl) AS cntintval, BIT_COUNTl) AS cntbinval;В результате:
cntintval cntbinval
-------------------- --------------------
7 85Битовый сдвиг
Теперь рассмотрим сдвиг бит. Предположим, вам даны @val и @offset, и нужно сдвинуть вправо или влево биты в @val на основе смещения @offset.
Для сдвига вправо, если @val является неотрицательным значением c типом целого числа со знаком (например INT), вы можете использовать выражение @val / POWER(2, @offset).
Для сдвига влево, если @val является неотрицательным значением c типом целого числа со знаком (например INT), и вы уверены, что крайние левые биты @offset + 1 в настоящее время не используются (равны 0), вы можете использовать выражение @val * POWER(2 , @offset). Во всех остальных случаях, в том числе, когда вышеуказанные гарантии не предоставляются или, когда @val имеет бинарный тип, вам понадобятся более сложные решения.
С новыми функциями и операторами вы просто подставляете входные данные без каких-либо манипуляций:
DECLARE @val AS INT = -1, @offset AS INT = 1;
SELECT @val >> @offset AS rightshift, @val << @offset AS leftshift;Получаем:
rightshift leftshift
----------- -----------
2147483647 -2Значение -1 с типом INT имеет следующее битовое представление:
1111 1111 1111 1111 1111 1111 1111 1111Сдвиг вправо на один бит приводит к следующему:
0111 1111 1111 1111 1111 1111 1111 1111Это целочисленное значение 2147483647.
Сдвиг влево на один бит приводит к следующему:
1111 1111 1111 1111 1111 1111 1111 1110Это целочисленное значение -2.
Вывод
Работа с битами не является обычной задачей для T-SQL, но она нужна для некоторых встроенных функций SQL Server и может быть уместна в некоторых пользовательских приложениях. До SQL Server 2022 в T-SQL были ограниченные возможности манипуляции битами, что в некоторых случаях требовало от вас написания сложного кода с математической акробатикой. В SQL Server 2022 представлены пять новых функций и два альтернативных оператора, обеспечивающие более простую и интуитивно понятную работу с битами.
Чего по-прежнему не хватает в T-SQL в этой области, так это агрегированных битовых функций, выполняющих агрегированные операции с битами AND, OR и XOR. Oracle, например, поддерживает такие функции. В качестве примера использования таких функций предположим, что пользователь является членом нескольких ролей, и разрешения каждой роли хранятся с использованием битовой маски, аналогично примеру, который использовался в начале статьи. Действующие разрешения пользователя представляют собой совокупность побитового ИЛИ битовых масок разрешений всех ролей, членом которых является пользователь.
В любом случае приятно видеть, как T-SQL продолжает развиваться и расширять функциональные возможности.
