SQL-Ex blog

Как это делалось

Например, вы хотите найти все базы данных на чтение-запись, используя системные объекты SQL Server 2022 (я думаю, что фактически это началось с 2005). Вы можете написать:

SELECT name

FROM sys.databases

WHERE is_read_only = 0

  AND name IN ('master','msdb','tempdb');

Этот запрос вернет те же три системные базы данных (поскольку они всегда будут находиться в режиме чтение-запись). При использовании старого объекта sysdatabases имелся только столбец status.

SELECT name, status

FROM   sys.sysdatabases

WHERE  name IN ('master','msdb','tempdb');

На моем компьютере и, весьма вероятно, у вас вывод этого запроса будет такой:

name           status

-------------- -----------

master         65544

msdb           65544

tempdb         65544

Я ожидаю, что 99% людей, читающих это, вероятно, подумают, что должна быть таблица статусов, содержащая значения status. Видя, что это число, кратное 2, вы может быть попали в тот 1%, которые подумают, что это битовая маска. Но если у вас не эйдетическая память, вы, вероятно, не знаете, что означают все эти биты.

Битовая маска является типом денормализации значений, когда вместо набора столбцов со значениями "включено" или "выключено" (без NULL) вы кодируете его следующим образом:

00000101

Теперь пользователь должен выяснить значение каждого из 8 битов в целочисленном/двоичном значении. Что касается статуса (status), то вы можете узнать, находится ли база данных в режиме только на чтение, используя десятую позицию. В версиях до 2022 этого можно добиться следующим образом:

SELECT name, status & 1024 AS is_readonly

FROM   sys.sysdatabases

WHERE  name IN ('master','msdb','tempdb');

Смысл этого в том, что если десятый бит в числе установлен в 1, то оно AND (&) значение вернет 1024 (поскольку оно вернет 1024 - что есть POWER(2,10)), и, поскольку это так, мы получаем 0 (не только на чтение). (Полный список битов можно найти здесь.)

Проверьте следующие 2 запроса, которые дают совпадающие результаты:

SELECT name

FROM   sys.databases

WHERE  page_verify_option_desc = 'CHECKSUM'

  AND  name IN ('master','msdb','tempdb');

 

SELECT name

FROM   sys.sysdatabases

WHERE  status & 65536 = 65536 

 AND name IN ('master','msdb','tempdb');

Оба запроса возвращают три строки. Хорошо, это основы, теперь давайте взглянем на несколько новый синтаксис, который делает это легче. Возьмем, например, значение 65536. Это 2 в 16-ой степени:

SELECT POWER(2,16); -- возвращает 65536

В SQL Server 2022 имеется 5 новых побитовых функций, которые имеют то же значение. Например, выяснение, установлен ли бит, стало намного проще. Вот эти функции:

• GET_BIT - проверить, установлен ли бит в двоичном значении


• SET_BIT - установить бит в двоичном значении и вернуть новое значение


• LEFT_SHIFT, RIGHT_SHIFT - сдвиг всех битов влево или вправо в двоичном значении


• BIT_COUNT - подсчитывает число установленных битов в значении

Наш предыдущий запрос статуса и контрольной суммы можно переписать так:

SELECT name, GET_BIT(status,10) AS is_readonly,

			 GET_BIT(status,16) AS is_checksum

FROM   sys.sysdatabases

WHERE  name IN ('master','msdb','tempdb');

Результат запроса:

name         is_readonly is_checksum

------------ -----------  -----------

master                 0            1

msdb                   0            1

tempdb                 0            1

Хотя я ВСЕ ЕЩЕ не собираюсь благословлять побитовое решение в 2022 году (тем более в 2000 году), это делает работу с ним гораздо менее неприятной.

Остальные побитовые функции менее полезны в тех решениях, которые я когда-либо видел, но они интересны. Например:

DECLARE @value VARBINARY(64) = 0;

SET @value = SET_BIT(@value,10);

SELECT @value 

Вот результат:

0x00000400

Это является двоичным значением для 1024 (преобразуйте значение к int, и вы увидите это). Заметим, что хотя биты могут технически нумероваться внутри SQL Server слева, побитовые операторы рассматривают значения, как если бы он нумеровались справа (так как это соответствует нашей обычной системе нумерации). Давайте теперь установим биты 1, 2, 3 и 4, а затем выполним побитовый сдвиг вправо:

SELECT SET_BIT(SET_BIT(SET_BIT(SET_BIT(0,1),2),3),4);

Выводится 30..., что может вызвать у вас недоумение (и у меня!), так же есть бит 0. Итак, 2 + 4 + 8 + 16 = 30.

Сдвигая 30 вправо на один разряд:

SELECT RIGHT_SHIFT(30,1);

Выводится 15, что складывается из 1 + 2 + 4 + 8. Давайте сдвинем еще раз:

SELECT RIGHT_SHIFT(30,2);

Это аналогично RIGHT_SHIFT(RIGHT_SHIFT(30,1),1) и возвращает 7. Биты отваливаются в сторону, так, например:

SELECT LEFT_SHIFT(RIGHT_SHIFT(30,100),100);

Возвращает 0, поскольку первый набор операторов RIGHT_SHIFT выталкивает биты за пределы разрядов. Поменяйте вызовы местами:

SELECT RIGHT_SHIFT(LEFT_SHIFT(30,100),100);

И, погодите... они все еще выпадают и возвращается 0. Почему? Поскольку это технически 31-битовое целое (знак занимает бит), поэтому вы теряете биты так или иначе. Итак, будьте внимательны, сдвигая биты!

Наконец, давайте рассмотрим функцию BIT_COUNT. Используя значение 30, которое, как мы знаем, составляют 2 + 4 + 8 + 16. Затем выполните следующее:

SELECT BIT_COUNT(30);

И вы увидите, что запрос вернет 4, означающее, что 4 бита равны 1.

Это скорее всего та информация, которую вы абсолютно никогда не сможете использовать по назначению, но чем больше вы узнаёте, тем больше она вам может когда-нибудь понадобиться!