Title
Optimize GUID Storage in SQL Server for OutSystems Entities
Problem
In many OutSystems projects, developers use GUIDs to expose entity identifiers externally instead of exposing the internal Integer IDs. This is a common approach when identifiers need to be shared through APIs, URLs, or integrations.
However, when these GUIDs are stored in SQL Server as NVARCHAR(36), they consume up to 72 bytes per value, compared with just 16 bytes when stored using SQL Server's native UNIQUEIDENTIFIER data type.
This difference can have a significant impact on database storage and performance, particularly in applications with large volumes of data.
Impact
Storing GUIDs as NVARCHAR introduces several potential disadvantages:
Increased database storage: Each GUID value requires up to 72 bytes instead of 16 bytes, increasing the size of table data.
Larger indexes: Indexes containing these GUID columns require more storage, potentially increasing memory consumption and the number of pages SQL Server needs to read.
Increased I/O: Larger indexes and data structures can lead to additional page reads and greater I/O requirements for queries.
Reduced scalability: In tables containing millions of records, the additional storage can accumulate significantly, increasing database resource consumption and potentially infrastructure costs.
These effects can become more pronounced when GUIDs are used in foreign keys, frequently queried columns, or multiple indexes.
Proposed Improvement
Introduce support for storing GUID attributes using SQL Server's native UNIQUEIDENTIFIER data type instead of representing them as NVARCHAR(36).
Ideally, this capability would be supported natively by the OutSystems platform, allowing developers to benefit from SQL Server's more compact GUID representation without requiring custom database implementations or workarounds.
The solution should preserve the ability to expose GUIDs through APIs and integrations using the standard string representation, ensuring compatibility with existing application requirements.
Expected Benefits
Reduced database storage requirements.
Smaller indexes and potentially better memory efficiency.
Reduced I/O overhead for workloads involving GUID-based lookups and joins.
Improved scalability for applications with large datasets.
Potential reduction in database infrastructure costs.