A packed flags column is easier to read when the SQL says which bit it needs. SQL Server 2022 added BIT_COUNT, GET_BIT, SET_BIT, LEFT_SHIFT, and RIGHT_SHIFT to make those operations explicit.
Define the Bits Before Using Them
Choose one meaning per position. In this example, position zero permits reading, one permits writing, two permits exporting, and three represents administration. Position zero is the least significant bit. The stored integer combines the enabled positions.
A value of one enables only reading. A value of three combines reading and writing. Those are defined teaching inputs, not a statement about anyone's actual permissions. The application still needs its complete authorization model. A single mask does not supply identity, ownership, or row-level security.
I keep the bit definitions beside the schema. Without that map, a query checking the fourth bit becomes a small act of archaeology. Use a scratch session for these examples. The constraint permits only the four defined bits and prevents negative masks or unexplained higher positions.
CREATE TABLE #FeatureFlags
(
ProfileID int NOT NULL PRIMARY KEY,
Flags int NOT NULL CHECK (Flags BETWEEN 0 AND 15)
);
INSERT #FeatureFlags VALUES (10, 1), (20, 3), (30, 5), (40, 15), (50, 0);
SELECT ProfileID, Flags FROM #FeatureFlags ORDER BY ProfileID;Read One Position With GET_BIT
GET_BIT takes the mask and a zero-based offset. It returns the bit at that offset, so the result expresses an enabled or disabled position directly. Do not confuse a zero-based offset with the numeric mask value used by the older bitwise operator.
For export, offset two corresponds to numeric mask four. The function's offset is two, not four. That distinction removes a common off-by-one or wrong-mask mistake. Keep literal offsets documented or use reviewed constants where the surrounding code defines the meanings.
The next query makes each chosen position visible. NULL masks would introduce unknown values rather than a clean disabled state. This example uses NOT NULL deliberately. If missing settings are valid in your system, define what a null mask means before converting it to zero.
SELECT ProfileID, Flags,
GET_BIT(Flags, 0) AS CanRead,
GET_BIT(Flags, 1) AS CanWrite,
GET_BIT(Flags, 2) AS CanExport,
GET_BIT(Flags, 3) AS IsAdministrator
FROM #FeatureFlags
ORDER BY ProfileID;Set or Clear Exactly One Bit
SET_BIT enables the chosen position by default. Its optional third argument supplies zero to clear it or one to set it. The remaining positions retain their values. That is safer to read than assigning a new complete mask when only one setting is changing.
The sample grants export to ProfileID 20 and removes administration from ProfileID 40. Each UPDATE has an explicit key predicate. Review both the selected profile and the intended offset before running a real permission change. A readable function does not compensate for targeting the wrong identity.
I check the resulting individual bits after a mask change. The numeric value alone is a weak review aid when several flags are enabled. If the application imposes combinations, such as export requiring read permission, enforce those business rules separately from the mechanics of setting one bit.
UPDATE #FeatureFlags
SET Flags = SET_BIT(Flags, 2, 1)
WHERE ProfileID = 20;
UPDATE #FeatureFlags
SET Flags = SET_BIT(Flags, 3, 0)
WHERE ProfileID = 40;
SELECT ProfileID, Flags, GET_BIT(Flags, 2) AS CanExport,
GET_BIT(Flags, 3) AS IsAdministrator
FROM #FeatureFlags
ORDER BY ProfileID;Count Enabled Positions With BIT_COUNT
BIT_COUNT returns how many positions contain one bits. It does not return the greatest offset or identify which permission is enabled. A count of two can describe read and write, read and export, or another combination. Keep the count's purpose separate from individual authorization checks.
Use it for reporting how many selected settings are enabled, or for validating a mask that permits exactly one selected option. For a mask containing unrelated categories, apply a reviewed category mask first. Counting every enabled position then claiming they are all permissions can mislabel the data.
The result also depends on the source type's width and bit representation. Negative signed values have enabled high bits in their two's-complement representation. This teaching column excludes them. Keep types consistent when comparing masks rather than changing width through an implicit conversion.
SELECT ProfileID, Flags, BIT_COUNT(Flags) AS EnabledFlagCount,
BIT_COUNT(Flags & 7) AS EnabledReadWriteExportCount
FROM #FeatureFlags
ORDER BY EnabledFlagCount DESC, ProfileID;
SELECT ProfileID, Flags
FROM #FeatureFlags
WHERE GET_BIT(Flags, 2) = 1
ORDER BY ProfileID;
Shift a Mask Without Repeated Arithmetic
LEFT_SHIFT moves bits toward higher positions. RIGHT_SHIFT moves them toward lower positions. Positions shifted outside the operand's width are lost, and newly exposed positions are filled with zeros. These functions manipulate the bit pattern rather than preserving a number's business meaning.
Start with an explicitly typed integer one to generate a mask for a permitted offset. Match that type to the column. The example restricts offsets to zero through thirty so the generated mask remains a positive signed int value. A different width needs its own approved range.
Do not use a shift as an unchecked substitute for multiplication or division on arbitrary signed business values. It discards bits and has logical bit semantics. Use ordinary arithmetic when arithmetic is the actual requirement.
DECLARE @BitPosition int = 2;
IF @BitPosition NOT BETWEEN 0 AND 30
THROW 50000, 'Choose a supported positive int mask position.', 1;
DECLARE @Mask int = LEFT_SHIFT(CONVERT(int, 1), @BitPosition);
SELECT @Mask AS ExportMask,
RIGHT_SHIFT(@Mask, @BitPosition) AS ShiftedBack;
SELECT ProfileID, Flags
FROM #FeatureFlags
WHERE (Flags & @Mask) = @Mask
ORDER BY ProfileID;Compare the Older Bitwise Form
The older predicate uses Flags & 4 to read export. Compare the result with four, not with one. Bitwise AND retains the numeric weight of the selected position. GET_BIT instead returns the selected bit's binary state.
Bitwise OR enables a mask's positions. AND with the complemented mask clears them. Those expressions remain valid T-SQL. The named functions make single-position intent clearer, especially when someone reviewing the code does not carry powers of two around in their head.
POWER also produces a mask, but its input type affects the result type and overflow behavior. Convert and bound it deliberately. The following comparison uses a small supported offset and an explicit integer conversion. It illustrates the relationship without introducing an unbounded floating-point mask generator.
DECLARE @Position int = 2;
DECLARE @OldMask int = CONVERT(int, POWER(CONVERT(bigint, 2), @Position));
SELECT ProfileID, Flags,
CASE WHEN (Flags & @OldMask) = @OldMask THEN 1 ELSE 0 END AS OldExportCheck,
GET_BIT(Flags, @Position) AS NewExportCheck,
Flags | @OldMask AS ExportEnabledMask,
Flags & ~@OldMask AS ExportClearedMask
FROM #FeatureFlags
ORDER BY ProfileID;Keep BIT_COUNT and GET_BIT Queries Understandable
A predicate extracting a bit does not automatically provide a selective index access path. Inspect the actual plan for large tables and representative distributions. Most rows enabling one common flag can make a selective access strategy unnecessary anyway. Measure before adding computed expressions or extra indexes.
Validate offsets against the operand type and the documented map. Do not accept an arbitrary user-supplied position without bounds and authorization checks. Check unsupported combinations and stale flags when the application evolves. Removing a setting from the user interface does not remove its old stored bit.
Which requirement actually needs a packed representation? Compact interchange or a stable externally defined mask provides a reason. Habit alone is weaker evidence. The new functions improve readability, but they do not make every collection of settings belong in one integer.
When Separate Bit Columns Beat BIT_COUNT and Masks
For a few ordinary application settings, named bit columns can be easier to inspect and constrain. They also avoid requiring every query to know an offset map. A related permission table supports a growing, descriptive permission model better than an expanding undocumented mask.
Use packed masks when the contract benefits from them. Use BIT_COUNT for a population count and GET_BIT or SET_BIT for a clearly defined position. Keep the type, map, and allowed combinations explicit. That makes SQL Server's bit functions useful without turning everyday schema design into a decoding exercise.
Related reading on this blog: SQL SERVER 2022: GENERATE_SERIES Function and Bitwise Puzzle: SQL in Sixty Seconds 160.
A flags integer is not a permission model, it is a compact representation that still needs clear rules.
Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.
