SQL Server 2014中sp_table_privileges的IS_GRANTABLE为何为VARCHAR(3)？
First off, you’re totally right—from a pure SQL Server-native standpoint, using a bit type for a yes/no flag like IS_GRANTABLE makes perfect sense. So why does this stored procedure stick with VARCHAR(3)? It all boils down to compatibility with industry standards and the layered design of system objects.
1. Compliance with ODBC Standards
The sp_table_privileges stored procedure is part of SQL Server’s support for ODBC (Open Database Connectivity) standard APIs. The ODBC specification explicitly defines the IS_GRANTABLE column as a character type that returns either 'YES' or 'NO' (or NULL in some edge cases).
SQL Server maintains this compatibility to ensure applications built on standard ODBC calls don’t break when interacting with SQL Server. Switching to a bit type would be a breaking change for countless existing systems expecting string values here, which is a risk the product team avoids.
2. Inheritance from Underlying System Views
As you discovered, sp_table_privileges doesn’t generate results from scratch—it pulls data directly from system views (like sys.sptableprivileges in older versions). These views are designed to align with the same ODBC standards, so their IS_GRANTABLE column is defined as VARCHAR(3) to match the expected output format.
The stored procedure acts as a thin wrapper around these views, so it inherits the column data type without modification. This keeps the system object hierarchy consistent and avoids unnecessary type conversions that could impact performance.
A Quick Verification
If you query the underlying view directly, you’ll confirm the type matches:
SELECT IS_GRANTABLE, SQL_VARIANT_PROPERTY(IS_GRANTABLE, 'BaseType') AS DataType FROM sys.sptableprivileges;
This will return varchar as the data type, proving the stored procedure’s output is directly inherited from the view.
At the end of the day, while bit is more efficient and "native" to SQL Server, the priority here is maintaining backward compatibility and adherence to widely used database standards.
内容的提问来源于stack exchange，提问作者kendowtl
