MSSQL Server Geography类型单点点存储字节数及存储方案对比咨询

Great questions—let's break down the details you're curious about clearly.

1. Storage Size of a Single Geography Point in MSSQL Server #

The Geography type in MSSQL is a CLR-based spatial type, and its storage size varies based on whether you include extra values like elevation (Z) or measures (M):

• For a basic Point with only X and Y coordinates (no Z/M), the total storage size is 24 bytes. Here's the breakdown:
  • 4 bytes for the version number
  • 4 bytes for the SRID (Spatial Reference ID)
  • 1 byte for the type identifier (Point is assigned the value 1)
  • 8 bytes each for the X and Y coordinates (both stored as double-precision floats)
  • Small alignment overhead to round up to a 24-byte total
• Adding a Z value (elevation) adds an extra 8 bytes, bringing the total to 32 bytes.
• Including both Z and M values adds 16 bytes total, making the Point 40 bytes in size.

2. Storage Comparison: Geography vs. Two Float Columns #

Let's compare the raw storage footprint for large datasets of points:

• Two float columns: In MSSQL, a float (which maps to 64-bit double-precision floats) uses 8 bytes per column. Storing X and Y as separate float columns takes 16 bytes per point.
• Geography Point (XY only): As noted above, this takes 24 bytes per point—50% more storage than the two float columns.
• If you need Z/M values: The gap widens. A Geography Point with Z/M is 40 bytes, whereas adding two more float columns for Z/M would only take 32 bytes (still 8 bytes less than the Geography type).

Key Tradeoffs to Keep in Mind #

While Geography uses more storage, it offers critical benefits that often justify the extra space:

• Built-in spatial functions: You can directly calculate distances (STDistance), check if points fall within a polygon (STWithin), run spatial joins, and more—no custom math or logic required.
• Spatial indexing: MSSQL supports spatial indexes on Geography columns, which drastically speed up spatial queries on large datasets.
• Standardized data: The Geography type follows OGC (Open Geospatial Consortium) standards, making it easier to integrate with other spatial tools or systems.

If storage is your absolute top priority and you don't need any spatial functionality, two float columns are more efficient. But for most spatial use cases, the extra storage is well worth the convenience and performance gains of the Geography type.

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