SQL Server数据库设计：选用位掩码列还是多个bit类型列？

Hey Paul, let's walk through this decision based on your current SQL Server schema and typical usage with Visual Studio Community 2017. The choice between a bitmask column (like Visibility in UserSpecificTor1) and separate bit columns (like NotInteresting, Wrong, Seen in UserSpecificTor2) depends on your business logic, maintainability needs, and query patterns. Let's break down both options:

Pros #

• Storage efficiency: A single int column can hold multiple boolean states (each bit represents one state). For small numbers of states, this impact is negligible, but it scales better if you need to add more states later without altering the table schema.
• Concise multi-state filtering: You can use bitwise operators to filter multiple states at once. For example, to find all torrents marked either "Not interesting" or "Wrong", you'd write:

  SELECT * FROM UserSpecificTor1 WHERE Visibility & 0x03 != 0;
  

Cons #

• Poor readability: Raw hex values like 0x01 or 0x02 don't convey business meaning at a glance. New developers (or even you, months later) will need to cross-reference bit definitions to understand what each value represents.
• Index inefficiency: SQL Server struggles to optimize queries using bitwise operations on bitmask columns. If you frequently filter by individual states (e.g., "find all Wrong torrents"), indexes on a bitmask column won't perform as well as indexes on separate bit columns.
• Maintenance overhead: You have to strictly manage bit assignments to avoid conflicts. Adding a new state requires picking an unused bit (e.g., 0x04 for "Seen") and updating all related code/enums to reflect this.

Pros #

• Crystal clear semantics: Column names directly map to business logic—anyone looking at the UserSpecificTor2 table can immediately understand what each column does, no external references needed.
• Simpler, less error-prone queries: Filtering is straightforward. For example, to find torrents marked "Wrong" and "Seen", you'd write:

  SELECT * FROM UserSpecificTor2 WHERE Wrong = 1 AND Seen = 1;
  

• Better index performance: You can create indexes on individual bit columns (or composite indexes for common filter combinations) to speed up frequent queries. Even though bit columns have low cardinality, SQL Server can still optimize these indexes for small result sets.
• Easy scalability: Adding a new state (like "Favorite") just requires adding a new bit column to the table—no need to rework existing logic or worry about bit conflicts.

Cons #

• Marginal storage impact: Each bit column is packed into bytes (8 bits per byte), so 3 columns only take up 1 byte of storage—hardly a concern for most applications. Only if you end up with dozens of states would this become an issue, which doesn't seem to be your case.
• Slightly wider table: More columns mean a wider table schema, but this is a tradeoff for clarity and maintainability that's almost always worth it in small-to-medium scale applications.

Looking at your schema, UserSpecificTor2 already separates NotInteresting, Wrong, and Seen as independent states—suggesting these are not mutually exclusive (a user could mark a torrent as both "Wrong" and "Seen", for example). In this case, separate bit columns are the better choice for these reasons:

1. They align with your existing business logic where states are independent, not mutually exclusive.
2. Working with them in Visual Studio (whether writing raw SQL, LINQ queries, or entity models) will be more intuitive and less error-prone.
3. Maintenance is simpler—you won't have to manage bitmask definitions or debug confusing bitwise operations down the line.

If you do need to track mutually exclusive states (e.g., a torrent can only be "Neutral", "Not interesting", or "Wrong"), a bitmask could work—but even then, a tinyint column with a check constraint (or a lookup table) might be more readable than a bitmask.

内容的提问来源于stack exchange，提问作者Paul