locked
Geometry column size? RRS feed

  • Question

  • I have a table that contains a geometry column (Position).  I would like to know how much space is be consumed by values in this column.

    I tried using DATALENGTH against the column like so:

    SELECT SUM(DATALENGTH(Position)) / 1048576 as PositionSizeMB FROM P203

    Unfortunately this results in an arithmetic overflow error.  How do I calculate the spaced used by a geometric type column?

     

     

     

    Monday, November 8, 2010 11:22 PM

Answers

  • Hi,

    The DATALENGTH will return a int data type value if the column data type is geometry as you can see in http://msdn.microsoft.com/en-us/library/ms173486.aspx. However, the sum of all column's DATALENGTH value may result overflow, you may cast each int DATALENGTH value to bigint or other larger data type and then sum them.

    SELECT SUM(CAST(DATALENGTH(Position) AS BIGINT))/1048576 AS PositionSizeMB FROM P203
    

    Hope this helps.

    Thanks,
    Chunsong


    Please remember to click "Mark as Answer" on the post that helps you, and to click "Unmark as Answer" if a marked post does not actually answer your question. This can be beneficial to other community members reading the thread.
    Tuesday, November 9, 2010 6:24 AM