时间戳

首页 > 解决方案 > 在 MSSQL 服务器地理类型中存储一个点需要多少字节

sql-server - 在 MSSQL 服务器地理类型中存储一个点需要多少字节

问题描述

我正在尝试比较将大量点存储为地理类型与表中两种浮点类型的相对大小。

标签： sql-serverbytestoragepointgeography

解决方案

我进行了测试，并在只有 Geogaphy 类型的表中添加了 100k 点。最后，整个未索引的表有 3.6 MB 大，因此每行占用大约 36 个字节。将此与占用 8 个字节的 2 个浮点数进行比较。

代码：

CREATE TABLE Geog (  XY Geography)

DECLARE @i int  = 0; 
WHILE @i < 100000
BEGIN
  INSERT INTO Geog (XY) VALUES ( geography::STGeomFromText('POINT(-122.35900 47.65129)', 4326)) 

  SET @i = @i + 1; 
END

要查看表格大小：

SELECT 
    t.NAME AS TableName,
    s.Name AS SchemaName,
    p.rows AS RowCounts,
    SUM(a.total_pages) * 8 AS TotalSpaceKB, 
    CAST(ROUND(((SUM(a.total_pages) * 8) / 1024.00), 2) AS NUMERIC(36, 2)) AS TotalSpaceMB,
    SUM(a.used_pages) * 8 AS UsedSpaceKB, 
    CAST(ROUND(((SUM(a.used_pages) * 8) / 1024.00), 2) AS NUMERIC(36, 2)) AS UsedSpaceMB, 
    (SUM(a.total_pages) - SUM(a.used_pages)) * 8 AS UnusedSpaceKB,
    CAST(ROUND(((SUM(a.total_pages) - SUM(a.used_pages)) * 8) / 1024.00, 2) AS NUMERIC(36, 2)) AS UnusedSpaceMB
FROM 
    sys.tables t
INNER JOIN      
    sys.indexes i ON t.OBJECT_ID = i.object_id
INNER JOIN 
    sys.partitions p ON i.object_id = p.OBJECT_ID AND i.index_id = p.index_id
INNER JOIN 
    sys.allocation_units a ON p.partition_id = a.container_id
LEFT OUTER JOIN 
    sys.schemas s ON t.schema_id = s.schema_id
WHERE 
    t.NAME = 'Geog'    
GROUP BY 
    t.Name, s.Name, p.Rows

推荐阅读